Convert Excel to csv with exact formatting
16 次查看(过去 30 天)
显示 更早的评论
Hello,
I have hundreds of Excel files where are stored columns:
x,y,z, temperature
Currently, I open them in Excel, remove first line with description, shift the data 1 line up, change format to "Number" with 9 decimal places for all cells and save it in .csv format (In Excel are 2 options for saving .csv - it should not be UTF-8, the option shown in the attached picture is correct.).
I would like to ask for help with scripting it all in Matlab.
Test files are in the attachment.
I used this script but it didn't produce correct result.
file = dir('*.xlsx'); % make sure your are navigated to the right folder on matlab
s= size(file,1);
for i= 1:s
Data = xlsread(file(i).name);
filename=file(i).name;
filename= filename(1:end-5); % to remove extension from filename
csvwrite([filename '.csv'], Data);
end
;
BR
Mathew
4 个评论
Cris LaPierre
2024-2-13
Do you need to keep all the data in separate files, or would it be acceptable to combine all the data into a single csv file?
采纳的回答
Cris LaPierre
2024-2-13
编辑:Cris LaPierre
2024-2-13
To achieve what you have requested, I believe you will need to load the data into MATLAB and then write it to a csv file using fprintf. The preferred write functions (writetable, writematrix) use 'long g' while your file uses '%.9f'.
Here is the code for a single file that I think produces your desired result. You can incorporate this into your loop to work for all files.
% Load current file as text file
filename = "TEST_BEFORE.xls";
d=readmatrix(filename,"FileType","text")
% create new csv file
[fpath,fname,fext] = fileparts(filename)
newFile = fullfile(fpath,fname +".csv")
fid = fopen(newFile,'w');
% write data to csv file using the specified format
fprintf(fid,'%.9f,%.9f,%.9f,%.9f\r\n',d');
fclose(fid);
% View results
type(newFile)
7 个评论
Cris LaPierre
2024-2-13
Good to be aware of. This must be a region thing, as you appear to be using a period for the thousands separator instead of a comma. Here is what I see in Excel.
更多回答(0 个)
另请参阅
类别
在 Help Center 和 File Exchange 中查找有关 Spreadsheets 的更多信息
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!