How do I add a column to data pulled from a spreadsheet to a wdata table?
Udzielono odpowiedziI have a Spreadsheet with several sheets. I want to iterate each sheet, get the data, append a column to each sheet (the name of the sheet), and then import that into one wdata table.
In one test I created a chain with the List Sheets Workiva command, which feeds into a Import Spreadsheet into Table command that uses iteration. This works, but I cannot seem to add a new column or transform the data in any way.
So my next idea was to use List Sheets, then Get sheet Data and write that to a file, then use the Tabular Transformation commands to inject a column, then import the file into wdata. However I do not know how to write the output of Get Sheet data to a file. Any help in doing this right would be appreciated.
-
Hello Waldo,
After your 'Get Sheet Data' call, insert the node 'Insert Column' found in Tabular Transformation (https://support.workiva.com/hc/en-us/articles/360035645892#section15). Inside 'Insert Column', set the value of 'Input file' to 'Get Sheet Data' > 'Data', and select the appropriate delimiter. Next, set the new column a Header such as 'Sheet Name', and add the name of the sheet returned from 'List Sheets' for the current iteration in 'Data value' field. Finally, choose the index of the new column for where it will be added into your data (0 is the first column index). Select the 'Preview results' checkbox to be able to see a sample of the output in the Logs.
1
Thank you. I could swear I tried that before and it did not work, but 10 seconds before your response I did just this and it works. Thanks!
0Zaloguj się, aby dodać komentarz.
Komentarze
Komentarze: 2