Insert Excel rows without disturbing neighboring table cells

Is it possible to write to an "existing" Excel file template, but leave some columns intact? I want to add excel formulas to a dataset, so my thought is that I could create an excel template matching the header of the data in ETL tool transformation, then add X amount of columns to the table that should be untouched, because they have formulas. When data is added to the table, it would automatically apply the formulas to all rows. I cannot use the Formula field definition in the Writer because our largest formula is 1,961 characters long and most are a couple hundred. Converting the mess of IF, LEFT, RIGHT, AND, OR, SUBSTRING, etc would be a huge pain.

I've tried many different ways to get it to work and couldn't figure out something that works. In the Excel writer I've tried:

• Using a template
• Tried the variations of Data Starting from with First Row or A2
• Sheet and table references.

The formulas always got cleared out.

Yes, it is possible

Please set "Ignore Nulls and Keep Original Data" to to True

Excel cell formatting

www.etl-tools.com/wiki/advanced-etl-proc...riter-targets/excel/


 

When I do that, it keeps the first row but does not extend the formulas in the table.

I'd like to use a template in a way to set some formulas externally, insert data into the table, and use the formula values in other transformations. Extending the formula character limit in the Writer would also work, even if it is cumbersome to see 20 characters at a time since I can copy from a text editor.

Screenshots:

Part of the message is hidden for the guests. Please log in or register to see it.

We will have a look at it for you

We have increased Excel formula field size to 8k (Excel Writer)
We also added edit button so it much easier to insert large formulas.

Please keep us posted on your progress

Writing formulas works, for the most part. After running it with a bunch of formulas, it is initially showing values of "0" when in ETL tool while in Excel the value looks correct. Also, it's giving a message of Inconsistent Formula when they are all the same. If I re-save the file manually, it loads correctly in ETL. I attached a sample, hopefully you can reproduce it with that. Columns 22-32 are formulas except for 27 which was a lookup transformation.