Problems with importing Excel csv format

Hi. I have a lot of excel files with extension .csv that I would like to import to matlab. I think the problem is not really matlab oriented, maybe more excel. When I import the file emediately, I get the message "No data is available for review" or something like that. But if I just press the save button in my excel spread file and then click "yes" to some " format is not compatible with csv.." question, then it works again.

This clicking is very time consuming because of the many files. Have someone had related problems and solved it or any solutions to my bad explanation? Thanks on forehand! Fredrik

[Kevin]

Email me a small sample of your .csv file and I can try to help you import it into matlab.

Answer 1

[Fangjun Jiang]

The message "some format is not compatible with csv.." indicates that the file contains some formatting that is not supported by .csv file.

Where does the .csv file come from? Can you use csvread() or xlsread() directly without opening the file in Excel? What if you open the .csv file with an text editor like M-editor. Do you see anything abnormal?

I suspect your .csv file has some problems. Use a text editor such as Notepad, Wordpad etc. to open it and visually inspect it to see if anything out of ordinary.

In MATLAB, you can use Excel COM server, go through a loop, open each .csv file and save it again. This is to utilize Excel to fix the problem. You probably can do that in Excel alone using Macros.

If you are using the Excel COM server, the following snip of code can be used.

xlCSV=6; %this constant is defined by MS Excel
ExcelApp.DisplayAlerts=false;
WorkBook.SaveAs(CSV_File,xlCSV);

Answer 2

[Fredrik]

csvread() did not work. Got message:

*_??? Error using ==> dlmread at 145 Mismatch between file and format string. Trouble reading number from file (row 1, field 1) ==> # ins

Error in ==> csvread at 54 m=dlmread(filename, ',', r, c);_*

xlsread gave me: ans = []

Which did not seem that helpfull.

Importdata gave me:

??? Error using ==> importdata at 214 Unable to load file. Use TEXTSCAN or FREAD for more complex formats.

Caused by: Index exceeds matrix dimensions.

I have not tried M-editor yet.. Thanks for your help so far anyway!

Answer 3

[Fredrik]

When I use open() i get the text and values just as I would have wanted to the left of the display, but to the right it says "No data is available for preview", if that helps anything!

Answer 4

[Fredrik]

Yes you are right. The problem seems to be that a proper .csv file should have "" when a sentence has a , in it, but this file does only seem to have one ". When it is resaved, excel adjust this by it self, but then I still will have to open the file, and then click a couple of times.

Any Idea of matlab programming that can fix those bunny ears problem or any other way to fix them? The size of the file is quite large so using textscan seems a bit tough.. Thank you

[Fangjun Jiang]

In MATLAB, you can use Excel COM server, go through a loop, open each .csv file and save it again. This is to utilize Excel to fix the problem. You probably can do that in Excel alone using Macros.

[Fredrik]

This seem to be a good idea. But what script should I use after

exlFile = exlWkbk.Open([docroot '/techdoc/matlab_external/examples/000169_2011-08-18_stat.csv']);

Should "csvwrite('SavedDataFromMatlab.csv',exlFile)" work?

[Fangjun Jiang]

I happened to do something similar before. See update in my answer.

Answer 5

[Kevin]

Why are you opposed to using xlsread?

xlsread reads your file in perfectly from what I can see...

Try:

[Data Text] = xlsread('000169_2011-08-18_stat.csv');

Answer 6

[Walter Roberson]

csvread() and dlmread() is documented as only reading data that has no text in it at all.

You can get around that restriction if you specify cell ranges, starting with a cell "below" any text lines and to the right of any text. The lines at the top that you skip can contain anything, but any cells to the left that you skip must not have any whitespace and must not have an embedded commas.

Answer 7

[Fredrik]

Thank you for your input on the matter. When I try "[Data Text] = xlsread('000169_2011-08-18_stat.csv');" I get Data as [] , and everything in Cells as Text instead, meaning that all Data is strings in Text{62}, Text{63} and so on.. Maybe I could use something to remake these "csv-strings" to data by some command?

When I try "WorkBook.SaveAs(CSV_File,xlCSV);", I get the error:

??? The class "WorkBook" is undefined. Perhaps Java is not running.

Trying to fix that:

exl.ActiveWorkbook.SaveAs('/techdoc/matlab_external/examples/000169_2011-08-18_stat.csv', 6);

Then I got a pop up from excel saying "Cant acces the file .... The file might be damaged, is on a server that is not answering or it is "protected from writing". (Translated from Swedish). Can that last thing be a issue?

Thank you all again!

[Fangjun Jiang]

I saw you had your code as "exlFile = exlWkbk.Open([docroot '/techdoc/matlab_external/examples/000169_2011-08-18_stat.csv'])" so I though you've gone through the Excel COM server code stuff. To apply the technique to your code, you'll replace 'WorkBook' with 'exlWkbk', replace 'CSV_File' with whatever the variable for your new .csv file name.

Answer 8

[Fredrik]

Feels like I got a deosonable solution now.. I use xlsread() on the file, and I get the Data as empty, but everything in text. Then I take out the data from the text.

[Data2 Text2] = xlsread('000169_2011-08-18_stat.csv','A63:A206');

if isempty(Data2)
    disp('Data is Empty, Something wrong with file')

Text=Text2';
TextLen=length(Text);
NewText=cell(TextLen,144);
Data=zeros(TextLen,75);
for x=1:length(Text)
    SplitData=textscan(Text{x},'%s','delimiter',',');
    NewText{x}={SplitData{1}{1},SplitData{1}{2}};
    Data(x,:)=(str2double({SplitData{1}{3:end}}));
end

end

But there are problems with this solution. It needs the file to be a specific length and I also needs to know the row where the data starts. Edit: These things are now fixed :) Thank you all for your help!

[Fangjun Jiang]

Darn, forgot the [Num, Txt, Raw]=xlsread() completely! Stuck with the impression that your .csv file has some problems! One reminder, str2double() works on cell array so you don't need to go through a for-loop. try str2double({'1','2'})

Answer 9

[Sarah Palfreyman]

See extractFileText in Text Analytics Toolbox