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_code nvarchar(15)
type nchar(1),
city nvarchar(50),
city_type nchar(1),
state nvarchar(75),
state_code nchar(2),
area_code nchar(3),
timezone nvarchar(15),
GMT_Offset integer,
dst nchar(1),
latitude float,
longitude float
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
1 SQLNCHAR 0 0 "," 2 postal_code SQL_Latin1_General_Cp437_BIN
2 SQLNCHAR 0 0 "," 3 type SQL_Latin1_General_Cp437_BIN
3 SQLNCHAR 0 0 "," 4 city SQL_Latin1_General_Cp437_BIN
4 SQLNCHAR 0 0 "," 5 City_type SQL_Latin1_General_Cp437_BIN
5 SQLNCHAR 0 0 "," 8 state SQL_Latin1_General_Cp437_BIN
6 SQLNCHAR 0 0 "," 9 state_code SQL_Latin1_General_Cp437_BIN
7 SQLNCHAR 0 0 "," 12 area_code SQL_Latin1_General_Cp437_BIN
8 SQLNCHAR 0 0 "," 13 timezone SQL_Latin1_General_Cp437_BIN
9 SQLNCHAR 0 0 "," 14 gmt_offset SQL_Latin1_General_Cp437_BIN
10 SQLNCHAR 0 0 "," 15 dst SQL_Latin1_General_Cp437_BIN
11 SQLNCHAR 0 0 "," 16 latitude SQL_Latin1_General_Cp437_BIN
12 SQLNCHAR 0 0 "\r" 17 longitude SQL_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