2.2 Applescripts for xls/csv files
5.2008 - Applescripts to convert Excel-Datasets in .csv-files and to bring them in a form that is suitable for an import (insheet) in Stata
Here you can find several applescripts that might help you to work with data-files. They are tested on Mac OS X Leopard (10.5).
Open them in the Script-Editor and have a look at the code to understand in full detail what they do. Applescript can also be saved as an application such that a double-click is enough to run them. For sure you need to adjust the first few lines. They are written for a special problem I had with a dataset but for sure can be modified to be helpful in other situations.
You need the application “TextWrangler”. It is available for free at http://www.barebones.com/. M$ Excel is necessary for the first two scripts. Stata is necessary to run the do file.
- 1_countrycodes.scpt
- Takes all files in a folder, opens them with M$ Excel, and adds a column with the filename as an entry. The files are then saved in another folder. In the examle: The names of the original Excel-Files are based on the country-code of the country they contain the data for.
- 2_xlsTOcsv.scpt
- Takes all files in a folder, opens them with M$ Excel, and saves them as .csv-text-files (comma separated values). The files are then saved in another folder.
- 3_SearchReplace.scpt
- Takes all files in a folder, opens them on TextWrangler and runs several
search&replace procedures. Some of them use grep-expressions. The files are then saved in
another folder.
This is the most useful script and also the one that need the most modifications when applied in other situations. Note that TextWrangler is very “applescriptable” and that you can use the “record”-functionality in the Apple-Script-Editor while working in TextWrangler. - 4_merge-sort.scpt
- Puts the content of all files in a folder in one file, sorts al lines in this file alphabeticallly and then add a header in line 1 (containing the names of the columns).
- 5_split.scpt
- Takes the file from the last step and splits it in several single-files based on a list given in the script.
- X_deleteCSVFiles.scpt
- Deletes all files with extension .csv in a folder selected by the user. (Not used in my example.)
- stata.do
- A stata do-file that reads the .csv-files, converts them into single .dta-datasets and then merges them into one proper stata-dataset.
- Example-Dataset
- contains all folders and source-files necessary. Unpack the zip-file and save the folder example-data, for example to the Desktop. Check carefully all scripts for line that contain the path to this folder and adjust them accordingly. Run the applescript from 1 to 5. The run the do-file from within Stata.
Download all scripts and a folder with an example dataset as .zip-file, 228 KB.