Drexel dragonThe Math ForumDonate to the Math Forum



Search All of the Math Forum:

Views expressed in these public forums are not endorsed by Drexel University or The Math Forum.


Math Forum » Discussions » Software » comp.soft-sys.matlab

Topic: Exporting Dataset to Excel
Replies: 2   Last Post: Mar 21, 2013 11:39 AM

Advanced Search

Back to Topic List Back to Topic List Jump to Tree View Jump to Tree View   Messages: [ Previous | Next ]
Peter Perkins

Posts: 111
Registered: 8/12/11
Re: Exporting Dataset to Excel
Posted: Mar 20, 2013 5:13 PM
  Click to see the message monospaced in plain text Plain Text   Click to reply to this topic Reply

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





Point your RSS reader here for a feed of the latest messages in this topic.

[Privacy Policy] [Terms of Use]

© Drexel University 1994-2014. All Rights Reserved.
The Math Forum is a research and educational enterprise of the Drexel University School of Education.