Hi there, thanks for a great tool and support forum, it has helped me with a lot of queries in the past but not this one...
I am using magic parser to parse a csv file that i've successfully tested in the demo section. The problem is that the data is corrupted in the database table as it includes data from other columns for some reason. The data this is happening does have white space to the right-hand side but i've used rtrim to remove this.
Any help on why this is happening would be much appreciated?
Hi David, thanks for getting back to me. Your suggestion didn't work unfortunately but I have more information.
I have been using csv|44|1|39 at the bottom of my file and this seems to work for most of the data. Unfortunately it corrupts the data where there is an apostrophe in the product title. Where the product title includes a ' the data is being overlapped by other columns, e.g....
1960's Product
but when the product title is in the following format it is fine...
1960 Product
Any further help on this would be much appreciated as i've spent literally hours on Google trying to find an answer.
Hi,
That's makes sense - if it is apostrophes causing the problem make sure that you are making the data that you insert into your database "safe" for your SQL statement. The easiest way to do this is with PHP's mysql_escpae_string() function, so where you are constructing your SQL, make sure to enclose any variables coming from the XML (via $record in your myRecordHandler function) within the mysql_escape_string() function, e.g.
$sql = "INSERT INTO table SET field = '".mysql_escape_string($record["FIELD"])."'";Hope this helps!
Cheers,
David.
Hi David, thanks for quick reply. I am using the escape string for all inserts so it's really confusing why this is happening, below is the query i'm using...
$products_description ="
insert ignore INTO products_description SET
products_id = '".mysql_real_escape_string($record["ProductCode"] )."',
products_name = '".mysql_real_escape_string($record["ProductName"])."',
products_description = '".mysql_real_escape_string($record["WebDescription"] )."',
language_id = '".mysql_real_escape_string(1)."'
";The ProductName record is where the issues are occurring with the apostrophes.
Kind regards
Will
Hi David, I was wondering if there was any chance you could have a closer look at this if I send you the files - I am happy to pay a fee.
Regards
Will
Hi Will,
It may be corruption in the source data - if you could perhaps post the URL or if it's a file if you could put it online and post a link to it for me (i'll remove the URL before publishing your post), and also let me know how to identify a record that's causing the problem (such as the start of the description that then contains other fields) i'll check it out for you...
Cheers,
David.
Hi David, thanks a lot for helping with this.
The link to the feed is: {link saved}
An live example of a problem product can be viewed at:
{link saved}
This example product is on line 1844 and under the column ProductName in fancy.csv. The ProductName for this particular product is "1960's Groovy Chick Costume".
If you can delete both site links before publishing that would be much appreciated.
Thanks again
Will
Thanks, Will
I see what's going on. In actual fact, the data isn't quoted at all; it's just the first field (ProductCode) that happens to be single-quoted; that's all. Therefore, if parsing using this as a text delimiter, when the ' is encountered in the product name field for the example at line 1844 it is treated as the end of field.
Instead, what I would do is parse without using a text delimiter, e.g. using the Format String:
csv|44|1|0...and then, in your code to process each record, manually strip the quotes from from the ProductCode (if using), e.g.
$record["ProductCode"] = trim($record["ProductCode"],"'");Hope this helps,
Cheers,
David.
Hi David, that worked!!! - thanks so much for your help with this.
Hi there,
What is most likely happening is that some of your data is "quoted text", but it may not be being auto-detected by the parser, particularly if you are not specifying a Format String in the 3 optional parameter to MagicParser_parse().
For example, if you're using comma separated with header row, e.g.
csv|44|1|0...have a go using:
csv|44|1|34(basically, replace the last value with 34 which will force quoted text) - fingers crossed that's all it is!
Cheers,
David.