How can I more easily edit CSV tables in Access or Excel?
May 16, 2011 2:40 PM   Subscribe

I make mailing labels by pulling information about specific real-world locations into small database files. I used to get MS Access-ready DBase files, but now I have to work with CSV files, which don't seem to play well with Access. Please help me get more use out of Access, find out how to convert CSV to DBase or similar file types, or figure out better ways to use Excel. Long version inside.

I make labels for specific projects by pulling location-specific addresses from a huge collection of addresses. The old program I used generated DBase (dba) files, which I then tweaked in Access, deleting duplicates and adding extra addresses, and in the process a linked Microsoft Access Database (mdb or accdb) file was created. Someone took that cleaned up Dbase file by way of the mdb, and linked to it to print individualized letters.

Now we have a new system that is easier to use on the front-end, but it generates CSV files instead. Those CSV files create problems in Access, as I can create new fields for new recipients, but I cannot delete the duplicates or accidentally created rows or fields. I get the error message "Deleting data in a linked table is not supported by this ISAM."

Searching online for that error message turns up solutions that involve some level of coding, which seems like it should be overkill for something that happened automatically with dba files. In looking for a CSV to DBA converter only turns up programs to purchase, and I don't know enough about Access to know what other file types that I could create from the CSV that I currently generate.

The kludge fix is to delete data in Excel, but sorting data in Access was so much easier, especially when I sometimes deal with hundreds of addresses.

Example data:
Lot number  Name          Address      City       State  ZIP
455456602   Jane Doe      155 That St  Cityville  CA     90210
455456606   John Smith    123 That St  Cityville  CA     90210
455456644   Jane Doe      155 That St  Cityville  CA     90210
455456646   Frank Guy     127 That St  Cityville  CA     90210
455456647   Jane Doe      155 That St  Cityville  CA     90210
I would delete all but one Jane Doe address, as they're going to the same place, even though she has three lot numbers. If that list isn't cleaned, Jane Doe gets three letters for the same project, and the top row are titles for the columns, so they shouldn't be sorted when I'm sorting the entries by name or address.

In summary, my questions:
1. Is there some way to fix or override the ISAM error messages in Access? If not,
2. Is there an easy way to convert CSV files to Dbase files, or something else that Access will handle more easily? If not,
3. Is there a better way to sort rows of data by columns in Excel, when the rows need to stay together and the headers are the titles for the columns?

Any way to streamline this process is great, because this sort of task gets done pretty frequently, and often by people in a rush.

posted by filthy light thief to Computers & Internet (7 answers total) 1 user marked this as a favorite
If I understand your problem with Excel correctly...
3. When you sort in Excel you want the box next to "My data has headers" to be checked. If it is Excel will list the titles of the columns as the sort options instead of Column A, Column B, etc... This keeps the header line where it is, while sorting the rest of the data.
posted by grapesaresour at 2:58 PM on May 16, 2011

Best answer: I think the main problem you're running into is that, when you link to a CSV file from Access, the result looks like a database table, but has pretty severe restrictions on what you can do to it. Since deleting a record would require re-writing the whole file after that point, it just doesn't allow deleting records.

One solution is to import the CSV file into an Access table instead of linking to it directly. The import wizard looks very much like the link tables wizard, where you specify the layout of your CSV file, but the result is a copy of the CSV file inside the Access database. And since it's a native Access table, you can do everything with it.

A similar approach is to still link to the CSV file, but then run a Make Table query that selects all the records from it and creates a new Access table from it. This can be easier in the sense that whenever you need to run the process again, you just replace the original CSV file and re-run the make table query. You don't have to run the import wizard every time this way.

Either of these approaches should get you back to where you were before with the earlier format.
posted by FishBike at 3:14 PM on May 16, 2011

Best answer: Open the CSV file in Excel. Save it as an Excel file. Import the Excel file into Access. You can import into an existing table or create a new one. Proceed as before.

I'd be glad to talk you through this in more detail if you'd like. I teach Office. Although, if you have questions, ask soon before I dump all existing Office knowledge out of my brain for the summer to make room for margarita recipes and baseball scores.
posted by SuperSquirrel at 5:18 PM on May 16, 2011

Fishbike said what I was about to. Don't link to the CSV; import it.
posted by flabdablet at 5:32 PM on May 16, 2011

As has been said, one option is to import the CSV file and treat it as an actual table.

Another option would be to link to the CSV file, then run queries against it to rid of duplicates/missing values/whatever:

SELECT MIN( [lot number]) AS Lot number, Name, Address, City, State, Zip
FROM name_of_linked_CSV_table
WHERE Zip is not null and (lot number is not null and lot number <> 0)
GROUP BY Name, Address, City, State, Zip
Much easier to do in the query designer, but SQL is what it does behind the scenes anyway.

Use the results of the query to create the linked Access table.
posted by a person of few words at 7:42 AM on May 17, 2011

Response by poster: Thanks for your answers! I must confess, I am a complete Excel and Access noob, so I'll be figuring my way through both programs and trying these solutions.
posted by filthy light thief at 11:13 AM on May 23, 2011

Response by poster: Current solution: I open the CSV in Excel, tweak the column headers and change column order, then save as an Excel file. Then I open Access, create a blank database and import the Excel file, identifying the first row as column titles. Viola, I have an Access database!

This was way easier than I had thought it would be. Sometime in the future, I'll work on streamlining this process, but I wanted to post an update before I forgot about this. Thanks again for all the suggestions!
posted by filthy light thief at 4:19 PM on May 27, 2011

« Older How can I build a cloud based file server for...   |   Worthwhile but not-universal dental technologies Newer »
This thread is closed to new comments.