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.
This one has been driving me crazy. I know that when bringing in xlsx files via the input tool you can start on a selected row. However, you can't do the same with an xls (1997-2003) file and this is causing a big issue for me. For a project I'm working on, one of the source files is xls (I do not have control over this), and there is a column which has numeric values. However, in that same column, there is a date in the header area (row 7). The actual data headers are on row 11 with the data beginning on 12. What's happening is that Alteryx is reading the column as being in date format because of the row 7 value, and thus forcing all of the values I need into dates instead of double. I have yet to find a way around this. I don't care at all about the row 7 date value and would love to skip the first 10 rows. But the input sets the column to date format before I can apply any tools to skip rows.
A sample of the Excel file is attached. Any thoughts? Thanks.
I'm trying to avoid any file conversion outside of Alteryx since I am passing this process on to another party. By the same logic, it would be very simple to save it as an xlsx and start the input on the 11th row. If there is no solution within Alteryx, I'll probably have to go that route and make it part of the process, but I'd like to get around that if possible.
You can use some trick to automate this task to save the file to an XLSX format inside Alteryx.
Using the Run Command Tool, you can do this in background and return your XLSX file.
See WF as an example:
- There's a text Input with all the arguments of your command (Excel Folder, Command, XLS Path and New XLSX Path)
- You just need to concatenate them all in a Formula tool, adding whitespaces between the fields
- Use Select Tool to leave just your "Bat File" Field
Then the Run Command Tool comes.
- First, you will need to write your Bat File field to a batch script in your computer. I used the Temp folder as an example. As you notice, you need to select CSV as file format and put \0 as delimiter, keeping the .bat extension to make it work.
- Then, you will call this file in the "Command" part. Pretty simple.
- After all, you need to specify your XLSX file path in the "Read Results" part. Just pay attention that before the first time you run this, you won't be able to find the file itself (since it's going to be created with the bat file). Just copy the full file path and drop in the Path box, hit ok and run.
It should work if you respect all the rules. If it doesn't, let me know.
You can continue your workflow after the Output Anchor of the Run Command tool. Your Spreadsheet table will be the output with all the data.
Thanks, but I already tried that approach after searching and finding it. The results are not accurate. Here's a sample of what I got compared to the original data from Excel. The calculation doesn't work properly, but if there was a good calculation for this, that would be a preferred approach.
Thanks, that solution works. Just had to tweak the location of the Excel conversion executable (for me it is saved in C:\Program Files (x86)\Microsoft Office\root\Office16). Hopefully all users here have the same location or it will cause problems.
A calculation would be preferable to mitigate file location risk, but it doesn't appear that there's a valid calc for these values.
Gotcha. So the challenge is how Alteryx interprets the date is different from how Excel interprets the date. I need to noodle on this some more. All "dates" after 1/1/1900 will convert properly, it's just the ones that translate into 1899 dates that don't