how do i keep my format when exporting to csv or excel

I have a file:

    serialnumber    income
    '12491954158'    [21050]
    '23521956458'    [35235]
    '04311902557'    [59305]

i want to export both columns to either csv or excel including a third column containing the four middle numbers of the first column. i have tried the following:

fileID = fopen('export.csv','w');
        for i=2:length(data)
            serialnumber=data{i,1};
            income=data{i,2};
            birth=ssn(5:8);
            C=cell(1,3);
            C{1,1}=serialnumber;
            C{1,2}=income;
            C{1,3}=birth;
            fprintf(fileID,'%s; %d; %s; \n',C{1:3});

this gives me a desired output in excel

    12491954158    21050    1954
    23521956458    35235    1956
    4311902557     59305    1902

The problem is, that the serialnumbers which start with a zero looses the first number (e.g. last serialnumber above). can i do something about the format, so the number will be intact? and is it possible to export the columns as string, num, and string so i can import it again in the same format?

Answer 1

[Star Strider]

I would convert them to strings and save them to Excel as strings:

ID = 01123456789876;
IDstr = num2str(ID,'%014.0f')

IDstr =
    '01123456789876'

I doubt the ‘.csv’ format allows this, but Excel should.

[Chriss]

Like such?

fileID = xlswrite('export.xlsx');
        for i=2:length(data)
            serialnumber=num2str(data{i,1},'%014.0f');
            income=data{i,2};
            birth=num2str(ssn(5:8),'%014.0f');
            C=cell(1,3);
            C{1,1}=serialnumber;
            C{1,2}=income;
            C{1,3}=birth;
            fprintf(fileID,'%s; %d; %s; \n',C{1:3});
        end
end

[Star Strider]

Your xlswrite call is incorrect. See the documentation on xlswrite (link) for the proper syntax. You have to create the data first, then pass the data to xlsread. (The xlsread function will not create a file identifier that you can see or use.)

You need to change the format in the ‘IDstr’ assignment a bit:

IDstr = num2str(ID,'%011.0f');

since I initially miscounted the digits.

The ID numbers appear to be strings in your original file, so reading them in as a string rather than a numerical variable could solve that problem from the outset, avoiding the need to do the conversion. Excel should import them correctly. If you are using textscan, you need to use the '%s' format descriptor for that column.

If you read them in as a numeric variable, then you will have to do the conversion before you write to the Excel file.

You will have to experiment a bit to get the result you want.

Answer 2

[Guillaume]

It's not clear in your example where the ssn variable comes from.

Anyway, the root of your problem is not with matlab but with excel. Your code writes the strings fine, with the leading zero. The problem is that excel, upon reading the file, detects that the text is actually a number and automatically converts its format to numeric which strips leading zeros. The problem happens whether you save your file as csv or directly as xls(x) and whether or not you enclose your strings in quotes.

There's no easy workaround as that's the way Excel works. You can either:

• use the text import wizard in excel (under the data tab) and manually force the type of the first column to text in step 3 of the wizard
• use COM (through actxserver) to manipulate excel directly, inserting your data in a spreadsheet and forcing the format of the first column to text. Effectively, you'd be rewriting xlswrite.
• live with the loss of leading zero. In excel, you could afterward change the format of the column to custom with 00000000000 as the format. Excel would then automatically pad shorter numbers with 0 so all have 11 digits.

Note: a much easier way to write your csv (or xlsx) file would be:

t = cell2table(data, 'VariableNames', {'serialnumber', 'income'});  %convert to table
t.birth = extractBetween(t.serialnumber, 5, 8);

%write csv, non-quoted text
tablewrite(t, 'somefile.csv')

%write csv, quoted text
tablewrite(t, 'somefile.csv', 'QuoteStrings', true)

%write xlsx
tablewrite(t, 'somefile.xslx')

%in any of these, if you don't want the header
tablewrite(t, 'somefile.csv', 'WriteVariableNames', false)

But as said, none of this is going to fix your issue which is solely down to Excel.

[Chriss]

Hi Guillaume, thanks for your answer. will look into your rewrite of the csv. If the fail happens in excel, shouldn't i then keep the 0's if i export the file and then import it to matlab again? I have already tried this as part of my bug-finding process and the 0's are still gone.

[Guillaume]

If you use my second example (with 'QuoteStrings', true), then matlab has no problem reading back the leading zeros with

t = readtable('somefile.csv')