How to Import and Export Data in Access

Microsoft Access, Microsoft Office

The good news is, the import and export processes in Access—and in general—are two sides of the same coin. And there really isn’t any bad news, other than making sure the data are set up in a way that Access can understand—namely, something row-and-column-ish when importing. There’s even an External Data tab in the ribbon, so it’s just a matter of knowing what kind of data you’re bringing in, or what format the recipient needs if you’re exporting.

For an import, other than making sure you know where the data are, simply go to the External Data tab, and look in the Import and Link group. We’ll use a text document here.

We can choose whether this will start a new table, add on (append) to an existing table, or link to the data source (which allows updates, but has some limitations).

And we have to specify where the document is. We can do this in any order, but we have to do both.

If (as in this case) we want to append the data to an existing table, we need to tell the dialog box which one. And, though the box doesn’t say, we have to make sure the incoming info is set up to fit in the same “slots” (fields) as the existing info. (Opening the file and checking in advance is a good idea here.)

Then we go through the File Import wizard, which lets us see how the incoming data will work.

We choose “Delimited” or “Fixed Length” according to how the pieces of info divide up. (A delimiter is a marker telling us where things start and stop.)

We then indicate what kind of delimiter is there (commas, tabs, and semicolons are among the more common ones); and let the program do the rest.

Exporting is even simpler. We select (but don’t have to open) a table, query, or other object, go to the External Data tab. Then, in the Export group, we click the type of receiving file we need to create. And finally, specify location and file name (the program will default to the object name).

So, “Where are you getting the data?” and “Where are you putting the data?” are the only two questions we have to be specific about. Access pretty much handles it from there.

For more info, check out our Microsoft Access classes.