Bulk Insert CSV with quotes
-
I am attempting to insert the following CSV from Maxmind containing information about IP addresses http://www.maxmind.com/app/geolitecountry (You can download the latest version yourself on that page about half way down). I have been 75% successful using a) field and row terminators and b) a format file.
A sample of the original data looks like:
"1.0.0.0","1.0.0.255","16777216","16777471","AU","Australia""1.0.1.0","1.0.3.255","16777472","16778239","CN","China""1.0.4.0","1.0.7.255","16778240","16779263","AU","Australia""1.0.8.0","1.0.15.255","16779264","16781311","CN","China"With the following insert statement:
BULK INSERT IPCountries2FROM 'c:\Temp\GeoIPCountryWhois.csv'WITH(FIELDTERMINATOR = '","',ROWTERMINATOR = '"')GOI have been able to get the following data inserted (Please note the space before each FromIP, the very first row (1.0.0.0) doesn't have this space but does still have a quote):
FromIPToIPBeginNumEndNumCountryCodeCountry"2.21.248.02.21.253.2553499417634995711FRFrance"2.21.254.02.21.254.2553499571234995967EUEurope"2.21.255.02.21.255.2553499596834996223NLNetherlandsWith a format file:
BULK INSERT IPCountries2FROM 'c:\Temp\GeoIPCountryWhois.csv'WITH(FORMATFILE = 'C:\Temp\format.fmt')GOI get this data:
FromIPToIPBeginNumEndNumCountryCodeCountry"1.0.0.01.0.0.25539049575646378860013546360643331765809AUAustralia"1.0.1.01.0.3.25536184182373696937454121693301000058417CNChina"1.0.4.01.0.7.25534734564296354176493690192165997590065AUAustraliaNote the BeginNum and EndNum seem to have been multiplied.
My format file looks like this:
10.061SQLCHAR050"\",\""1FromIP""2SQLCHAR050"\",\""2ToIP""3SQLBIGINT019"\",\""3BeginNum""4SQLBIGINT019"\",\""4EndNum""5SQLCHAR050"\",\""5CountryCode""6SQLCHAR0250"\""6CountryName""Finally, my table looks like so:
FromIPvarchar(50)ToIPvarchar(50)BeginNumbigintEndNumbigintCountryCodevarchar(50)CountryNamevarchar(250)Hopefully the question is clear, I would like the Maxmind data correctly inserted into my database, with all quotes removed, no spaces at the front etc. There is unfortunately no possibility for manually modifying the CSV file (even as simple as opening and re-saving in Excel which seems to remove the quotes) as it is planned as an automatically updating task.
Many thanks in advance for any help.
-
use a dummy column 0 to handle the leading ", something like this (this is off the top of my head so don't hold me to the EXACT syntax):
0 SQLCHAR 0 0 "\"" 0 ignore ""
1SQLCHAR050"\",\""1FromIP""
2SQLCHAR050"\",\""2ToIP""
3SQLBIGINT019"\",\""3BeginNum""
4SQLBIGINT019"\",\""4EndNum""
5SQLCHAR050"\",\""5CountryCode""
6SQLCHAR0250"\""6CountryName""
The probability of survival is inversely proportional to the angle of arrival.
-
Thanks for getting back. I added a duff column and modified my format file slightly with guidance from http://sqlblogcasts.com/blogs/tonyrogerson/archive/2008/10/04/bcp-bulk-insert-format-file-escape-a-quote-more-to-the-point-loading-quoted-csv.aspx
My format file now looks like this:
10.071SQLCHAR01""1DuffColumn""2SQLCHAR050"\",\""2FromIP""3SQLCHAR050"\",\""3ToIP""4SQLBIGINT019"\",\""4BeginNum""5SQLBIGINT019"\",\""5EndNum""6SQLCHAR050"\",\""6CountryCode""7SQLCHAR0250"\""7CountryName""The only issue I have left is the BeginNum and EndNum are still being multiplied. Some data as it looks when copied in:
DuffColumnFromIPToIPBeginNumEndNumCountryCodeCountry"2.21.246.02.21.246.25537622541323459103234121138026186880051ATAustria"2.21.247.02.21.247.25534729011548222392833834587708097377331DEGermany"2.21.248.02.21.253.25539066453021353052673544675083450135603FRFrance"2.21.254.02.21.254.25536167326774880635393978430221584512051EUEurope"2.21.255.02.21.255.25540504878156224399873689066253239530547NLNetherlands"2.22.0.02.22.11.25537611238472774584833835151779037262899EUEurope"2.22.12.02.22.19.25539072093730751908353689630281078158643GBUnited KingdomAnyone encountered this before?
-
don't use SQLBIGINT in the format file. Use SQLCHAR for all of the fields.
The probability of survival is inversely proportional to the angle of arrival.
-
That solved it! Thank you for all your help
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply