Importing Loads of Fixedwidth flatfiles

  • stiej1977

    SSC Eights!

    Points: 952

    hi

    i've got a big bundle of fixedwidth flatfiles to import.

    i have the supplier documentation to show me where the columns in each file start/end.

    but, there's too many columns and too many files to go through to set up the data flow flat file source connections manually.

    as i have the documentation for the file structure for each file, is there a way for SSIS to read some form of format file built from that that shows SSIS where columns start/end for any given file? (i've looked at bcp format files, but it seems they don't specify startend positions for columns and to create the format file i need to specify the field terminator, something not present in these fixedwidth files)

    i won't be able to get the files sent over in delimited form so all hints/tips/help greatly appreciated.

    thanks

  • Phil Parkin

    SSC Guru

    Points: 244656

    Natively, I can't think of anything that will help you much.

    Cozyroc have a dynamic dataflow component which may be of interest (I have not used it).

    Presumably, the target table (or tables) are already defined and created?

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • stiej1977

    SSC Eights!

    Points: 952

    thanks. we have cozyroc, i'll take a look as to how much better they could be.

    No, the target tables are not yet defined/created either.

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 720436

  • Jeff Moden

    SSC Guru

    Points: 996830

    stiej1977 - Wednesday, February 21, 2018 8:44 AM

    hi

    i've got a big bundle of fixedwidth flatfiles to import.

    i have the supplier documentation to show me where the columns in each file start/end.

    but, there's too many columns and too many files to go through to set up the data flow flat file source connections manually.

    as i have the documentation for the file structure for each file, is there a way for SSIS to read some form of format file built from that that shows SSIS where columns start/end for any given file? (i've looked at bcp format files, but it seems they don't specify startend positions for columns and to create the format file i need to specify the field terminator, something not present in these fixedwidth files)

    i won't be able to get the files sent over in delimited form so all hints/tips/help greatly appreciated.

    thanks

    You have supplier documentation for the record layout of each file.  This could be pretty easy... what electronic form is the documentation in and can you attach one such document to a post so I could have a look?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • ZZartin

    SSC-Dedicated

    Points: 30414

    stiej1977 - Wednesday, February 21, 2018 8:44 AM

    hi

    i've got a big bundle of fixedwidth flatfiles to import.

    i have the supplier documentation to show me where the columns in each file start/end.

    but, there's too many columns and too many files to go through to set up the data flow flat file source connections manually.

    as i have the documentation for the file structure for each file, is there a way for SSIS to read some form of format file built from that that shows SSIS where columns start/end for any given file? (i've looked at bcp format files, but it seems they don't specify startend positions for columns and to create the format file i need to specify the field terminator, something not present in these fixedwidth files)

    i won't be able to get the files sent over in delimited form so all hints/tips/help greatly appreciated.

    thanks

    BCP can import fixed width files, if there's too many columns to set up by hand you can write a script the generate the format file for you.  Here's a simple format file for 3 fixed width columns.

    11.0
    3
    1   SQLCHAR     0   10  ""  1  COL_ONE      SQL_Latin1_General_CP1_CI_AS
    2   SQLCHAR     0   5  ""  2  COL_TWO      SQL_Latin1_General_CP1_CI_AS
    3   SQLCHAR     0   10  "\r\n" 3  COL_THREE     SQL_Latin1_General_CP1_CI_AS

  • Jeff Moden

    SSC Guru

    Points: 996830

    ZZartin - Wednesday, February 21, 2018 12:49 PM

    stiej1977 - Wednesday, February 21, 2018 8:44 AM

    hi

    i've got a big bundle of fixedwidth flatfiles to import.

    i have the supplier documentation to show me where the columns in each file start/end.

    but, there's too many columns and too many files to go through to set up the data flow flat file source connections manually.

    as i have the documentation for the file structure for each file, is there a way for SSIS to read some form of format file built from that that shows SSIS where columns start/end for any given file? (i've looked at bcp format files, but it seems they don't specify startend positions for columns and to create the format file i need to specify the field terminator, something not present in these fixedwidth files)

    i won't be able to get the files sent over in delimited form so all hints/tips/help greatly appreciated.

    thanks

    BCP can import fixed width files, if there's too many columns to set up by hand you can write a script the generate the format file for you.  Here's a simple format file for 3 fixed width columns.

    11.0
    3
    1   SQLCHAR     0   10  ""  1  COL_ONE      SQL_Latin1_General_CP1_CI_AS
    2   SQLCHAR     0   5  ""  2  COL_TWO      SQL_Latin1_General_CP1_CI_AS
    3   SQLCHAR     0   10  "\r\n" 3  COL_THREE     SQL_Latin1_General_CP1_CI_AS

    +1000 to that.  That's also why I'm asking for one of the copies of the provider's documentation.  Chances are the width of the columns in the file aren't going to match the ultimate table or be of the correct datatype.  We can "scrape" the provider's documentation for all of this.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • stiej1977

    SSC Eights!

    Points: 952

    Thanks all. So a sample from the supplier documentation re the schema of one of the flatfiles...

    Name Start Size Description
    CS001 1 5 Identifier – CS001
    ORG 6 14 Company registration number
    NAME 20 255 Company name

    and they're all like that. there's 41 files, and quite a few have 60 odd columns. there's many smaller one too.

    No destination tables have been made yet, so they'd only reflect the column names in each flat file. And datatypes are likely to all be nvarchar(x) to start off with. x being the Size from the file description above.

  • Jeff Moden

    SSC Guru

    Points: 996830

    stiej1977 - Thursday, February 22, 2018 2:31 AM

    Thanks all. So a sample from the supplier documentation re the schema of one of the flatfiles...

    Name Start Size Description
    CS001 1 5 Identifier – CS001
    ORG 6 14 Company registration number
    NAME 20 255 Company name

    and they're all like that. there's 41 files, and quite a few have 60 odd columns. there's many smaller one too.

    No destination tables have been made yet, so they'd only reflect the column names in each flat file. And datatypes are likely to all be nvarchar(x) to start off with. x being the Size from the file description above.

    That's perfect and makes life easy.  Copy that into a spreadsheet and write a formula to create column information for a CREATE TABLE statement then use the resulting table to create a BCP format file.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • David Burrows

    SSC Guru

    Points: 64736

    Jeff Moden - Wednesday, February 21, 2018 2:29 PM

    ZZartin - Wednesday, February 21, 2018 12:49 PM

    stiej1977 - Wednesday, February 21, 2018 8:44 AM

    hi

    i've got a big bundle of fixedwidth flatfiles to import.

    i have the supplier documentation to show me where the columns in each file start/end.

    but, there's too many columns and too many files to go through to set up the data flow flat file source connections manually.

    as i have the documentation for the file structure for each file, is there a way for SSIS to read some form of format file built from that that shows SSIS where columns start/end for any given file? (i've looked at bcp format files, but it seems they don't specify startend positions for columns and to create the format file i need to specify the field terminator, something not present in these fixedwidth files)

    i won't be able to get the files sent over in delimited form so all hints/tips/help greatly appreciated.

    thanks

    BCP can import fixed width files, if there's too many columns to set up by hand you can write a script the generate the format file for you.  Here's a simple format file for 3 fixed width columns.

    11.0
    3
    1   SQLCHAR     0   10  ""  1  COL_ONE      SQL_Latin1_General_CP1_CI_AS
    2   SQLCHAR     0   5  ""  2  COL_TWO      SQL_Latin1_General_CP1_CI_AS
    3   SQLCHAR     0   10  "\r\n" 3  COL_THREE     SQL_Latin1_General_CP1_CI_AS

    +1000 to that.  That's also why I'm asking for one of the copies of the provider's documentation.  Chances are the width of the columns in the file aren't going to match the ultimate table or be of the correct datatype.  We can "scrape" the provider's documentation for all of this.

    If in electronic form then agree 100%
    Any width difference only matters if the target width is smaller, data types would be the issue as BCP can be temperamental with data conversion (even with date/datetime)
    If there are lot of columns then I use BCP OUT to generate a format file, a few global edits to standardise the format and then change each column as required.

    Far away is close at hand in the images of elsewhere.
    Anon.

  • David Burrows

    SSC Guru

    Points: 64736

    Jeff Moden - Thursday, February 22, 2018 6:46 AM

    stiej1977 - Thursday, February 22, 2018 2:31 AM

    Thanks all. So a sample from the supplier documentation re the schema of one of the flatfiles...

    Name Start Size Description
    CS001 1 5 Identifier – CS001
    ORG 6 14 Company registration number
    NAME 20 255 Company name

    and they're all like that. there's 41 files, and quite a few have 60 odd columns. there's many smaller one too.

    No destination tables have been made yet, so they'd only reflect the column names in each flat file. And datatypes are likely to all be nvarchar(x) to start off with. x being the Size from the file description above.

    That's perfect and makes life easy.  Copy that into a spreadsheet and write a formula to create column information for a CREATE TABLE statement then use the resulting table to create a BCP format file.

    +100%

    Far away is close at hand in the images of elsewhere.
    Anon.

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

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