I need to import data from various database sources. At the moment I plan on importing files via a CSV file or XLS. All the data if in XLS would be on one sheet (notebook). Are there any advantages of using one format over the other? Thanks. David
Both have advantages and disadvantages.
Importing XLS is done using a Java-based application. In my experience, it is terribly slow and eats up enormous amounts of memory to the extent that it blows up your Java heap space, requiring a manual adjustment of this heap space (quite often to no avail). It happens to me all the time that a data structure that I know should fit in memory cannot be imported along the XLS route just because of these memory issues. It does, however, have conveniences such as the ability to read and automatically convert dates and times, select indivudal cells for import, and you can read both formulas and their results.
Importing CSV files with
Import can be much faster and takes less time and memory, although I often resort to reading line by line or as a block using
ReadList and do my own conversions when needed.
I would like to mention a few things.
XLS is widely used, but I always find it clunkier and more inconvenient than CSV.
CSV is more portable:
CSV format is supported well in other data processing ecosystems like R, pandas, Spark, etc. ("Right out of the box" for R and Spark.)
CVS files are readily viewed in the WWW interface of GitHub.
(BTW, for my projects, I often make R-data-packages that often also include the 'raw' CSV files the data frames are based on.)
I have seen a lot of posts in MSE discussing different tweaks or enhancements of
Importfor dealing with CSV and XLS files. It would be nice if the posters or users of those enhancement "package" the corresponding know-how and publish it at Wolfram Function Repository.
- For example, see
ImportCSVToDataset. (Yes, self-advertising here, but also a "do as I do" point.)
- For example, see