Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
Lawtsm
Starting Member
1 Post |
Posted - 2012-11-16 : 10:38:46
|
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 NN642001050720010507I 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 |
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-11-16 : 13:36:33
|
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. |
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2012-11-16 : 14:24:55
|
Can you change it from .DAT to .TXT? I've done that before and for some reason SQL had no problem loading the .txt file.JimEveryday I learn something that somebody else already knew |
|
|
|
|
|