Opening old Excel files in STATA 12

I ran into some importing old Excel files into STATA 12.  Since I thought others would probably be encountering the same problem, I decided to write a blog post about it.

We’re getting ready to produce a draft release of our China Multigenerational Panel Dataset – Shuangcheng (CMGPD-SC) so that users can kick the tires and report problems before we submit a final version to ICPSR for dissemination there.

As part of the preparation, we wanted to take advantage of the new facility in Stata 12 that allows Excel files to be opened directly.  Our ‘raw’ data consist of Excel spreadsheets entered by our coders, one per register.  Registers are annual or triennial.  For our Liaoning dataset, we have 737 registers coded.  For Shuangcheng, we have 338.  Previously, our procedures for automating the import of the registers in Stata were clumsy, and rarely survived upgrades to Stata or Windows.  At one point we were using the odbc command to loop through and read all the registers, but that broke when we moved to computers that were running 64 bit windows.  Then we wrote a macro to loop through the Excel files and write them to tab-delimited text fields, which STATA could read.

Converting our programs to use import excel was fairly straightforward.  Basically it just mean substituting import excel for insheet.

When we began running the programs, however, STATA was reporting that it could not load files, and came back with an r(603).  I did notice it could open all .xlsx files, but had more trouble with .xls files.  I began to wonder if the problem was with older versions of Excel files.  Perhaps the import capability assumed a recent version of Excel.  I saved some of the files as .xlsx files and sure enough, STATA could read them.

At that  point, it became necessary to convert the thousand or so files that were in older versions of Excel to .xlsx files.  Opening them one by one and saving them to .xlsx would be impractical.

I poked around on the net, and found that Microsoft had an Office File Converter tool available for download.   Here is an introduction and here is the download.  The tool requires that the Microsoft Office Compatibility Pack be installed.  By modifying the ofc.ini file, and adding the name of a folder under [FoldersToConvert] it is possible to direct OFC to attempt to convert all the old .xls files it finds in a specified folder to .xlsx.

fldr=C:UsersCameronDropboxSharedSkydriveCMGPD DataLN

Here is what my [ConversionInfo] section ended up looking like:


I ran ofc and sure enough, it chugged through the files and converted them and placed them in a directory under the original folder that was called Converted.

Now Stata is happily chewing through the converted files.




Relocated my blog

I relocated my blog from Google’s Blogger to WordPress.  The transition was pretty smooth.  However, I didn’t see an easy way to have WordPress Permalinks for blog entries match the ones at Blogger.  Blogger caps URL length, thus truncates the title of the blog entry in the URL if it is too long.  Wordpress doesn’t, so the URL includes the entire title of the blog entry.  This means that links to specific posts are now broken.  If you arrived here by following a link to a specific post, you can probably find it by entering some of the words from the URL into the search box.

I relocated my blog to ensure that it was accessible in China.  In China, access to my Blogger hosted blog was erratic.  Some people could access it in its entirety, some couldn’t access it at all, and some people could access it, but the posts were broken up and missing content.  I decided to relocate it to a hosted website at GoDaddy that has its own unique and stable IP address.

At the same time, I also moved over my personal website.  Previously I had it hosted at Google Sites.  It was inaccessible, or only occasionally accessible, in China.  Now it is also at my GoDaddy hosted site.  In case anyone it is interested, I am using Joomla.  So far I like Jooma.

Mobile computing, and data security

I wanted to write about how I try to keep my computer and my data secure when I’m on the road.

Most importantly, I recommend TrueCrypt for any laptop or external drive.  This is a free, open-source encryption utility. It allows for encryption of an entire system drive, requiring a password to boot. Whenever I get a new laptop, I encrypt the entire drive immediately.  This is very nice since it means that if your laptop is stolen, whoever has it will find it almost impossible to access anything on the drive, unless they also have you along with the laptop and can persuade you to give up the password. It should also be protection against drive cloning.  If you leave your laptop in the hotel  while you are travelling and someone from the security services comes in and gets your laptop and clones the hard drive using a purpose-built device they have brought with them and then replaces it, when they get the cloned drive back to their offices, it shouldn’t be very useful to them.  It also means that when it finally comes time to dispose of a  laptop, you can give it to a electronics recycler without worrying that one of their employees will gain access to your hard drive.  I’ve been using TrueCrypt for years and it doesn’t seem to degrade performance or otherwise cause problems.  In my experience, it has been rock solid.  I have donated money to TrueCrypt, and hope you will do so as well.

You can also encrypt removable storage, external hard drives and so forth with TrueCrypt.  This may be even more important.  I don’t use external storage or removable storage as much as I used to, but I do encrypt all of it.  Again, this means that if I lose my external hard drive or a thumb drive, I don’t really have to worry about it.  Of course, it is probably a good idea to keep at least one unencrypted thumb drive on hand for presentations and so forth.

Needless to say, I use a VPN.  If your employer offers a VPN, USE IT ALL THE TIME WHEN YOU ARE NOT IN YOUR OFFICE OR HOME.  I am fortunate that UCLA offers a VPN.  Obviously it comes in handy for accessing UCLA resources.  But it also offers security when I’m away from the home and office, and simply need to access websites.  The UCLA VPN seems to work pretty well in China, except when I try to connect on my Android cell phone via China Mobile.  It certainly works pretty well from  my laptop.  I think there are commercial VPN services available but I am not very familiar with them.  I did try one of them but was not satisfied with the results.

I also have two-factor authentication turned on at every service that offers it.  IF TWO-FACTOR AUTHENTICATION is available, TURN IT ON.  Gmail and Facebook both offer two-factor authentication.  If you log in from an unrecognized computer, you are required not only to enter your password (hopefully something more secure than 123456789 or PASSWORD) but a numeric code from an application running on your smartphone.  This should dramatically reduce the chances that someone could gain unauthorized access to either account.  Of course, if you lose your cellphone, and you haven’t secured it with a PIN or a pattern, you may be in trouble.  But hopefully whoever swipes or finds your cellphone won’t also know your password.  Unless the reason they have your cellphone is that they are holding you at gunpoint.  I went ahead and deleted almost all the email accounts I had on services that do not yet offer two-factor authentication.  I wish more sites, especially banks, would switch to two-factor authentication.

As much as possible, I only use sites like GMail and Facebook that require https (HTTP over SSL) for all interaction with the site.  For email or any other application where I am transmitting potentially important information, I try to avoid any site that doesn’t require https for I don’t know how secure https really is, but it has to be better than an unencrypted connection.

For remote access to files at my office and home, I used to use LogMeIn Hamachi to create a personal VPN that links together my office and home machines. I access shared folders on those machines, and leave most of my documents, media, and data there, to be accessed and worked with remotely over the VPN. Hamachi in my experience has been extremely robust, and folder sharing across the VPN works very smoothly in Vista and Windows 7. Folder sharing was more erratic under XP but I think that was a problem with XP, not Hamachi.  Increasingly, however, I just use various sync software, like Dropbox..

For deleting files permanently on the laptop, I use Eraser, an open-source data removal utility. Remember that when you delete files there are still traces left on the hard drive, and a utility like Eraser should wipe them out completely.  If you’re disposing of a computer and want to wipe the hard drive, you should also look at Darik’s Boot and Nuke, which allows you to wipe out a system drive.

One minor issue related to data security with synchronization programs like Dropbox: keep in mind that anything you share is only as secure as the least protected computer you share with.  If you share a folder with someone, anyone who gains access to their computer can access whatever you have shared with them.  Since Dropbox and some other services don’t yet allow ‘read only’ shares, they can not only read whatever you have shared with them, but modify it or delete it.  If you find out in time that you can recover originals from Dropbox, that mitigates the risk somewhat, but when sharing, think about how well secured the computers are of whoever you share with.  Is that person someone you trust to use a strong password, run virus protection and firewall, and otherwise secure your computer?  Along those lines, don’t install Dropbox or other sync software on any of your own computers unless you feel that you can secure them.  Speaking of Dropbox, I see that they just added two-factor authentication!