read values in column from xlsx files in SSIS

Question

read values in column from xlsx files in SSIS

kpann

SSC-Addicted

Points: 423
More actions
April 17, 2013 at 9:31 am

#271280

is it possible to write a code that will read the values in a specific column from xlsx files in SSIS? i'm comfortable with visual basic 2008.
in a_square.xlsx, there's a column named "country"
and in b_a_circle.xlsx, there's a column named "country_region"
i want it to read the values in country in a_square.xlsx and also read the values in country_region in b_a_circle.xlsx so it'll create a new file if there's a match (for example, if the country has a US value in it and the country_region has a US value in it as well)
sorry i know this sounds too confusing but i don't know how to explain it better.

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

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

david.alcock SSCertifiable Points: 6021 More actions · Answer 1

I would be tempted to load both spreadsheets into two sql tables then output matches to a new spreadsheet based on a join but that is because I am much more comfortable in SQL.

'Only he who wanders finds new paths'

@SQLFRNDZ SSCrazy Eights Points: 9095 More actions · Answer 2

Take 2 excel sources and select only 1 attribute in each as you requeried then use a lookup tranformation and excel destination. i hope understand

[font="Tahoma"]
--SQLFRNDZ[/url]
[/font]

kpann SSC-Addicted Points: 423 More actions · Answer 3

i know this sounds silly but will it be helpful if i upload my zip folder so you can understand it better?

Alan Burstein SSC Guru Points: 61141 More actions · Answer 4

kpann (4/18/2013)
i know this sounds silly but will it be helpful if i upload my zip folder so you can understand it better?

By zip folder are you talking about the xlsx file saved as a zip? If so then, yes, this would be helpful.

I am curious about why what david suggested would not help. That is the approach I would take: import each worksheet into SQL and perform a query.

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

kpann SSC-Addicted Points: 423 More actions · Answer 5

I'm working on a SSIS project that will automate every process. I still want to give it a try...

When you download the zip files, you'll notice that I have two xlsx files named unmatched_circle_square and joins. The joins.xlsx file will show you the right result I want for the unmatched_circle_square.xlsx file. The unmatched_circle_square.xlsx file outputs are incorrect (please take a look at B_Circle_1 and A_Square, you'll notice there are some rows that match). Another question - how come if I do a merge join transformation and the data for squareid and squarecoordination are not showing? Is there a way to write a code in a script task that reads the values in specific columns (let's say from color column) from excel files and then create unmatched excel files based on colors? Like...unmatched_b_circle_1_a_square_fuchsia.xlsx and so on?

Prior to executing the Test_Square_Circle.dtsx, please run first and second sequence containers and disable the third sequence container. Once both sequence containers run successfully, please disable them and re-enable the third sequence container.

I know it's way too complicated but I would really appreciate anyone's help with this matter.

Alan.B (4/18/2013)
kpann (4/18/2013)
i know this sounds silly but will it be helpful if i upload my zip folder so you can understand it better?
By zip folder are you talking about the xlsx file saved as a zip? If so then, yes, this would be helpful.
I am curious about why what david suggested would not help. That is the approach I would take: import each worksheet into SQL and perform a query.

Alan Burstein SSC Guru Points: 61141 More actions · Answer 6

kpann (4/18/2013)
I'm working on a SSIS project that will automate every process. I still want to give it a try...
When you download the zip files, you'll notice that I have two xlsx files named unmatched_circle_square and joins. The joins.xlsx file will show you the right result I want for the unmatched_circle_square.xlsx file. The unmatched_circle_square.xlsx file outputs are incorrect (please take a look at B_Circle_1 and A_Square, you'll notice there are some rows that match). Another question - how come if I do a merge join transformation and the data for squareid and squarecoordination are not showing? Is there a way to write a code in a script task that reads the values in specific columns (let's say from color column) from excel files and then create unmatched excel files based on colors? Like...unmatched_b_circle_1_a_square_fuchsia.xlsx and so on?
Prior to executing the Test_Square_Circle.dtsx, please run first and second sequence containers and disable the third sequence container. Once both sequence containers run successfully, please disable them and re-enable the third sequence container.
I know it's way too complicated but I would really appreciate anyone's help with this matter.
Alan.B (4/18/2013)
kpann (4/18/2013)
i know this sounds silly but will it be helpful if i upload my zip folder so you can understand it better?
By zip folder are you talking about the xlsx file saved as a zip? If so then, yes, this would be helpful.
I am curious about why what david suggested would not help. That is the approach I would take: import each worksheet into SQL and perform a query.

Got it. This will take a little while for me to setup and mess with I will get back to you asap.

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001