topic Inserting new Column into an existing Excel file in Talend Studio

Inserting new Column into an existing Excel file

_AnonymousUser — Sat, 16 Nov 2024 11:53:50 GMT

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

Re: Inserting new Column into an existing Excel file

Anonymous — Fri, 27 Sep 2013 04:41:43 GMT

Hi,
Please check out the option "Append" in component tFileOutputExcel.
Best regards
Sabrina

Re: Inserting new Column into an existing Excel file

Anonymous — Sat, 28 Sep 2013 21:37:03 GMT

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.

Re: Inserting new Column into an existing Excel file

Anonymous — Tue, 08 Oct 2013 07:25:08 GMT

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

Re: Inserting new Column into an existing Excel file

Anonymous — Tue, 08 Oct 2013 20:35:26 GMT

Good to learn every day a new feature 😉