Import CSV file into Access

[matthewR]

I have a CSV file that I would like to import into Access. Some of the fields are numbers but they are really text. When I import them, the leading 0s are truncated. Is there a way to prevent this?

[Wendell]

The quickest way is to append it to an existing table where the data is defined as text rather than numeric. When you do the import external data, that is one of the options you have.

[matthewR]

There is an existing table in the database that was imported from an Excel file. Can I copy this database without the data and append to this empty table?

[Wendell]

You could do that, or you could create a new table that has the correct data types set for the various fields and import it into that. We often do that where we have to import data on a regular basis, and simply run a delete query against that table before we do the import.

[matthewR]

How would I structure a delete query to delete data from a table?

[Wendell]

It's one of the basic action query types like Make Table, or Append, etc. In it's simplest form it looks like

Code: Select all

DELETE * FROM tblMyTable

That SQL syntax will delete all the records in the specified table. It will warn you before it does unless you have warnings turned off. But it is very powerful and cannot be undone, so make sure it does what you want before you start using such a query. I usually create it as a SELECT query to start with and test it, and then change it to a DELETE.

[Carol W.]

mathewR,

Here is another possibility, in addition to Wendell's solution.

If you use the Import Spreadsheet Wizard, there is a dropdown setting from which to choose the data type. If you choose Text, the imported data retains leading zeroes.

See attached screenshot of Import Spreadsheet Wizard.

import spreadsheet wizard.jpg

Also see screenshot (attached) of resulting table.

access table.jpg

Hope this helps.

[matthewR]

Thank you both for you help.