Problems with importing Excel csv format

6 次查看（过去 30 天）
Fredrik
Fredrik 2011-10-19
回答： Sarah Palfreyman 2018-4-30
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 个评论
Kevin
Kevin 2011-10-19
Email me a small sample of your .csv file and I can try to help you import it into matlab.

请先登录，再进行评论。

请先登录，再回答此问题。

回答（9 个）

Fangjun Jiang
Fangjun Jiang 2011-10-19
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);
Fredrik
Fredrik 2011-10-20
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!
Fredrik
Fredrik 2011-10-20
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!
Fredrik
Fredrik 2011-10-20
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 个评论
显示 1更早的评论
Fredrik
Fredrik 2011-10-21
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 2011-10-21
I happened to do something similar before. See update in my answer.

请先登录，再进行评论。

Kevin
Kevin 2011-10-22
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');
Walter Roberson
Walter Roberson 2011-10-22
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.
Fredrik
Fredrik 2011-10-24
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 个评论
Fangjun Jiang
Fangjun Jiang 2011-10-24
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.

请先登录，再进行评论。

Fredrik
Fredrik 2011-10-24
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 个评论
Fangjun Jiang
Fangjun Jiang 2011-10-24
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'})

请先登录，再进行评论。

Sarah Palfreyman
Sarah Palfreyman 2018-4-30
See extractFileText in Text Analytics Toolbox

类别

在 Help Center 和 File Exchange 中查找有关 Spreadsheets 的更多信息

标签

产品

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!

Translated by