numeric columns read as dates

[benzipperer]

Thanks for the excellent package!

With the spreadsheet TRACT_ZIP_092019.xlsx, readxl guesses columns 3-6 are dates, but they should be numeric:

read_excel("TRACT_ZIP_092019.xlsx")

# A tibble: 170,193 x 6                                                        
   tract zip   res_ratio           bus_ratio           oth_ratio          
   <chr> <chr> <dttm>              <dttm>              <dttm>             
 1 0100… 36067 1900-01-01 00:00:00 1900-01-01 00:00:00 1900-01-01 00:00:00
 2 0100… 36067 1900-01-01 00:00:00 1900-01-01 00:00:00 1900-01-01 00:00:00
 3 0100… 36067 1900-01-01 00:00:00 1900-01-01 00:00:00 1900-01-01 00:00:00
 4 0100… 36067 1899-12-31 00:36:16 1899-12-31 13:58:28 1899-12-31 06:00:00
 5 0100… 36066 1899-12-31 23:23:43 1899-12-31 10:01:31 1899-12-31 18:00:00
 6 0100… 36068 1899-12-31 04:44:07 1899-12-31 10:58:22 1899-12-31 00:10:21
 7 0100… 36067 1899-12-31 00:01:17 1899-12-31 00:27:00 1899-12-31 00:00:00
 8 0100… 36066 1899-12-31 19:14:34 1899-12-31 12:34:36 1899-12-31 23:49:38
 9 0100… 36067 1900-01-01 00:00:00 1900-01-01 00:00:00 1900-01-01 00:00:00
10 0100… 36067 1900-01-01 00:00:00 1900-01-01 00:00:00 1900-01-01 00:00:00
# … with 170,183 more rows, and 1 more variable: tot_ratio <dttm>

The following command produces the desired output

read_excel("TRACT_ZIP_092019.xlsx", col_types = c("text", "text", "numeric", "numeric", "numeric", "numeric"))

By the way, why does the latter take longer to execute even though I'm specifying the column types?

Spreadsheet source: https://www.huduser.gov/portal/datasets/usps_crosswalk.html#data

[jennybc]

Connected to #388 and #559

[jennybc]

Yeah, styles.xml contains this:

<numFmts count="1">
<numFmt numFmtId="164" formatCode="0.000000000;[Red]0.000000000"/>
</numFmts>

And changing the negative number format from red to, e.g., the minus sign fixes the problems.

[jennybc]

I'm going to consider this a duplicate of #388.

[benzipperer]

Thank you for your help! By the way, why does read_excel take longer to execute when I specify the column types, as opposed to when I let it auto-detect columns?

[jennybc]

why does read_excel take longer to execute when I specify the column types, as opposed to when I let it auto-detect columns?

Based on my knowledge of the code, this is very surprising, because it would seem we are doing less work. But I suppose the work we save re: guessing could be more than cancelled out if we end up with different types in the two scenarios. I'm sure we spend the bulk of our time parsing and populating cells.

If you want to pursue this, I'd need a clean reprex using bench (http://bench.r-lib.org) or similar that shows exactly what you're seeing. That would also be a separate, new issue.