Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Excel 1997-2003 Format Input - Need To Skip Rows

barrnone
6 - Meteoroid

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.

12 REPLIES 12
john_miller9
11 - Bolide

@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.

 

Reading xls File.png

 

barrnone
6 - Meteoroid

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
17 - Castor
17 - Castor

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:

 

 

BatFile.PNG

 

- 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
15 - Aurora
15 - Aurora

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
6 - Meteoroid

@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.

clipboard_image_0.png

barrnone
6 - Meteoroid

@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
15 - Aurora
15 - Aurora
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
6 - Meteoroid

@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
15 - Aurora
15 - Aurora

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

Labels