Inconsistent CSV file Import error:

piotrekkaminski commented 7 years ago

From @Nym77 on November 20, 2016 18:30

I am using a standard Luma installation with example and I am trying to add products in magento using an automatic generated csv file.

If I use the file BB-ProductsWorking.csv I am able to import the products and file is parsed without errors.
If I am using the file BB-Products.csv I obtain the following error ( Curly quotes used instead of straight quotes in row(s): )
If I remove the rows from the above file the error does not disappear but is present in 4 different rows

Please note also that all files are formatted using the same program and in particular raw 1 and 2 in both BB-ProductsWorking.csv and BB-Products.csv are identical. Therefore they both should be correctly parsed.

Preconditions

Magento Version: last current version with example PhP 7 installed in MAMP

Steps to reproduce

import BB-ProductsWorking.csv and enjoy the results :-)
import BB-Products.csv and experience the error

Expected result

the file BB-Products.csv should be imported correctly or the error message should explain the reason of the failure

Actual result

see short description

files for reproducing the behaviour

BB-Products.zip

Copied from original issue: magento/magento2#7495

piotrekkaminski commented 7 years ago

From @Nym77 on November 21, 2016 8:58

I did some deeper investigation. The file looks correct if you split it import less then 25 products at the time. If you try a larger amount you get the following error: "Curly quotes used instead of straight quotes in row(s):"

piotrekkaminski commented 7 years ago

From @veloraven on November 23, 2016 13:48

@Nym77 thank you for your report. Please identify which version of Magento you are running

piotrekkaminski commented 7 years ago

From @Nym77 on November 24, 2016 8:45

@veloraven Magento 2.1.2 CE

I looked more into that and i found that a product what containing in the short description a " . This was a regular " not a curly one. However it was messing with the csv file.

Still the error report in magento was completely misleading as it was pointing to the wrong kind of error and to the wrong row.

Is there any documentation regarding the proper formatting of a csv file?

piotrekkaminski commented 7 years ago

From @veloraven on November 28, 2016 10:38

@Nym77 thank you for the update.

piotrekkaminski commented 7 years ago

From @tobias-forkel on April 11, 2017 3:9

@Nym77 I had exactly the same problem today. My product CSV file was fine, but for some reason I got the error message Curly quotes used instead of straight quotes in row(s).

After two hours of headache I ended up using Google Spreadsheet.

Open Google Spreadsheet
File > New > Spreadsheet
Upload your xlsx or csv
Import your file to the new spreadsheet
Remove new lines 5.1 Edit > Find and Replace 5.2 Find \n 5.3 Replace with nothing 5.4 Check Search using regular expressions 5.5 Hit the button Replace All

In my case I had to remove new lines for the column categories. You may can skip step 5.

Export the spreadsheet via File > Download as > CSV
Import the downloaded file in System > Import

So it looks like TextEdit on OSx just broke the CSV format all the time during search / replace operations. I didn't try vim or nano.

piotrekkaminski commented 7 years ago

From @IlnitskiyArtem on July 18, 2017 6:24

Hi, @Nym77. Internal ticket MAGETWO-{70803}, which tracks this GitHub issue, is in our issue backlog.

piotrekkaminski commented 7 years ago

From @magento-engcom-team on October 5, 2017 9:30

@Nym77, thank you for your report. We've created internal ticket(s) MAGETWO-70803 to track progress on the issue.

TomashKhamlai commented 6 years ago

I got a little bit different situation. It is reproducible but I found the problems inside the CSV file.

If I am using the file BB-Products.csv I obtain the following error: case2

If I am using the file BB-ProductsRemovedRow.csv I obtain the following error: case3

Th error "Curly quotes used instead of straight quotes in row(s): 1, 2, 84, 85" is caused by wrong formatting in the name column the string 'Ventilatore Portatile Spray FunFan Rosso' should be double-quoted. There is an easy solution to this: get LibreOffice it is free, open-source and cross-platform. Open CSV. When prompted, check that only "Comma" is used as a separator and press "Ok". Save the file after it was loaded without modifying it. And that's it. There will be no error about "Curly quotes"

The error "Wrong URL/path used for attribute image in row(s): 67" is caused by 'ó' symbol inside of 2 columns.

TomashKhamlai commented 6 years ago

@dmanners I checked this and think that Import works good, however, some things can be improved.

dmanners commented 6 years ago

@TomashKhamlai thanks, could you also make a note of any suggested improvement idea you have. That would really help us out.

TomashKhamlai commented 6 years ago

@dmanners, if the error occurs Magento can check if there is a way to parse it in some other way then modify to supported standard and try again. According to comments the source of the error is the way of saving files that were made by TextEdit. It is also known that MS Excel also has problems with CSV. According to the same comments, we can see that Google Sheets it is a good instrument, probably there is some instrument in some composer repository that can do the same as Google Sheets or LibreOffice. The better idea is to open an issue on Microsoft Excel and TextEdit GitHub issue trackers :)))

The best way to improve is to write an article on Magento DevDocs that covers the problems with saving CSV files in the most popular word- and table-processors.

id-graziano commented 6 years ago

I am getting this error on import and it is not due to a funny curly quote or anything like that... it is from a regular apostrophe. After hunting this error down in the code base, I can see that it is occurring at this line: https://github.com/magento/magento2/blob/2.3-develop/app/code/Magento/ImportExport/Model/Import/Source/Csv.php#L82. I am on 2.2.3 right now but the code is the same.

if (strpos($element, "'") !== false) {
    $this->_foundWrongQuoteFlag = true;
    break;
}

Can someone please explain why a regular apostrophe is causing this error? I originally didn't believe it, so I copied that character out of the code base and analyzed it, to make sure it was in fact a regular apostrophe, and it is: U+0027 : APOSTROPHE {APL quote}.

This error is occurring for me because I have an apostrophe inside a text attribute. Is this intended? Are we not allowed to have a regular apostrophe inside a text attribute? If the above is true, how are we supposed to use an apostrophe inside a text attribute?

Just in case anyone was curious, these are the unicode values for actual curly apostrophes:

U+2019 : RIGHT SINGLE QUOTATION MARK {single comma quotation mark}
U+2018 : LEFT SINGLE QUOTATION MARK {single turned comma quotation mark}

magento-engcom / import-export-improvements