Import vs ReadList CSV file with Currency Values

Question

A testing file with the specified format can be downloaded from here

Let's read a few lines from this file

(recLines = ReadList[csvFileName, Record, 3]) // TableForm

SalesOrderID,ProductID,ProductName,OrderDate,ShipDate,Revenue
51217,779,Soft Cushion,5/7/2014,11/7/2014,"$2,400"
51217,788,Soft Cushion,5/7/2014,11/7/2014,"$2,400"

ls[[2]] // FullForm
"51217,779,Soft Cushion,5/7/2014,11/7/2014,\"$2,400\""

Had I used the Import I would have got

recData = Import[csvFileName];
recDate[[1;;3]]//TableForm

SalesOrderID    ProductID   ProductName OrderDate   ShipDate    Revenue
51217   779 Soft Cushion    5/7/2014    11/7/2014   2400
51217   788 Soft Cushion    5/7/2014    11/7/2014   2400

Head /@ csvData[[3]]
{Integer, Integer, String, String, String, Integer}

This is exactly the output I would like to get, but instead of using Import I want to use ReadList.

I have read various posts related to ReadList and this is the closest I have found to an answer that will use ReadList

rl = ReadList[csvFileName, Word, WordSeparators->{","}, RecordSeparators->{"\n"}, RecordLists->True]

rl[[1 ;; 3]] // TableForm

 SalesOrderID   ProductID   ProductName OrderDate   ShipDate    Revenue 
  51217 779     Soft Cushion    5/7/2014        11/7/2014       "$2	400"
  51217	788	Soft Cushion	5/7/2014   	11/7/2014	"$2      400"

rl[[3]] // FullForm

List["51217","788","Soft Cushion","5/7/2014","11/7/2014","\"$2","400\""]

Head /@ rl[[3]]
{String, String, String, String, String, String, String}

Could you please continue this to a full answer or perhaps share a better solution with ReadList ? For example, is it possible to read and transform at the same time with ReadList, by specifying the header, i.e. types of the record items, and get exactly the same result as that of Import ?

I also have this challenge question for the experts of the language:

ReadList is a memory efficient and fast method of reading and parsing CSV files. But it cannot detect automatically the data types of the record items, something that Import does AUTOMAGICALLY. Wouldn't be possible to implement somehow a ReadList that can also detect the header format?

If not, suppose we do know what is the format of the records, and suppose it does not change. There are all these cryptic types and options of ReadList to assist you in getting the parsing of data items right. Can somebody explain me in the file I specified how I can use these types and options to achieve reading lists same way like Import function ?

Answer 1

As long as you are using ReadList to read in the entire file, and then acting on each line, you may as well just feed the result of ReadLine into ImportString.

test = ImportString[
    StringJoin[
     Riffle[ReadList["shoes_revenue.csv", String], "\n"]
     ],
    "CSV"]; // AbsoluteTiming
(*{2.7918, Null}*)

which is marginally faster than this answer

conv = {ToExpression@#[[1]], 
    ToExpression@#[[2]], #[[3]], #[[4]], #[[5]], 
    ToExpression@StringTake[StringJoin@#[[6 ;;]], {3, -2}]} &;
(wrdlst = 
   conv /@ (ReadList["shoes_revenue.csv", Word, 
       WordSeparators -> {","}, RecordSeparators -> {"\n"}, 
       RecordLists -> True])[[2 ;;]];
  PrependTo[wrdlst, 
   Flatten@StringSplit[ReadList["shoes_revenue.csv", Record, 1], ","]];
  ) // AbsoluteTiming
(* {3.00567, Null} *)

Answer 2

Here are some approaches with slightly modified versions of your input CSV file. If you are looking for an efficient way to read the data, the input data should be as friendly as possible to MMA.

However in the last example, I will give one possible way to process your original data.

1. ReadList with formats

With this approach, the most problematic in your data is the Revenue field where values contain a comma ($2,400). So let's say you don't have this problem (I directly changed this to a numeric value but I could have left it as a string with just the comma removed)

Let's say your file is :

csv = "51217,779,Soft Cushion,5/7/2014,11/7/2014,2400\n51217,788,Soft Cushion,5/7/2014,11/7/2014,3200";

Print@csv

str = StringToStream@csv;
rl = ReadList[str, 
{Number, Character, Number, Character, Word, Word, Word, Character, Number},
RecordSeparators -> {"\n"}, WordSeparators -> {","}, RecordLists -> False]
Close@str;

{{51217, ",", 779, ",", "Soft Cushion", "5/7/2014", "11/7/2014", ",", 2400},

{51217, ",", 788, ",", "Soft Cushion", "5/7/2014", "11/7/2014", ",", 3200}}

and finally you extract the parts you want (you get rid of the commas) :

rl[[All, {1, 3, 5, 6, 7, 9}]]

{{51217, 779, "Soft Cushion", "5/7/2014", "11/7/2014", 2400},

{51217, 788, "Soft Cushion", "5/7/2014", "11/7/2014", 3200}}

and you can check that :

Head /@ rl[[All, {1, 3, 5, 6, 7, 9}]][[2]]

{Integer, Integer, String, String, String, Integer}

2. ReadList combined with ToExpression

(I learned this powerful method from @Jakob Akkerboom in this post)

This approach will fit data like these (here it is better not to have commas inside the fields, so for the sake of simplicity i removed these):

csv = "51217,779,\"Soft Cushion\",\"5/7/2014\",\"11/7/2014\",2400\n\
51217,788,\"Soft Cushion\",\"5/7/2014\",\"11/7/2014\",3200";

Print@csv

then just

str = StringToStream@csv;
rl = ReadList[str, String, RecordSeparators -> {"\n"}] // 
Map[ToExpression["{" ~~ # ~~ "}"] &, #] &
Close@str;

{{51217, 779, "Soft Cushion", "5/7/2014", "11/7/2014", 2400},

{51217,788, "Soft Cushion", "5/7/2014", "11/7/2014", 3200}}

and you can verify:

Head /@ rl[[1]]

{Integer, Integer, String, String, String, Integer}

3.ReadList with your original dataset

The idea is to import each line of the CSV file as string, then to do the processing to suit your needs. If you deal with very big files, this approach could be adapted to read the file by chunks (using low level Read function instead of ReadList).

r1 = ReadList[csvFileName, String];
r2 = Map[StringReplace[#, 
       "\"$" ~~ pat : (DigitCharacter .. ~~ "," ~~ DigitCharacter ..) ~~"\"" :> 
StringDelete[pat, ","]] &, r1] // Map[StringSplit[#, ","] &, #] &;
r2[[2 ;;, {1, 2, 6}]] = r2[[2 ;;, {1, 2, 6}]] // ToExpression;

r2 // TableForm

and

Head /@ r2[[2]]

{Integer, Integer, String, String, String, Integer}

4. Lazy lists

But if you have to deal with very big files, maybe you should convert your data to @Leonid Shifrin's lazy lists.

Answer 3

I am not sure what exactly you are looking for, but you could apply ToExpression to those columns that should be Integers instead of Strings:

 Head@ToExpression["1234"]
 (* Integer *)

Similarly, you can combine the last two columns, drop the Dollar symbol and convert to integer:

ToExpression@StringDrop["$2" <> "843", 1]
(*2843 *)

Answer 4

ReadList with Word

It seems to me that the best you can do with ReadList in this case is to parse each line of the file and get the field values back as strings. For the monetary comma value, you get a list with two string values and this is the major obstacle to tackle.

Note that this ReadList use has already been added to my question above, but nobody continued this for a full answer

We know the format of the fields, two integers, followed by a string, followed by two dates, followed by the monetary value.

conv = {
        ToExpression@#[[1]], ToExpression@#[[2]],
        #[[3]], 
        #[[4]], #[[5]],
        ToExpression@StringTake[StringJoin@#[[6 ;;]], {3, -2}]} &;

Map the Pure Function above to the output of ReadList

   wrdlst = conv /@ (ReadList[csvFileName, Word, WordSeparators -> {","}, RecordSeparators -> {"\n"}, RecordLists -> True])[[2 ;;]];

   PrependTo[wrdlst, Flatten@StringSplit[ReadList[csvFileName, Record, 1], ","]];

   Head /@ wrdlst[[3]]
   {Integer, Integer, String, String, String, Integer}

In my machine it is about 1.7sec faster than the answer provided by @SquareOne. It is also close to Jason's answer