How to Bulk Insert csv with double quotes around all values?

Question

I am trying to insert a .csv file into SQL Server 2008 R2.

The .csv is 300+MB from http://ipinfodb.com/ip_database.php Complete (City), 4.0M records.

Here're the top 5 lines, with 1st line = column headers:

"ip_start";"country_code";"country_name";"region_code";"region_name";"city";"zipcode";"latitude";"longitude";"metrocode"
"0";"RD";"Reserved";;;;;"0";"0";
"16777216";"AU";"Australia";;;;;"-27";"133";
"17367040";"MY";"Malaysia";;;;;"2.5";"112.5";
"17435136";"AU";"Australia";;;;;"-27";"133";

I tried Import and Export Data, and BULK INSERT, but haven't been able to import them correctly yet.

Shall I resort to use bcp? can it handle stripping the ""? how?

Thank you very much.

Answer 1

Is this a single import or are you wanting to schedule a recurring import? If this is a one-time task, you should be able to use the Import and Export Wizard. The text qualifier will be the quotation mark ("), be sure to select column names in the first data row, and you'll want to convey that the field delimiter is the semicolon (;).

I'm not certain the file is properly formatted - the last semicolon following each of the data rows might be a problem. If you hit any errors, simply add a new column header to the file.

EDIT: I just did a quick test, the semicolons at the end will be treated as part of the final value in that row. I would suggest adding a ;"tempheader" at the end of your header (first) row - that will cause SQL to treat the final semicolon as a delimiter and you can delete that extra column once the import is complete.

Answer 2

Your data looks pretty inconsistent since NULL values don't also carry a quotation enclosure.

I believe you can create a format file to customize to your particular csv file and its particular terminators in SQL SERVER.

See more here: http://lanestechblog.blogspot.com/2008/08/sql-server-bulk-insert-using-format.html

Answer 3

Got it, forgot to set Text Qualifier as " http://twitpic.com/2qh1c8