Stuck trying to import Spreadsheet into Table
BeantwoordI am stuck once again trying to do somethign that seems very trivial. I have a spreadsheet with several columns. I need to query columns A, B and say E and load that into a table. That is it. So what I built is:
So I need the ability to refresh this data. How I understand it, is I need a file associated with a table that I can unload and reload. So to unload the file (assuming it may already exist) I need the ID of the file to pass to Unimport File. I do not have that - just a name. So I created the branch to the right to list files, get file properties for each file, and a conditional that if the name of the file matches the name I want, then store the Id in a dynamic variable.
Once done I need to first delete this query that got created when the first trial run failed. I set the Crate Query to create a temporary query but now I am stumped - I cannot see the query in the WDesk UI (as per the note on that checkbox), so I cannot manually delete it. Fine - I list queries, iterate and get query metadata and look for the query name in the conditional to get its ID to delete it. But the conditional is failing. I thought that like in normal programming a conditional has a pass and fail branch - so I connected both the red exclamation and the grey checkbox "Link Condition" to try and skip if it does not find the query - but it just fails and stops.
Firstly, I do not think the loop is right. I have Get Query Metadata to iterate over the queries but I think it is iterating alone and the conditional is not applied to each iteration. So then I tried grouping them. That results in the same issue - the conditional is never matched and it fails to delete the query. So the create query call fails with:
Result: Error creating query: duplicate query name. Code 409
See final chain here:
So it seems like List queries will not return queries marked as Temporary - which means it is impossible to use temporary queries as they do not get cleaned up and cause duplicate errors. First issue.
Fine - workaround is to not use temporary queries. For now I just renamed the query. So I got further.
Second issue - it seems like a conditional does not have an ELSE condition. So it is not possible to use the red or grey outcomes to flow out from a conditional if it was NOT met. So I replaced it with a group - the outcome of the conditional is no longer dependent on the ELSE. This seems to work.
Another issue - the conditional on file properties does not work. The Get file Properties return three files, one has a name attribute of "name": "ChainAJEAccounts.csv" and my conditional is:
yet the conditional is never met - the log shows a blank value for all iterations of the variable "file properties.name". This causes future failures.
Lastly - this process seems incredibly inefficient. 8 minutes to run just to import a couple of rows from a spreadsheet into a table? What am I doing wrong / what can I do more efficiently?
Also - this will be much easier if Get query Metadata and Get File Properties supported searching by name and not just ID.
-
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.
1
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?
0
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.
0U moet u aanmelden om een opmerking te plaatsen.
Opmerkingen
3 opmerkingen