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

  • Rehan Ahmad

    Mr or Mrs. 500

    Points: 565

    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.
  • DinoRS

    SSCrazy

    Points: 2513

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

  • Rehan Ahmad

    Mr or Mrs. 500

    Points: 565

    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.

  • frederico_fonseca

    SSChampion

    Points: 14172

    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)

     

  • Jeffrey Williams 3188

    SSC Guru

    Points: 88110

    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

     

    [font="Verdana"]Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster[/url]
    Managing Transaction Logs[/url]
    [/font]

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

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