Importing Data into Excel

1
Importing Data into Excel from the AS400
2
After you have completed this power
point training session, you will be able to
import data into one spreadsheet using multiple
tables from the AS400.
Test data

Student master file
3
Open Excel and click on Data
4
1 - Select Import External Data 2 Select
New Database Query
5
A Choose Data Source window will open. 1 -
Choose your data Source 2 - Click ok.
Your data source will be AS400-QLIBXXX (XXX
Schools Initials)
6
At this time you will be prompted to log into the
system. Enter your AS400 log on information.
7
We are going to pull in data from 2
tables. Lets say we need to have Student
information along with the Students Final grades
for a course and the EOCT score associated with
the course. In order to do this we will need
data from both the Stumast and the GRDHIST tables
on the AS400. At this point, you will be given a
list of tables and columns to choose from.
Since we are going to be requesting data from 2
tables, we will need to do a couple of
things. For this query, choose the Stumast file
and select the items that you need from this
table. You must select the items in the order
that you would like them to appear in your query.
If you do not select them in order, you can
use the arrows to the right of the window to move
your selected items into the order that you need
them to be shown. Once you choose the items that
you need from this table you will click next. The
next screen will allow you to filter the data to
narrow down the amount of data the AS400 has to
import. Lets get started..
8
At this point, you will be given a list of tables
and columns to choose from. You can now select a
library to choose from. In this case Stumast. By
clicking on the sign, you can open the
library and choose the fields that you need by
clicking on the gt. Then click next.
9
If you choose data from more than one table
before you set your filter criteria.you might
receive the following error.
This just means that the amount of data that you
have requested is to large for excel to handle.
You must choose data from one table 1st, set the
filter and then go back and choose the data from
the other table.
10
The next window allows you to filter the data to
only collect the data that you require. 1 -
Choose the column to filter 2 - Choose how you
would like to filter the data. 3 Choose Back so
you can go back and finish your query
By choosing status equals A you ensure that you
are getting Active Students
11
At this point, you can choose the other items
from the tables that you would like to include in
your query. Then click on next.
12
Since you are pulling data from 2 different
tables, you will receive the following message.
This is not an error. This is just letting you
know that you are going to have to join or link
the 2 tables from which you are needing data.
Click OK
13
This is the Query editor. This is where you join
the tables that you have chosen. You join the
tables by choosing the items that are the same
in each table. Since the Student ID number is
the same in both tables, we are going to use
this to join the tables.
14
You must join the tables so that you can view the
information from both tables rolled up into
one. Join is to combine two tables by matching
the values in corresponding columns. In result,
you will get a merged table which consists of the
first table, plus the matched rows copied from
the second table. Join is crucial for a
cross-table roll up summary.
To join the tables, find the matching items and
link them by dragging one on top of the other.
In the case, the STUDID should be dragged on top
of GHSTID.
15
You must edit the join type once you link the
tables. You do this by double clicking on the
link created between the two tables. When you
click on this link, a join window will open.
This is the window in which you much choose your
join type. In this case, we want to show all
active students and only the records from
GRDHIST that match. So we will want to choose
the 2nd option. Then click on close
16
We are now ready to return the data to an excel
spreadsheet. Click on File and then on Return
data to MS Excel.
17
You will be asked where you would like to Import
the data. By default existing worksheet Is
checked. Accept this by clicking OK.
18
You will be asked to sign on again. Once you
enter your log on information, click on OK.
The data will populate the excel spread sheet.
19
Your data should now show up in the excel spread
sheet for you to review and sort.
20
If after reviewing your data you realize that you
forgot to add a column, you can go back in and
edit your query. Click on Data, Import external
data and then edit query.
21
You will be prompted to log into the system
again. Enter your AS400 log on information.
22
You will receive a message telling you that this
query can not be edited by the Query Wizard.
This is ok. Click OK to move on to the MS
Query Editor.
23
You will see this Microsoft Query Screen again.
In order to add a column from one of the tables,
click on Records and Add column
24
An Add Column box will pop up. In the Field box,
click on the drop down arrow and scroll down
until you see the column you are wanting to add.
You dont need to enter anything into the
column heading or total fields.
25
We are now ready to return the new data to our
excel spreadsheet. Click on File and then on
Return data to MS Excel.
26
You will be asked to sign on again. Once you
enter your log on information, click on OK.
The new data will populate the excel spread
sheet.
27
Congratulations! You have now created a query,
linked 2 tables from the AS400 and then imported
that data into an excel spread sheet.
28
Lets try that one more time..

• Lets practice linking tables one more time.
  This time lets choose Stumast and SCRGHST to join
  and pull information from.

29
To begin, open a new excel sheet in
Microsoft Excel. Go to Data, Import External
Data, and select new Database Query.
30
Choose your data source. This will be the
selection AS400-QLIBXXX (XXX SCHOOLS
INITIALS). Click OK.
31
Sign on using your AS400 log-in and password.
32
At this point, you will be given a list of tables
and columns to choose from. To obtain student
demographics, select the library STUMAST by
clicking on the next to the library name. You
will be given a list of columns which contain
Student information.
33
From this list, you can choose the items that
you would like to see in your report.
STATUS SCHOOL STUDID STUNAM GRADE HOMERM SEX
ETHNIC SPARA SFSCOD Choose the columns in the
order you wish them to appear in your Excel
document. Click NEXT when you have selected all
the columns from this library you want to appear
in your query.
34
On the next screen, you will be able to add your
criteria. You will be adding 2 items. 1 -
Click on STATUS and set it equal to A. This
will make sure your query only brings in
Active students. Once you have done this click
on Back.
35
You will be back at the library listing. The 2nd
library that you will need to choose is SCRGHST.
GTSYR, GCMPNT, GTMTST, GSCLSC, GPASFL Choose
the columns in the order you wish them to appear
in your Excel document. Click NEXT when you have
selected all the columns from this library you
want to appear in your query.
36
The next thing that you will see is a
warning. All this is saying is that you have
picked data from 2 tables and That you are going
to have to manually join them. Click OK
37
On the next screen you will see two tables and
then you will see your data below the 2 tables.
You are going to have to join the two tables. In
the Stumast table and the SCRGHST table find the
STUDID and the GSTUID. Both of these are the
student id numbers in the tables. Click and hold
your mouse down on the STUDID and drag it over to
the CTSTID to create a link. If a successful
link has been created, you will see a line
between the two fields.
38
Now you must define the join type. 1 - Double
click on the line 2 A window will open and give
you 3 options. I usually choose the option that
gives me all students from stumast and only the
records that match from GHSGT because there might
be some children that are attending the school
but might not have a GHSGT score. Choose the one
that best fits what you are looking for. Then
click ADD and then click Close
39
You need to set 1 additional Criteria. You need
to set Test year equal to 2008Please click on
the top of the TSTYR column. This will highlight
the entire column. Then click on Criteria and
the choose Add Criteria
40
A new window will open called Add Criteria. In
the Add Criteria Box, make sure the field is
SCRGHST. GTSYR, the operator is set to
EQUALS, and the value is set to 2008. Press
Add and then Close.
41
Now you are ready to return your data to your
excel document.Click on File and then choose
Return Data to Microsoft Office Excel
42
You will then be asked if you want the data in
the existing worksheet. Click OK. You may be
asked to log on again. Log on using your AS400
ID and password. The data will then appear in
your document.
43
Congratulations! You have now created a query,
linked 2 tables from the AS400 and then imported
that data into an excel spread sheet.again!
44
Refreshing data in your excel file
Now that you have successfully pulled your data.
You might need to go back and refresh it from
time to time to make sure that you have the most
current data.
Click on the Data tab And choose Refresh Data.
You will be asked To provide your login
Information. Once you Have done that, your
Excel sheet should Refresh with current Data.
45
Questions?
46
Please feel free to call us for help. Jan
Grace Director of Testing and Instructional
Technology Houston County School System (478)
988-6328 ext. 10197  [phone] mobile (478)
988-6102 FAX Steve Ellis [phone]  Ext
10328 Jennifer Stafford [phone]  Ext
10186