how to import plain text file, with mixed formats with strings that contain commas [closed]

I have been struggling with this basic task, and I am surprised it is this hard to do in Mathematica. I get lost in the maze of Import and options and sub-options and after trying many things, still not able to figure how to do it.

There are few questions on this, but none of them have the answer that works for me. Here is the problem. I have plain text file. Each line has this mixed format

  number,number,string,number,number,string,string


The strings are quoted, and a string can also have commas and escaped "\" characters in it. So reading the line as string and using StringSplit on comma and such, does not really work.

Here is example of the file. 2 lines only: (call it test.txt)

9,1,"",19,0.0151,"This , is string one"," this is \\ another string"
10,2,"note",22,0.0199,"This , also string"," and this too"


I need to read this file into Mathematica, so I can process each line.

Clearly, the result of the import should be a list of lists. Each one of the lists represent one line. This is what I tried

1) Using Import with Table

data = Import["test.txt", "Table"]


The problem with this, is that each line in one full string!

data[[1]] // FullForm


So one has to parse out the entries out, which does not work, since strings can has commas in them also. So can't use StringSplit and use "," as separator.

2) Looked at supported elements in file

 data = Import["test.txt", "Elements"]


Plaintext does not work. It reads the whole file as one string. Same as using String. Also Lines does not work. Same problem. Same as Data.

3) tried to use CVS. renamed the file to CVS, but Mathematica did not like it.

 data = Import["test.cvs", "CVS"]


3) tried "TSV"

data = Import["test.txt", "TSV"]


but again, each list is actually one string. Back to square one.

related questions

how-to-read-a-list-from-a-file-which-has-mixed-types-of-data with answer there that says It seems there is no ready way to read in this mixed type data. The answer there does not work, since it uses StringSplit on commas, which does not work in this case.

And others, but none of them really have a solution to this basic issue.

In Matlab, this is trivial to do. Matlab has textscan which I use for everything.

fileID = fopen('test.txt','r');
data = textscan(fileID,'%d %d %q %d %f %q %q','Delimiter',',');
fclose(fileID);


Anyone knows how to do the same in Mathematica? Using 10.3.1 on windows 7.

• Importing as CSV worked for me with V10.3.0 running on OS X 10.10.2. I put the two lines you gave as an example in a file called test.csv on my desktop and imported it into a Mathematica notebook. I got the following (FullForm): List[List[9, 1, "", 19, 0.0151, "This , is string one", " this is \\\\ another string"], List[10, 2, "note", 22, 0.0199, "This , also string", " and this too"]]. Why is this not a good result? – m_goldberg Jan 7 '16 at 0:37
• @m_goldberg as I mentioned in the post, I get an error when using CVS. I also renamed the file as cvs and tried it. I mentioned this in the question, I am on windows 7, 10.3.1. Please see 3) above. It could be a different OS thing issue. – Nasser Jan 7 '16 at 0:38
• I read the post carefully, and I'm aware of what you said. But I can not replicate your error on my system. Are you saying that code like Import[FileNameJoin[{\$HomeDirectory, "Desktop", "test.csv"}]] doesn't work on your system because you are on Windows or because you are using V10.3.1 rather than V10.3.0? – m_goldberg Jan 7 '16 at 0:47
• Note I did not give the 2nd element "CVS" -- which is not a mime type -- perhaps you meant "CSV", but you should not have to specify that. – m_goldberg Jan 7 '16 at 0:56
• @m_goldberg OMG! You are right. I used the wrong 3 letters. When I changed the file name to test.csv from test.cvs then it worked :) Sorry about the confusion. You method also works. I have a pharmacy called CVS next to where I live, may be that is why I made this mistake. – Nasser Jan 7 '16 at 0:58

The default "FieldSeparators" for "Table" do not match the field separators in your file. Set them to your required separators. Look at the Import options subsection of the Options section in "Table" documentation.
data = Import["test.txt", "Table", "FieldSeparators" -> {","}]