Search All of the Math Forum:

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

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

 Messages: [ Previous | Next ]
 Peter Perkins Posts: 156 Registered: 8/12/11
Re: Exporting Dataset to Excel
Posted: Mar 20, 2013 5:13 PM

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
> 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

Date Subject Author
3/19/13 Kevin Ellis
3/20/13 Peter Perkins
3/21/13 Kevin Ellis