SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Need help loading a dat. file
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Lawtsm
Starting Member

1 Posts

Posted - 11/16/2012 :  10:38:46  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 11/16/2012 :  13:36:33  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
2869 Posts

Posted - 11/16/2012 :  14:24:55  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.16 seconds. Powered By: Snitz Forums 2000