April 28, 2010 at 8:29 am
Hello, I have a file that contains character, date and decimal data that I need loaded into a table. The file layout is different than the table so some mapping needs to be done.
This will run on SQL Server 2005 and 2008.
Here is what I have:
Table layout:
postal_codenvarchar(15)
typenchar(1),
citynvarchar(50),
city_typenchar(1),
statenvarchar(75),
state_codenchar(2),
area_codenchar(3),
timezonenvarchar(15),
GMT_Offsetinteger,
dstnchar(1),
latitudefloat,
longitudefloat
Data:
CountryName,PostalCode,PostalType,CityName,CityType,CountyName,CountyFIPS,ProvinceName,ProvinceAbbr,StateFIPS,MSACode,AreaCode,TimeZone,UTC,DST,Latitude,Longitude
USA,10121 ,S,Manhattan,N,New York,36061,New York,NY,36,5600,212/646/917,Eastern,-5.0,Y,40.750095,-73.998867
USA,10121 ,S,New York,D,New York,36061,New York,NY,36,5600,212/646/917,Eastern,-5.0,Y,40.750095,-73.998867
USA,10121 ,S,Nyc,N,New York,36061,New York,NY,36,5600,212/646/917,Eastern,-5.0,Y,40.750095,-73.998867
USA,33155 ,S,Miami,D,Miami-Dade,12086,Florida,FL,12,5000,305/786,Eastern,-5.0,Y,25.737315,-80.309137
USA,33155 ,S,South Miami,N,Miami-Dade,12086,Florida,FL,12,5000,305/786,Eastern,-5.0,Y,25.737315,-80.309137
Format File:
9.0
12
1SQLNCHAR00","2postal_code SQL_Latin1_General_Cp437_BIN
2SQLNCHAR00","3type SQL_Latin1_General_Cp437_BIN
3SQLNCHAR00","4city SQL_Latin1_General_Cp437_BIN
4SQLNCHAR00","5City_typeSQL_Latin1_General_Cp437_BIN
5SQLNCHAR00","8stateSQL_Latin1_General_Cp437_BIN
6 SQLNCHAR00","9state_codeSQL_Latin1_General_Cp437_BIN
7 SQLNCHAR00","12area_codeSQL_Latin1_General_Cp437_BIN
8 SQLNCHAR00","13timezoneSQL_Latin1_General_Cp437_BIN
9 SQLNCHAR00","14gmt_offsetSQL_Latin1_General_Cp437_BIN
10 SQLNCHAR00","15dstSQL_Latin1_General_Cp437_BIN
11 SQLNCHAR00","16latitudeSQL_Latin1_General_Cp437_BIN
12 SQLNCHAR00"\r"17longitudeSQL_Latin1_General_Cp437_BIN
When I run this I get the following error message:
Msg 4862, Level 16, State 1, Server BASEES261SQL2K8, Line 1
Cannot bulk load because the file "C:\DBA\PostalCode\SQLServer\LoadPostalCode1.fmt" could not be read. Operating system error code (null).
What datatype(SQLNCHAR...) shouold be used for the non character columns and what length should be specified in the format file? How can I get this to load?
Thank you for any assistance.
Best Regards,
~David
April 28, 2010 at 9:19 am
i don't really have an answer for you but I have to solutions for you to figure out what the issue is.
1) Use the import task from SQl and have SQL create your table just as a test. import into that. This will make sure that your file is actually set up the way you think it is. If the data gets in and everything looks good then
2) Use your bulk statement to go into the table that SQL set up. If that works start to change the datatypes SQL defaulted to what they should be ie decimal and such. that will tell you where you data is jacked up in the fmt. From there you can mess with the "With" block in Bulk insert to get it to work.
April 28, 2010 at 12:10 pm
I now have this working :-). What I did was use the BCP command to create the correct BCP format file. Here is the command that I used:
bcp <myDB>.<mySchema>.<myTable> format nul -c -t, -f LoadTable.fmt -T
Best Regards,
~David
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply