DTS importing text file to table

Hello,

I am new to DTS and have been able to generate a package that will import the data that was unstructured from a text file into a table in one column.

In the format:

media_id = BEX300
density = dlt3 (21)
allocated = 06/24/2005 00:54:32 (1119570872)
last_written = 07/03/2005 23:37:48 (1120430268)
expiration = 08/01/2008 23:00:00 (1217628000)
last_read = N.A (0)
kbytes = 285083765
retention_level = 0
status = 0x8
vmpool = 35
res1 = 0

media_id = BEX308 ----and so on

how would I import the data after the = into a table which has been created with the headings infront of the =

please help cant figure it out been working with SQL satements but limited knowledge and success

Can't load that file directly, need do some transformation work with activex or so.

is there no way as the data is in a table called IT, can use a sql statement in the DTS package to take out after the = into specific columns in another table, which are the same names as the ones before the =

Possible but will not be efficient, I rather to find way to create structured data file.

I would create some sort of intelligent grep statement that strips out all the of the stuff before the = sign to the carriage return and inclide the carriage return and replace it with a comma. this would turn it into a CSV file that you could use. Assuming that all records had the same amout of fields all of the time. This would allow you to import it using DTS.

If, as you say, you already have the data in a one-column table, you can use this UPDATE statement to eliminate the text before the equals sign:

UPDATE YourTable
SET YourColumn = SUBSTRING(YourColumn, CHARINDEX('=', YourColumn) +2, LEN(YourColumn))