next »

[dixieproducts]

In order to avoid some of the issues with populating the VirtueMarts products, can someone point me to a tutorial thread, page, or site for mass uploading products?   Things like: what layout is needed in the CSV file, what order of columns, headers needed, customizations to VirtueMart code, what is needed in order as far as categories, xrefs etc... before you upload.......

I know that people recommend hacking the PHP file for mass upload to accept arcane delimiters like ~ or | to be used to avoid errors in MySQL, and have seen that thread, but I would like to prepare the flat-file correctly so that one large mass upload works right away.

Thanks in advance, and if there is a thread that I missed on this, sorry for the duplication, but I couldn't find it in the forums.

todd.at.dixieproducts

[mosey]

I'm not very advanced with my use of csv but if one was trying to test out the csv upload by using the default fields perhaps this may come in useful?

I use Excel as I'm more familiar with it than say with Access so hope this will be applicable. Step 4 is particularly crucial to this.

==============================================================

1. Enter all the fields in order into separate columns in your excel table.

For the sake of convenience, I have attached a text file (fieldheading.txt) with the csv field headers generated when saved from excel. It should be relatively straightforward to import this back into Excel again.

You'll probably have noticed that the csv upload section now comes with a handy 'skip first line' checkbox, I would recommend that this is checked everytime as thefirst row will be the field-headers.

I have also followed the notation of each field for each column - they should be fairly self-explanatory as well. It is possible to remove all the unnecessary field headers from VM as well as the excel file to save on all this space but for 'attempted tutorial' purposes we will keep to the original.

2. Start populating the table with on a per-row basis for each item.

As this is our first test, I suggest just adding one or two items. If you have no need for certain fields, just leave them blank. It is worth noting that certain fields such as product_sku are always
required.

   Categories - VM does provide a helpful explanation on how it all works. It is brilliant that VM adds categories automatically when using a csv upload.  A quick recap:

         Maincat/subcat1 (can be the standard data inputted into the column for product_categories - means that the item will be categorised to 'subcat1')

         Maincat/subcat1|Maincat/subcat2 ( means item will be categorised to subcat1 and subcat2)

   Manufacturer - If no manufacturers have been added, then I would strongly suggest inputting this field as 1. Otherwise VM tends to generate multiple Generic Manufacturers (if I remember correctly). 1 = default manufacturer.

   Excel Fields - I have also found that it is useful to set ALL fields in Excel to the format 'General' instead of eg. Text etc.

   Special Characters - Hopefully this might change in future versions of VM (Virtuemart) - but currently with names/descriptions etc. that require special characters such as ', it really helps to always add a \ before it. It is certainly applicable to categories as well.

   For instance:
   Betty's Biscuit Tin -> Betty\'s Biscuit Tin

3. Save the file as test.xls

Optional but good for an example perhaps?

4. If on WindowsXP, click on Start->Control Panel->Regional Settings->Language->Delimiter Settings  and change the delimiter value (eg. for English(American)) to ; (semicolon)

Everyone has different ways of setting their delimiters. I have found that ; (semi-colon) is particularly useful for me but this may vary for each user. I should add that delimiters separate each column/field (just in case) I have found that changing the delimiter hasn't affected any other programs that I run although it's worth noting that it may affect some programs.

As I am a WindowsXP user I'm not sure if this part is applicable to eg. Linux/Mac users.

5. Save the worksheet from test.xls as test.csv file

It's quite far down the list for the later Office veresion of Excel so you may well have to scroll a bit. It will ask you whether or not you'd like to edit/save etc. when you then change something in the workbook/worksheet, but these can be safely ignored (either clicking Yes/No where applicable)

6. Login to the Joomla! administration backend and click onto the VM (Virtuemart) csv upload. (Use CSV upload) Import/Export tab.

For clarification.

7. Set the delimiter to ; and Field Enclosure Char to none. Check the box for Skip first line Select test.csv and upload the file.   

This is the final step and is very important as well. If all goes well then you should then receive a report with upload succeeded (for products of sku blah)

==============================================================
Hopefully this is of some help to others. It is probably not the most ideal way of doing a csv upload (I tend to limit my product uploads from 50-100 items per csv upload) and so far it certainly doesn't cover the more advanced things at present (such as product attributes/product parent id - will build on this in future posts) but it has definitely worked for me.

Comments, criticisms and suggestions are very welcome! 

[cigarhat]

Not sure if this is the case with anyone else, but Firefox does not allow me to upload using CSV. It gives me a mime type not accepted error. Using IE, everything works as it should.

Thanks for the great tutorial Mosey!

[adriand]

If you are using a mac, I would also suggest saving the file as CSV(Windows).  the regualr CSV format has incompatible line endings.  However, I don't know if a way to change the default delimiter from "," to ";" so I would suggest double checking that you escape all your commas in the text fields.  In the case that you have commas within a field, then make sure to select the "" field enclosure checkbox on the csv upload page (dont' worry, it will "do the right thing(tm)" on fields that don't have the enclosures)

[sesheridan]

Anyone know how to import customers?

I cannot figure out the link field between jos_users and vm_users in the sql db.

[joomlales]

I'm not very advanced with my use of csv but if one was trying to test out the csv upload by using the default fields perhaps this may come in useful?

I use Excel as I'm more familiar with it than say with Access so hope this will be applicable. Step 4 is particularly crucial to this.

==============================================================

1. Enter all the fields in order into separate columns in your excel table.

For the sake of convenience, I have attached a text file (fieldheading.txt) with the csv field headers generated when saved from excel. It should be relatively straightforward to import this back into Excel again.

You'll probably have noticed that the csv upload section now comes with a handy 'skip first line' checkbox, I would recommend that this is checked everytime as thefirst row will be the field-headers.

I have also followed the notation of each field for each column - they should be fairly self-explanatory as well. It is possible to remove all the unnecessary field headers from VM as well as the excel file to save on all this space but for 'attempted tutorial' purposes we will keep to the original.

2. Start populating the table with on a per-row basis for each item.

As this is our first test, I suggest just adding one or two items. If you have no need for certain fields, just leave them blank. It is worth noting that certain fields such as product_sku are always
required.

   Categories - VM does provide a helpful explanation on how it all works. It is brilliant that VM adds categories automatically when using a csv upload.  A quick recap:

         Maincat/subcat1 (can be the standard data inputted into the column for product_categories - means that the item will be categorised to 'subcat1')

         Maincat/subcat1|Maincat/subcat2 ( means item will be categorised to subcat1 and subcat2)

   Manufacturer - If no manufacturers have been added, then I would strongly suggest inputting this field as 1. Otherwise VM tends to generate multiple Generic Manufacturers (if I remember correctly). 1 = default manufacturer.

   Excel Fields - I have also found that it is useful to set ALL fields in Excel to the format 'General' instead of eg. Text etc.

   Special Characters - Hopefully this might change in future versions of VM (Virtuemart) - but currently with names/descriptions etc. that require special characters such as ', it really helps to always add a \ before it. It is certainly applicable to categories as well.

   For instance:
   Betty's Biscuit Tin -> Betty\'s Biscuit Tin

3. Save the file as test.xls

Optional but good for an example perhaps?

4. If on WindowsXP, click on Start->Control Panel->Regional Settings->Language->Delimiter Settings  and change the delimiter value (eg. for English(American)) to ; (semicolon)

Everyone has different ways of setting their delimiters. I have found that ; (semi-colon) is particularly useful for me but this may vary for each user. I should add that delimiters separate each column/field (just in case) I have found that changing the delimiter hasn't affected any other programs that I run although it's worth noting that it may affect some programs.

As I am a WindowsXP user I'm not sure if this part is applicable to eg. Linux/Mac users.

5. Save the worksheet from test.xls as test.csv file

It's quite far down the list for the later Office veresion of Excel so you may well have to scroll a bit. It will ask you whether or not you'd like to edit/save etc. when you then change something in the workbook/worksheet, but these can be safely ignored (either clicking Yes/No where applicable)

6. Login to the Joomla! administration backend and click onto the VM (Virtuemart) csv upload. (Use CSV upload) Import/Export tab.

For clarification.

7. Set the delimiter to ; and Field Enclosure Char to none. Check the box for Skip first line Select test.csv and upload the file.   

This is the final step and is very important as well. If all goes well then you should then receive a report with upload succeeded (for products of sku blah)

==============================================================
Hopefully this is of some help to others. It is probably not the most ideal way of doing a csv upload (I tend to limit my product uploads from 50-100 items per csv upload) and so far it certainly doesn't cover the more advanced things at present (such as product attributes/product parent id - will build on this in future posts) but it has definitely worked for me.

Comments, criticisms and suggestions are very welcome! 

I just thought I'd add a bit to this topic as I don't think other people should get a headache like I did. 

I also had that pesky error while uploading with Firefox. Here's what I did to get around it:

Follow instructions and save as CSV
Open the CSV with Notepad
Select All - Copy
Open new text file with Notepad
Paste
Save Notepad text file as your upload file

A very quirky method. But Excel CSV & TXT files would not upload.

This way works - over and over again!

Les (Joomlales)

[Jeff_S]

I think one of the biggest issues with the CSV import utility, is that the FIELDS USED for UPDATING EXISTING products cannot be used (in almost all cases) for ADDING NEW products. I just spent a few hours trying to add new product to my catalog that VM was reporting as being added, yet no product is there - I had an extra column for "product_publish" field that works like a charm for publishing, unpublishing existing product, but if that field is in my CSV Configuration for adding NEW products, nothing gets added to the catalog, even though it reports that they were added.

This whole system is messed up for sure, and I think the problems lie more within the PHP code, and not the actual CSV files.  I think the code for this system should be revisited and looked at a little deeper. The system should allow for the following:

1. The CSV Import Configuration should not have to be changed after initial setup in the backend

2. Updating Existing/Adding New Product should be able to work in the same CSV Import

3. Since we dont want to overwrite existing db field values if we dont have new field data to import
    3.1 Force checking if a field is in the config, and the "required" option is set to NO:
       3.1.1 If the field is empty, ignore the field altogether - dont update the db with blank/empty fields <-- MOST IMPORTANT
        3.1.2 If the field is NOT empty, update the db.

4. The PHP code should first check the fields to make sure they are required for import, if they are, make sure there is data in the field, if they are not required, check to see if they are NOT empty, if they are not empty, add or update the db entry, if they are empty and NOT required, skip the field and do not update the db.

More to come...

[Edit: Added May 25 2006]

Okay, some other developments I have recently noticed:

First of all, I must state that I am not doing anything fancy with my CSV imports, just straight SKU, short description, full description, name, price, category, tax id, available date, and availability.

I tried adding new products to our catalog today (about 92), and I end getting products being put in multiple categories (some in 2, others in 3 categories) when there is only one category assigned to each product, and all products are supposed to be going into the same category.  Also, getting multiple prices added to catalog for several of the products being added, but not all products. The biggest thing I noticed, is that VM reports 92 items successfully added to the catalog, but only 80-83 of the 92 are in the catalog.

I also then tried breaking up the 92 products into smaller CSV imports (5-10 products) making sure to double check that I was escaping all problematic chars if any (!,', etc) and then do the import.  If I have 5 products being imported, VM says 5 imported, but when I check the categoy, only 4 are there, one has the wrong Manufacturer, and all 4 have multiple prices!! I have attached one of the CSVs in .txt format and a screenshot of the configuration page and the the product page.

This whole CSV system not working properly is getting really frustrating (especially with the amount of product we have that needs to be updated regularily) ... to the point where the owner of the store is seriously considering dropping VM altogether and going with another system.

The CSV SYSTEM NEEDS TO WORK PROPERLY and CONSISTENTLY!

More to come...

[Edit: Added May 25 2006]

Added this to the bugtracker: http://virtuemart.net/index.php?option=com_flyspray&do=details&id=783&Itemid=83&option=com_flyspray&Itemid=83

Side note: I have also tried with different delimeters: , and ; same results.

[attachment deleted by admin]

[Demeva]

One other tip I found after pulling out my hair for 3 days is to make sure that the file names of your images are all in lower case.

[redlens]

csv upload is definately bugged out - hopefully it will be developed in the future. 

Though i would prefer similar funcionatlity within the backend so that products can be added/removed in a table-like environment; adding one by one with childs and parents and product types is too time consuming

[ende42]

because of these issues i end up in:

• not displaying multiple prices at all because i can't stop virtuemart from getting multiple prices from a csv where there are just single prices
• adding a flush database-button which nukes the products- and the product_prices-database
• importing every csv 2 times because after the first import i end up with each product beeing assigned multiple times to the same category and the (!sic) each product showing up multiple times in the backend. a second csv-import fixes this.

this ain't funny. you should have seen our customes faces when i told him: "from time to time you have to nuke the database manually. otherwise your price-database will have 50.000 entries (300 products, updated via cron 5 times a day) at the end of the month. look here: we made a button for this. ah… and did i tell you, that you have to import the csv twice if you don't want to see each product 2 or 3 times in the backend?"

perhaps i should make a new "import csv twice button" or what?

i know that i should be glad, that ppl are giving away a system like joomla + virtuemart for free. but at the moment i hate myself for deciding to choose J+V for this project.

niko.

don't get me wrong: still kudos to soeren for all the hard work!

[Rob Brideson]

This appears to be a major sticking point with me after spending so many hours over the past month to get this to work. There is nothing special about the csv import I am trying to achieve, it is a basic sku, s_proddesc, prod_desc and that is it. I thought I would leave the whole import until I was sure that a test of 1-5 products worked correctly.

I have encountered all of the problems that have been reported here and I am still no closer to resolving the problem. An additional problem is that the information that is being imported is physically located on another server in another country and is on another software called Filemaker which is the controlling database for all products, which was made on a Mac so there are inherent problems there. I am not sure if this Mac problem has been solved in as far as the latest products have been input with a windows pc to negate the extra characters but the fact that the database was constructed on a Mac may well have it's own problems that are added to the mix.

Over the past month we have resorted to cut and paste of each field via the admin panel which is a bit of a pain, but it got the job done but there are currently 500 more products to pull over with more to come as they are sourced. Of course it does not end there as more products are sourced daily and will need to be added to virtuemart once checked etc.

My question to the forum is this. Is there a way of importing the products direct into the products table in sql format without disturbing what is there already and without having to upload the whole database each time?

I am not an expert at this so I am not entirely confident with the MySQL side but would appreciate some insight as to the validity of what I have asked and of security implications. Recently the hosting server crashed to the point where both files and databases were lost and it took 10 days for a message to come back from data recovery that was not good and the site had to effectively be put back together from backups here in 5 days. During this period it was stated to me that csv backups were unstable and caused more problems than solved them and SQL backups would be the better option for the future. Needless to say daily cron jobs set up now backup the database and work very well. Hence my question. I have successfully used the sql backups on one or two occassions in the past week and am in awe at the ease with which this has worked with no problems at all  and wondered how this could be used to solve the csv problem.

[R. de Vroede]

I've managed to get CSV working, specifically with using "column headers" in the export/import.
The problem was in exporting that the column headers where wrong for the exported data.

I am using VirtueMart v1.0.10

Important note: Use "column headers" when exporting/importing. And Regular Upload when importing.

You can find the patch in the bugtracker issue mentioned above:
http://virtuemart.net/index.php?do=details&task_id=783&option=com_flyspray&Itemid=83&option=com_flyspray&Itemid=83

[goodgbb]

I have got these errors while using "CSV upload" from Virtuemart.

Warning: fgetcsv() [function.fgetcsv]: enclosure must be a character in /home/xxx/public_html/xxx/administrator/components/com_virtuemart/classes/ps_csv.php on line 314

Warning: Invalid argument supplied for foreach() in /home/xxx/public_html/xxx/administrator/components/com_virtuemart/classes/ps_csv.php on line 258

Warning: fgetcsv() [function.fgetcsv]: enclosure must be a character in /home/xxx/public_html/xxx/administrator/components/com_virtuemart/classes/ps_csv.php on line 314

Warning: fgetcsv() [function.fgetcsv]: enclosure must be a character in /home/xxx/public_html/xxx/administrator/components/com_virtuemart/classes/ps_csv.php on line 314

Warning: fgetcsv() [function.fgetcsv]: enclosure must be a character in /home/xxx/public_html/xxx/administrator/components/com_virtuemart/classes/ps_csv.php on line 314

Warning: fgetcsv() [function.fgetcsv]: enclosure must be a character in /home/xxx/public_html/xxx/administrator/components/com_virtuemart/classes/ps_csv.php on line 314

Warning: fgetcsv() [function.fgetcsv]: enclosure must be a character in /home/xxx/public_html/xxx/administrator/components/com_virtuemart/classes/ps_csv.php on line 314

By the way, when I use "CSV Imprved" component then I imported my products completely.