How can I convert a file containing rows with different number of columns to a .csv format ?

Dear all,

In order to use a software that works with .csv files, I am trying to convert my data from Excel to CSV format. I have 17 different files then doing it manually wouldn't be a huge loss of time but I cannot because when I save them as .csv files, it chooses a number of columns equal to the maximum row length in the file and the end of the rows that are smaller are considered as missing data and thus filled with commas.

Ex:

0 1 2 3 4 5 6 7 8 9 10 => 0,1,2,3,4,5,6,7,8,9,10

0 1 2 3 4 5 => 0,1,2,3,4,5,,,,,,

0 1 2 3 4 5 6 => 0,1,2,3,4,5,6,,,,

I am thus trying to do it with matlab but my problem comes from the fact that my files contain:

- text, numbers and empty cells

- rows with different number of columns

... because it's the way data have to look like to be read by the software.

I do not manage yet to make it work with the xlsread/csvwrite commands because the structure is destroyed (Text, number and empty cells are separated) and I face the same kind of problems as cited above ('NaN,NaN,NaN,NaN' at the end of the smaller rows).

If someone already encountered that kind of problem, I would be very pleased to read some insights !

Thank you very much in advance,

Emma

[dpb]

csvwrite isn't capable of anything but regular numeric output so it's out. Matlab doesn't have a packaged general csv tool, unfortunately. You could look at File Exchange and see if somebody has provided one already.

If not, to write it in Matlab is necessary to use low-level fprintf and formatting strings to write the file. If each file is a given format it's not too bad but if each file has a mixture of lines of the various content and length then the only way to do so would be on a line-by-line basis, parsing each line content to build/use the proper format for each.

If you already have the data in Excel and unless you're going to generate a bunch more in Matlab, it might in this case be more efficient to use Excel to export the file as .csv. You should be able to use ActiveX and control this from Matlab altho I'm not the one to be able to tell you the how, specifically for that...

Answer 1

[Emma Vilarem]

Thank you very much for your answer. I am afraid I'll have to do it on a line-by-line basis indeed. I am going to look to the ActiveX documentation, thank you for your suggestion !

[dpb]

Find an Excel support group for the specifics of what to send; I've not used it enough but what I always get totally bogged down in trying to get anywhere from the doc's alone...