This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
we receive a lot of Excel files that we upload into a SQL database. BEFORE we run the actual upload scripts we want to implement some basic quality checks via QS scripts. One of the checks is to double-check whether empty rows and/or columns are in the Excel table.
The manual task right now, uses the Excel command "CTRL END" to jump to the end of the table (both rows and columns). In case the highlighted cell is out of the actual data range, we know there are empty rows/columns and the report will be complained towards the creator.
Any ideas how I would perform such a simple check within Alteryx?
Only the check is needed - no task to clean the data for now.
If you load the file into Alteryx and then use the __ tool, this tool will tell you how many Null or Non-Null row entries there are for each column - thus you can identify if there are all-null columns if this tool indicates there are 0 non-null entries.
The attached demonstrates the concept.
If this solution (or another one) solves your issue, please make sure to mark it as an accepted solution so the community knows you've been helped. If I missed the mark, let me know and I can take another stab!
Thanks Zak for your quick answer. Your solution is almost there, I would need to see also the areas right and below of the actual table. I copied your data into an Excel sheet, datacould have been overwirtten from the last month and therefore some areas still show as columns and rows. I add a screenshot to show you the result in Excel using "CTRL END". So I would need to identify column G and row 12 as empty rows/columns. Does this make sense for you? Can I find them as well?
Your WF will find the empty column at the end but not the empty row below. Please see Excel2.docx
That will be a challenge because in Excel its actually grabbing a totally empty column - Alteryx will only import something with data (header or data) - in your instance, would that extra column be TOTALLY blank, or would it have a column header?
In the Excel2.docx document I made some screenshots. As you can see Alteryx detects column F7 as empty (with Nulls) and also the last row 11. I would need to "somehow" detect rows/columns with all values shown as Null.