Classic file types are no longer available for use as of January 2021. You can transition your classic files or download a PDF. Learn More

Stuck trying to import Spreadsheet into Table




  • Jeff Hickey

    Hi Waldo, there are quite a bit to unpack here, so I will start with your initial question "I have a spreadsheet with several columns.  I need to query columns A, B and say E and load that into a table." The simplest way to achieve this is with a combination of the Workiva command Get Sheet Data and the Tabular Transformation command Column Filter.

    Within the Get Sheet Data node, in the Region field select all columns encompassing the full region you want to select. In your example, since you want columns A, B, and E, the value for Region would be "A:E".

    Next, in the Column Filter node, select the Data variable from the Get Sheet Data node output. Check the box to Preview results (this is not required but useful when debugging). Since we know which columns we want, leave Pattern Type as Index. In the Pattern field enter "0:1,4" since the column are 0 indexed. Finally check the box for Inverse since we want to keep these columns.

    The above two commands will select and create a file containing data in columns A, B, and E of the Sheet. Next, I recommend reviewing the Update datasets in a table as a chain tutorial which is a great example of how to look for existing files by name in a Wdata Table. The tutorial walks through the process of replacing or appending files in a table. Specifically take a look at Step 3, Step 4, and Step 6 in the tutorial. Below is a screenshot of an example Chain of the above steps put together where the Conditional is used to determine if the file will be replacing an old file or appending data to the table based on Filter Rows.

  • Waldo Nell

    Thanks for taking the time to respond so thoroughly.  This works - but I still do not understand why my method failed. I get that this is more efficient but in theory mine would have worked too?

  • Jeff Hickey

    Hi Waldo, it's tough to say why it failed without error messages and walking through your chain. In theory you could run a query that uses the Spreadsheet as a data source and then get the results of the query. There are probably several ways to go about this to get to your goal end result. But the two nodes in my previous comment would probably be the quickest for this use case.


Please sign in to leave a comment.