Excel 1997-2003 Format Input - Need To Skip Rows

[barrnone]

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.

[john_miller9]

@barrnone 

Do you have the ability to save the file as a text file before bringing into Alteryx?  I've just converted it and it works pretty seamlessly.

[barrnone]

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.

Thanks.

[Thableaus · Accepted solution]

Hi @barrnone 

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.

Cheers, 

[estherb47]

Hi @barrnone 

Start with the Sample tool, to skip the first 9 rows, and then Dynamic Rename to push the first row into the headers.

Then look at this solution and copy the formulas. Should work like a dream. Let me know if it helps.

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Date-Time-to-Number/m-p/310844

Cheers!

Esther

[barrnone]

@estherb47 

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.

[barrnone]

@Thableaus 

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.

Thanks.

[estherb47]

Hi @barrnone

Just figured out how to tweak that formula! In the first formula tool, change the ‘day’ to ‘year’

Let me know if that fixes. First pass that I took looks like it did.

Cheers!
Esther

[barrnone]

@estherb47 

No, that didn't work for me.  The two formulas are now:

if DateTimeYear([Field_13_3])=1899
then DateTimeAdd([Field_13_3],1,'year')
else [Field_13_3]
endif

and

DateTimeDiff([Current Format],'1900-01-01 00:00:00','seconds')/60/60+24

The numbers aren't close.  For example, the first one (LBP) is 0.000871263641529 in the spreadsheet and 8736.020833 after the calculation.

[estherb47]

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

More to come.

Cheers!

Esther