How to import, run sql and export CSV data in Microsoft Access database.

If you have lot of structured data that you need to work with, it will be easier if you load them into a database like microsoft access and then use SQL to query the data as needed. Here I am going to import a csv file containing NASDAQ stock list into Access database into a new table.

STEPS to Import CSV into Access DB.

Step 1: Type access in search and open Access.
Step 2: Create a Blank database.
Step 3: Provide a name and save it wherever you want.
Step 4: Then Click External Data -> New Data Source -> From File -> Text File.
Step 5: Select the csv file you want to import and click OK. Here I am going to import into a new table. So I left the default option for import data into new table selected.
Step 6: Click Next in the Import text Wizard.
Step 7: If your first row contains Field Names, then select that check box. Note that Comma is selected as delimiter. Then click Next.
Step 8: Now you can select the Indexed column. I am keeping Symbol as my Indexed column.
Step 9: Here you can select the primary key. I selected Symbol as my primary key.
Step 10: Now you can provide the name of the table to be created to load the data.
Now the table STOCK_LIST got created in our Access database and ready to run queries. If you don’t like the column names you can just right click the column name and click Rename Field to modify the column names.

Steps to Run SQL Queries in Access DB

Now we are all set to run sqls against the STOCK_LIST table.

STEP 1: Click Create in the top menu bar and then click Query Design.
Step 2: Now click SQL View as shown below.
Step 3: Now in the window type the SQL query and click Run.
Step 4: The results of the query will be shown immediately. If you want to switch back to the SQL View and modify the SQL, click the SQL button at the right hand bottom of the window as shown below.


Step 1: Double click the table you want to export, then click External Data in the top menu bar. Then click Text File.
(OR) Right click the table you want to Export and click Export -> Text File as shown below.
Step 2: Now type the name of the csv file to export data and click OK.

Note: If you select the check box “Export data with formatting and layout“, you will get the message “You selected more records than can be copied onto the Clipboard at one time” and will be limited to only 65000 records to export. So I am not selecting that.

Step 3: Click Next for the below window.

Step 4: Select “Include Field Names on First Row” checkbox to add the column names. Make sure Comma is selected as the delimiter. Then click Next.

Step 5: In this screen make sure the file name where you want to export the data is correct and click Finish.

Conclusion: In this quick guide we saw how to import csv file into Microsoft Access database. Run SQL queries on it and then export the data as csv.

%d bloggers like this: