upload product inventory using excel or csv files

hello,

is it possible to bulk upload product inventory using excel or csv files.


regards,

Hello,

If you go to the Catalog/Products you can export/import Excel - but I have had no luck at all with that.

Posted the same question as you a few days ago and someone offered to do it for me for $150 but yeah, that didn't fly.   So not sure what to say.

I read a fair bit of the doc's on this and it just doesn't seem to work like it says in the ol' brochure but if you have any luck pls let me know.

Cheers,
DC

I pumped thousands of rows directly into the database from a XML file, and also from a text. I did not use the web front-end. Had no problems at all. Just have to make sure what table holds what data. Place the correct data into the correct table. I did that by creating a database with no data, and then using the admin tool to add one category, with one single product. I could then see what tables stored what data.

I am a database administrator, so this is very simple for me to create a SSIS package. That way I can always load new data if any of my wholesale suppliers add new products.

But yes, anything is possible.

Andrew SQLDBA

hello,

that sounds interesting, did you create

database with no data

used ssis convert database into xml file

did you then just use the nop admin panel to export file using xml or any other way

now everytime you have to insert the products do you do the following

enter values into your database convert it to xml and update database

what happens to the image field, do you just enter the url of the image or is there any other way

regards,

All I did was code a SSIS package to insert the data into certain tables, as long as that data is not already there. This package reads different file types, depending on the warehouse that sent us the list. I receive different file types from different suppliers.

Why would you convert a database to XML? That is a huge bottleneck and would slow everything down. When I first created the database, I chose not to insert the sample data. I have also taken that same script and created a few other databases, just for testing. I do not store the images in the database, I store them on the drive.

Every time that I get a new product list, from any of my suppliers, I simply place the file on the drive, and run the SSIS package. I have coded the package to know what to do with the file type that is there. The package can also pump data in from multiple files, it simply takes one file at a time. There is never a duplicate product in my database, because I do not allow that to happen. I use constraints in the database, and I heck for that specific product before inserting a new one.

I do not truncate all the tables and start over with a full load each time, that would be crazy. I do not pump all the data out of the database, and then turn right around and insert it all back in one product at a time, using the Admin tool. That would just be insane. You keep talking about converting the data in the database to a XML file. Why would I do that? I work in the database. Once the data is in the database, I do not remove it.

I simply created a SSIS Package, using stored procedures and C# in the package, to pump the data from a couple different file types that I receive from my multiple suppliers. I verify the product does not already exist before I perform the insert. If I were to be doing this on a daily basis, I would probably pump this into a different database, and then pump only the new data into the Store database. But I receive a new lsit only about every 3 - 6 months.

Hope this clarifies for you. All you need to do is keep things simple. Do not use a front-end web app to perform the tasks that a database performs. Load the data directly into the database.

Andrew SQLDBA

sounds interesting, will have a go at it

regards

Hi Andrew,

For those of us that are not dba's how would someone go about creating a ssis package and using stored procedures and C# in the package for inserting data on a weekly basis.

thank you

Have you ever used SSIS? Do you have any database programming experience?

You need a SQL Server that has SSIS installed. I used stored procedures because I do not like ad-hoc inline code. C# is used in some of the tasks within the package. Things like reading the file from the directory.

I am not, nor cannot give you a recipe of steps to try and follow. Programming of any type does not work that way.

You figure out what table(s) in the database you want to add records. If those table(s) are the parent table, then you must add the child rows first. If this is for a list table, then you just add the row(s).

It is just a matter of determining what table(s) you want to pump data into, from what type of data source, and then write the code to do just that.

Andrew SQLDBA

how do you know which value goes into which table cause product information is not just in one table it is scattered in various tables.

regards,

Here is what I did:
I created a database with no sample data. I used the the admin tool to create a category or two, filling in all the fields possible, and then I created a product or two. I used SQL Server to tell me what tables have data in them. I then simply map the columns in the text file to the column in the respective table. I created my SSIS package to map the text file column to the table column.

You could also use a separate "staging" database, pump all the data into there, cleanse it on the way in, and them select from one database to the other. It took an afternoon of my time to do that, but now it takes nothing for me to load new data into my database.

Always more than one way to do everything in programming.

Andrew SQLDBA