SQL Server spatial query : import spatial information from Excel sheet

I have an Excel sheet containing geographic information about US counties as follows:

NAME, STATE, latitude, Longitude

I would like to import this information into a SQL Server database directly using data i开发者_如何学运维mport/export service.

However, I want to merge latitude, longitude columns from the Excel sheet and store the information in SQL Server as Geography or Geometry data type.

Could someone please help me with this issue?

The import/export wizard is difficult to work with and I don't think you can write a complicated query to transform the lat long to geography but you can try this from SQL management studio.

 SELECT * INTO Your_Table FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel 12.0;Database=C:\temp\testimportxlsx.xlsx',
 'SELECT ID, Lat, long, sp_geography = geography::STGeomFromText(''Point('' + CAST(Longitude AS VARCHAR(50)) 
 + '' '' + CAST(Latitude AS VARCHAR(50)) + '')'', 4326)  FROM [Sheet1$]')

This assumes your spreadsheet has the fields lat, long and ID. It writes a new field called sp_geography that is the transformed lat and long fields into a field of datatype geography.

You may have to download and install the ACE provider. If so search for the error message.