Convert Excel to csv with exact formatting
10 visualizaciones (últimos 30 días)
Mostrar comentarios más antiguos
Mathew Smith
el 13 de Feb. de 2024
Comentada: Alexander
el 13 de Feb. de 2024
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 comentarios
Cris LaPierre
el 13 de Feb. de 2024
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?
Respuesta aceptada
Cris LaPierre
el 13 de Feb. de 2024
Editada: Cris LaPierre
el 13 de Feb. de 2024
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 comentarios
Cris LaPierre
el 13 de Feb. de 2024
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.
Alexander
el 13 de Feb. de 2024
That's it. I opened it in an editor and got now the same data. Thanks again.
Más respuestas (0)
Ver también
Categorías
Más información sobre Spreadsheets en Help Center y File Exchange.
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!