Skip to content
Printer-friendly version

Importing the Data File into Access and Excel

Instructions for importing the public schools data text file into Microsoft Access or Microsoft Excel.

Instructions for importing a text (.txt) file into Microsoft Access

NOTE:
The steps in this process are performed using Microsoft Access 2010; your user interface may be slightly different, depending on your version of Microsoft Access.

  1. Save the TXT (.txt) file (pubschls.txt) to your local hard drive (or wherever you choose to save it on your computer).
  2. Open Microsoft Access.
  3. In Access, you can simply select to create a new "blank database" (or advanced Access users can use another template they wish to work with).
  4. Select the "External Data" tab.
  5. Locate the icon to "import a text file" (it's in the "Import & Link" section).
  6. Click on the icon to "import a text file" (the words may simply say "Text File," with an icon next to the words; if you "mouse over" the icon, you may see the words "import text file").
  7. Use the "Browse..." feature to find your .txt file that contains the source of the data you'd like to import.
  8. Select the "Import the source data into a new table in the current database" button (if not already selected).
  9. Click "OK."
  10. In the Import Text Wizard dialogue box:
    1. Select "Delimited." Then click on the "Next" button.
    2. Select the "Tab" button, and check the box that reads "First Row Contains Field Names." Then click on the "Next" button.
    3. In the "Data Type" drop-down menu box (under "Field Options"), select "Text";
  11. Pay attention to the "Do not import field" box. If the box is checked, you may want to "un-check" it, as it may affect your next step. Then click on the "Next" button.
  12. Select the "Choose my own primary key" button, and select "CDSCode" from the drop-down menu box. If you do not see "CDSCode" in the list of choices, try hitting the "Back" button to ensure that you haven't omitted that column/field from being imported. Click on "Next."
  13. The screen should read "Import to Table," with a box that contains your file name (minus the file extension).
  14. Click on the "Finish" button.
  15. Click "Close." Your file should now be accessible in Microsoft Access.


Instructions for importing a text (.txt) file into Microsoft Excel

NOTES:
The public schools data file is already available in Microsoft Excel format. For users that still may wish to import the public schools .txt data file into Excel, these instructions are provided.

The steps in this process are performed using Microsoft Excel 2010; your user interface may be slightly different, depending on your version of Microsoft Access.

  1. Save the TXT (.txt) file (pubschls.txt) to your local hard drive (or wherever you choose to save it on your computer).
  2. Open Microsoft Excel.
  3. Just a small note: Be sure that cell A1 (in your Excel worksheet) is the one that's highlighted, to ensure that your data are imported in the right spot in the Excel worksheet. (Not doing this will simply mean that your data get plopped into whatever part of your worksheet where the cell is selected; this should make sense once you go through the importing process.)
  4. Click on the "Data" tab, and select "From Text" (in the "Get External Data" section).
  5. Find your pubschls.txt tile, select it, and click on the "Import" button.
  6. The "Text Import Wizard" box will appear. Do the following:
    1. Step 1 of 3: Select "Delimited." Then click on the "Next" button.
    2. Step 2 of 3: Under "Delimiters," check the "Tab" box. Then click on the "Next" button.
    3. Step 3 of 3: You are going to define the "column data format" specifically for 6 columns.
      1. FIRST: You'll set "Text" as the format for these 3 columns: "CDSCode," "NCESDist," and "NCESSchool."
        To accomplish this:
      2. In the "Data preview" box, select/highlight the "CDSCode" column (by simply clicking anywhere in that column). (You may notice that the headings/formats at the top of each column may all say "General," which is the default data format setting.)
      3. Select the "Text" button (under "Column data format").
      4. Do the same thing for the "NCESDist" and "NCESSchool" columns (setting "Text" as the column data format).
      5. NEXT: You'll set "Date" as the format for these 3 columns: "OpenDate," "ClosedDate," and "LastUpdate".
        To accomplish this:
      6. In the "Data preview" box, locate and select/highlight the "OpenDate" column. (You may need to scroll to the right to find this column.)
      7. Select the "Date" button (under "Column data format").
      8. Do the same thing for the "ClosedDate" and "LastUpdate" columns (setting "Date" as the column data format).
    4. Click on the "Finish" button.
  7. When the "Import Data" box pops up, select "Existing worksheet," and click "OK."
  8. Voilà! Your .txt file has now been imported into your Excel worksheet.

 

Go back to the Public Schools Database Web page.

Questions:   CDS Administration | cdsadmin@cde.ca.gov | 916-327-4014
Download Free Readers