7
$\begingroup$

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

Improve this question
$\endgroup$
2
  • 1
    $\begingroup$ It's probably good to know about this. When the importer tries to be too smart, it can work against you. $\endgroup$
    – Szabolcs
    Commented Nov 14, 2014 at 15:37
  • $\begingroup$ Very helpful. That is a gotcha that could take a long time to figure out! $\endgroup$
    – David Kerr
    Commented Nov 14, 2014 at 20:13

2 Answers 2

Reset to default
8
$\begingroup$

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.

Improve this answer
$\endgroup$
3
  • $\begingroup$ Those names are single fields according to RFC 4180: ietf.org/rfc/rfc4180.txt $\endgroup$
    – Alan
    Commented Nov 14, 2014 at 16:16
  • 1
    $\begingroup$ I think the point you just removed is quite important. Yes, RFC4180 is there as a recommendation, but in practice there's no single standard that CSV readers/writers (or people piecing together CSV files) follow, which causes all kinds of trouble. $\endgroup$
    – Szabolcs
    Commented Nov 14, 2014 at 16:23
  • $\begingroup$ Again, very helpful. Thank you. $\endgroup$
    – David Kerr
    Commented Nov 14, 2014 at 20:14
1
$\begingroup$

I would like to mention a few things.

  1. XLS is widely used, but I always find it clunkier and more inconvenient than CSV.

  2. 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.)

  3. I have seen a lot of posts in MSE discussing different tweaks or enhancements of Import for 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.)
Improve this answer
$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.