Search All of the Math Forum:
Views expressed in these public forums are not endorsed by
NCTM or The Math Forum.



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



