Excel 1997-2003 Format Input - Need To Skip Rows

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.

SpotRates20190131.xls

Input

Accepted answers

Thableaus

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,

RunCommandSaveXLSX.yxmd

All comments

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.

Reading xls File.png

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.

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.

estherb47

Hi again @barrnone

It looks like some of the precision will be lost in the conversion, unfortunately.

So, in the method outlined in the post i referenced, leave the first formula tool as is, adding one day to anything from 1899.

in the second formula tool, try this instead:

DateTimeDiff([F5],'1899-12-31 00:00:00','seconds')/86400. This calculates the difference between the "date" and a benchmark date of 12/31/1899 in seconds, and divides by the number of seconds in a day.

Not a perfect solution at all, but the numbers are closer to the original. So, the best solution might very well be running a command that converts the file type for you.

Cheers,

Esther

barrnone

Thanks, @estherb47. I kind of figured there would be some precision loss and that might not be acceptable for the end user. The loss is generally close to zero, but there is one that is coming up 0 in the calculation when it should have a value (VEF) and a few that are off by 1-2%. You can see that below -- number is coming from your formula, SPOT RATE2 is the actual value generated using the BAT file method to generate an xlsx file.

CURRENCY2	Field_13_3	Current Format	number	SPOT RATE2	% Diff
LBP	1899-12-30 00:01:15	1899-12-31 00:01:15	0.0008680556	0.0008712636	0.37%
LKR	1899-12-30 00:10:36	1899-12-31 00:10:36	0.0073611111	0.0073647173	0.05%
LVL	1900-01-02 14:02:31	1900-01-02 14:02:31	2.5850810185	2.5850773663	0.00%
MAD	1899-12-30 03:18:29	1899-12-31 03:18:29	0.1378356481	0.1378301150	0.00%
MXN	1899-12-30 01:38:53	1899-12-31 01:38:53	0.0686689815	0.0686632746	-0.01%
MYR	1899-12-30 07:42:50	1899-12-31 07:42:50	0.3214120370	0.3214143469	0.00%
NDC	1899-12-30 04:42:41	1899-12-31 04:42:41	0.1963078704	0.1963031765	0.00%
NOK	1899-12-30 03:44:17	1899-12-31 03:44:17	0.1557523148	0.1557469353	0.00%
NZD	1899-12-30 21:46:41	1899-12-31 21:46:41	0.9074189815	0.9074212569	0.00%
PEN	1899-12-30 09:28:40	1899-12-31 09:28:40	0.3949074074	0.3949100004	0.00%
PGK	1899-12-30 09:21:55	1899-12-31 09:21:55	0.3902199074	0.3902180747	0.00%
PHP	1899-12-30 00:36:19	1899-12-31 00:36:19	0.0252199074	0.0252149300	-0.02%
PKR	1899-12-30 00:13:35	1899-12-31 00:13:35	0.0094328704	0.0094319136	-0.01%
PLN	1899-12-30 08:27:47	1899-12-31 08:27:47	0.3526273148	0.3526256583	0.00%
PYG	1899-12-30 00:00:19	1899-12-31 00:00:19	0.0002199074	0.0002174554	-1.13%
ROL	1900-01-01 07:31:58	1900-01-01 07:31:58	1.3138657407	1.3138655714	0.00%
RON	1899-12-30 07:37:11	1899-12-31 07:37:11	0.3174884259	0.3174853677	0.00%
RUB	1899-12-30 00:28:53	1899-12-31 00:28:53	0.0200578704	0.0200614539	0.02%
SAR	1899-12-30 08:24:27	1899-12-31 08:24:27	0.3503125000	0.3503127732	0.00%
SDR	1900-01-01 20:06:29	1900-01-01 20:06:29	1.8378356481	1.8378312197	0.00%
SEK	1899-12-30 03:29:04	1899-12-31 03:29:04	0.1451851852	0.1451849332	0.00%
SGD	1899-12-30 23:25:25	1899-12-31 23:25:25	0.9759837963	0.9759809623	0.00%
SKK	1899-12-30 01:27:42	1899-12-31 01:27:42	0.0609027778	0.0609032388	0.00%
THB	1899-12-30 01:00:36	1899-12-31 01:00:36	0.0420833333	0.0420853796	0.00%
TRY	1899-12-30 06:05:03	1899-12-31 06:05:03	0.2535069444	0.2535114798	0.00%
TTD	1899-12-30 04:38:39	1899-12-31 04:38:39	0.1935069444	0.1935043589	0.00%
TWD	1899-12-30 01:01:37	1899-12-31 01:01:37	0.0427893519	0.0427902559	0.00%
TZS	1899-12-30 00:00:49	1899-12-31 00:00:49	0.0005671296	0.0005670546	-0.01%
UAH	1899-12-30 01:08:28	1899-12-31 01:08:28	0.0475462963	0.0475439644	0.00%
USD	1900-01-01 07:32:05	1900-01-01 07:32:05	1.3139467593	1.3139500000	0.00%
UYU	1899-12-30 00:58:04	1899-12-31 00:58:04	0.0403240741	0.0403211776	-0.01%
VEB	1899-12-30 00:00:53	1899-12-31 00:00:53	0.0006134259	0.0006118687	-0.25%
VEF	1899-12-30 00:00:00	1899-12-31 00:00:00	0.0000000000	0.0000052867	100.00%
VND	1899-12-30 00:00:05	1899-12-31 00:00:05	0.0000578704	0.0000566258	-2.20%
XCD	1899-12-30 11:38:09	1899-12-31 11:38:09	0.4848263889	0.4848212441	0.00%
ZAR	1899-12-30 02:22:34	1899-12-31 02:22:34	0.0990046296	0.0990102164	0.01%

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703