If you do not see the menu on the left click here to see it

From Excel to Stata

The ‘quickie’ way

This is the easiest way to put data in Excel into Stata, just copy-and-paste. Select and copy in Excel and paste in Stata’s data editor.

Let’s check out one other dataset. Click here to download the data for this section. For convenience, save it in your H:\ drive in a folder name “statadata” (make it if you do not have it).

Var1	Area in Square Kilometers
Var2	Area in Square Miles
Var3	Population
Var4	Imports
Var5	Exports
Var6	Type of Regime

The excel file should look like this:

Once in excel, select the table, copy it (Ctrl-C or Edit - Copy) and paste it into the Stata editor by clicking in the data editor icon mark by the red arrow below or by typing edit in the command window.

If you get the following message just click OK. An go back to the “Data Editor” window.

The data editor looks like a spreadsheet (but it is not). Paste the data from excel. The first row will become the variable names. If the first row does not have variable names they will be named var1, var2, var2, etc.:

WARNING: When copy and pasting you may loose some data if numeric values have some string characters. If you compare the missing values in the picture above (cells with dots “.”) with the excel data they are actual values but with a letter attached. If you do not need this data then do nothing. If you do, the save the file as *.csv and import it into Stata (see the “insheet” section below)

IMPORTANT: Variable names must be in the first row. Select only the data you need (do not select the entire spreadsheet)

You should see the following in the main Stata screen.

If you want to label the variables type the following.

[IMPORTANT: You may have to re-write the quotations in Stata]

label variable var1 “Area in Square Km”

label variable var2 “Area in Square Mi”

label variable var3 “Population”

label variable var4 “Imports”

label variable var5 “Exports”

label variable var6 “Type of regime”

The variables window should look like this:

Type describe for a first look:

The insheet way

This is another more systematic way of importing data into Stata.

From Excel, save the data as *.csv (comma-separated-value or comma-delimited, which is basically ASCII). The first row should have the variable names (with no spaces).

Once saved in csv format, open Stata and type in the command line

insheet using "H:\statadata\\Banks\cnvselect.csv"

Or if you change the directory in Stata just type

insheet using cnvselect.csv

If you prefer to use the menu do the following:

In the dialog box click “Browse” then in “Files of type:” select the *.csv option and find your dataset. Once you find it click “Open” and then “Ok”.

Once you find it you should see the following, then press OK.

Your data is now in Stata format, remember to save it, type:

save testdata03, replace

Type:

browse

You will see your data in the browse window

For Stata, red color means error, in this case it was expecting numeric variables but some values have letters attached so Stata reads them as string characters.

To convert those string characters into numeric you need to use destring command (type help destring for details) as follows.

Previous inspection of the variables tells us that the following characters are mixed with numeric data:

destring, replace ignore(A X W P M G R W X E Y C Q <)

In this case, the option ignore helps Stata to remove any string character attach to any number.

After using destring, use compress to convert the variables to their most efficient format.