Convert Excel to csv with exact formatting

18 views (last 30 days)
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 Comments
Cris LaPierre
Cris LaPierre on 13 Feb 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?
Mathew Smith
Mathew Smith on 13 Feb 2024
Edited: Mathew Smith on 13 Feb 2024
Hello Cris, all content from single .xls file should be converted to single .csv with the same name.
file1.xls --> file1.csv
file2.xls --> file2.csv
...

Sign in to comment.

Accepted Answer

Cris LaPierre
Cris LaPierre on 13 Feb 2024
Edited: Cris LaPierre on 13 Feb 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")
d = 76×4
10.5000 -123.4500 -15.4000 22.0049 2.5000 -131.4500 -15.4000 22.0060 4.7095 -125.9300 -15.4000 22.0053 7.5850 -124.0000 -66.5000 22.0089 10.5000 -123.4500 -66.6648 22.0086 10.5000 -123.4500 -59.8418 22.0082 10.5000 -123.4500 -51.0206 22.0077 10.5000 -123.4500 -41.0998 22.0070 10.5000 -123.4500 -31.1387 22.0062 10.5000 -123.4500 -22.2012 22.0054
% create new csv file
[fpath,fname,fext] = fileparts(filename)
fpath = ""
fname = "TEST_BEFORE"
fext = ".xls"
newFile = fullfile(fpath,fname +".csv")
newFile = "TEST_BEFORE.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)
10.499999900,-123.450004000,-15.399999900,22.004892300 2.499999940,-131.449997000,-15.399999900,22.006008100 4.709544130,-125.929981000,-15.399999900,22.005331000 7.584952750,-123.999998000,-66.500000700,22.008871100 10.499999900,-123.450004000,-66.664837300,22.008598300 10.499999900,-123.450004000,-59.841767000,22.008243600 10.499999900,-123.450004000,-51.020577500,22.007686600 10.499999900,-123.450004000,-41.099760700,22.007000000 10.499999900,-123.450004000,-31.138725600,22.006227500 10.499999900,-123.450004000,-22.201215800,22.005430200 2.499999940,-131.449997000,-67.500002700,22.011762600 3.487332960,-127.599999000,-66.500082600,22.010149000 2.499999940,-131.449997000,-21.331403400,22.007078200 2.499999940,-131.449997000,-30.064424500,22.008306500 2.499999940,-131.449997000,-41.416864800,22.009611100 2.499999940,-131.449997000,-52.294973300,22.010603000 2.499999940,-131.449997000,-61.294570600,22.011287700 5.219800860,-125.440046000,-61.168666900,22.009122800 4.472422410,-126.189932000,-19.314624400,22.005754500 4.599055280,-126.048252000,-25.753455200,22.006393400 5.730436650,-125.027284000,-54.559961000,22.008598300 4.930196330,-125.707418000,-46.078231200,22.008174900 5.404550580,-125.282630000,-34.681838000,22.007110600 5.152309310,-125.500023000,-40.640380200,22.007692300 7.350409400,-124.096081000,-15.399999900,22.005071600 10.499999900,-123.450004000,-18.800607000,22.005140300 7.189451720,-124.167122000,-17.357312100,22.005247100 3.072899300,-128.477097000,-15.399999900,22.005611400 2.499999940,-131.449997000,-18.365701700,22.006591800 3.009318370,-128.641143000,-17.357312100,22.005937600 4.589553920,-126.058653000,-17.357312100,22.005544700 9.013878180,-123.589247000,-66.588252800,22.008722300 9.016760620,-123.588704000,-63.170880100,22.008579300 5.251488180,-125.476003000,-66.500000700,22.009353600 6.339597050,-124.616913000,-63.834331900,22.009004600 10.499999900,-123.450004000,-63.253298400,22.008428600 10.499999900,-123.450004000,-55.431172300,22.007944100 7.678617260,-123.964027000,-60.505215100,22.008619300 7.988199590,-123.854548000,-57.200864000,22.008379000 10.499999900,-123.450004000,-46.060167300,22.007328000 7.988199590,-123.854548000,-52.790269300,22.008098600 7.495061030,-124.035798000,-48.549402500,22.007871600 10.499999900,-123.450004000,-36.119241300,22.006628000 7.495061030,-124.035798000,-43.588995900,22.007516900 7.792503110,-123.922087000,-37.890799300,22.007024800 7.636418570,-123.980068000,-40.870070500,22.007278400 10.499999900,-123.450004000,-26.669969800,22.005832700 7.276179270,-124.128319000,-28.446091300,22.006254200 7.792503110,-123.922087000,-32.910279900,22.006610900 7.189451720,-124.167122000,-20.757921000,22.005542800 7.276179270,-124.128319000,-23.977337400,22.005836500 2.742653950,-129.494607000,-66.846989100,22.010929100 2.499999940,-131.449997000,-64.397282900,22.011514700 2.750763440,-129.462704000,-63.897326600,22.010681200 4.259407990,-126.444504000,-63.834376600,22.009586300 2.499999940,-131.449997000,-25.697913000,22.007722900 3.009318370,-128.641143000,-20.323015700,22.006322900 3.043205710,-128.552377000,-23.542432100,22.006696700 2.499999940,-131.449997000,-35.740643700,22.008997000 3.043205710,-128.552377000,-27.908941700,22.007221200 3.262472800,-128.041446000,-32.373130300,22.007576000 2.499999940,-131.449997000,-46.855919100,22.010139500 3.132557030,-128.332183000,-43.747548000,22.008768100 3.262472800,-128.041446000,-38.049351400,22.008152000 3.193101610,-128.192812000,-41.028622500,22.008491500 2.499999940,-131.449997000,-56.794770100,22.010955800 3.353094910,-127.855316000,-53.427469000,22.009382200 3.132557030,-128.332183000,-49.186602200,22.009216300 3.211598380,-128.151640000,-61.231616900,22.010009800 3.353094910,-127.855316000,-57.927265800,22.009691200 5.470882170,-125.228420000,-57.864312100,22.008865400 4.535317890,-126.118720000,-22.534040700,22.006074900 4.988492470,-125.651434000,-30.217647600,22.006759600 5.319143650,-125.354201000,-50.319094200,22.008390400 5.040267950,-125.602663000,-43.359305700,22.007936500 5.277298860,-125.390023000,-37.661109100,22.007402400
  7 Comments
Cris LaPierre
Cris LaPierre on 13 Feb 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
Alexander on 13 Feb 2024
That's it. I opened it in an editor and got now the same data. Thanks again.

Sign in to comment.

More Answers (0)

Products


Release

R2022b

Community Treasure Hunt

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

Start Hunting!