Post reply

no. of columns in excel may vary

  • June 20, 2014 at 11:19 pm

    #290321

    Hi,

    I want to insert data periodically from an excel file into a table using a SSIS. But each time the no. of columns in excel may vary.

    Suppose, the 1st time we receive excel file may contain 10 columns the second time it may contains 12 columns etc.,

    Thanks in advance.

  • June 20, 2014 at 11:46 pm

    #1723746

    ykonline (6/20/2014)

    Hi,

    I want to insert data periodically from an excel file into a table using a SSIS. But each time the no. of columns in excel may vary.

    Suppose, the 1st time we receive excel file may contain 10 columns the second time it may contains 12 columns etc.,

    Thanks in advance.

    The simplest is to always import the maximum number of columns and ignore the null columns in the stage area. Other options are i.e. programmatically building the import, one package for each document type triggered from a master package which detects the type etc..

    😎

  • June 21, 2014 at 3:50 pm

    #1723811

    ykonline (6/20/2014)

    Hi,

    I want to insert data periodically from an excel file into a table using a SSIS. But each time the no. of columns in excel may vary.

    Suppose, the 1st time we receive excel file may contain 10 columns the second time it may contains 12 columns etc.,

    Thanks in advance.

    Does that also mean that the columns present in a 10 column spreadsheet could be in different positions for the 12 column spreadsheets?

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

  • June 21, 2014 at 3:57 pm

    #1723813

    Jeff Moden (6/21/2014)

    ykonline (6/20/2014)

    Hi,

    I want to insert data periodically from an excel file into a table using a SSIS. But each time the no. of columns in excel may vary.

    Suppose, the 1st time we receive excel file may contain 10 columns the second time it may contains 12 columns etc.,

    Thanks in advance.

    Does that also mean that the columns present in a 10 column spreadsheet could be in different positions for the 12 column spreadsheets?

    Missing here is the information on if and how one import relates to another. For what it's worth, those are, in my experience the first signs of a fully meta-data driven ETL requirements. And that is starting with a toothpick and ending up with a forest.

    😎

  • June 27, 2014 at 11:46 pm

    #1725631

    No, Columns positions will be same but we may receive new columns that

    may not be exists in previous sheet which we received.

    For Example,

    First time, we receive the Sheet with C1,C2,C3 columns.

    When we receive sheet for Second time, it may contains columns like C1,C2,C3,C4

    and for the third time we may receive with C1,C2,C3,C4,C5,C6 etc.,

    Thanks in advance

  • June 28, 2014 at 12:20 pm

    #1725677

    ykonline (6/27/2014)

    No, Columns positions will be same but we may receive new columns that

    may not be exists in previous sheet which we received.

    For Example,

    First time, we receive the Sheet with C1,C2,C3 columns.

    When we receive sheet for Second time, it may contains columns like C1,C2,C3,C4

    and for the third time we may receive with C1,C2,C3,C4,C5,C6 etc.,

    Thanks in advance

    What are the actual column names in this example, please? I ask because there may be a post import optimization to help with normalization if they're named right.

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

  • July 5, 2014 at 12:13 am

    #1727132

    Please find the details below

    Date CAD CZK EUR

    1/1/20101.052018.34750.6977

    1/2/20101.053218.39850.6957

    1/3/20101.051718.33600.6982

    1/4/20101.037718.16240.6935

    1/5/20101.037118.25410.6943

    Sometimes we may receive additional(new) columns like GBP, MXN, MYR etc.,

  • July 5, 2014 at 12:32 am

    #1727133

    ykonline (7/5/2014)

    Please find the details below

    Date CAD CZK EUR

    1/1/20101.052018.34750.6977

    1/2/20101.053218.39850.6957

    1/3/20101.051718.33600.6982

    1/4/20101.037718.16240.6935

    1/5/20101.037118.25410.6943

    Sometimes we may receive additional(new) columns like GBP, MXN, MYR etc.,

    ykonline (6/20/2014)

    Hi,

    I want to insert data periodically from an excel file into a table using a SSIS. But each time the no. of columns in excel may vary.

    Suppose, the 1st time we receive excel file may contain 10 columns the second time it may contains 12 columns etc.,

    Thanks in advance.

    Seems simple enough. What does the target table (where you want to insert from an excel file into a table) look like?

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

  • July 5, 2014 at 1:11 am

    #1727135

    Below is my target table and data should be inserted like below.

    Date RepCurr Earning

    1/1/2010CAD1.0520

    1/1/2010CZK18.3475

    1/1/2010EUR0.6977

    1/2/2010CAD1.0532

    1/2/2010CZK18.3985

    1/2/2010EUR0.6957

    1/3/2010CAD1.0517

    1/3/2010CZK18.3360

    1/3/2010EUR0.6982

    1/4/2010CAD1.0377

    1/4/2010CZK18.1624

    1/4/2010EUR0.6935

    1/5/2010CAD1.0371

    1/5/2010CZK18.2541

    1/5/2010EUR0.6943

    1/1/2010USD0.9506

    1/1/2010USD0.0545

    1/1/2010USD1.4333

    1/2/2010USD0.9495

    1/2/2010USD0.0544

    1/2/2010USD1.4374

    1/3/2010USD0.9508

    1/3/2010USD0.0545

    1/3/2010USD1.4323

    1/4/2010USD0.9637

    1/4/2010USD0.0551

    1/4/2010USD1.4420

    1/5/2010USD0.9642

    1/5/2010USD0.0548

    1/5/2010USD1.4403

    Thanks in Advance!!!

  • July 6, 2014 at 1:23 pm

    #1727246

    ykonline (7/5/2014)

    Please find the details below

    Date CAD CZK EUR

    1/1/20101.052018.34750.6977

    1/2/20101.053218.39850.6957

    1/3/20101.051718.33600.6982

    1/4/20101.037718.16240.6935

    1/5/20101.037118.25410.6943

    Sometimes we may receive additional(new) columns like GBP, MXN, MYR etc.,

    Just to set some expectations, if knowledge of SSIS were gasoline, I wouldn't have enough to drive a sugar-ant's mini-bike through a matchbox. Part of the reason for that is that I've not needed to use SSIS to do such things. Instead, I do it all in T-SQL.

    Doing the required dynamic unpivot is simple but we need to get the data into a table where we can actually work on it.

    That leads me to a couple of questions before I can help (others that know SSIS, please DO jump in)...

    1. Is there a way that SSIS can automatically create a table with the right number of columns (no matter how many) from a given spreadsheet? If so, this will be an absolute cake walk. If not, we'll have to bake the cake, first.

    2. If the answer to question #1 above is "No, SSIS can't automatically create a table with the right number of columns for a given spreadsheet", then are you allowed to use the ACE drivers and OPENROWSET?

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

  • July 6, 2014 at 1:43 pm

    #1727247

    Jeff Moden (7/6/2014)

    ykonline (7/5/2014)

    Please find the details below

    Date CAD CZK EUR

    1/1/20101.052018.34750.6977

    1/2/20101.053218.39850.6957

    1/3/20101.051718.33600.6982

    1/4/20101.037718.16240.6935

    1/5/20101.037118.25410.6943

    Sometimes we may receive additional(new) columns like GBP, MXN, MYR etc.,

    Just to set some expectations, if knowledge of SSIS were gasoline, I wouldn't have enough to drive a sugar-ant's mini-bike through a matchbox. Part of the reason for that is that I've not needed to use SSIS to do such things. Instead, I do it all in T-SQL.

    Doing the required dynamic unpivot is simple but we need to get the data into a table where we can actually work on it.

    That leads me to a couple of questions before I can help (others that know SSIS, please DO jump in)...

    1. Is there a way that SSIS can automatically create a table with the right number of columns (no matter how many) from a given spreadsheet? If so, this will be an absolute cake walk. If not, we'll have to bake the cake, first.

    2. If the answer to question #1 above is "No, SSIS can't automatically create a table with the right number of columns for a given spreadsheet", then are you allowed to use the ACE drivers and OPENROWSET?

    There are rather straight forward ways of doing this in SSIS, i.e. a package reads the first portion of the file, determines the structure, creates a table or a package to create the table and creates another package for the transfer. But although one can do all kinds of stuff with one technology it may not be the right approach. In other words, it all depends on the environment, file location etc.. For example, if the files are accessible from the SQL Server, wrapping an OPENROWSET in some dynamic sql might is an option, maybe splitting the roles, use SSIS to transfer the files from a remote location and then fire the code on the server to digest them. In fact, I have found that the combination of the two is simple to create and maintain.

    BTW, one of my favourite methods is to read the file one line in a row and use a splitter:Whistling: to parse it, great for multi document files.

    So here is the question, can you describe the environment, the data source (where are the files coming from), the application/SSIS server and it's relation to the SQL Server etc.?

    😎

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

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