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:
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.
Thanks!
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 90210I 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.
Thanks!
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
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
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
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:
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
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, ZipMuch easier to do in the query designer, but SQL is what it does behind the scenes anyway.
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
ORDER BY Zip
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
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
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.
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