Post reply

Importing Loads of Fixedwidth flatfiles

  • February 21, 2018 at 8:44 am

    #410258

    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

  • February 21, 2018 at 8:59 am

    #1980629

    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?

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • February 21, 2018 at 9:01 am

    #1980632

    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.

  • February 21, 2018 at 9:04 am

    #1980635

    Maybe Biml can help - http://www.sqlservercentral.com/stairway/100550/

  • February 21, 2018 at 12:29 pm

    #1980669

    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.

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

  • February 21, 2018 at 12:49 pm

    #1980672

    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

  • February 21, 2018 at 2:29 pm

    #1980690

    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.

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

  • February 22, 2018 at 2:31 am

    #1980731

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

    NameStartSizeDescription
    CS00115Identifier – CS001
    ORG614Company registration number
    NAME20255Company 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.

  • February 22, 2018 at 6:46 am

    #1980760

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

    NameStartSizeDescription
    CS00115Identifier – CS001
    ORG614Company registration number
    NAME20255Company 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.

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

  • February 27, 2018 at 6:58 am

    #1981277

    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.

  • February 27, 2018 at 6:59 am

    #1981278

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

    NameStartSizeDescription
    CS00115Identifier – CS001
    ORG614Company registration number
    NAME20255Company 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 10 (of 10 total)

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