0

I'm trying to import geography data from a CSV file in to SQL server using the bcp (bulk copy tool) but I can't figure out the format. I would like to use something like this in the CSV files but I get errors on the POINT column:

101932694,POINT(44.0586891174316 -69.2067337036133 4326),2014-07-30,24452353

How can I format geography data in the CSV file so bcp will accept it?

Rahul Tripathi
  • 168,305
  • 31
  • 280
  • 331
user1346563
  • 65
  • 7

2 Answers2

1

OK, it could not find any information on importing human readable geography data using bcp, so I converted the point data:

POINT (44.058689117431641 -69.206733703613281 4326),...

To the binary form:

E6100000010D000000203B4D51C000000020830746400000000000E6B040,...

And put the binary form in my CSV file. bcp seemed to work fine with that.

user1346563
  • 65
  • 7
0

I don't know anything about BCP, but hopefully this will give you what you need.

Try importing your data to a temporary table as a string, then importing it into the real table with an update.

Inside of SQL Server spatial data is stored like a varbinary, i.e. your point will be stored as 

0xE6100000010C010000203B4D51C0FAFFFF1F83074640

If you try and move the data with SSIS, it actually treats it as varbinary, not as spatial. Because of this, a conversion is required to get your string into the correct format.

SELECT GEOGRAPHY::STGeomFromText('POINT(44.0586891174316 -69.2067337036133)', 4326)
or
SELECT GEOGRAPHY::Point(-69.2067337036133,44.0586891174316,4326)

For those reasons, I am guessing BCP does not implicitly recognize the conversion, and you will probably need to do it manually.

CSV => Temp Table =>

INSERT INTO RealTable (GeogColumn) SELECT GEOGRAPHY::STGeomFromText(GeogString,4326) FROM TempTable
hcaelxxam
  • 616
  • 3
  • 14
  • OK. I found a reference to the binary format. I think I will try converting my text data "point(x y)" to the binary format and putting that in to the CSV file. If that does not work, I'll try hcaelxxam suggestion. – user1346563 Sep 16 '16 at 16:02