deleting the last row of csv file

Hello. I have a large csv file with header information. I simply wish to delte the last row,replacing it as blanks and resave the file with the same name. I would have thought this was easy but have had a devil of a time doing this. Ideally, I would like a method to work on both Mac and PC platforms, so not sure how well suited xlswrite is (or if that previously nonfunctionality on Macs is no longer an issue)—but would want ultimately to merely go to that last row and delte it. I would need to keep everything else in the the same, including the header info and .csv format. Thank you in advance.

Answer 1

[dpb]

If have R2020b or later, the simplest portable coding solution would be

data=readlines('yourfile.csv');
data(end)="";
writematrix(data,'yourfile.csv')

Try as above, you may want/need the 'QuoteStrings',0 name-value pair argument; it is supposed to be off by default for text files, but I've found that isn't always true; it does quote strings on its own volition. I've not yet been able to discern the condition(s) under which that occurs enough to be able to predict when it will/will not produce the unexpected result so end up either setting it to be sure or by just waiting to see what it does and then fix it if needs must...

[Walter Roberson]

This is indeed the easiest way these days.

However, you need to be careful about what the "last" line of a file is. You have to ask whether newline (possibly proceeded by carriage return) is the line terminator or if it is the line separator .

ABC<newline>
DEFG<newline>

If <newline> is the line terminator then the "last" line here would be the DEFG<newline> . But if <newline> is the line separator then the "last" line here would be the emptiness between the second <newline> and the end of file.

What does computing history say?

Computing history for MacOS and Linux has been clear: for them, the <newline> has always been defined as a line separator -- so the emptiness between the <newline> and the end of file would be the "last" line for them.

DOS originated as a modified version of CP/M, which defined the end of file for text as being the location of the first control-Z (character code 26). DOS continued that in the days before it started recording file sizes. Historically, it was perfectly legal to have, for example, DEFG<control-z> and the DEFG part would be the last line. But DOS evolved through a period in which <carriage return><newline> was treated as the line terminator and text files that ended part way through the line without the terminator were considered to be broken and in need of fixing. But then afterwards, at some point after DOS had become Windows, Windows changed the convention so that <newline> without <carriage return> became the standard, with the <newline> being classed as a line separator and with <carriage return><newline> line terminator being continued to be supported for backwards compatibility.

All of which is leading to the fact that in the above code, if data(end) is already empty after the readlines(), then you probably want to delete it as being the emptiness between the last <newline> and end of file, and then proceed to work on the last line that remains.

[dpb]

I considered whether to use

data=readlines('yourfile.csv');
data=data(strlength(data)>0);
data(end)="";
writematrix(data,'yourfile.csv')

or not, Walter, and decided to wait and hear back first since there could potentially be meaningful blank records in the file...

[hxen]

Hi DB. First thanks for replying. I had considered the writematrix, but as mentioned I need to presever the header info and it seems writematrix messed things up. Moreover I tried your code and it did mess up the formating and still didn't get rid of the last line. I have attached here an example CSV file. I already have code to determine if the last row should be delteted based on whether it has zeros or not. In the sample I attached, I would then want to deltete line 14 all while preserving the formating and header information. Thanks again in advance.

[dpb]

fn='https://www.mathworks.com/matlabcentral/answers/uploaded_files/1117700/CSV_example.csv';
data=readlines(fn);
data(end)=[];
writematrix(data,'newfile.csv')
type('newfile.csv')

"scorer,DLC_mobnet_100_OFT_MasterMay5shuffle1_950000,DLC_mobnet_100_OFT_MasterMay5shuffle1_950000,DLC_mobnet_100_OFT_MasterMay5shuffle1_950000,DLC_mobnet_100_OFT_MasterMay5shuffle1_950000,DLC_mobnet_100_OFT_MasterMay5shuffle1_950000,DLC_mobnet_100_OFT_MasterMay5shuffle1_950000,DLC_mobnet_100_OFT_MasterMay5shuffle1_950000,DLC_mobnet_100_OFT_MasterMay5shuffle1_950000,DLC_mobnet_100_OFT_MasterMay5shuffle1_950000,DLC_mobnet_100_OFT_MasterMay5shuffle1_950000,DLC_mobnet_100_OFT_MasterMay5shuffle1_950000,DLC_mobnet_100_OFT_MasterMay5shuffle1_950000"
"bodyparts,Ear_left,Ear_left,Ear_left,Ear_right,Ear_right,Ear_right,Nose,Nose,Nose,Center,Center,Center"
"coords,x,y,likelihood,x,y,likelihood,x,y,likelihood,x,y,likelihood"
"0,695.0332031,341.4277344,0.999976039,631.4180908,374.0528564,0.999998212,633.3059082,299.3645935,0.999995112,718.5075684,412.7980042,0.999989867"
"1,695.0332031,909.8771362,0.999994397,631.4180908,877.765625,0.999995708,633.3059082,956.4130859,0.999999881,718.5075684,822.5265503,0.999999166"
"2,695.0332031,918.8446655,0.999998093,631.4180908,885.2223511,0.999999285,633.3059082,968.2403564,0.999999762,718.5075684,831.8912354,0.999992967"
"3,679.0130615,928.8504028,0.999999046,619.0322876,887.8853149,0.999997973,609.2219238,975.1612549,0.999999881,709.7404175,851.4117432,0.999999762"
"4,663.2994995,936.2693481,0.99999702,604.4048462,888.1544189,0.999996305,588.2171021,975.1612549,0.999998927,694.9490967,859.3980103,0.999996543"
"5,636.8436279,937.9779663,0.999996662,582.0732422,888.1544189,0.999998093,558.5767822,975.1612549,0.999999881,675.3751831,866.4855957,0.999999762"
"6,602.519104,937.9779663,0.999993086,561.0428467,888.1544189,0.999997258,527.0523682,960.8823242,0.999999881,663.0440674,871.9560547,0.999997735"
"7,570.4298706,939.3907471,0.999992847,550.6521606,881.6591797,0.99999702,503.1895142,947.4260864,0.999999881,651.6503906,874.0117188,0.999993682"
"8,543.6765137,941.3130493,0.999996424,524.6485596,881.6591797,0.999991655,482.1900024,945.1132202,0.999999881,633.796875,876.1392212,0.999984741"
"9,513.6217041,944.1096802,0.999999046,499.8921814,881.6591797,0.999999642,462.3742371,943.9442749,0.999999523,605.8339844,887.8470459,0.999976397"

OK, this is one of the cases where the default 'QuoteStrings' name-value pair that is supposed to be 'false' by default isn't -- I mentioned you might find this out in the original Answer. You can force it by using

writematrix(data,'newfile.csv','QuoteStrings',false)
type('newfile.csv')

scorer,DLC_mobnet_100_OFT_MasterMay5shuffle1_950000,DLC_mobnet_100_OFT_MasterMay5shuffle1_950000,DLC_mobnet_100_OFT_MasterMay5shuffle1_950000,DLC_mobnet_100_OFT_MasterMay5shuffle1_950000,DLC_mobnet_100_OFT_MasterMay5shuffle1_950000,DLC_mobnet_100_OFT_MasterMay5shuffle1_950000,DLC_mobnet_100_OFT_MasterMay5shuffle1_950000,DLC_mobnet_100_OFT_MasterMay5shuffle1_950000,DLC_mobnet_100_OFT_MasterMay5shuffle1_950000,DLC_mobnet_100_OFT_MasterMay5shuffle1_950000,DLC_mobnet_100_OFT_MasterMay5shuffle1_950000,DLC_mobnet_100_OFT_MasterMay5shuffle1_950000
bodyparts,Ear_left,Ear_left,Ear_left,Ear_right,Ear_right,Ear_right,Nose,Nose,Nose,Center,Center,Center
coords,x,y,likelihood,x,y,likelihood,x,y,likelihood,x,y,likelihood
0,695.0332031,341.4277344,0.999976039,631.4180908,374.0528564,0.999998212,633.3059082,299.3645935,0.999995112,718.5075684,412.7980042,0.999989867
1,695.0332031,909.8771362,0.999994397,631.4180908,877.765625,0.999995708,633.3059082,956.4130859,0.999999881,718.5075684,822.5265503,0.999999166
2,695.0332031,918.8446655,0.999998093,631.4180908,885.2223511,0.999999285,633.3059082,968.2403564,0.999999762,718.5075684,831.8912354,0.999992967
3,679.0130615,928.8504028,0.999999046,619.0322876,887.8853149,0.999997973,609.2219238,975.1612549,0.999999881,709.7404175,851.4117432,0.999999762
4,663.2994995,936.2693481,0.99999702,604.4048462,888.1544189,0.999996305,588.2171021,975.1612549,0.999998927,694.9490967,859.3980103,0.999996543
5,636.8436279,937.9779663,0.999996662,582.0732422,888.1544189,0.999998093,558.5767822,975.1612549,0.999999881,675.3751831,866.4855957,0.999999762
6,602.519104,937.9779663,0.999993086,561.0428467,888.1544189,0.999997258,527.0523682,960.8823242,0.999999881,663.0440674,871.9560547,0.999997735
7,570.4298706,939.3907471,0.999992847,550.6521606,881.6591797,0.99999702,503.1895142,947.4260864,0.999999881,651.6503906,874.0117188,0.999993682
8,543.6765137,941.3130493,0.999996424,524.6485596,881.6591797,0.999991655,482.1900024,945.1132202,0.999999881,633.796875,876.1392212,0.999984741
9,513.6217041,944.1096802,0.999999046,499.8921814,881.6591797,0.999999642,462.3742371,943.9442749,0.999999523,605.8339844,887.8470459,0.999976397

If your're computing a row to delete logically, can use the index into the array as logical indexing expression or actual line number(s) to delete besides just end as the subscripting expression.

Remember, however, if you delete multiple rows that must either do so in one subscripting expression or from last to least. If otherwise, deleting a row prior to another that is to be removed will change the indexing into all those following the first.

[hxen]

Thank you DPB. I appreciate your time and feedback!