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 speadsheet 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 CRCT grades In order to do
this we will need data from both the Stumast and
the SCRCRCT 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 might 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 choosen. 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 CTSTID.
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
SCRCRCT 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 may 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.
If you do not receive this message, it will take
you back to the table listing and you will be
allowed to choose the additional data you need.
Most of the time if you have joined 2 tables,
you will receive this message. If you are only
working out of one table, you wont receive this
message. You will be sent straight into the
table listing.
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 SCRTCRCT to join and
  pull information from.

29
Lets start by opening a new excel sheet in
Microsoft Excel. Click on Data, Import External
Data, and select new Database Query.
30
Choose your data source. AS400-QLIBXXX (XXX
SCHOOLS INITIALS). Click OK.
31
Sign on using your AS400 log-in and password.
32
You will see 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 now will be back at the table listing. The
2nd table that you will need to choose is
SCRCRCT. TSTYR REASS REAPL ELASS ELAPL MATSS
MATPL SCISS SCIPL SSSS SSPL For test scores
(Scaled Scores) you would choose ELASS
MATSS, REASS, SCISS, SSSS For Performance
Level instead you would choose ELAPL REAPL,
MATPL, SCIPL, or SSPL 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 might 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. If you receive
this message, 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 SCRCRCT table find the
STUDID and the CTSTID. 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 CRCT because there might
be some children that are attending the school
but might not have a CRCT 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 2007Please 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
SCRCRCT. TSTYR, the operator is set to
EQUALS, and the value is set to 2007. 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

• The teachers at your school need to find out some
  information on their students. They need to know
  who failed the CRCT last year.
• Please pull current students that failed, their
  grade, ethnicity, sex, special ed, free and
  reduced status and their crct scores and
  performance levels just for last year and their
  home room. Sort the information by grade and
  homeroom.

47
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 Wanda Holland [phone]  Ext
10328 Jennifer Stafford [phone]  Ext
10186