Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

no. of columns in excel may vary Expand / Collapse
Author
Message
Posted Friday, June 20, 2014 11:19 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 3:16 AM
Points: 10, Visits: 173
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.


Post #1584548
Posted Friday, June 20, 2014 11:46 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 12:43 AM
Points: 2,421, Visits: 6,738
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..
Post #1584551
Posted Saturday, June 21, 2014 3:50 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 8:55 PM
Points: 35,618, Visits: 32,214
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1584733
Posted Saturday, June 21, 2014 3:57 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 12:43 AM
Points: 2,421, Visits: 6,738
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.
Post #1584735
Posted Friday, June 27, 2014 11:46 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 3:16 AM
Points: 10, Visits: 173
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
Post #1587091
Posted Saturday, June 28, 2014 12:20 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 8:55 PM
Points: 35,618, Visits: 32,214
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1587217
Posted Saturday, July 5, 2014 12:13 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 3:16 AM
Points: 10, Visits: 173
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.,
Post #1589463
Posted Saturday, July 5, 2014 12:32 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 8:55 PM
Points: 35,618, Visits: 32,214
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1589465
Posted Saturday, July 5, 2014 1:11 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 3:16 AM
Points: 10, Visits: 173
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!!!
Post #1589468
Posted Sunday, July 6, 2014 1:23 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 8:55 PM
Points: 35,618, Visits: 32,214
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1589692
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse