Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2004
    Location
    Worland, WY
    Posts
    1

    Unanswered: Import Multi-Row Record Text File with DTS

    I have a text file I need to import into a SQL Server table. Each record spans several lines. Does anyone have a vbscript routine that wil go thru this text file and put each record on one row? Once the records are one row, DTS will easily handle the import. Or is there a better way?

    Sample multi-row records:

    WRLDWYXCDS1 ALT101 APR04 21:30:24 6879 FAIL ALT
    HOST 00 0 08 00 DN 3073477171 1st CYCLE
    TEST TYPE CKTTST DIAGNOSTIC RESULT BIC/EBS LC TRBL:PTRN 000E S= 1 R= 0
    ACTION REQUIRED Replace Card CARD TYPE 6X21AC

    WRLDWYXCDS1 ALT101 APR04 22:31:37 7672 FAIL ALT
    HOST 00 0 08 00 DN 3073477171 1st CYCLE
    TEST TYPE CKTTST DIAGNOSTIC RESULT BIC/EBS LC TRBL:PTRN 000E S= 1 R= 0
    ACTION REQUIRED Replace Card CARD TYPE 6X21AC
    Last edited by eoffshore; 04-09-04 at 17:52.

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    DTS questions don't get a lot of answers on this forum. Perhaps most developers feel as I do, that DTS is a kluge solution of several different poorly integrated technologies. If you are lucky, my posting this reply will shoot your post back to the top of the list queue and somebody proficient in DTS will give you an answer.

    Otherwise, I use DTS only for what it is best at; transfering and importing raw data. I avoid using DTS for transforming data, prefering instead to load the data into staging tables in my database and then run stored procedures to transfer it to the main database schema. I think one of the big advantages of this is that you separate the import process from the transform process, allowing you to flag and retain records that fail your business rules.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734

    Re: Import Multi-Row Record Text File with DTS

    Originally posted by eoffshore
    I have a text file I need to import into a SQL Server table. Each record spans several lines. Does anyone have a vbscript routine that wil go thru this text file and put each record on one row? Once the records are one row, DTS will easily handle the import. Or is there a better way?

    Sample multi-row records:

    WRLDWYXCDS1 ALT101 APR04 21:30:24 6879 FAIL ALT
    HOST 00 0 08 00 DN 3073477171 1st CYCLE
    TEST TYPE CKTTST DIAGNOSTIC RESULT BIC/EBS LC TRBL:PTRN 000E S= 1 R= 0
    ACTION REQUIRED Replace Card CARD TYPE 6X21AC

    WRLDWYXCDS1 ALT101 APR04 22:31:37 7672 FAIL ALT
    HOST 00 0 08 00 DN 3073477171 1st CYCLE
    TEST TYPE CKTTST DIAGNOSTIC RESULT BIC/EBS LC TRBL:PTRN 000E S= 1 R= 0
    ACTION REQUIRED Replace Card CARD TYPE 6X21AC
    You don't need VB for this. It would be faster to create a staging table with one column. You want the column to be a char so you don't lose your fixed length. Have a select statement push it into a second staging table with char, but big enough for all four rows.

    Then, you can either use a final parsing statement to divide it up, or export to a fixed length text file. It should be quicker to do it this way in sets then to parse through in VB where you'll have to parse through each line and length section.
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    In your case it looks like you have records with five elements, where each element is a separate line in your raw data.

    One solution to this is to import each record into a staging table as a single string of characters. A possible layout for your staging table would be:

    RecordID (Identity)
    GroupID (Int)
    RecordType (Int)
    RecordString (Varchar(500))
    ImportErrors (Varchar(100))

    As the data is imported into the staging table a RecordID is created to maintain the order.

    Next you run a query(s) against your table to identify the RecordType for each record. For instance, maybe all the type 2 records start with 'HOST'

    Lastly, group the records like this:

    Update StagingTable
    set GroupID = SubTable.GroupID
    from StagingTable
    inner join
    (select StagingTable.RecordID, Min(GroupRecords.RecordID) GroupID
    from StagingTable
    inner join StagingTable GroupRecords
    on StagingTable.RecordID >= GroupRecords.RecordID
    and GroupRecords.RecordType = 1) SubTable
    on StagingTable.RecordID = SubTable.RecordID

    Now you have identified your imported records and their relationships to eachother, and you can load them into your schema. It's not neat or pretty, but it works. For each step you can flag problems by appending an error message to the ImportErrors field.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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