hi great work by you guys, slowly getting my head around things to do with drupal and drupal commerce.

been trying to import products using feeds and feeds_commerce modules but i keep getting this error on all entries.

SQLSTATE[HY000]: General error: 1366 Incorrect integer value: '' for column 'commerce_price_amount' at row 1

please can you point me in the right direction as to a solution to this issue?

help is gratefully received.

be gentle as i am a drupal newbie

thanks

steve aka zydec2009.

CommentFileSizeAuthor
#7 animalhealthcattle.zip3.54 KBzydec2009

Comments

zydec2009’s picture

zydec2009 commented
Component: Code » Miscellaneous
pcambra’s picture

pcambra
he/him
Primary language Spanish
commented

Hi Steve, could you paste a sample of the prices you're importing?

zydec2009’s picture

zydec2009 commented
Status: Active » Closed (works as designed)

Hi

all my fault, didn't realise that the header: line was wrong. simple mistake on my part but one that will cause problems.

solved now

thanks

imiksu’s picture

imiksu
Location Finland
commented
Status: Closed (works as designed) » Active

Could you be more specific what was wrong? I'm experiencing now the same error message failing with 903 of 3991 products saying that error message and then in the end with message "SQLSTATE[21S01]: Insert value list does not match column list: 1136 Column count doesn't match value count at row 1".

This is not happening with 1.0 version, started failing when using 1.1. I'm using feeds 2.0-alpha3 with 1110762-2 patch.

imiksu’s picture

imiksu
Location Finland
commented
Issue tags: +error, +Drupal, +import, +sql, +Feeds, +products, +commerce
pcambra’s picture

pcambra
he/him
Primary language Spanish
commented
Issue tags: -error, -Drupal, -import, -sql, -Feeds, -products, -commerce

iMiksu can you please provide a sample of failing data?

zydec2009’s picture

zydec2009 commented
StatusFileSize
new animalhealthcattle.zip3.54 KB

here is the data base i was trying to use to load up the products.

i did get it to work once but have since done a clean install and it hasn't worked for me since still giving the error i got in my main posts

also it will only bother to look at it if i make it no header.

hope this helps

pcambra’s picture

pcambra
he/him
Primary language Spanish
commented

zydec2009, in your case, I get these errors and not the original that you posted in the issue:

SQLSTATE[HY000]: General error: 1366 Incorrect string value: '\xA0' for column 'title' at row 1
Error message SQLSTATE[HY000]: General error: 1366 Incorrect string value: '\xA0',\x0A ...' for column 'message' at row 1

These are due the "strange titles" that you have in your file:

Milk Barﾙ 5 compartment calf feeder
Milk Barﾙ Spare Teat

This has nothing to do with Commerce feeds but feeds itself.

zydec2009’s picture

zydec2009 commented

hi thanks

solves that problem then. i will remove those characters and see if that makes this one work.

your right it wasnt the original database, i completely rewrote the database to see if that would solve it.

if you want i will upload the original database that threw up the first error.

zydec2009’s picture

zydec2009 commented

right removed those characters and got the following errors

SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '' for key 2

SQLSTATE[HY000]: General error: 1366 Incorrect string value: '\xA0 A co...' for column 'message' at row 1

still doesn't do what it says on the tin

rfay’s picture

rfay
he,him
Primary language English
Location Palisade, CO, USA
commented

@zydec2009, you seem to have already an entry with "message" as '', and now you're trying to re-import, and that's a unique field.

Are you importing products? If not, this is a feeds issue. It also seems that you're not giving the full error message here. The complete error message would help us help you.

I also recommend to you the Commerce Devel module, which will give more context on some errors (most errors).

zydec2009’s picture

zydec2009 commented

not to worry anymore, got fed up with drupal 7 and the extra workload it would have placed on my project. and have now gone back to drupal 6 and ubercart.

yeah i know i have to enter it all manually again (because ive been put off of feeds)

the errors where exactly the ones displayed on my screen, and where not re imports in fact the last one was done on a clean install of 7 and drupal commerce, and on a new sql database.

has this been tested on all versions of office excel exported csv's ?? or on them at all ?? just a thought as to what might be causing the initial errors all the time, as i have said only one imported correctly and that was more by luck than judgement.

zydec2009’s picture

zydec2009 commented

i would however be happy to try on a complete new install try that database again and paste up the errors if any it gives me.

rfay’s picture

rfay
he,him
Primary language English
Location Palisade, CO, USA
commented
Title: Can anyone help me with this error. » SQLSTATE[HY000]: General error: 1366 Incorrect string value: '\xA0' for column 'title' at row 1
Status: Active » Fixed

Please feel free to reopen this or create a new one.

Please use a more descriptive title for all issues you create though.

Changing the title for posterity.

rfay’s picture

rfay
he,him
Primary language English
Location Palisade, CO, USA
commented

Sorry - forgot to ask your question.

has this been tested on all versions of office excel exported csv's ?? or on them at all ?? just a thought as to what might be causing the initial errors all the time, as i have said only one imported correctly and that was more by luck than judgment.

There is really no such thing as an "office excel csv". There's just CSV. Office may create new problems (it always has). But it's the responsibility of the person doing imports to validate that the character set and content of the CSV are correct. You may have had a character set issue.

But the bottom line: Getting the CSV ready for import is not a feeds issue. It has absolutely *nothing* to do with Commerce Feeds.

zydec2009’s picture

zydec2009 commented

ok bash the newbie

at this rate actinic is looking better and better

imiksu’s picture

imiksu
Location Finland
commented

The original problem was about invalid integer value.. See this issue which might cause this problem: #1183006: Price of zero gets dropped (because if empty() is used)

Status: Fixed » Closed (fixed)

Automatically closed -- issue fixed for 2 weeks with no activity.

DecoHokie’s picture

DecoHokie commented

I just had a similar issue and resolved it by removing blank cells at the end of the Excel document and thus cleaned up the .CSV file.

ursula’s picture

ursula commented

I posted a fix for the character set problem in the Feeds issue queue. Might work for this issue as well.
http://drupal.org/node/1140194#comment-4858112

iwerksom’s picture

iwerksom commented

Excel 2010 got a nasty habbit of adding "" so when you save the csv file

sku,title,price,image
a12355455,"Another one for the road",1000,"http://ecx.images-amazon.com/images/I/51JQbhO0MmL.jpg"

is changed to

sku,title,price,image
"a12355455,""Another one for the road"",1000,""http://ecx.images-amazon.com/images/I/51JQbhO0MmL.jpg"""

When I try to import the file i get the mentioned error

applynx’s picture

applynx commented

I received a similar error with the default Excel character encoding. Exporting the CSV with character set 'UTF-8' resolved the issue.

hockey2112’s picture

hockey2112 commented

#22 Fixed it for me too. Thanks!

squarecandy’s picture

squarecandy commented

Thanks applynx and others - this thread was helpful.
Seems like a feeds documentation issue mostly.

CSV files must be:
* encoded w/ windows line endings
* UTF-8 character encoding
* Free of extra blank records/lines

techmsi’s picture

techmsi commented

Adding to what squarecandy wrote:

  1. Open the file in Text Wrangler
  2. At the bottom change the encoding to Unicode(UTF-8)
  3. Then change the line endings to Widows(CRLF)

This worked for me.
I hope it helps.

purabdk’s picture

purabdk commented

I am thinking convering the whole txt file to UTF-format and parsing the CSV is not good idea. Instead of that you can use following code.
It issue happen when character set is not in UTF-8 format.
If you got the error while using the db_insert then you can use the following code
$val = mb_check_encoding($val, 'UTF-8') ? $val : utf8_encode($val);

if it is just matter of some field values.

qartz’s picture

qartz commented
Issue summary: View changes

I've been getting plenty of such errors while trying to import html pages from remote website with Feeds Crawler. This bug appears when Drupal tries to put some fetched non-utf8 string to the unicode mysql table, which is a common situation for non-english Drupal installations.

To workaround this issue, open your feeds/plugins/FeedsHTTPFetcher.inc

and change line 54 from

$this->raw = $result->data;

to

$this->raw = mb_convert_encoding($result->data, "UTF-8", "CP1251");

Don't forget to change 'CP1251' to whatever charset your source site has. This hack needs mbstring to be installed.