Results 1 to 6 of 6

Thread: DTS importing text file to table

  1. #1
    Join Date
    Mar 2007
    Posts
    2

    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

  2. #2
    Join Date
    Sep 2002
    Posts
    5,938
    Can't load that file directly, need do some transformation work with activex or so.

  3. #3
    Join Date
    Mar 2007
    Posts
    2
    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 =

  4. #4
    Join Date
    Sep 2002
    Posts
    5,938
    Possible but will not be efficient, I rather to find way to create structured data file.

  5. #5
    Join Date
    Mar 2007
    Posts
    3

    HMm

    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.

  6. #6
    Join Date
    Dec 2004
    Posts
    502
    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))

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •