Post reply

Importing File in SQL Server (Having Header and Footer and no delimiters)

  • October 10, 2019 at 1:35 pm

    #3687729

    Dears,

    I have a flat file exported from an application which has no delimiters and have header and footer as well. Following is the screenshot of file being shown in Textpad (Not opens in the same format in notepad).

    How we could import such problematic flat file into a new SQL DB table. Sample date added in Excel and notepad file as well. Copying data from Excel to Textpad will provide same format mentioned in provided image.

     

    Format View in Textpad

     

    Attachments:
    You must be logged in to view attached files.

  • October 10, 2019 at 2:35 pm

    #3687751

    You can define the Flat File Source to have a fixed width Input rather than delimited Rows

  • October 10, 2019 at 3:25 pm

    #3687766

    I have upload such similar file with Ragged Right option and setting fixed width. Problem is with lines in the start and end and report description mentioned in the top 3 rows. Import wizard generates error for the last column.

  • October 10, 2019 at 6:36 pm

    #3687825

    not a problematic file at all - fixed positions for all columns so its easy to process.

     

    Do NOT use the import wizard - you need to manually process the file (SSIS can be hard, easier in T-SQL)

    So define the file as a single column with the max size of each row and load onto a staging table.

    from the staging table you select all rows that are

    • Not empty
    • are not like '======='
    • do not have the particular header strings on the exact positions they are on

    the remaining rows you can use substring and cast to convert to the individual columns as desired and insert into your final main table (or do further process with the data)

     

  • October 10, 2019 at 6:38 pm

    #3687827

    A basic outline for this:

    1. File Connection Manager - set to a single column for the maximum width of the file.
    2. Connect to a Script Component.  In the script component, define the output columns you need - in the script you process each line and determine whether or not that line get processed.  If it gets processed add a line to the output buffer and parse the data into each output column for the buffer.
    3. Process Header/Trailer records - if needed as separate outputs

     

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

Viewing 5 posts - 1 through 4 (of 4 total)

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