Hi, I pasted the first row of a dat. file onto here. The spaces between columns in the date file are varying.
20110201270 000000056BACON MARION 37013 550 F194604021998111920000310180200402270003335.35N0000000000.00721.00CLOSED NN642001050720010507
I have been unable to load this dat. file onto sql. I tried using bulk insert (with \t as the fieldterminator) but have been getting errors. My guess is I need a format file? Please advice. Really appreciate any help. Thanks
If space is the delimiter, using \t as field terminator obviously wouldn't work.
If space is the field terminator and the number of spaces vary unpredicatably, I am not sure that using a format file would help.
One option would be to bulk insert into a staging table with a single column, then use SQL replace command to replace multiple spaces with a single space, and then split the string using space as delimiter. There are splitting functions available for example here: http://www.sqlservercentral.com/articles/Tally+Table/72993/
It may also be possible to clean the data of spurious spaces in an SSIS task.
Need help loading a dat. file
Posted - 11/16/2012 : 10:38:46
