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.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Need help loading a dat. file

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 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

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.
Go to Top of Page

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.

Jim

Everyday I learn something that somebody else already knew
Go to Top of Page
   

- Advertisement -