Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


no. of columns in excel may vary


no. of columns in excel may vary

Author
Message
ykonline
ykonline
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 180
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.
Eirikur Eiriksson
Eirikur Eiriksson
SSCertifiable
SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)

Group: General Forum Members
Points: 6693 Visits: 17686
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..
Cool
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)

Group: General Forum Members
Points: 44993 Visits: 39879
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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Eirikur Eiriksson
Eirikur Eiriksson
SSCertifiable
SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)

Group: General Forum Members
Points: 6693 Visits: 17686
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.
Cool
ykonline
ykonline
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 180
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
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)

Group: General Forum Members
Points: 44993 Visits: 39879
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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
ykonline
ykonline
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 180
Please find the details below

Date    CAD    CZK    EUR
1/1/2010   1.0520   18.3475   0.6977
1/2/2010   1.0532   18.3985   0.6957
1/3/2010   1.0517   18.3360   0.6982
1/4/2010   1.0377   18.1624   0.6935
1/5/2010   1.0371   18.2541   0.6943

Sometimes we may receive additional(new) columns like GBP, MXN, MYR etc.,
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)

Group: General Forum Members
Points: 44993 Visits: 39879
ykonline (7/5/2014)
Please find the details below

Date    CAD    CZK    EUR
1/1/2010   1.0520   18.3475   0.6977
1/2/2010   1.0532   18.3985   0.6957
1/3/2010   1.0517   18.3360   0.6982
1/4/2010   1.0377   18.1624   0.6935
1/5/2010   1.0371   18.2541   0.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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
ykonline
ykonline
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 180
Below is my target table and data should be inserted like below.

Date RepCurr    Earning
1/1/2010   CAD   1.0520
1/1/2010   CZK   18.3475
1/1/2010   EUR   0.6977
1/2/2010   CAD   1.0532
1/2/2010   CZK   18.3985
1/2/2010   EUR   0.6957
1/3/2010   CAD   1.0517
1/3/2010   CZK   18.3360
1/3/2010   EUR   0.6982
1/4/2010   CAD   1.0377
1/4/2010   CZK   18.1624
1/4/2010   EUR   0.6935
1/5/2010   CAD   1.0371
1/5/2010   CZK   18.2541
1/5/2010   EUR   0.6943
1/1/2010   USD   0.9506
1/1/2010   USD   0.0545
1/1/2010   USD   1.4333
1/2/2010   USD   0.9495
1/2/2010   USD   0.0544
1/2/2010   USD   1.4374
1/3/2010   USD   0.9508
1/3/2010   USD   0.0545
1/3/2010   USD   1.4323
1/4/2010   USD   0.9637
1/4/2010   USD   0.0551
1/4/2010   USD   1.4420
1/5/2010   USD   0.9642
1/5/2010   USD   0.0548
1/5/2010   USD   1.4403

Thanks in Advance!!!
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)

Group: General Forum Members
Points: 44993 Visits: 39879
ykonline (7/5/2014)
Please find the details below

Date    CAD    CZK    EUR
1/1/2010   1.0520   18.3475   0.6977
1/2/2010   1.0532   18.3985   0.6957
1/3/2010   1.0517   18.3360   0.6982
1/4/2010   1.0377   18.1624   0.6935
1/5/2010   1.0371   18.2541   0.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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search