Creating EXCEL work book

[nivas]

I have a sequential file which has coma seperated values. I need to create EXCEL work book with this data. Based on the First Filed in the sequential the data should be moved to different tabls in the EXCEL work book. I should be able to name the tab with first field value in the sequential file. Could any body suggest some hints. Thanks in advance.
thanks
Nivas

[chulett]

That would be a job for Excel code, not an ETL tool.

[nivas]

I am able to create multiple EXCEL spread sheets based on first filed in the sequential file. Now instead of creating multiple spread sheets I want to create multiple tabs in one spread sheet.

thanks
nivas

[ray.wurlod]

Excel worksheet names are presented as "system table" names, preceded by "$". Therefore, when you ask your ODBC stage to create the table, you need to give a table name preceded by "$".

[ag_ram]

I have a sequential file which has coma seperated values. I need to create EXCEL work book with this data. Based on the First Filed in the sequential the data should be moved to different tabls in the EXCEL work book. I should be able to name the tab with first field value in the sequential file. Could any body suggest some hints. Thanks in advance.

I believe that the most of what you require can be resolved by ETL tool DataStage.

For your information:

MS Excel Worksheet can be configured as Database and its individual Sheet can be treated as tables (Sheet1$, Sheet2$, Sheet3$,...) using ODBC Mirosoft Execel Driver. The first line of each Worksheet is considered to be the columns name of that table(Worksheet).

My Suggestion:

1. Create a MS Excel file with all necessary Worksheets(tables) with proper name and define column names in the Worksheet(table) to be as first line.
2. Create a ODBC Microsoft Excel Driver and Configure that to your target MS Excel file as Database.
3.

Based on the First Filed in the sequential the data should be moved to different tabls in the EXCEL work book

Create a Job Which seems to be in the fashion that [Seq File --> Transformer --> (Multiple) ODBC ]
Multiple ODBC Stages are required to load input records into different Worksheets after decided which Worksheet it needs to go by Transformer Stage.

This suggested way may help you in designing a ETL Job for your requirement.

[nivas]

Thanks for the response. Now I am getting the following error. Please help me out

EXCELTest..map.DSLink13: DSD.BCIPut call to SQLExecute failed.
SQL statement:UPDATE `Sheet1$` SET COL2 = ?, COL3 = ?, COL4 = ? WHERE (COL1 = ?)
SQLSTATE=S1000, DBMS.CODE=-3035
[DataStage][SQL Client][ODBC][Microsoft][ODBC Excel Driver] Operation must use an updateable query.

thanks
Nivas

[ag_ram]

I am not aware of what Update action you selected.

Try to keep Update action "Clear the table, then insert rows".

[ag_ram]

I am not aware of what Update action you selected.

Try to keep Update action "Clear the table, then insert rows".

[ray.wurlod]

As noted earlier, DataStage (nor any other client tool) is unable to create the workbook itself. The workbook and the worksheet within it must exist, and the column headings must exist in the worksheet. This, basically, is what the error message is telling you. The column names in your job design, needless to say, must correspond with the column names on the worksheet, and you must have write permission to the workbook (and the area into which you are writing must not be protected).

[nivas]

Hi
I was able to insert data from flat file to excel workbook with the help from our earlier posts. Now I got another one.
When I insert a numeric/integer value into the spread sheet, the datastage is actually putting a apostrophe at the begining. For example the the actual value is -2014807879 but , the datastage is inserting into the spread sheet
as '-2014807879

Please help me out how do I get rid of this apostrophe.

thanks
Nivas