MATLAB Answers

Problems with importing Excel csv format

36 views (last 30 days)
Fredrik
Fredrik on 19 Oct 2011
Answered: Sarah Palfreyman on 30 Apr 2018
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

  1 Comment

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

Sign in to comment.

Answers (9)

Fangjun Jiang
Fangjun Jiang on 19 Oct 2011
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);

  0 Comments

Sign in to comment.


Fredrik
Fredrik on 20 Oct 2011
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!

  0 Comments

Sign in to comment.


Fredrik
Fredrik on 20 Oct 2011
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!

  0 Comments

Sign in to comment.


Fredrik
Fredrik on 20 Oct 2011
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

  3 Comments

Fangjun Jiang
Fangjun Jiang on 20 Oct 2011
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
Fredrik on 21 Oct 2011
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
Fangjun Jiang on 21 Oct 2011
I happened to do something similar before. See update in my answer.

Sign in to comment.


Kevin
Kevin on 22 Oct 2011
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');

  0 Comments

Sign in to comment.


Walter Roberson
Walter Roberson on 22 Oct 2011
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.

  0 Comments

Sign in to comment.


Fredrik
Fredrik on 24 Oct 2011
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!

  1 Comment

Fangjun Jiang
Fangjun Jiang on 24 Oct 2011
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.

Sign in to comment.


Fredrik
Fredrik on 24 Oct 2011
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!

  1 Comment

Fangjun Jiang
Fangjun Jiang on 24 Oct 2011
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'})

Sign in to comment.


Sarah Palfreyman
Sarah Palfreyman on 30 Apr 2018
See extractFileText in Text Analytics Toolbox

  0 Comments

Sign in to comment.

Sign in to answer this question.