load multiple excel files(with different number of rows in every file) into a single table

Question

Post reply

load multiple excel files(with different number of rows in every file) into a single table

bmr270

Default port

Points: 1477
More actions
September 9, 2014 at 11:09 am

#293015

Hi,
I have to load multiple excel files into a single table (which has 50 columns (ntext)), but an excel file can contain any number of columns but less than 50 columns.
Example:
ExcelFile1: has 20 columns with 15 rows
ExcelFile2: has 12 columns with 10 rows
If we load above two files into a single table, the table should contain total 25 rows(first 15 rows with 20 columns and 10 rows with 12 columns)
How to all the unformatted excel files into a single table?
Thank you

Viewing 7 posts - 1 through 6 (of 6 total)

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

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 1

That's correct.

Was there a question you wanted to ask?

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Luis Cazares SSC Guru Points: 183698 More actions · Answer 2

You need to use 2 data flow tasks.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 3

Luis Cazares (9/9/2014)
You need to use 2 data flow tasks.

How can you tell that's the answer he's looking for? He didn't ask any question.

2 would not be correct. He said:

... an excel file can contain any number of columns but less than 50 columns ...

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Luis Cazares SSC Guru Points: 183698 More actions · Answer 4

You're right.

But with no question, there's no wrong answer. 😀

"If you don't know where you are going, any road will get you there." - Lewis Carroll

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 5

Luis Cazares (9/9/2014)
You're right.
But with no question, there's no wrong answer. 😀
"If you don't know where you are going, any road will get you there." - Lewis Carroll

I don't agree 100% with Lewis Carroll.

"If you don't know where you want to be, you'll have a hard time getting there." - Alvin Ramard (2014-09-09) 😀

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

COZYROC One Orange Chip Points: 28499 More actions · Answer 6

The standard Data Flow Task supports only static metadata (columns), defined at design time. If you can use third-party solutions, I would recommend you check the commercial COZYROC Data Flow Task Plus. It is an extension of the standard Data Flow Task, which supports dynamic metadata at runtime. You can handle input files with arbitrary number of columns. No programming skills are required.

---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/