One Star

Inserting new Column into an existing Excel file

Hello,
I built a Talend job that pulls data out of an Oracle database and maps it to an existing Excel file. My job runs succesful and the new data appears in the Excel file in the correct Row/Column cell I need it to. However, the data is overwriting the existing data in Column A, I need this new data to appear in a new column and move existing columns back a letter (i.e existing column A is now column B).
Example:
Existing file has data in Column A, B, C
After Talend job runs, new data is inserted into a new column -- Excel file will then have Column A, B, C, D (colulmn A containing the new data).
I've been working on this for a while now and think I pushed Talend to its limits. Any ideas how to accomplish this?
Thanks
4 REPLIES
Moderator

Re: Inserting new Column into an existing Excel file

Hi,
Please check out the option "Append" in component tFileOutputExcel.
Best regards
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
Seventeen Stars

Re: Inserting new Column into an existing Excel file

Hi Sabrina, please correct me if I am wrong but this option allows to add rows and not columns right?
@Colby, I suggest reading the existing excel file and merging its data with the data from your database, best way is holding all data in a separate database table and recreate the excel file. This way you can do all things with the build-in components from Talend.
In case of it is no problem using custom components:
You can try the component tFileExcelSheetInput and tFileExcelSheetOutput. The first one reads the sheet, you have to merge the data and the last on writes a new sheet. tFileExcelWorkbookSave can delete sheets not needed anymore.
So you have minimum 2 ways to solve the problem. Good luck.
Moderator

Re: Inserting new Column into an existing Excel file

Hi,
Actually, "Append"option allows to add content.
For example;
Existing file looks like
id;name;sex
1;shong;m
2;sabrina;f
I want to insert a new column "title" into file
If you check out "Append existing file" not "Append existing sheet", the actual result should be:
id;name;sex;title
1;shong;m;l
2;sabrina;f;m
If you check out "Append existing file" and "Append existing sheet", the actual result should be:
1;shong;m
2;sabrina;f
1;shong;m;l
2;sabrina;f;m
Best regards
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
Seventeen Stars

Re: Inserting new Column into an existing Excel file

Good to learn every day a new feature ;-)