Discover MakerZone

MATLAB and Simulink resources for Arduino, LEGO, and Raspberry Pi

Learn more

Discover what MATLAB® can do for your career.

Opportunities for recent engineering grads.

Apply Today

Thread Subject:
Exporting Dataset to Excel

Subject: Exporting Dataset to Excel

From: Kevin Ellis

Date: 19 Mar, 2013 19:48:05

Message: 1 of 3

Hello,

I have a question about exporting a dataset to Excel. My dataset is 51954x22 and I am using the export MatLab command to place the data into an Excel Spreadsheet. Fairly simple and easy to do. Now in my dataset I have a column name called CreditCardNumber with numerical data comprised of the last four digits of a credit card number with which the transaction was made. A sample excerpt is shown:

FFCFinal.CreditCardNumber(1:8)

ans =

    '2437'
    '2101'
    '7498'
    '0319'
    '0083'
    '1839'
    '5442'
    '2176'

Now the most annoying thing about MatLab is how data is imported and exported. In this case, I cannot simply use the export command to export my dataset without having to add leading apostrophes. Take the CreditCardNumber column for example. If I did not add a leading apostrophe prior to exporting I would receive the following:

    2437
    2101
    7498
    319
    83
    1839
    5442
    2176

In each cell in Excel. Thus, I would lose entries with leading zeros. So I have had to use the following command on many of the columns of data in the dataset to add the leading apostrophe and in this case, looks like this:

FFCFinal.CreditCardNumber = cellfun(@(x) strcat('''',x),...
            FFCFinal.CreditCardNumber,'UniformOutput',false);

Then I can export and everything works and looks right. However, the previous command increases my runtime tenfold. The analysis section of my code takes around 30 seconds and simply "intializing the data" for export takes 90 seconds. So my questions are:

1) Is there a way to export everything as a string using the export command without having to add apostrophes? I don't mind exporting the numerical data as a string.

2) If not, is there a faster way to add apostophes?

3) Is there a way to not have to add apostrophes at all?

I have been dealing with this frustrating problem for awhile, mainly by sitting around and letting my programs run. Just curious if there is a more efficient way to export/import data from Excel. Any help would be much appreciated. Thanks.

Kevin

Subject: Exporting Dataset to Excel

From: Peter Perkins

Date: 20 Mar, 2013 21:13:09

Message: 2 of 3

Kevin, I am no expert on Excel, but I think the behavior that you
describe is due to Excel. I've made a note to have this looked into.

In the meantime, using the built-in vectorization in strcat speeds
things up:

 >> digits = '0123456789';
 >> CreditCardNumber = cellstr(digits(randi(4,51954,4)));
 >> CreditCardNumber(1:4)
ans =
     '2330'
     '0222'
     '1222'
     '1210'
 >> tic, CreditCardNumber2 = cellfun(@(x)
strcat('''',x),CreditCardNumber,'UniformOutput',false); toc
Elapsed time is 7.070305 seconds.
 >> tic, CreditCardNumber2 = strcat('''',CreditCardNumber); toc
Elapsed time is 0.352959 seconds.

Hope this helps.


On 3/19/2013 3:48 PM, Kevin Ellis wrote:
> Hello,
>
> I have a question about exporting a dataset to Excel. My dataset is
> 51954x22 and I am using the export MatLab command to place the data into
> an Excel Spreadsheet. Fairly simple and easy to do. Now in my dataset I
> have a column name called CreditCardNumber with numerical data comprised
> of the last four digits of a credit card number with which the
> transaction was made. A sample excerpt is shown:
>
> FFCFinal.CreditCardNumber(1:8)
>
> ans =
> '2437'
> '2101'
> '7498'
> '0319'
> '0083'
> '1839'
> '5442'
> '2176'
>
> Now the most annoying thing about MatLab is how data is imported and
> exported. In this case, I cannot simply use the export command to export
> my dataset without having to add leading apostrophes. Take the
> CreditCardNumber column for example. If I did not add a leading
> apostrophe prior to exporting I would receive the following:
>
> 2437
> 2101
> 7498
> 319
> 83
> 1839
> 5442
> 2176
>
> In each cell in Excel. Thus, I would lose entries with leading zeros. So
> I have had to use the following command on many of the columns of data
> in the dataset to add the leading apostrophe and in this case, looks
> like this:
>
> FFCFinal.CreditCardNumber = cellfun(@(x) strcat('''',x),...
> FFCFinal.CreditCardNumber,'UniformOutput',false);
>
> Then I can export and everything works and looks right. However, the
> previous command increases my runtime tenfold. The analysis section of
> my code takes around 30 seconds and simply "intializing the data" for
> export takes 90 seconds. So my questions are:
>
> 1) Is there a way to export everything as a string using the export
> command without having to add apostrophes? I don't mind exporting the
> numerical data as a string.
>
> 2) If not, is there a faster way to add apostophes?
>
> 3) Is there a way to not have to add apostrophes at all?
>
> I have been dealing with this frustrating problem for awhile, mainly by
> sitting around and letting my programs run. Just curious if there is a
> more efficient way to export/import data from Excel. Any help would be
> much appreciated. Thanks.
>
> Kevin

Subject: Exporting Dataset to Excel

From: Kevin Ellis

Date: 21 Mar, 2013 15:39:06

Message: 3 of 3

Peter Perkins <Peter.Remove.Perkins.This@mathworks.com> wrote in message <kid8p5$i1l$1@newscl01ah.mathworks.com>...
> Kevin, I am no expert on Excel, but I think the behavior that you
> describe is due to Excel. I've made a note to have this looked into.
>
> In the meantime, using the built-in vectorization in strcat speeds
> things up:
>
> >> digits = '0123456789';
> >> CreditCardNumber = cellstr(digits(randi(4,51954,4)));
> >> CreditCardNumber(1:4)
> ans =
> '2330'
> '0222'
> '1222'
> '1210'
> >> tic, CreditCardNumber2 = cellfun(@(x)
> strcat('''',x),CreditCardNumber,'UniformOutput',false); toc
> Elapsed time is 7.070305 seconds.
> >> tic, CreditCardNumber2 = strcat('''',CreditCardNumber); toc
> Elapsed time is 0.352959 seconds.
>
> Hope this helps.
>
>
> On 3/19/2013 3:48 PM, Kevin Ellis wrote:
> > Hello,
> >
> > I have a question about exporting a dataset to Excel. My dataset is
> > 51954x22 and I am using the export MatLab command to place the data into
> > an Excel Spreadsheet. Fairly simple and easy to do. Now in my dataset I
> > have a column name called CreditCardNumber with numerical data comprised
> > of the last four digits of a credit card number with which the
> > transaction was made. A sample excerpt is shown:
> >
> > FFCFinal.CreditCardNumber(1:8)
> >
> > ans =
> > '2437'
> > '2101'
> > '7498'
> > '0319'
> > '0083'
> > '1839'
> > '5442'
> > '2176'
> >
> > Now the most annoying thing about MatLab is how data is imported and
> > exported. In this case, I cannot simply use the export command to export
> > my dataset without having to add leading apostrophes. Take the
> > CreditCardNumber column for example. If I did not add a leading
> > apostrophe prior to exporting I would receive the following:
> >
> > 2437
> > 2101
> > 7498
> > 319
> > 83
> > 1839
> > 5442
> > 2176
> >
> > In each cell in Excel. Thus, I would lose entries with leading zeros. So
> > I have had to use the following command on many of the columns of data
> > in the dataset to add the leading apostrophe and in this case, looks
> > like this:
> >
> > FFCFinal.CreditCardNumber = cellfun(@(x) strcat('''',x),...
> > FFCFinal.CreditCardNumber,'UniformOutput',false);
> >
> > Then I can export and everything works and looks right. However, the
> > previous command increases my runtime tenfold. The analysis section of
> > my code takes around 30 seconds and simply "intializing the data" for
> > export takes 90 seconds. So my questions are:
> >
> > 1) Is there a way to export everything as a string using the export
> > command without having to add apostrophes? I don't mind exporting the
> > numerical data as a string.
> >
> > 2) If not, is there a faster way to add apostophes?
> >
> > 3) Is there a way to not have to add apostrophes at all?
> >
> > I have been dealing with this frustrating problem for awhile, mainly by
> > sitting around and letting my programs run. Just curious if there is a
> > more efficient way to export/import data from Excel. Any help would be
> > much appreciated. Thanks.
> >
> > Kevin

Peter,

Thanks for the response. This will reduce my runtime significantly, considering the many times I need to add apostrophes. As you said, the issue with exporting could be because of Excel, but as I mentioned before a majority of my program's runtime is either importing or exporting to and from Excel. Mainly due to changing the imported data type from a numerical array to a string array or exporting data with apostrophes added so Excel will not remove leading zeros from important data fields.

I have tried everything from "pre-formatting" the Excel spreadsheet to adding apostrophes to make this work. Always searching for a better solution and so far youj provided me with one that at least reduces runtime. Thanks.

Kevin

Tags for this Thread

What are tags?

A tag is like a keyword or category label associated with each thread. Tags make it easier for you to find threads of interest.

Anyone can tag a thread. Tags are public and visible to everyone.

Contact us