March 23, 2012 at 5:37 am
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 IPCountries2
FROM 'c:\Temp\GeoIPCountryWhois.csv'
WITH
(
FIELDTERMINATOR = '","',
ROWTERMINATOR = '"'
)
GO
I 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.2553499596834996223NLNetherlands
With a format file:
BULK INSERT IPCountries2
FROM 'c:\Temp\GeoIPCountryWhois.csv'
WITH
(
FORMATFILE = 'C:\Temp\format.fmt'
)
GO
I get this data:
FromIPToIPBeginNumEndNumCountryCodeCountry
"1.0.0.01.0.0.25539049575646378860013546360643331765809AUAustralia
"1.0.1.01.0.3.25536184182373696937454121693301000058417CNChina
"1.0.4.01.0.7.25534734564296354176493690192165997590065AUAustralia
Note the BeginNum and EndNum seem to have been multiplied.
My format file looks like this:
10.0
6
1SQLCHAR050"\",\""1FromIP""
2SQLCHAR050"\",\""2ToIP""
3SQLBIGINT019"\",\""3BeginNum""
4SQLBIGINT019"\",\""4EndNum""
5SQLCHAR050"\",\""5CountryCode""
6SQLCHAR0250"\""6CountryName""
Finally, my table looks like so:
FromIPvarchar(50)
ToIPvarchar(50)
BeginNumbigint
EndNumbigint
CountryCodevarchar(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.
March 23, 2012 at 6:50 am
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.
March 23, 2012 at 8:54 am
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.0
7
1SQLCHAR01""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 Kingdom
Anyone encountered this before?
March 23, 2012 at 10:50 am
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.
March 26, 2012 at 1:56 am
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