Can we import a textfile without delimiters and fixed length .........

[narenkv]

We have a text file in this format

20020101KARL KILD 30 102 FLOOR STREET,CALAGRY US
00001 TAXDEPT 00001 34 XXXXXXXXXXXXXXXXXXXXXXXXXXX
20020111JOSEPH LIL 30 104 FLOOR STREET,NEW YORKUS
00001 TAXDEPT 00001 34 XXXXXXXXXXXXXXXXXXXXXXXXXXX

As you see here the textfile which i need to be imported is in not having a delimiter nor having fixed length.
Note: This text file contains information more than of one tables where fields are unevenly distributed among the textfile

But I know from where the fields start and end

Example :

1st and the 3rd line contains date (8 char),name (12 char),Record type(2 char),address (28 char)
2nd and 4th record contains employee number(5 char), Deptname (8 char),deptcode (6 char),Record type(2 char),
other information (25 char)

Can I write an Active Script in DTS where I can read line by line from the text file and update it to different tables

Thanks
NAREN

 

[nigelrivett]

See

http://www.nigelrivett.net/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.

 

[narenkv]

Hi nigelrivett

But how do you take care in Active Script as information is spread acros the group of rows .

My problem is I need import this text file to different tables . And very important is each line does not go to indivisual table.

i.e one table record to be inserted would contain information from the first as well as informaton from the second record

Thanks
NAREN

 

[nigelrivett]

Import into a staging table with an identity column and use an SP to insert from there.

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