http://www.mathworks.com/matlabcentral/newsreader/view_thread/327687
MATLAB Central Newsreader  Exporting Dataset to Excel
Feed for thread: Exporting Dataset to Excel
enus
©19942015 by MathWorks, Inc.
webmaster@mathworks.com
MATLAB Central Newsreader
http://blogs.law.harvard.edu/tech/rss
60
MathWorks
http://www.mathworks.com/images/membrane_icon.gif

Tue, 19 Mar 2013 19:48:05 +0000
Exporting Dataset to Excel
http://www.mathworks.com/matlabcentral/newsreader/view_thread/327687#900410
Kevin Ellis
Hello,<br>
<br>
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:<br>
<br>
FFCFinal.CreditCardNumber(1:8)<br>
<br>
ans = <br>
<br>
'2437'<br>
'2101'<br>
'7498'<br>
'0319'<br>
'0083'<br>
'1839'<br>
'5442'<br>
'2176'<br>
<br>
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:<br>
<br>
2437<br>
2101<br>
7498<br>
319<br>
83<br>
1839<br>
5442<br>
2176<br>
<br>
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:<br>
<br>
FFCFinal.CreditCardNumber = cellfun(@(x) strcat('''',x),...<br>
FFCFinal.CreditCardNumber,'UniformOutput',false);<br>
<br>
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:<br>
<br>
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.<br>
<br>
2) If not, is there a faster way to add apostophes?<br>
<br>
3) Is there a way to not have to add apostrophes at all?<br>
<br>
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.<br>
<br>
Kevin

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

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