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.

[barrnone]

Exactly. Excel has its own way of interpreting dates as numeric values.  That's how we end up with things like 43684 for today's date (8/7/2019).  There is an algorithm for converting that to a real date in other applications, including Alteryx, using a base date.  But a formula in Excel won't be the same as the formula in Alteryx.

Thanks for your help!

[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%