Read from write into excel file.

Enzopre · Post by **Enzopre** » Mon Jul 28, 2014 3:14 pm

Hi to all, my requirement is this:

I have an excel file (.xls) with multiple sheets (three sheets for precision: sheet1, sheet2 and sheet3) and I must read data from sheet1, sheet2 and sheet3 and write them in a new sheet, say, sheet4 of same excel file.

How to realize this in datastage 8.1 server edition?
Is it possible?

thanks in advance.

chulett · Post by **chulett** » Mon Jul 28, 2014 5:16 pm

Well... I have no actual experience with this as I've never worked on a Windows edition of DataStage but you should have Excel ODBC drivers you can leverage for this. At least for the reading part. No clue how well they write back to Excel or if writing back to the same file you are reading will be an issue.

Basically, the workbook / file itself is the database and each worksheet is considered a table. You'll need to create a DSN to access it and make sure it is not marked 'Read Only' which seems to be the default. If you do an exact search here for 'Excel' you'll find examples of conversations on this topic, mostly about how to read them directly, very little on writing to them as anything other than a csv.

You sure this isn't a more appropriate task for an Excel macro?

Enzopre · Post by **Enzopre** » Tue Jul 29, 2014 12:12 am

Thanks Craig/Chulett

Indeed, I already searched for the "excel" key word in the forum and I found only topic that tells about how to read from excel files but not how to write in another sheet of same excel file.

I think that this task is not possible to do.

ray.wurlod · Post by **ray.wurlod** » Tue Jul 29, 2014 4:52 am

Does the write sheet exist and have column headings in place? Then you can import its "table definition" just like any other sheet. And write to it, provided that the executing process has write permission to the workbook. Beware that each worksheet is a system table to the ODBC driver for Excel.

Enzopre · Post by **Enzopre** » Tue Jul 29, 2014 6:23 am

Hi Ray

No, the write sheet does not exist it should be created "online" by the job.
Is it possible?

thanks.

chulett · Post by **chulett** » Tue Jul 29, 2014 7:03 am

Try it, let us know. It would basically be a 'create table' statement. Create and populate it yourself once so you can import and leverage the metadata. And in your shoes I would check with support, see what advice they have.

Enzopre · Post by **Enzopre** » Tue Jul 29, 2014 3:11 pm

Okay, as soon as I can try, I'll let you know. I'm waiting for account to access to datastage.

thanks.

ray.wurlod · Post by **ray.wurlod** » Tue Jul 29, 2014 3:22 pm

Don't forget that system table names must end with "$". So, if your worksheet is to be called MySheet, then the table name used in clients (whether DataStage, MS Access or whatever) needs to be MySheet$.