PDA

View Full Version : easy populate question



Tangwystel
08-16-2011, 07:55 AM
1136 - Column count doesn't match value count at row 1

INSERT INTO products_groups VALUES ( 1, 11, 22 )

I have downloaded my csv file via EP, added about 36 entries as a test, simply copying and pasting the entire row, so everything seems to be identical, and now I keep getting this error. It seems to be a common error (it's been posted on these boards before) but I haven't found a solution. I'm using OpenOffice on a Mac to do the spreadsheet.

Also, can you add a product from scratch into the database or do you have to enter the model number into OscMax first? When I go to "add new only" from EP, it tells me the model numbers do not exist.

ridexbuilder
08-16-2011, 08:28 AM
1136 - Column count doesn't match value count at row 1

Can you post a portion of your file - 1st couple of entries - wrap it in the [ CODE ] tags?


Also, can you add a product from scratch into the database or do you have to enter the model number into OscMax first? When I go to "add new only" from EP, it tells me the model numbers do not exist.
That is the major 'selling point' of EP: the ability to add hundreds/thousands of products easily. Try choosing 'normal'. Remember model numbers must be unique.

Tangwystel
08-16-2011, 09:42 AM
I do have unique model numbers. That's not the problem. I should have noted earlier that the first few entries upload OK and then it stops with a later entry, so I'll try to put a few entries here.

I'm not sure what you mean by wrapping it in the [code] tags - sorry, I'm a novice. I uploaded the csv file to www.unplannedpeacock.com/catalog/sampleEP.csv

ridexbuilder
08-16-2011, 09:56 AM
See these little icons above where you type in a post ^^^^^^
Obviously, post the relevant portion of the csv file - preferably the last good one followed by the failing entry.


:bonk:

Tangwystel
08-16-2011, 10:12 AM
"v_products_model" "v_products_name_1" "v_products_description_1" "v_products_url_1" "v_tab1_1" "v_tab2_1" "v_tab3_1" "v_tab4_1" "v_tab5_1" "v_tab6_1" "v_products_name_2" "v_products_description_2" "v_products_url_2" "v_tab1_2" "v_tab2_2" "v_tab3_2" "v_tab4_2" "v_tab5_2" "v_tab6_2" "v_products_name_3" "v_products_description_3" "v_products_url_3" "v_tab1_3" "v_tab2_3" "v_tab3_3" "v_tab4_3" "v_tab5_3" "v_tab6_3" "v_products_image" "v_products_msrp" "v_products_hide_from_groups" "v_products_qty_blocks" "v_products_min_order_qty" "v_products_price" "v_products_specials_price" "v_customer_price_1" "v_customer_group_id_1" "v_customer_price_2" "v_customer_group_id_2" "v_customer_price_3" "v_customer_group_id_3" "v_customer_price_4" "v_customer_group_id_4" "v_products_quantity" "v_products_weight" "v_date_avail" "v_date_added" "v_categories_image_1" "v_categories_name_1_1" "v_categories_name_1_2" "v_categories_name_1_3" "v_categories_image_2" "v_categories_name_2_1" "v_categories_name_2_2" "v_categories_name_2_3" "v_categories_image_3" "v_categories_name_3_1" "v_categories_name_3_2" "v_categories_name_3_3" "v_categories_image_4" "v_categories_name_4_1" "v_categories_name_4_2" "v_categories_name_4_3" "v_categories_image_5" "v_categories_name_5_1" "v_categories_name_5_2" "v_categories_name_5_3" "v_categories_image_6" "v_categories_name_6_1" "v_categories_name_6_2" "v_categories_name_6_3" "v_categories_image_7" "v_categories_name_7_1" "v_categories_name_7_2" "v_categories_name_7_3" "v_manufacturers_name" "v_attribute_options_id_1" "v_attribute_options_name_1_1" "v_attribute_options_name_1_2" "v_attribute_options_name_1_3" "v_attribute_values_id_1_1" "v_attribute_values_name_1_1_1" "v_attribute_values_name_1_1_2" "v_attribute_values_name_1_1_3" "v_attribute_values_price_1_1" "v_attribute_values_stock_1_1" "v_attribute_values_id_1_2" "v_attribute_values_name_1_2_1" "v_attribute_values_name_1_2_2" "v_attribute_values_name_1_2_3" "v_attribute_values_price_1_2" "v_attribute_values_stock_1_2" "v_attribute_values_id_1_3" "v_attribute_values_name_1_3_1" "v_attribute_values_name_1_3_2" "v_attribute_values_name_1_3_3" "v_attribute_values_price_1_3" "v_attribute_values_stock_1_3" "v_attribute_options_id_2" "v_attribute_options_name_2_1" "v_attribute_options_name_2_2" "v_attribute_options_name_2_3" "v_attribute_values_id_2_1" "v_attribute_values_name_2_1_1" "v_attribute_values_name_2_1_2" "v_attribute_values_name_2_1_3" "v_attribute_values_price_2_1" "v_attribute_values_stock_2_1" "v_attribute_values_id_2_2" "v_attribute_values_name_2_2_1" "v_attribute_values_name_2_2_2" "v_attribute_values_name_2_2_3" "v_attribute_values_price_2_2" "v_attribute_values_stock_2_2" "v_tax_class_title" "v_status" "EOREOR"
"8111103" "Cardinal Bombyx Silk Top" "<p> Our 100% Silk Top is a soft, easy to spin 100% long staple silk. This stuff is so luxurious you could swim in it. 125 g per braid.</p>" "cardinalST.jpg" "0.0000" "@" "1" "1" "38" 19 1 "1" "0.250" "2011-08-08 10:58:31" "buntingSilkDK.jpg" "Yarn and Dyed Fiber" "cardinalTS.jpg" "Red Colorways" "cardinalTS.jpg" "Cardinal" "2" "Which Club?" "2" "Socks Only!" "3" "Peacock Potpourri" "4" "I'll Spin my Own" "3" "Do you want it carded?" "5" "Yes!" "6" "No" "Taxable Goods" "Active" "EOREOR"
81111015 "Cardinal Whisper Lace " "<p> Our Whisper Lace is a drapey and super soft and silky 10 percent Cashmere, 20 percent Silk, 70 percent Alpaca blend. It comes in huge 100 g skeins of 1300 yards. This yarn takes color very delicately, so expect a lighter variation of this colorway.</p>" "cardinalWL.jpg" "0.0000" "@" "1" "1" 44 22 "1" "1" "0.250" "2011-08-08 11:00:42" "buntingSilkDK.jpg" "Yarn and Dyed Fiber" "cardinalTS.jpg" "Red Colorways" "cardinalTS.jpg" "Cardinal" "2" "Which Club?" "2" "Socks Only!" "3" "Peacock Potpourri" "4" "I'll Spin my Own" "3" "Do you want it carded?" "5" "Yes!" "6" "No" "Taxable Goods" "Active" "EOREOR"

Tangwystel
08-16-2011, 10:25 AM
I noticed that Open Office wasn't adding quotes to my entered fields, so I tried it in Excel, and I get the same error.

ridexbuilder
08-16-2011, 12:29 PM
In OpenOffice/LibreOffice...
Format the 1st column as text if your Product Model No. has no letters.
File Save as, filename.csv, Edit Filter Settings, Field Delimiter {Tab}, OK

Tangwystel
08-16-2011, 12:57 PM
I'm getting the same error with that way of saving. I noticed that if I cut and paste an item into the next row of the spread sheet, it works fine, even if I change some of the text. However, as soon as I change the v_customer_price_1 and the v_customer_group_id_1 field even using the same exact everything else, it gives me an error.

ridexbuilder
08-16-2011, 01:41 PM
That is also because it is numerical, format the columns as text.









[Join us for the next gripping instalment in this SCalc tutorial. :p]

ridexbuilder
08-16-2011, 01:52 PM
An alternative, might be to change EP and your spreadsheet to use non-quoted text. See inline documentation for EP, within Admin.

Tangwystel
08-16-2011, 03:24 PM
I tried formatting those columns as text, and it didn't make a difference. Still got the error.

ridexbuilder
08-16-2011, 04:16 PM
Opening up with a text editor will show up columns (fields) that are still numeric i.e. the number is not wrapped in double quotes.

Tangwystel
08-16-2011, 05:10 PM
"v_products_model","v_products_name_1","v_products_description_1","v_products_url_1","v_tab1_1","v_tab2_1","v_tab3_1","v_tab4_1","v_tab5_1","v_tab6_1","v_products_name_2","v_products_description_2","v_products_url_2","v_tab1_2","v_tab2_2","v_tab3_2","v_tab4_2","v_tab5_2","v_tab6_2","v_products_name_3","v_products_description_3","v_products_url_3","v_tab1_3","v_tab2_3","v_tab3_3","v_tab4_3","v_tab5_3","v_tab6_3","v_products_image","v_products_msrp","v_products_hide_from_groups","v_products_qty_blocks","v_products_min_order_qty","v_products_price","v_products_specials_price","v_customer_price_1","v_customer_group_id_1","v_customer_price_2","v_customer_group_id_2","v_customer_price_3","v_customer_group_id_3","v_customer_price_4","v_customer_group_id_4","v_products_quantity","v_products_weight","v_date_avail","v_date_added","v_categories_image_1","v_categories_name_1_1","v_categories_name_1_2","v_categories_name_1_3","v_categories_image_2","v_categories_name_2_1","v_categories_name_2_2","v_categories_name_2_3","v_categories_image_3","v_categories_name_3_1","v_categories_name_3_2","v_categories_name_3_3","v_categories_image_4","v_categories_name_4_1","v_categories_name_4_2","v_categories_name_4_3","v_categories_image_5","v_categories_name_5_1","v_categories_name_5_2","v_categories_name_5_3","v_categories_image_6","v_categories_name_6_1","v_categories_name_6_2","v_categories_name_6_3","v_categories_image_7","v_categories_name_7_1","v_categories_name_7_2","v_categories_name_7_3","v_manufacturers_name","v_tax_class_title","v_status","EOREOR"
"0808111","Cardinal Merino and Silk Laceweight","<p> Our Merino and Silk Laceweight comes in 100g skeins of 1300 yards. &nbsp;It is a bouncy, easy to block laceweight yarn.</p>",,,,,,,,,,,,,,,,,,,,,,,,,,"cardinalSML.jpg","0.0000","@","1","1","30",,"15","1",,,,,,,"1","0.250",,"2011-08-08 11:00:00","buntingSilkDK.jpg","Yarn and Dyed Fiber",,,"cardinalTS.jpg","Red Colorways",,,"cardinalTS.jpg","Cardinal",,,,,,,,,,,,,,,,,,,,"Taxable Goods","Active","EOREOR"
"080817","Yarn Club Bill Me","<p> Want to pay right before each shipment is shipped? &nbsp;This is the way to do it. &nbsp;You&#39;ll pay for the first shipment now, and then each month and a half (approximately), you&#39;ll be billed with Paypal right before I ship. &nbsp;This will continue until you tell me to stop sending.</p> <p> &nbsp;</p> <p> You can pick whether you&#39;d like to get the &quot;Peacock Potpourri&quot; yarn (a mixture of weights), the &quot;Socks Only&quot; yarn (Diva Sock, Twisty Sock, or Peacock Sock) or the &quot;I&#39;ll Spin My Own&quot; fiber club (100g of Merino Top). &nbsp;</p>",,,,,,,,,,,,,,,,,,,,,,,,,,"yarnclubbutton.jpg","0.0000","@,1","1","1","30",,,,,,,,,,"1","1.000",,"2011-08-08 11:22:58","club.jpg","Clubs",,,"yarnclubsample.jpg","Yarn and Fiber Club",,,,,,,,,,,,,,,,,,,,,,,,"Taxable Goods","Active","EOREOR"
"0808116","One Shot Yarn Club","<p> Want to try out our yarn club? &nbsp;This is the way to do it. &nbsp;You can get one installment of our yarn club as a sampler. &nbsp;If you want the auto-renewing Paypal option, please go to <a href=""http://www.unplannedpeacock.com/catalog/product_info.php?cPath=11_24&amp;products_id=9"">Yarn Club - Bill me</a>.</p> <p> &nbsp;</p> <p> You can pick whether you&#39;d like to get the &quot;Peacock Potpourri&quot; yarn (a mixture of weights), the &quot;Socks Only&quot; yarn (Diva Sock, Twisty Sock, or Peacock Sock) or the &quot;I&#39;ll Spin My Own&quot; fiber club (100g of Merino Top). &nbsp;</p>",,,,,,,,,,,,,,,,,,,,,,,,,,"yarnclubbutton.jpg","0.0000","@,1","1","1","30",,,,,,,,,,"1","1.000",,"2011-08-08 11:23:26","club.jpg","Clubs",,,"yarnclubsample.jpg","Yarn and Fiber Club",,,,,,,,,,,,,,,,,,,,,,,,"Taxable Goods","Active","EOREOR"
"8111101","Cardinal Top","<p> Our Merino Top is a soft, easy to spin 100% long staple merino. 100 g per braid.</p>",,,,,,,,,,,,,,,,,,,,,,,,,,"cardinalMT.jpg","0.0000","@","1","1","15",,"7.5","1",,,,,,,"1","0.250",,"2011-08-08 10:58:00","buntingSilkDK.jpg","Yarn and Dyed Fiber",,,"cardinalTS.jpg","Red Colorways",,,"cardinalTS.jpg","Cardinal",,,,,,,,,,,,,,,,,,,,"Taxable Goods","Active","EOREOR"

As far as I can tell, all my quotes, etc, are in the right place. I changed EP to comma as the field separator. I downloaded a fresh EP file, tried to change the prices, still getting the error on update.

Tangwystel
08-16-2011, 06:23 PM
Ugh, I'm giving up. Tomorrow I'll try entering a full product set into OscMax and then downloading the whole thing, then seeing if I can cut and paste from there (50 products at a time). I sure hope it works, otherwise it's gonna be a night mare doing it all on the web.

ridexbuilder
08-17-2011, 12:24 AM
Exclude fields with no data - make it easier to debug.
Upload product/categories first, then other fields.

Tangwystel
08-17-2011, 04:07 AM
The same thing is still happening - I entered in 15 new products with new model numbers, excluded the blank columns, put the categories first, made sure everything was text, the field delimiter was commas. It uploads 1 entry fine and I get the "New Product!" message, then I get the same

1136 - Column count doesn't match value count at row 1

INSERT INTO products_groups VALUES ( 1, 7.5, 34 )

I don't understand why it works with one, but not the rest. Everything seems to be the same.

In fact, I can cut and paste the exact same line 3 times (changing model numbers), it will upload the first one and then the error will occur. I don't see how the column value count is changing between the first product and the second no matter what I do to try to guarantee it is the same.

Tangwystel
08-17-2011, 04:12 AM
"v_categories_image_1","v_categories_name_1_1","v_categories_image_2","v_categories_name_2_1","v_categories_image_3","v_categories_name_3_1","v_products_model","v_products_name_1","v_products_description_1","v_products_image","v_products_msrp","v_products_hide_from_groups","v_products_qty_blocks","v_products_min_order_qty","v_products_price","v_customer_price_1","v_customer_group_id_1","v_products_quantity","v_products_weight","v_date_added","v_tax_class_title","v_status","EOREOR"
"buntingSilkDK.jpg","Yarn and Dyed Fiber","cardinalTS.jpg","Red Colorways","cardinalTS.jpg","Cardinal","081701","Cardinal Merino and Silk Laceweight","<p> Our Merino and Silk Laceweight comes in 100g skeins of 1300 yards. &nbsp;It is a bouncy, easy to block laceweight yarn.</p>","cardinalSML.jpg","0.0000","@","1","1","30","15","1","1","0.250","2011-08-08 11:00:00","Taxable Goods","Active","EOREOR"
"buntingSilkDK.jpg","Yarn and Dyed Fiber","cardinalTS.jpg","Red Colorways","cardinalTS.jpg","Cardinal","081702","test1","<p> Our Merino and Silk Laceweight comes in 100g skeins of 1300 yards. &nbsp;It is a bouncy, easy to block laceweight yarn.</p>","cardinalSML.jpg","0.0000","@","1","1","30","15","1","1","0.250","2011-08-08 11:00:00","Taxable Goods","Active","EOREOR"
"buntingSilkDK.jpg","Yarn and Dyed Fiber","cardinalTS.jpg","Red Colorways","cardinalTS.jpg","Cardinal","081703","test2","<p> Our Merino and Silk Laceweight comes in 100g skeins of 1300 yards. &nbsp;It is a bouncy, easy to block laceweight yarn.</p>","cardinalSML.jpg","0.0000","@","1","1","30","15","1","1","0.250","2011-08-08 11:00:00","Taxable Goods","Active","EOREOR"

ridexbuilder
08-17-2011, 04:18 AM
Just done a test with my own data set.
Methinks PGM may have borked EP when he last updated it (sure it was 'im) ;)
Time to add this to the bugtracker, Tangwystel