Database workflow advice?

Question

I've got a product database that I need to update a couple of times a week and am looking for some advice on what workflow I should setup. I can export my database from my online store as a CSV, I then need to modify a couple of columns and then reimport the CSV to my online store. All the infomation I I am updating the product database CSV with is either from CSV's or easily convertable to CSV.

I've got Excel, Access and the open office suite available to me. I want it to be as automated as possible. I'm a novice at all this but perfectly comfortable trawling help files and forums for the nitty gritty detail, what I need is advice on a strategy.

Answer 1

Excel is likely fine, as it supports CSV import and export out of the box and I don't think Access CSV handling is much different or better. I have never used Open Office.

The challenge will be to make sure that the CSV are exported in the same formats as the original (especially regarding dates and number formats). If you're lucky, simple Import Wizard settings will work out of the box, but you'll need to do some testing to be sure.

So your first task is to test the import/export quality: import the CSV in Excel (choose the proper major settings like comma-separated or fixed width, but try to leave the rest as-is to begin with), then export it back right away to a different CSV file, then compare both files.

Are there differences? Repeat the process again with different Import Wizard settings, like the format (dates/number) of specific columns. Basically you'll need to investigate those differences individually until you find the proper steps to avoid them. Take notes of what import settings improve the quality of your export. Each time you try something new, check if your software is able to reimport them correctly or not - understanding how it handles various CSV inputs will be useful to you.

If this part works well and you can achieve a consistent export that your software can read correctly, most of the job is over! Now you can attempt to simplify the task by recording your steps as an Excel Macro and add buttons to your excel worksheet do it in a consistent manner. I imagine a button on your worksheet to Load, and another to Save/Overwrite would be nice.

If you can't manage to get a good export, I suggest you ask a new question here for more specific suggestion on how to import/export those particular lines of CSV that are giving you trouble, including some sample data, so we can try help you more specifically. Good luck.