Convert Excel to csv with exact formatting

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

[Les Beckham]

The "TEST_BEFORE.xls" file that you have attached is not an Excel workbook, it is a plain text file. Also, the code you show appears to be reading .xlsx files rather than .xls files. Are you sure that you attached the right files?

What is it about the results that you are getting that are not the "correct result"?

[Mathew Smith]

Hello Les,

unfortunatelly, input file TEST_BEFORE.xls is correct. This is a product of software which I use and I have to deal with it.

The code which I use is not good enough, probably I firstly exchanged .xls for .xlsx in Excel for testing. As my code doesn't work, therefore I ask you for help.

By not "correct results" I mean that I use the .csv files as inputs for next calculations. And results of those calculations were incorrect if I tried to do formatting in Matlab rather than in Excel. I found out that only correct formatting can be done in Excel using procedure described above but I believe that the same can be done in Matlab too.

[Cris LaPierre]

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]

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

...

Answer 1

[Cris LaPierre]

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

[Alexander]

Just for my understanding, why is 104.999.999 converted to 10.499999 but -123.450.004 is converted to -123.450004 and not to -12.3450004?

[Cris LaPierre]

I don't see a value of 104.999.999.

Here is how the values compared to the sample TEST_AFTER.csv file

[Mathew Smith]

Hello Cris, thank you very much! Do you know how to convert all files in a given folder if files are named: file 1_1.xls, file 2_2.xls, file 3_3.xls ...?

[Cris LaPierre]

You just need to combine my code for a single file into a for loop that loops through every file. You already wrote code for that.

[Alexander]

@Cris LaPierre, I opened the TEST_BEFORE.xls with Excel and I got what I attached as jpg. Very confusing. I'll take care of that in future. Thank you.

[Cris LaPierre]

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]

That's it. I opened it in an editor and got now the same data. Thanks again.