How to import data from Excel sheet to existing stata 12.0, If the data file in stata already created......!

[Mohmand Javed]

I have a created a file in Stata 12.0 and import 30 responses from Excel sheet which i have collected through an online survey questionnaire using google forms. Now I am getting more responses which I want to import from the same Excel sheet to that already created file in Stata 12.0.
The existing file have string data which I have changed to numeric data already in Stata..Now when I am importing new data from excel sheet , the data type is mismatching....Please help me in this regard that how i can transfer new responses from Excel to that existing data file of Stata...
Thanks a lot

[Clyde Schechter]

Well, you have to first import each batch of responses into a separate file. Then you have to deal with the type mismatch by deciding on which data type should prevail. Presumably one of the data sets is giving you strings and the other is giving you a numeric variable. So the question becomes: why are you getting strings imported in a variable that should be numeric. Or vice versa. The commonest situations are:

1. The variable has only missing values in the data set that is giving a numeric value: all the values are ,.
or
2. The variable that is giving strings has invalid responses or errors that cannot be read as numbers: 2..5 when it should be 2.5, or "about 3" instead of an exact numeric value, or "N/A" when it should just be missing value.

If your variable should truly be numeric, then you have to identify and fix the observations that are giving you a string variable. You can find them by -use-ing the data set where it comes in as string and then run -browse if missing(real(name_of_the_involved_variable))). How to fix them, of course, will depend on the specifics of what you find.

If your variable should truly be string, but it is all missing values in one data set, then you can -drop- that variable from that data set and then proceed to combine the two data sets together. Stata will not mind the absence of the variable. But before you do that, you should ask yourself why you have only missing values for that variable in that batch: it is likely that something went wrong in the data gathering or in the recording of the data subsequent to that. So you should investigate that.

There is a less common situation that can arise. When Stata imports data from Excel, it decides whether a variable is string or numeric by looking at the value in the first row of the spreadsheet. If it finds a number there, it starts out assuming it's numeric, though it will "change its mind" if it subsequently encounters something that is not a number. But if the first cell is blank, Stata interprets that as a null string and prepares to import a string variable. Even if everything that comes along later is a number, every number can be represented as a string, so Stata has no reason to "change its mind" and you end up with a string. The solution in this case is to apply the -destring, replace- command to that variable before combining the two data sets.

If you need more specific advice, do post back, but be sure to provide examples of the data from both data sets. Be sure to use the -dataex- command to do that. If you are running version 16 or a fully updated version 15.1 or 14.2, -dataex- is already part of your official Stata installation. If not, run -ssc install dataex- to get it. Either way, run -help dataex- to read the simple instructions for using it. -dataex- will save you time; it is easier and quicker than typing out tables. It includes complete information about aspects of the data that are often critical to answering your question but cannot be seen from tabular displays or screenshots. It also makes it possible for those who want to help you to create a faithful representation of your example to try out their code, which in turn makes it more likely that their answer will actually work in your data.

[Javed Mohmand]

Thanks very much Clyde for your help...

Actually I am appending more observation in a stata data file, in which I have already copied 30 observations (string data) from Excel sheet and converted into numeric data...Now I am adding more rows of observations, which are actually string data from Excel sheet...I was directly copying that string data to that already created stata data file but definitely its not copying because due to the data type mismatching....
So I need suggestions that how I can easily copy that string observations........?? (how to convert these new observations from string to numeric and copy it to the same stata file...)Thanks a lot

[Clyde Schechter]

The phrase "converting string observations to numeric" can mean two different things in Stata, depending on what kind of strings we are talking about.

If the strings are things like "Male" and "Female" and you are converting them to numeric values like 1 and 2, that is one thing (-help encode-). If the strings are things like "123.45" and "789" and you are converting them to 123.45 and 789, that is a different thing (-help destring-). And then there are "pathological" situations 1. and 2. from post #2 in this thread that require fixing the data first.

As I said before, if you need more specific advice, you need to show example data.

[Javed Mohmand]

1. Your Job Title	2. Your Gender	3. Your age.	4. Which city you are located in Pakistan?	5. Your highest qualification.	6. Your company was establish in year.	7. Name of your Organization
Software Engineer	Male	41 to 50	Peshawar	MS (CS)	10/5/2010	NetSaf
Software Engineer	Male	26 to 30	Islamabad	MS (CS)	11/6/2012	MaxWay

Example of my data set .....I have some demographic questions and some 5 point Likert scale questions as well......As you mentioned for the 'Male' and 'Female'.....I am doing the same....I have converted for 30 responses (string to numeric)...now when i am adding more observations from Excel sheet to that stata file, stata data file is not accepting it because I have already changed that string data into numeric and the new observations are string data actually.....

[Clyde Schechter]

Javed, you need to read the responses and respond accordingly. The above example of your data set is not useful. It does not give crucial metadata. That's why I asked you to use -dataex- and explained how to get it in #2.

However, since your explanation is that you are taking variables that are initially inherently strings, like job titles, genders, cities, and converting them to numbers, I can tell you this much:

You have to first put all the data sets together with these variables maintained as strings. Only after that is done, is it safe use -encode- to make conversions to numeric in the combined data set. If you make the conversions separately in each set as you go along, you are likely to do it differently in different data sets and end up with a useless mess.

Again, if you want more specific advice, you have to show example data using -dataex-. Tableaus like the one that you no doubt spent a lot of time and effort creating are simply not helpful in this situation (and hardly ever are.)

[Javed Mohmand]

Ok Thanks a lot Dear Clyde...will get back to you if there is any query....