Post reply

Multi Format Text File

  • April 5, 2007 at 5:23 pm

    #153512

    Hello. I am in the process of migrating an old app to SQL Server. The old app reads hundreds of different flat file formats. One of the more complex ones is a multi-format delimited file. For example:

    01^Bob Johnson^123 Main St^Anytown^St

    02^Book1^$20

    02^Book2^$30

    03^Gift Cert^Happy Birthday^$100

    This file is delimited with the ^ character. Note that the first 2 characters identify the row type. All 01 rows have data in the format: Name, Street Address, City, State. All 02 have data in the format: Book name, price. Etc.

    Any clever ideas on how to parse this? I tried setting it up as a flat file source with the ^ delimiter. It doesn't work - in this example it wraps the third row to the end of the second row and keeps adding columns to fill out the row.

    The only option that I can think of is to pull the entire row into one long column, and then use a script component to manually substring each column out.

    Any help would be greatly appreciated.

    Thanks,

    Chris

  • April 5, 2007 at 9:24 pm

    #698967

    You can import the data to sql server in to 1 big VARCHAR field then use REPLACE to replace the ^ with something useful like a comma. Then Export the data using the LIKE operator to put the different files in to seperate test files. The you should be able to import your tables in to a table with more specific fields.

    Example:

    CREATE TABLE #Temp1

    (Field1 VARCHAR (1000))

    Use DTS or whatever to import the data. Each row gets its own row.

    UPDATE #Temp1

    SET Field1 = REPLACE(Field1, '^', ',')

    <----You may want to double check the syntax for REPLACE.

    SELECT Field1 FROM #Temp1 WHERE Field1 LIKE '01%'

    SELECT Field1 FROM #Temp1 WHERE Field1 LIKE '02%'

    SELECT Field1 FROM #Temp1 WHERE Field1 LIKE '03%'

    Run one at a time and export your results to a text file.

    OR

    Just use DTS to pull in the file use the ^ as the seperator in to a table that has generic fields for as many fields are needed for the longest row. Then use the select to pull each row and insert in to a more useful table.

    I prefer just using DTS as I stated 2nd but it is up to you. both ideas shoudl work just fine.

    NOTE: if you use DTS it would be easier to automate the process for later use

    Hope I said something useful

    Cheers,

     

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply