Excel sheet directly as an Source

ORACLE_1 · Post by **ORACLE_1** » Mon Jul 06, 2009 3:57 pm

Hey Gurus ,

I am trying to to find out ways to read data from Excel Sheet on a windows box from the datastage tool (server installed on Unix box).
I can think of two ways -

1. ODBC - Not sure if this will work , and how? Do I require to download any other tools/driver on the server box?

2. Convert the Excel to CSV - This works fine but not able to automate the Excel to CSV conversion? Any other ways to do it? I am aware of the sharewares available online but any other way?

I did try to search on this topic but did not get any relevant information.

Thanks in Advance.

jcthornton · Post by **jcthornton** » Mon Jul 06, 2009 4:26 pm

Even with the ODBC option, you would still need to get the data accessible from the server. That would entail setting up the workstation (I am assuming since you are talking about Excel from Windows) to be accessible through ODBC as a server or as an FTP server.

Then you connect via ODBC or FTP.

Or you choose another option.

I have had a lot more success with CSV files saved off to a network share that is accessible to both the user updating the spreadsheet and DataStage.

Either train the user to save the CSV file off, or build a macro that can be easily triggered to save the file off to the correct location. It may even be possible to trigger the macro to work whenever the spreadsheet gets saved, if that is your preference (potential problems with this however).

Either way, have a read-only copy of the spreadsheet somewhere so that the correct layout can be recovered when somebody accidentally messes it up.

chulett · Post by **chulett** » Mon Jul 06, 2009 4:39 pm

And they will 'accidentally' mess it up. Repeatedly.

ORACLE_1 · Post by **ORACLE_1** » Mon Jul 06, 2009 4:41 pm

jcthornton wrote:...build a macro that can be easily triggered to save the file off to the correct location. It may even be possible to trigger the macro to work whenever the spreadsheet gets saved, if that is your preference (potential problems with this however).

I agree that CSV file is the best option to implement here which will make things lot more easy and transparent.
The excel that I am trying to read contains "formulaes" which point to other excel sheet. But not an issue I am still able to save it as CSV without an issue with all the data intact.
The issue is to automate this , the user enters data in the underlying sheets and not in the main excel , so running the macro at that time is not possible ( i think ). Also one of the resources developed this macro which is very unreliable when "Scheduled" to run. Is it possible if you can share an example or so to help?

Kryt0n · Post by **Kryt0n** » Mon Jul 06, 2009 4:52 pm

You can run a macro on just about any event but running a macro every time a user changes something would make it unworkable... and create one unhappy user!

Easiest method that springs to my mind is to override the save event (quick peek and I can see there is a Before Save event)... has the advantage that the user is expecting to wait at this point and you only ever write to the CSV what the user wants to save.

jcthornton · Post by **jcthornton** » Mon Jul 06, 2009 5:10 pm

So the changes would be made in a single workbook, but the users would not be updating the worksheet that needs to be exported?

If this is the case that you are dealing with, I have not built such a macro, but it looks like it can be done using the BeforeSave event.

Worksheets("Sheetname").Activate
ActiveWorksheet.SaveAs "<targetfile>", fileformat:=xlCSV

This should point you in the right direction.