The following Applescript will convert a folder you select from Excel files to Excel XML files (XLS to XLSX).  You must have an Excel version installed that supports XLSX obviously.

set theFolder to choose folder with prompt "Choose the folder that contains your Excel files"
tell application "Finder" to set theFiles to (files of theFolder)
set fileCount to count theFiles
repeat with i from 1 to fileCount
	set fName to text 1 thru -5 of ((name of item i of theFiles) as text)
	if ((name of item i of theFiles) as text) ends with ".xls" then
		set tName to (theFolder as text) & fName & ".xlsx"
		tell application "Microsoft Excel"
			activate
 
			open (item i of theFiles) as text
 
			tell active workbook
				save workbook as filename tName file format Excel XML file format with overwrite
			end tell
 
			close active workbook without saving
		end tell
	end if
end repeat

Background

I own a small company that specializes in obtaining information from public and proprietary sources.  We then take this information and try to make it easier for the average user to browse and work with.  For this service, subscribers pay a small monthly fee.  Most of the information we work with is is related to Medicare and Medicaid financial data.

We have obtained the information we have requested from most of the U.S. states with very few problems arising.  We try to make it as easy as possible for the state agencies and offload most of the processing and parsing of the data to our developers.  The agencies usually comply within a week or two and send everything we asked for on a CD, or if they are really good they’ll send it through an online file transfer service.  Throughout all of these transactions, the worst thing that’s happened is a confused employee that wasn’t sure if they had the requested data.  That is, until contacting the Alabama Medicaid Agency.

The request for data from Alabama began as they have for all the other states.  As with all the other states, it’s unusually difficult to find the right person to talk to in order to get the data you want.  Usually after 10-15 minutes of referrals or continuous promises to have someone call you back, you finally find the right person.  After finding the right person, you still have to ask for the data the way they are accustomed to referring to it, which of course I don’t know initially. I finally found the person in charge of nursing home auditing, Keith Boswell, who was also in charge of the data I wanted to request.  This of course would be the beginning of a 3-4 month ordeal that is still unresolved and I believe there have been many violations of the Alabama Open Records Act in the Alabama Code throughout this entire period.

I’m going to now provide as much detail as possible about these 3-4 months, from which it should be obvious that there has been a violation of Alabama law as well as plenty of government inefficiency and waste.

(more…)

I recently received some data in the Advantage Database Server file format (.adt). In the past I have worked with DBF, Access, and .xls. I was able to find some way to retrieve data from these formats using open source software. The open source solutions were much easier to work with, usually not requiring any sort of driver installation, etc.

I searched thoroughly for an easy way to retrieve information from this set of .ADT files I was dealing with. Sybase provides tools to work with the data, but all of them require using their software or drivers developed by them. I would have settled for this if it wasn’t such a hassle to figure out how to use them. I asked the question on Stackoverflow.com and got an answer from one of the employee’s of Sybase. While helpful, the suggested Perl driver was one of the things I was avoiding. I did attempt it and after a little hassle, moved on to creating a simpler solution.

(more…)

Some of the data I’ve been dealing with lately is in Microsoft Access databases (.mdb files). I’ve been surprised with the lack of support for manipulating these files outside of Access. There are a couple solutions, but nothing satisfied me needs. The worst data set came to me in a few hundred different .mdb files. It looked like the person that exported the database exported a copy of the database for each unique facility. So each facility had a file, but the schema for each file was exactly the same. It was apparent that in order to do any sort of complex queries on the data, the files would have to be combined. I was recommended a tool called mdbtools by somebody at a local Ruby meetup. I ended up using this along with Ruby inside of a Rake task to convert all my .mdb files into a single MySQL database. (more…)

The content of my main web application, Myhealthcaresource, will contain more than 15000 detailed financial reports for nursing facilities at its future peak.  Each of these reports contains textual information as well as monetary values.  It might list administrator names, employee names/salaries, owners, products or services purchased.  I wanted to have all of this information searchable on Google, without Google caching the page.  I also wanted to let users who came from Google as a result of searching for this information see it without having to log in, but only the page that they found through Google search. (more…)