William Matsuoka
  • Introduction
  • Topics
  • Stata
  • Teaching
    • ECON 641L
    • ECON 640L >
      • Econ-Data
  • Blog
  • About

W=M/Stata

Welcome

Putexcel 14.1

11/29/2015

12 Comments

 
With the release of Stata 14.1, Stata made a good amount of changes to one of my favorite commands: putexcel – I have mixed feelings about this. 

First, I want to take the opportunity to stress version control.  Always begin your do-files with the version command.  If you don’t, you’ll likely get the error r(101) “using not allowed” when trying to implement your old putexcel commands in a do-file.  A quick note about version: you must run this command with the putexcel statement.  Just having it at the top of your do-file and calling it doesn’t work, unless you run the do-file all the way through.  Think of using version control like using a local macro. 

​What I like:

​Putexcel has been simplified…
…but it requires changing the way you think about putexcel.  Think of the command in a more modular structure.  
  1. Set your Excel file that you wish to beautify
  2. Put all your text/data into the spreadsheet
  3. Format blocks of like things

Example:
putexcel set MyBook.xlsx, sheet("MyData")
putexcel A1=("Title of Data") A2=("Subtitle") A3=formula("SUM(A4:A20)"), bold
putexcel A3:A20, nformat(number_sep) hcenter
.
The updated options simplify your code a bit:
No more bold(on), rather just bold or nobold and there’s no need for that modify command.

Update: as of the December 21st update, modify is an option again...

Advanced number formats may be used:
No more restrictions, the advanced number format can now be used and can be found under putexcel advanced – which makes sense since it was able to be used already in the Mata xl() object.
Picture
putexcel set MyBook.xlsx, sheet("MyData")
putexcel A3, nformat(#.000) nobold border(all)

What I don't like:

Using not allowed!  I liked the using statement within putexcel, especially when I needed to add something as simple as a title on an excel spreadsheet.  Now, I have to use two lines of code instead of one.  This is a minor complaint compared to the next section.

The new syntax – it’s very difficult to work with large files, which is why I’ll be sticking with my “version 14.0” command when writing putexcel statements to large excel files.  Why?  I cannot find a simple way to write different format-types in one statement (say bold font in cell A1 and a border around cell B2).  I have to do this in two separate commands.  This is a big problem when you’re working with large excel files because Stata keeps opening the file, saving it, and closing the file for EVERY putexcel statement.  We could waste minutes writing borders and adding different colors.  Granted, Excel files shouldn’t be 40MB but we have them; and management wants to keep them that way, putting the Stata programmer between a rock and a hard place.  Check out my previous post on “block” putexcel for working with very large excel files.
​

What I hope happens:

We get the best of both worlds – the putexcel set keeps the Excel file open, so that it doesn’t continuously save file after file but the new format stays.  If we have a putexcel set, why not putexcel close?  Oh, and using is added back as an optional/hidden feature of putexcel.  That, my friends, would be my ideal putexcel command: not too hot, not too cold; simple, yet powerful.
12 Comments
Tim
1/4/2016 03:37:15 am

Will - I've got a question about your beef with the new, 2-line-requiring syntax vs. the old single-line syntax. If I'm understanding your statement properly, Stata opens/modifies/saves the Excel file in question each time an instance of -putexcel- is run.

I'm actually running into a problem precisely because (as far as I can tell) that is NOT how Stata is treating my spreadsheet...and when I look at the Excel file's properties in Windows Explorer, it shows a timestamp for the 'Date Modified' property that matches the time when the latest -putexcel- instance was run...but the 'Date Saved' property is several minutes prior.

Since I'm trying to use putexcel to make changes to some 'selector' cells in one tab of the spreadsheet which then trigger changes in the headers of a second sheet, the fact that 'saved' != 'modified' becomes a problem - a subsequent -import excel- command winds up hauling in the spreadsheet's contents from the time of the last 'save' rather than from the time of the last 'modification', even if several minutes have elapsed.

Do you know if there's some sort of "hard save" (forced save?) thing I can do via Stata that would force its memory to sort of refresh its memory?

Reply
Will link
1/4/2016 12:08:40 pm

Hello Tim, thank you for that question - I've been at it for a few hours now and it's quite frustrating (and is likely a Stata Support fix).

I was able to (somehow), get this same error - though I'm having trouble recreating it and it seems to be specific to the data contained within the corrupt workbook. I made a fresh non-corrupt workbook to compare using export excel on the auto dataset. For the non-corrupt workbook, Stata closes and saves the book properly after every putexcel command.

For the corrupt book, I use putexcel and even Mata's xl() command and, no matter what I try, I keep getting the "Date Modified"!="Date Last Saved" error. What makes me believe that it's workbook data specific is that once I copy all the worksheets in the corrupt book to the non-corrupt book, the non-corrupt book starts giving the same errors.

I am not aware of any "memory refresh" workarounds since the xl.close_book() class should automatically save the workbook after xl.set_mode("open") is specified. Once more, when set_mode is not specified, Stata saves the workbook after every command such as xl.put_string(1, 1, "test"). Unfortunately I don't have any other answers, but I will send my examples to Stata Tech Support.

The script below creates a "nice" workbook for me, and shows how - using this specification - Stata saves the file after each putexcel statement with a one minute lag.

sysuse auto, clear
export excel auto.xlsx, sheet("AutoData") firstrow(varl) replace
putexcel set auto.xlsx, sheet("AutoData") mod
putexcel A1:A5, font("Arial", 10)
* Look at the time stamp for save and modified
forvalues i = 1/12 {
sleep 5000
di "`=60-`i'*5' Seconds Left to look at your file"
}
putexcel A1:A5, font("Arial", 10)
* Look at the time stamp for save and modified again

Thanks,
Will

Reply
Tim
1/5/2016 01:11:48 am

With my deepest apologies for having sent you down an hours-long rabbit hole, I really appreciate the response. I would never have been able to troubleshoot that in the depth that you did - don't know enough about the inner workings of how Stata interacts with Office .***x files - but it's reassuring that you encountered the same behaviors.

I'm finding the blog extremely informative and helpful, by the way - thanks for putting out such great content! Next time I wind up back in San Diego (just left there following the holidays), I hope you'll let me buy you a beer or coffee or whatever your (non-ghost pepper) vice of choice happens to be.

Reply
Vicky
1/13/2016 08:00:32 am

Hi Will,

I'm just discovering putexcel after I created all of these matrices that I'd like to export. I've given these matrices row and column names using the matrix rownames/colnames commands, but when I go to use the putexcel matrix names export option STATA tells me that names are not found. below is a sample of my code. Any thoughts on why this might be happening?

loc o=1
loc m=1
foreach t in Component Weighted {
matrix overall_`t'= [m`t'_2011\m`t'_2012\m`t'_2013]
matrix rownames overall_`t' = 2011 2012 2013
matrix colnames overall_`t'= eval obs stPerception growth achievement tKnow/prof
matrix list overall_`t'
putexcel A`o'=matrix(overall_`t',names)

Reply
Vicky
1/13/2016 08:18:07 am

I should add that when I exclude the names export option the putexcel command works perfectly. However I need the row and col names bc i plan to make graphs and things in excel once I have them in there.

Thanks!
Vicky

Reply
Will link
1/13/2016 08:59:19 am

Hi Vicky,

Try moving the name statement from the matrix() command to options:

putexcel A`o'=matrix(overall_`t'), names

I like your way better - it seems more intuitive to have the - names - option embedded within the matrix expression. Please let me know if that didn't solve the issue.

Thanks!
Will

Vicky
1/13/2016 09:33:28 am

Thanks, Will! That did it. It's always the little things!

Marc
5/31/2017 08:16:09 am

Hi Will

I recently updated my Stata and first I did not know about the changes to the Putexcel command. I was supriesed when my "old" code didn't work anymore. I used putexcel to wirte certin values to preformated excel spredsheets e.g:


foreach sheetname of global sheets{

foreach y of num 0/5 {
result= some calculation...
putexcel `colname'3=( `result' ) using `"$myfile"' , sheet(`"`sheetname'"') modify keepcellformat
local ++i
}

local ++j
}

this code is very slow but it did the trick. Now I tried to achive simelar results by using the updated putexcel command

First change I did to write the results into a matrix isntead of writing it each time directly into the excel file (what is slow as you know). My Problem now with the new command is, that i cannot modify exicting spredsheets in excel


foreach sheetname of global sheets{
foreach thismatrix of global mnames{
putexcel set "test", sheet(`sheetname') modify
putexcel A1=matrix(`thismatrix')
}
}
error:
worksheet test could not be created
is there any way to achive what I want with the new command?

Thx already.

Reply
Will link
5/31/2017 11:30:53 am

Hi Marc,

Unfortunately, I tested your code snippet on my end and it seems to be working just fine. I can't say I've ever come across a "worksheet [sheetname] could not be created" error before except when the worksheet name was too long to conform to excel standards. Puzzling. This is what I ran:

cd c:\data

global sheets é test
global mnames A B

matrix A = 0, 0, 0, 0, 0
matrix B = 0 \ 8 \ 7 \ 6 \ 5

foreach sheetname of global sheets {
foreach thismatrix of global mnames {
putexcel set "test", sheet(`sheetname') modify
putexcel A1=matrix(`thismatrix')
}
}

Perhaps you could contact Stata Help with the issue. I'd also be happy to look at your excel file to see if it works on my computer if you'd like to send that to me.

Regards,
Will

Reply
Marc
6/1/2017 05:00:12 am

hi Will

Thank you for your quick answer. After a good night sleep and some testing with other Excelfiles/sheets. I figured out what the problem was. Honestly its a bit embarrassing... The sheetname was wirten only with capital letters.
I guess what happens is,that Excel spredsheet names are not case sensitive but for Stata they are.
e.g. if I creat a spredsheet in EXCEL called TEST and I try to use putexcel set filename, sheet(test) modify. Stata wants to creat a new sheet called "test" what excel doesn't allow.... leading to the error:
worksheet test could not be created

I am sorry having wasted your time with this issue since it was more or less a syntax error. However I thought some people might accidently will have the same trouble so I thought I post it here.

Regards
Marc

Caroline C
2/14/2018 10:07:39 am

Very helpful- Thank you!

Reply
Mia Wells link
1/2/2021 03:57:39 am

Hi thhanks for posting this

Reply



Leave a Reply.

    Author

    Will Matsuoka is the creator of W=M/Stata - he likes creativity and simplicity, taking pictures of food, competition, and anything that can be analyzed.

    For more information about this site, check out the teaser above!

    Archives

    July 2016
    June 2016
    March 2016
    February 2016
    January 2016
    December 2015
    November 2015
    October 2015
    September 2015

    Categories

    All
    3ds Max
    Adobe
    API
    Base16
    Base2
    Base64
    Binary
    Bitmap
    Color
    Crawldir
    Email
    Encryption
    Excel
    Exif
    File
    Fileread
    Filewrite
    Fitbit
    Formulas
    Gcmap
    GIMP
    GIS
    Google
    History
    JavaScript
    Location
    Maps
    Mata
    Music
    NFL
    Numtobase26
    Parsing
    Pictures
    Plugins
    Privacy
    Putexcel
    Summary
    Taylor Swift
    Twitter
    Vbscript
    Work
    Xlsx
    XML

    RSS Feed

Proudly powered by Weebly
  • Introduction
  • Topics
  • Stata
  • Teaching
    • ECON 641L
    • ECON 640L >
      • Econ-Data
  • Blog
  • About