loading mixed data (csv format) using Textscan

Hi, I have a problem loading csv file using textscan function (it doesn't have to be this function)

the data format is

12/6/2010, "$213,680,460.70 ", 0.28%, 1.24, ,0.228074442

12/7/2010, "$576,336,234.32 ", -0.95%, 1.24, ,0.323535468

12/8/2010, "$448,346,151.34 ", -0.72%, 1.23, ,0.228460815

so, the problem comes from the dollar amount column, as comma delimiter recognize 1000 separator as a delimiter as well.

How can I load the csv file with the following format?

12/6/2010, 213680460.70 , 0.28%, 1.24, ,0.228074442

12/7/2010, 576336234.32 , -0.95%, 1.24, ,0.323535468

12/8/2010, 448346151.34 , -0.72%, 1.23, ,0.228460815

Answer 1

[Stephen23]

Here is one way that uses regexprep to remove the commas from numbers inside quotation marks. The altered string is then parsed by textscan:

str = fileread('temp.txt');
str = regexprep(str,'"\$(\d{1,3}(,\d{3})*?(\.\d+)?)\s*"','${strrep($1,'','','''')}');
C = textscan(str,'%s%f%f%%%f%f%f','Delimiter',',');

Which generates this output:

>> C{1}
ans = 
  '12/6/2010'
  '12/7/2010'
  '12/8/2010'
  '12/8/2010'
>> C{2}
ans =
             213680460.7
            576336234.32
            448346151.34
                   12.34
>> C{3}
ans =
                    0.28
                   -0.95
                   -0.72
                   -0.72
>> C{4}
ans = 
... ETC

The test-file that I used is here:

Answer 2

[Sean de Wolski]

Have you tried using the import tool (Import Data button on the home tab)?

[albert lee]

I tried xlsread, importdata, csvread, and almost all the default functions to load csv data. all of them gave me broken data

[Sean de Wolski]

That's not what I suggested, I suggested using the Import Tool which will allow you to specify how things should behave interactively. The best part is you can then generate code from it which will give you the textscan syntax you need.