CSV import doesn’t quote a strings in only 2 rows

  • Resolved snyggapa

    (@snyggapa)


    5 years, 2 months ago

    Hi Peter

    Thank you for your great work (and also the new “truncate before import” checkbox on importing CSVs saves a manual step every time. excellent)

    I do have one mystery though where WPDA is not quoting a string on only two records in a CSV file, which is causing an import error on only 2 rows out of 359.

    WDPA knows the rows fails but I had to use query monitor to extract the query. It is a big insert statement (I can provide the full lot and table design if you need it) but the relevant part is below – where the english line starting SM13 is not being quoted by WPDA for some reason. On 357 out of the 359 rows that line gets quoted and on two it fails. Note that the CSV itself is not quoted but there are no commas in it so I believe this is not necessary.

    Any help where to look would be appreciated. Thanks

    Steve

    ,'SM13'
,'SM13'
,'SM13'
,SM13 is for swimmers with a visual impairment.<br/><br/> Their peripheral vision is restricted and/or they are able to see the end of the pool at a maximum of five metres.
,'SM13 est destiné aux nageurs ayant une déficience visuelle.<br/><br/>Leur vision périphérique est restreinte et / ou ils peuvent voir le bout de la piscine à un maximum de cinq mètres.'
Viewing 9 replies - 1 through 9 (of 9 total)
  • Thread Starter snyggapa

    (@snyggapa)

    5 years, 2 months ago

    update – if I catch the SQL for the 2 rows that fail in Query Monitor plugin, paste into notepad and quote the 2 string, stick a ; and carriage return on the end I can upload the .SQL into “import data/ execute scripts” part of data explorer and the two rows go in OK, so that proves that fundamentally the system is OK, just missing the quotes around one string is very odd.

    charles godwin

    (@charlesgodwin)

    5 years, 2 months ago

    Check the tool / program generating the CSV. Perhaps it has a setting to do strict quoting. some tools try to be “efficient” and generate as few quotes as possible.

    Thread Starter snyggapa

    (@snyggapa)

    5 years, 2 months ago

    sadly it’s Excel and you get what you are given. In Microsoft’s defence, it doesn’t actually need to be quoted in the CSV as it contains no commas – and the 2 rows affected look materially no different to the 300+ that work which also are not quoted.

    WPDA picks up the unquoted columns and quotes them for the Insert in 99% of the rows but these two don’t get quoted…It’s a true oddity!

    charles godwin

    (@charlesgodwin)

    5 years, 2 months ago

    I have a python tool that cleans this stuff up. I currently don’t publish it but if there’s interest, I can.

    It also strips those phantom columns on the right and blank rows.

    Thread Starter snyggapa

    (@snyggapa)

    5 years, 2 months ago

    Hi Charles, kind offer, thanks but I can handle it for now – only two rows and I don’t intend to do this often – but possibly something that the plugin should handle (or at least review in case there is a wider issue)

    Thanks

    Plugin Author Passionate Programmer Peter

    (@peterschulznl)

    5 years, 2 months ago

    Hi Guys,

    Interesting discussion! I wonder what I can do to prevent this error in WPDA?

    @snyggapa Can you send me a part of your CSV file containing those 2 rows that don’t import and two rows that do?

    @charlesgodwin How do you cleans this stuff in python? How can I do it with php?

    Thanks to both of you! 🙂
    Peter

    Thread Starter snyggapa

    (@snyggapa)

    5 years, 2 months ago

    not sure if the forum will like the multiple language UTF-8 characters but here you go :). Sorry for the header.. excel!

    first 2 rows go in , second two fail – Script (English) column is the troublesome one

    I filled i your contact form if you could reply I can send you a fuller example if this isn’t enough. Thanks

    Sport,EN Sport order,FRENCH Sport name,FR Sport order,JAPANESE Sport name,JA Sport order,Sub-Sport,FR Sub-Sport,JA Sub-Sport,Impairment Group,FR Impairment Group,JA Impairment Group,Type,Category,FR Category,JA Category,Script (English),FRENCH Script,JAPANESE Script,Button Colour,Displaygroup,Image 1,Heading 1,CharacterVid 1,Image 2,Heading 2,CharacterVid 2,Image 3,Heading 3,CharacterVid 3,Image 4,Heading 4,CharacterVid 4,Image 5,Heading 5,CharacterVid 5,Image 6,Heading 6,CharacterVid 6,Image 7,Heading 7,CharacterVid 7,Image 8,Heading 8,CharacterVid 8,Image 9,Heading 9,CharacterVid 9,Image 10,Heading 10,CharacterVid 10,In Other Sports,Video Link 1,Video Link 2,Team Size,Team Points,Team Example 1,Team GROUP 1,Team Example 2,Team GROUP 2,URL,FRENCH URL,JAPANESE URL,Summer or Winter Sport,Tokyo 2020 Event,Beijing 2022 Event,DisplayStyle,HeaderImage,Extra Info,Men’s Events,Women’s Events,Mixed Events,On-screen Class Title,,,,,,,,,,,SLOVAK Sport NAME,SK Sport Order,SK Sub-Sport,SK Impairment Group,SK Category,SLOVAK Script,slovak new url,,URL,,,FRENCH URL,,,,,,,,,,,,,,,,,,,,,,,,,,,,`

    Swimming,,Natation,,水泳,,Individual Medley,Quatre nages,個人メドレー,VISUAL,VISUEL,視覚障害,,SM11,SM11,SM11,SM11 is for swimmers with near-total visual impairment.<br><br/>All swim in blacked-out goggles. Head tappers and stroke-counting are used to precisely execute the turns.,SM11 est destiné aux nageurs ayant une déficience visuelle quasi totale.<br><br/>Tous nagent avec des lunettes noircies. Pour exécuter les virages avec précision le nageur utilise le comptage de coups et l’aide d’un assistant muni d’une perche au bout rembourré.,SM11は、ほぼ完全な視覚障害の水泳選手のクラスです。<br><br/>全員が目隠しをして泳ぎます。ターンを正確に行うためヘッドタッパーとストロ－クカウンティングが使用されます。,1,22,37,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,Y,,,,,,,,,/sports/swimming/individual-medley/sm11,/fr/sports/natation/quatre-nages/sm11,/ja/sports/swimming/individual-medley/sm11,Summer,Y,,2,,,200 Individual Medley,200 Individual Medley,/,SM11,,,,,,,,,,,Plávanie,,Individuálna medza,VIZUÁLNE,SM11,SM11 je pre plavcov s takmer totálnym poškodením zraku.<br><br/>všetky plávajú v skanzených panách. Na presné vykonanie otáčania sa používajú hlavové plienky a počítanie zdvihu.,/sk/sports/plavanie/individualna-medza/sm11,,/sports/swimming/individual-medley/sm11,/sports/swimming/individual-medley/sm11,Y,/fr/sports/natation/quatre-nages/sm11,/fr/sports/natation/quatre-nages/sm11,Y,,,,,,,,,,,,,,,,,,,,,,,,,,
Swimming,,Natation,,水泳,,Individual Medley,Quatre nages,個人メドレー,VISUAL,VISUEL,視覚障害,,SM12,SM12,SM12,SM12 is for swimmers with a visual impairment.<br/><br/>They have a restricted field of view and some cannot see the black line on the bottom of the pool. They can see the end of lane from one metre away.,Le SM12 est destiné aux nageurs malvoyants.<br/><br/>Ils ont un champ de vision restreint et certains ne peuvent pas voir la ligne noire au fond de la piscine. Ils peuvent voir le bout de la ligne de nage à un mètre de distance.,SM12は、視覚障害のある水泳選手のクラスです。<br><br/>これらの選手は、視野が限られており、プール底面の黒い線が見えない人がいます。選手たちがレーンの端を見ることができるのは、1メートル離れたところからとなります。,9,22,29,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,Y,,,,,,,,,/sports/swimming/individual-medley/sm12,/fr/sports/natation/quatre-nages/sm12,/ja/sports/swimming/individual-medley/sm12,Summer,N,,2,,,/,/,/,SM12,,,,,,,,,,,Plávanie,,Individuálna medza,VIZUÁLNE,SM12,SM12 je pre plavcov s poškodením zraku.<br/><br/>majú obmedzené zorné pole a niektorí nevidia čiernu čiaru na spodnej strane bazéna. Môžu vidieť koniec jazdného pruhu z jedného metra smerom od neho.,/sk/sports/plavanie/individualna-medza/sm12,,/sports/swimming/individual-medley/sm12,/sports/swimming/individual-medley/sm12,Y,/fr/sports/natation/quatre-nages/sm12,/fr/sports/natation/quatre-nages/sm12,Y,,,,,,,,,,,,,,,,,,,,,,,,,,
Swimming,,Natation,,水泳,,Individual Medley,Quatre nages,個人メドレー,VISUAL,VISUEL,視覚障害,,SM13,SM13,SM13,SM13 is for swimmers with a visual impairment.<br/><br/> Their peripheral vision is restricted and/or they are able to see the end of the pool at a maximum of five metres.,SM13 est destiné aux nageurs ayant une déficience visuelle.<br/><br/>Leur vision périphérique est restreinte et / ou ils peuvent voir le bout de la piscine à un maximum de cinq mètres.,SM13は、視覚障害のある水泳選手のクラスです。<br><br/>選手たちの周辺視野が限られています、および/または、選手たちがプールの端を見ることができるのは、最大5メートル離れたところからとなります。,13,22,36,,https://vimeo.com/315833397/d2844b8397  |  https://vimeo.com/315828761/5cf5813803  | https://vimeo.com/315678125/5afe850189  |  https://vimeo.com/315819538/a600fed566,,,,,,,,,,,,,,,,,,,,,,,,,,,,Y,,,,,,,,,/sports/swimming/individual-medley/sm13,/fr/sports/natation/quatre-nages/sm13,/fr/sports/swimming/individual-medley/sm13,Summer,Y,,2,,,200 Individual Medley,200 Individual Medley,/,SM13,,,,,,,,,,,Plávanie,,Individuálna medza,VIZUÁLNE,SM13,SM13 je pre plavcov s poškodením zraku.<br/><br/> ich periférne videnie je obmedzené a/alebo môžu vidieť koniec bazéna maximálne päť metrov.,/sk/sports/plavanie/individualna-medza/sm13,,/sports/swimming/individual-medley/sm13,/sports/swimming/individual-medley/sm13,Y,/fr/sports/natation/quatre-nages/sm13,/fr/sports/natation/quatre-nages/sm13,Y,,,,,,,,,,,,,,,,,,,,,,,,,,
Swimming,,Natation,,水泳,,Individual Medley,Quatre nages,個人メドレー,VISUAL,VISUEL,視覚障害,,SM13 (SM12-13),SM13 (SM12-13),SM13 (SM12-13),S13 is for swimmers with a visual impairment.<br/><br/> Their peripheral vision is restricted and/or they are able to see the end of the pool at a maximum of five metres.,S13 est destiné aux nageurs ayant une déficience visuelle.<br/><br/>Leur vision périphérique est restreinte et / ou ils peuvent voir le bout de la piscine à un maximum de cinq mètres.,S13は、視覚障害のある水泳選手のクラスです。<br><br/>選手たちの周辺視野が限られています、および/または、選手たちがプールの端を見ることができるのは、最大5メートル離れたところからとなります。,17,22,36,,https://vimeo.com/315833397/d2844b8397  |  https://vimeo.com/315828761/5cf5813803  | https://vimeo.com/315678125/5afe850189  |  https://vimeo.com/315819538/a600fed566,29,,,,,,,,,,,,,,,,,,,,,,,,,,,N,,,,,,,,,/sports/swimming/individual-medley/sm13-sm12-13,/fr/sports/natation/quatre-nages/sm13-sm12-13,/ja/sports/swimming/individual-medley/sm13-sm12-13,Summer,Y,,2,,,200 Individual Medley,200 Individual Medley,/,SM13,,,,,,,,,,,Plávanie,,Individuálna medza,VIZUÁLNE,SM13 (SM12-13),S13 je pre plavcov s poškodením zraku.<br/><br/> ich periférne videnie je obmedzené a/alebo môžu vidieť koniec bazéna maximálne na päť metrov.,/sk/sports/plavanie/individualna-medza/sm13-sm12-13,,/sports/swimming/individual-medley/sm13-sm12-13,/sports/swimming/individual-medley/sm13-sm12-13,Y,/fr/sports/natation/quatre-nages/sm13-sm12-13,/fr/sports/natation/quatre-nages/sm13-sm12-13,Y,,,,,,,,,,,,,,,,,,,,,,,,,,
    charles godwin

    (@charlesgodwin)

    5 years, 2 months ago

    @peterschulznl

    Python has good csv reading libraries. I exploit them. I have not tried it in PHP.
    I’ll send you the code, later today, via your private contact.

    I have the advantage of just doing a read/write and I don’t load the database table. Makes the cost of failure less.

    Another thought is what I saw in Oracle’s MSQL GUI workbench. It seems to take every line of the csv and uses a prepare statement and then does an insert. It is slow but effective.

    Thread Starter snyggapa

    (@snyggapa)

    5 years, 2 months ago

    Hi Peter, I found the issue – one “mapped” column had a database field name that wasn’t quite right – “Script” (with a capital S) – whereas the “table” column also had the same field but without the capital S.

    If I upload with “Script” as the mapped column then those 2 rows fail to import. If I drag “Script” to the right and then drag in “script” as pictured below then all rows import OK.

    that column was recreated at some point (changed from varchar to TEXT ) so it is possible that it used to be Script and is now script – and WPDA kind of thinks they are the same but not the same!

    Once I have saved the mapping and gone back to it, then the erroneous “Script” field is gone from the right hand list – so this looks like a really obscure issue caused by changing the case of a field name !

    Thanks for your assistance

    Steve

Viewing 9 replies - 1 through 9 (of 9 total)

The topic ‘CSV import doesn’t quote a strings in only 2 rows’ is closed to new replies.