Importing text file into SQL table

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

Import/Export (DTS) and Replication (2000)

Importing text file into SQL table

Author

Topic

sahu74
Posting Yak Master

100 Posts

Posted - 2006-05-02 : 16:30:32

Hi,
I have a text file in the following format.


06921200003
06921200003         115555555555444455  1  11
06921200003         1155555555555455551    62
06921200003         115555555555555555    112
06921200001
06921200001         1154455555554445551    6*
06921200001         113333333333333333 1   62
06921200001         114545554444345555  1  11
06921200001         114545554435455545 1   12
06921200001         115455554444344443  11 *2
06921200001         1133455554343324431    62
06921200002
06921200002         115555555555555555  1  12
06921200002         115555555545555555   1 11
06921200002         115555555534344444 1   22
06921200002         115555555555555555  1  12
06921200002         115455554454544555 1   12

Please note that there is a [CR]/[LF] in some rows after the 11th Column (example: row 1, row 5 etc). Is there a way to import this into a SQL table excluding the lines which end with the 11th column or by substituting a null for the remaining columns where the row ends at the 11th column. Each number after the 11th Column need to go into a separate column.

Thank you for your help in advance.

PKS.

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2006-05-03 : 07:23:26

Bring it into a staging table as is, then create a stored proc to handle the final insert.

Mark

sahu74
Posting Yak Master

100 Posts

Posted - 2006-05-03 : 10:56:48

Can you please elaborate a little more on how to bring the data in the text file into a staging table? I mean the DTS task that I should use.
Once I am able to bring the data in each row into a single column, I think I will be able to massage the data and manage it.

Thanks again.
PKS.

nr
SQLTeam MVY

12543 Posts

Posted - 2006-05-03 : 12:42:36

It's a lot easier using bulk insert.
have a look at
http://www.nigelrivett.net/SQLTsql/ImportTextFiles.html

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

JohnnySQL
Starting Member

3 Posts

Posted - 2006-05-03 : 12:50:09

Just create a table to temporarily store the data.

Create table stage(varchar(5000)) --Length will be the length of your longest row.

In DTS just create a text file source then do not use a row delimeter. Make it fixed length and manually set your length. Next just create a data transformation task to your database.

Next just create an execute SQL task and select the data out of the table row by row and do your insert into your target table. I do something similiar to this except with BCP and without DTS, then just use SubString to parse the data. Having the CR/LF in the middle of your data makes it painfull.

Hope this helps.

sahu74
Posting Yak Master

100 Posts

Posted - 2006-05-03 : 16:51:38

Thank you nr. That worked just perfect.

Johnny, I am a little curious if your method will work. Since, all rows are not of same length, I was wondering if the fixed length option will work. Any comments or insight?

PKS.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources