How to separate the double columns while reading file

Question

I have a file which has contents of the form

10533 "MENDOZAT " 115.0000 : 1 1.00000

Now I want to separate the words and get it as a list. like

{10533,"MENDOZAT   ",115.0000,:,1,1.0000}

My code is

x = ReadList[filename,Word,RecordLists->True,WordSeparators->{" ","\t"}]

But I am getting the result as

{10533,"MENDOZAT, ",115.0000,:,1,1.0000}

As you can see the results are different ("MENDOZAT " in the first one which is one word, "MENDOZAT, ", which are two words). I tried to include "" in the word separators but getting an error.

Is there any way I can seperate them?

Answer 1

Try Import with the following argument:

ImportString["10533 \"MENDOZAT \" 115.0000 : 1 1.00000
 10533 \"MENDOZAT \" 115.0000 : 2 1.00000
 10533 \"MENDOZAT \" 115.0000 : 3 1.00000", "Table", 
 Delimiter -> "\t"]

Answer 2

Your example record has two delimiters, the double quotes and the unquoted spaces. One approach to process this record is to define a function, f1, that splits the record according to the spaces. We will end up keeping only the first field and the last 4 fields from this split. We also define a function, f2, that splits the record on the double quotes, and only keeps the second field. The last function, f3, joins the first field from f1, the field from f2 and the last 4 fields from f1 into a list of strings.

Here are the function definitions and an example of how they are used:

f1 = Flatten[StringSplit[#]] &;
f2 = Flatten[StringSplit[#, "\""]][[2]] &;
f3 = Flatten[{f1[#][[1]], f2[#], f1[#][[-4 ;;]]}] &;
x = f3 /@ ReadList[filename, String]

The advantage of this method is that the second field can have any number of spaces anywhere in the field. A disadvantage is that the second field must be double quoted, as in your example record, even if it doesn't contain any spaces.

A second disadvantage may be that all fields are converted to strings. To get numbers in some of the fields we would apply ToExpression to those fields. This conversion could be incorporated into function f3.