Update columns in an existing Excel file

[saqib]

I workflow where I need to update single columns in a large spreasheet with formulas in most columns. I will need to reference the column using the column header (1st row).

How do I achieve this with Alteryx?

[ivoller]

Hi,

Can you expand on what exactly you're trying to achieve? Generally when you use an Excel file as input to Alteryx, the columns contain the data values only. Similarly, when you write back the formulas will be overwritten. You may be able to overcome this by writing back to CSV - see https://community.alteryx.com/t5/Alteryx-Knowledge-Base/Writing-Formulas-to-Excel/ta-p/12337 by @MattD.

Cheers,

Iain

[saqib]

@ivoller,

Essentially what I need to do is at the end of the workflow, I need to update data certain columns in an existing spreadsheet. I don't want to overwrite the spreadsheet.

[ivoller]

In that case you can set the output options in the Output Tool to Overwrite Sheet and make sure that the 'Write to File or Database' field is set to use either an existing named range in the spreadsheet or the cells that you want to write to. e.g. c;\folder\file.xlsx|||myrange or c;\folder\file.xlsx|||Sheet1$F2:F20

Make copies first :-)

Iain

[BenMoss]

@ivoller solution should do the trick!

An alternative solution would be to simply bring in the remainder of the file that you don't want to overwrite into alteryx, then stitch your tables together inside Alteryx using the join tool and then overwrite the whole sheet.

Ben

[saqib]

@BenMoss, wouldn't bring the content in and stitching using join overwrite the macros in the Excel file?

[BenMoss]

Sorry yes of course, hence why you are looking for this solution!

Ben

[saqib]

@ivoller. I am not following you. Can you please explain?

[ivoller]

Sure. If you can identify the columns or ranges that need to be written back to the excel without changing any other columns or formulas then the option to overwrite a sheet where you specify a range as part of the file specification will work. It would probably be easier to demonstrate if you could provide examples of the excel file to be updated and the data you expect to output from the workflow to the excel.

[saqib]

@ivoller, i think I understand what you are recommending. But how would I implement this for dynamic column names. I don't have the column ranges during the development of the workflow. The column ranges are dynamic, and need to be reference by the Column Title (1st row) rather than Spreadsheet ranges......