ODBC Data import

Hi,
new here but have a lot of database experience.
Has anyone created an access database with linked tables (ODBC)for importing/editing product data from a spreadsheet.

I see someone hase done a table export, populate and import but this looks a bit messy and not something I could expect a client to do.

I want to create a simple front end that imports data from a formatted spreadsheet to MSaccess and populates linked tables in zencart at the touch of a button.

Using ODBC it should be fairly straight forward.

Has anyone done this.
Does anyone have a schema for product population.(including all languages)

If not, are there many people out there that would like this as an add on.
Don't need to have MSaccess as can be made an exe.

Cheers
Hank

I have done this in the past for a different shopping cart. I had several suppliers that supplied product feeds with inventory, pricing, and product data. I used Access to read those files and manage my products and pricing. The problem is dealing with the different input formats for each product feed. If what you're suggesting is something to just work with ZC product data then I think it's a bit of overkill.

I totally see what you mean, but this and other clients have over 10,000 products, we are looking at saving a lot of money over a short time, and it isn't too difficult to achieve, if I can find the relevant schemas. I'm only asking if someone has done it before to save trawling through to see which is affected.

Also once it is done I feel a lot of other forum members may benefit.

Did your app work for zencart?

The layout of the source data is never stable or the same. I use this application Navicat Premium. I create the scripts that this app uses to transfer the data. Of course the end user will need this app locally and it is a added cost to the end user. I have found that it is cheaper using this app then writing a custom system. It seems to never fail that after a short while the end user changes suppliers or the supplier changes there layout.

Skip

Hankstains:

I totally see what you mean, but this and other clients have over 10,000 products, we are looking at saving a lot of money over a short time, and it isn't too difficult to achieve, if I can find the relevant schemas.

The schemas shouldn't be a problem as you can set that up on import. Import each into a seperate table and save with the mappings done at import. Save the import so you don't need to do the mappings again. The format shouldn't change. Then just build queries to update the ZC data and export it. I managed over 17,000 products from 5 suppliers this way. Each had their own feed schema and I just mapped it to the shopping cart.

Hankstains:

IDid your app work for zencart?

No but there's no reason why it wouldn't.

skipwater:

The layout of the source data is never stable or the same.

It seems to never fail that after a short while the end user changes suppliers or the supplier changes there layout.

Skip

Yup, it's bound to happen. The larger suppliers weren't much of a problem but some of the smaller ones just didn't seem to care that the changes they made to the feeds might have an impact on somebody else.

Thank you both for some very good advice.
You're right that the only certain thing is uncertainty, especially when it comes to relying on third parties not to up and do something off the wall, like change their layout.

I see Navicat is available for a 30 day trial so when I have a spare minute I will give it a go. It does look good.

In the meantime, can you seee any reason why I shouldn't use access and ODBC to modify all the product descriptions, when the data was imported from OScommerce it had a strange way of inserting 2 fairly random words at the beginning of each desc. Trouble is someone else has already started to manually edit the descriptions through ZCart. so can't do a blanket remove. It is taking an age. So if I can give them all the descriptions in table format it should make it a lot quicker. I'm guessing

Thanks again for your advice

Hank

This might help http://www.zen-cart.com/wiki/index.php/Developers_-_Database_Schema
If you download the OsCommerce Data Importer and look at the code for products it might help. (It is sql to sql not obdc).

Skip

Hi Skipwater.
Thanks for the schema info. Very useful.

Trouble is someone else imported the data from OSCraperce so I am stuck with what is. The client is quite happy to modify all the descriptions manually, I thought I would try and make it a little easier.

So as before, can you see any problems?
cheers
Hank