6

I have a large CSV file (~1gb) with mixed data. Basically with strings and integers. It takes very long to import this whole dataset with Import[] so I would like to use ReadList. A line contains of {int, string, string, string, int, int, string, int, string, string, int, int, int (or NaN), int (or NaN), string}. These entries are separated by commas. The tricky part is that some of the strings may contain commas as well, but are then enclosed by double quotes. Is there a way I can accomplish reading this correctly with ReadList?

m_goldberg
  • 107,779
  • 16
  • 103
  • 257
Thijs
  • 680
  • 4
  • 11
  • 4
    Unless you have huge amount of memory, you won't be able to import this file as a whole, in any case, because typically Mathematica uses 10-20 times more memory in RAM than the size of file on disk, due to its symbolic nature (unless packed arrays and other efficient structures are utilized, which won't happen automatically during import). Have a look here for a solution which effectively uses Import, but splits the data into chunks and might work for you. – Leonid Shifrin Nov 15 '14 at 12:39
  • 2
    In addition, you could use this solution to transfer your file into chunked form suggested there - which would allow you to load those chunks on demand. You could combine these two, to have a workable solution. You can also look at this question (of which your question seems to be a duplicate). – Leonid Shifrin Nov 15 '14 at 12:41
  • consider reversing my answer here http://mathematica.stackexchange.com/questions/65629/do-write-through/65649#65649 – george2079 Nov 15 '14 at 13:53
  • Leonid, thanks for your answer. The links you provide are very helpful, but I can't find how they solve the problem with commas enclosed by quotes. Where should I look for that? – Thijs Nov 15 '14 at 14:06
  • 2
    @Thijs I'd open it via some dedicated software and switch to .tsv. – Kuba Nov 15 '14 at 14:36
  • 1
    @Thijs The first link I gave is to code which de facto uses Import (via ImportString), so you can give the same spec there. If Import works for you in principle (but is just slow), then that code should also work for you (but be faster / more memory-efficient). If Import can't handle your format, then it is another story. You might need to write some custom parser. – Leonid Shifrin Nov 15 '14 at 14:36
  • 1
    @ Thijs, I had a similar issue. I don't know if that helps you. But what I did was I imported the data into Mathematica and wrote the data into .mat format. From next time on, it's many times faster. You can use Matlab as well to write the data .mat format. – ramesh Nov 15 '14 at 17:14
  • @Thijs there are now a few answers on ReadList and CSV field values that contain commas here – Athanassios Feb 03 '16 at 10:30

1 Answers1

5

I would read the csv file as a stream. It's very quick.

First, I use the following function to look for specific lines.

ClearAll[readLine];
readLines[stream_, search_?StringQ] := 
 With[{stro = FindList[stream, search]}, 
  ImportString[StringJoin[Riffle[stro, "\n"]], "Table"] /; 
   stro =!= {}]

Then I open a stream:

file = "data.csv";
str = OpenRead[file];
read = readLine[str, "whatever"];
Close[str]; (*Close the stream *)

Then use the following to cut each line into list of strings

ds = StringSplit[#, ","] & /@ 
  read;  (*Split the strings for each items in each line*)

Then you can use the following to transform the strings to integers:

ds[[All, 1, 9]] = IntegerPart@ToExpression@ds[[All, 1, 9]];

Hope that helps

Xavier
  • 155
  • 9
  • 1
    Does this also deal with the problem of CSV string entries that contain commas? E.g.: 1,2,"Item 3, with a comma",4 – Thijs Nov 15 '14 at 21:37
  • 1
    Probably not but you haven't shared any example so I gave a possible solution on which you can work on – Xavier Nov 16 '14 at 07:40