Importing a CSV file in MS-Access

Question of the day – How do you import a CSV file in MS-Access, to edit and save it?

Answer – It is not as easy as you guys think. When you import a CSV file in MS-Access, it “links” a table to the underling CSV file in a Read-only mode. There is no way you can edit it and then save it back.

But here is a neat trick to make it editable  – First you need to create an empty table with just columns and no data. A really quick way to create the table beforehand is to open the csv file and delete all data except for the header row. Now save it as a different csv file. Import the newly saved file and Access will create an empty table from it. Now import the real CSV file into that table. Access will bring (read – import) in all the data and will let you edit it.

Advertisements

Author: anand

Currently CleverTap. Founder & ex-CTO - burrp. A techie and an entrepreneur at heart. Have worked in US, China and India.

6 thoughts on “Importing a CSV file in MS-Access”

  1. Anand instead of going through all the trouble just use OpenOffice
    Spreadsheet it has an option to import csv files and edit the data. On the other hand i would like to know why do you want to use MS-Access ?
    Like most microsoft products it is not standards compliant.

  2. OO Spreadsheet has the same limitation as Excel – max number of rows that can be imported. You cannot import/edit a file having more than 65535 rows. My CSV contained over 100,000 rows.

    The reason we used MS-Access was to create a “form” layout over the CSV data, where in we could show one row of data at a time. We really didn’t need any of the ACID (database) properties. Just needed a way to modify some of the data and then export it back as a CSV file.

  3. Hi anand,

    i have imported to csv files into ms-access but i want to know how can i compae two csv files in ms-access…..i need the ans asap..thank u

  4. i have imported to csv files into ms-access but i want to know how can i compae two csv files in ms-access…..i need the ans asap..thank u

  5. Use Text pad to compare two CSV files.. :-) or Windiff.. and there are many such tools out there to compare two files..
    Hint : sorting it out before comparision will help you save a lot of time..

  6. hi anand,i dont know how to import a csv file to MS access.I just created a csv file using the matlab function csvwrite(‘csvlist1.dat’,[x,y]). here the name of the csv file created is ‘csvlist1’. Now my problem is how to import this CSV file from the matlab to MS-Access??…plzz help me..

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s