Skip to main content
California Department of Education Logo

Importing Data Files into Access and Excel

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

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

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

  1. Save the text 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 database they wish to work with).
  4. Select the EXTERNAL DATA tab located in the Ribbon.
  5. In the Import & Link group, select Text File to open the Import Text Wizard dialogue box.
  6. In the Import Text Wizard dialogue box, perform the following steps:
    1. Select the Browse... button and navigate to the text file that contains the source of the data you'd like to import and select Open.
    2. Select the Import the source data into a new table in the current database radio button (if not already selected) and select OK to continue.
    3. Select the Delimited radio button and select the Next button to continue to the next screen.
    4. Select the Tab radio button, and select the checkbox that reads First Row Contains Field Names. Select the Next button to continue.
    5. In the Data Type drop-down menu under Field Options, select Short Text for all columns.
      • The import may fail if certain data types like dates are not converted to text. This is because some records may contain a No Data value for dates, which would fail if the column data type was Date.
    6. Pay attention to the Do not import field checkbox. If the checkbox is checked, you may want to uncheck it, as it may affect your next step. Select the Next button to continue.
    7. Select the Choose my own primary key radio button, and select CDSCode from the drop-down menu box. Select the Next button to continue.
      • If you do not see CDSCode in the list of choices, select the Back button to return to the previous screen and ensure that you haven't omitted the CDSCode column from being imported.
    8. In the input field under Import to Table, you may choose the change or keep the default table name.
    9. Select the Finish button and select the Close button to close the Import Text Wizard dialogue box.
    10. Verify in the Object panel (left panel), a new table is created under the name provided in the input field in step h.

Your file should now be accessible in Microsoft Access.

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

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 2013; your user interface may be slightly different, depending on your version of Microsoft Access.

  1. Save the text file (pubschls.txt) to your local hard drive (or wherever you choose to save it on your computer).
  2. Open Microsoft Excel and open a new blank workbook.
  3. Verify cell A1 (in your Excel worksheet) is the active cell to ensure that your data is 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. Select the DATA tab located in the Ribbon, and select From Text within the Get External Data group.
  5. Navigate to your pubschls.txt file and select the Import button to open the Text Import Wizard dialogue box.
  6. In the Text Import Wizard dialogue box, perform the following steps:
    1. Select the Delimited radio button and select the My data has headers checkbox
    2. Select the Next button to continue.
    3. Under Delimiters, select the Tab checkbox and select the Next button to continue.
    4. Select the first column CDSCode, and under Column data format select Text radio button.
    5. Repeat previous step (step d) for all columns, setting the data format to text.
      • Tip: You can hold SHIFT to select multiple columns
    6. Select the Finish button to close out the Text Import Wizard dialogue box.
  7. When the Import Data dialogue box pops up, select Existing worksheet.
  8. Enter =$A$1 into the input field if you want the data to start in the first cell, otherwise choose an appropriate cell.

Your text file has now been imported into your Excel worksheet.

Go back to the Public Schools and Districts Data Files web page.

Questions:   CDS Administration | cdsadmin@cde.ca.gov | 916-327-4014
Last Reviewed: Thursday, February 28, 2019
Recently Posted in School & District Information
  • 2020–21 District Reorganizations & Charter Changes (added 06-Jul-2020)
    Contains information on local educational agency (LEA) reorganizations, territory transfers, lapses, and name changes, as well as information on charter school restructuring and changes in authorizers for the 2020–21 school year.