importing inconsistant text file

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 inconsistant text file

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-11-10 : 08:27:11

Elton writes "I think i'm driving myself mad trying to do this.
I am trying to import data from a text file into sql, sounds quite easy right. The following is sample data i want to import.

DPLRMain 672,'651018053','DD1000000729','PRICC','PRICC','19991111','1447','THEA,M CPT','','19991111','1447','THEA,M CPT','','','6510','19991111','','','','','U','19991111','','','DSP'
DPLRBill 672,'19991111','1','L','0.0002','NEW','1','5.50','1447','THEA,M CPT','','19991111','','','','DSP'
DPLRBill 672,'19991111','2','L','1000.0000','NEW','1','49.20','1447','THEA,M CPT','893','19991111','','','','DSP'
DPLRBill 672,'19991111','3','L','1205.0000','NEW','1','10.60','1447','THEA,M CPT','893','19991111','','','','DSP'
DPLRBill 672,'19991111','4','L','3182.0000','NEW','1','24.30','1447','THEA,M CPT','894','19991111','','','','DSP'
DPLRBillCode 672,'L0.0002','4543D0006510.~651018053,6610*PRICC','','','DSP'
DPLRBillCode 672,'L1000.0000','3755H0006502.~651018053,6610*PRICC','','','DSP'
DPLRBillCode 672,'L1205.0000','3797H0006502.~651018053,6610*PRICC','','','DSP'
DPLRBillCode 672,'L3182.0000','4001B0006501.~651018053,6610*PRICC','','','DSP'
DPLREEPAT 672,'','','','','','','','','','DSP'
DPLRLabOrdTest 672,'0.0002','1','1','5.50','','','','','','','','','DSP'
DPLRLabOrdTest 672,'1006.0000','1','1','','','','','893','','','','','DSP'
DPLRLabOrdTestComp 672,'1006.0000','1000.0000','1','49.20','','DSP'
DPLRLabOrdTestComp 672,'1006.0000','1205.0000','1','10.60','','DSP'
DPLRLabOrdTest 672,'3182.0000','1','1','24.30','','','','894','','','','','DSP'
DPLRLabSpec 672,'BA','1','894','','DSP'
DPLRLabSpec 672,'HA','1','893','','DSP'

What this basically means is that these records need to go to different tables. I have got two working ways of importing the data
1) I use a generic program that loops through a folder, picks up the files and then imports the data via a stored proc, everything starting with DPLR in the sample above are different procs. This works quite well, just extremely slow depending on the volume of data as it process one record at a time.
2)I wrote a little script that does exactly the same thing using dts. I sit with the same problem.

The other disadvantage is I have to many stored procs.

I am thinking of another way of doing this, have not yet had time to think it through or try it yet. I am thinking of using a temp table with maximum number of colums, then dumping all the data into that table, instead of having the proc name I'll replace it with a table name, then I'll have one proc that does a select insert or bulk insert for all the data with the same table. As I said before, have not really tried this as yet, am not even to sure even if it would work or not.

I am quite sure I'm not the first and surely not the last person to attempt this. So please if any one anywhere has any ideas on how I could go about this, I would really appreciate the help.

Thanking you in advance.
Elton"

robvolk
Most Valuable Yak

15732 Posts

Posted - 2004-11-10 : 08:40:34

You can modify a technique suggested here:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=41614

Use findstr on the file(s) to separate out the data that's distinct to each table and put it into a separate file. For example:

findstr "DPLRLabSpec" myFile.txt >DPLRLabSpec.txt
findstr "DPLRLabOrdTest" myFile.txt >DPLRLabOrdTest_All.txt
findstr/V "DPLRLabOrdTestComp" DPLRLabOrdTest_All.txt >DPLRLabOrdTest.txt
findstr "DPLRLabOrdTestComp" DPLRLabOrdTest_All.txt >DPLRLabOrdTestComp.txt

Examples 2-3 show how to handle the DPLRLabOrdTest rows, or any other type of dupe or similar values. Once all of these are separated into individual files, you can import each file according to their particular format. You can ignore the stored procedure entirely if the rest of the data can be inserted directly into a table. bcp and BULK INSERT can use a format file here to exclude or rearrange columns in the file. Books Online has more details on format files under "bcp".

You can still import them into one file and do some data matching to import them into their final destination. You may want to try both ways and see which works better for you.

nr
SQLTeam MVY

12543 Posts

Posted - 2004-11-10 : 13:58:26

Have a look at this.
http://www.nigelrivett.net/ImportTextFiles.html
and this
http://www.nigelrivett.net/s_ProcessAllFilesInDir.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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources