4 ms·
Actually when you use R functions to import CSV files, they also guess data types and make some conversions by default. The difference is that in R you can turn
by temp8964 5y ago
Actually when you use R functions to import CSV files, they also guess data types and make some conversions by default. The difference is that in R you can turn them off if you want to. But in Excel there are automatic changes you can't turn off.
- capnahab 5y agoAs a total R beginner I was hugely put off by the complexities of data cleansing especially columns with ?different data types.
- HPsquared 5y agoYou can set the data type to "text" when importing CSVs, that turns most things off.
- corty 5y agoMost, but not all. That is the entire point: Excel is never safe in any configuration.
- FabHK 5y agoNot sure about that. It is my understanding that Excel only tries to guess the type of an input if it has not been explicitly specified by the user. The problem with "things that look like dates being interpreted as dates" comes from not specifying that a column has type "text".
- Xelbair 5y agoEven if you do specify a column as a text excel WILL assume that it contains dates sometimes. Happened to me multiple times, even when i set each cell as a text. Seems like copypasting a tab separated values resets the cells to their default state.
- crazygringo 5y agoIf "not all", can you provide an example of what gets converted if you specify text? Just want to know to avoid future gotchas.
- HPsquared 5y agoI'm not sure, have to try it out but text fields can be delineated by starting with a single quote ('), that might be a special case. It might not be though, I don't have Excel open to check. Pretty much everything is left alone if you specify "text" at the import stage though. (This is using the text import wizard, or text-to-columns)
- croes 5y agoIf you try reading an Excel file via SSIS you are out of luck. You can't turn of the auto guessing feature.
- fukmbas 5y agoNot true