Import csv file does not work

  • sergewebservice

    (@sergewebservice)


    2 years, 8 months ago

    Hi there,

    I formatted my csv file and it still does not import. I get this message:

    Database Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘WHERE id = 4’ at line 1

    I tried with a single line of data with these columns:

    first_name,last_name,phone,email,membership_status,gardening_experience,volunteer___administration,volunteer___community_events,volunteer___gardening,volunteer___gardening_expertise,volunteer___garden_of_lights,volunteer___operations,volunteer___supplier,message,mailing_list,approved,membership,relevant_notes_and_past_data,id

    “firstname”,”lastname”,””,”[email protected]”,””,””,””,””,””,””,””,””,””,””,””,””,””,”market garden plant sale production “,”3”

    Anyone has a clue how to solve the issue?

    Serge

Viewing 15 replies - 1 through 15 (of 15 total)
  • Thread Starter sergewebservice

    (@sergewebservice)

    2 years, 8 months ago

    update: it imports one at a time even if I have more than one and the error message evolves. The id number increases:

    Database Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘WHERE id = 9’ at line 1

    looks like the second line import hits a sql error for some reason. I use notepad++ to edit the csv file.

    Plugin Author xnau webdesign

    (@xnau)

    2 years, 7 months ago

    Not sure what the issue is there. Try using a different line return character, Typically, it should be “\n” also, make sure the file encoding is UTF-8 no BOM.

    Thread Starter sergewebservice

    (@sergewebservice)

    2 years, 7 months ago

    Hi,

    it is already “\n” and the file encoding is UTF-8 no BOM.

    I’m trying different settings and still one data enters only, each time. Now message is …near ‘WHERE id = 12’ at line 1. I have 180 participants to enter.

    thanks,

    Serge

    Thread Starter sergewebservice

    (@sergewebservice)

    2 years, 7 months ago

    Hello, I tried backslash r and it is the same as backslash n, only one line is entered. The error must be in the plugin or the mysql. Anyone has an idea about a solution or a clue leading to the solution?

    Plugin Author xnau webdesign

    (@xnau)

    2 years, 7 months ago

    The problem is most likely in the CSV file, we just haven’t found it yet. CSV is a very fragile format, and it is sometimes hard to get it right.

    I notice when you posted the two lines of your CSV that it was using “curly” quotes. You must only use striaght quotes for the enclosure.

    There is no need to quote (enclose) every field data, an enclosure is only needed if the data contains one of the special characters used in the CSV: comma, line return, apostrophe, etc.

    Also, make sure the import settings are correct, for example, using auto detect sometimes doesn’t work (depends on the contents of the CSV) so you need to set the delimiter and enclosure characters to a specific one…and then make sure that is the character that used by the CSV for that purpose.

    Thread Starter sergewebservice

    (@sergewebservice)

    2 years, 7 months ago

    I tried all the settings possible to upload and it is , and ” since a while. That is straight quotes. I tried overwrite, not overwrite etc. Now I have tried again with no quotes at all on two lines and still only one record is added at a time. I tried every line change backslashes n and r and it does one line. I have also taken an export csv to edit into import csv and still the same issue. I just tried utf-8 with BOM, same issue, but it did enter one record.

    The issue must be in the sql or the sql command. The csv file can’t possibly be at fault.

    Plugin Author xnau webdesign

    (@xnau)

    2 years, 7 months ago

    OK, that sounds good.

    Did you try changing the “CSV Imports in the Background” setting? This is in the pugin settings under the Advanced tab.

    Yes, there is a problem in the query, but solving the issue means finding out why in this case it is failing.

    Thread Starter sergewebservice

    (@sergewebservice)

    2 years, 7 months ago

    I had not tried that setting. I did it now and it worked through the whole 62 lines left. Solved in that setting. Is it normal that it does not work directly?

    Plugin Author xnau webdesign

    (@xnau)

    2 years, 7 months ago

    @sergewebservice the background import doesn’t work on some systems due to server caching interfering with the processing of the import queue. This is apparently what was happening in your case.

    Thread Starter sergewebservice

    (@sergewebservice)

    2 years, 7 months ago

    @xnau , no, it is the contrary. It is working when set to do it in the background. It is NOT working when NOT set to work in the background. i.e. the interference you mention is actually helping it work. You should listen to us and try to find what causes the bug without referring to magic.

    Plugin Author xnau webdesign

    (@xnau)

    2 years, 7 months ago

    @sergewebservice OK, I musunderstood about which setting worked for you, but anyway you got it working.

    But don’t be rude, I’m trying to help. I’ve been doing this a long time, and I can tell you that the only way to properly solve issues is to eliminate the most common reasons for the problem and go from there. It wasn’t at all clear that your CSV wasn’t the problem, so we started by making sure everything was correct with that.

    I can’t take it for granted that when someone says the CSV is correct, they know what they’re talking about. I don’t know them and if they’re wrong about that (happens a lot), we’re wasting each other’s time because we haven’t established the basics. Doesn’t mean I’m not listening.

    When your question came in, I tested it. I always do to try to make sure there isn’t a bug. I didn’t see a bug, so then the task is to see what’s different about what you are doing and your server setup…which I really need to know because that might help me find a bug that I don’t see in my tests.

    I still don’t know why it wasn’t working and that worries me, but just getting it to work for you is the main thing.

    Thread Starter sergewebservice

    (@sergewebservice)

    2 years, 7 months ago

    Hi @xnau,

    well, I had set to record the debug log and it shows that the errors are still there but in the background setting, it restarts on its own. So it makes it through one line at a time. Although it seems that sometimes the error is skipped. The debug log is at:

    https://groundswellnetwork.ca/wp-content/uploads/participants-database/pdb_debugging_651e7eab6eb39.txt

    What other info would you need to find out the issue?

    Plugin Author xnau webdesign

    (@xnau)

    2 years, 7 months ago

    Thanks, this is helpful information, and it makes sense that the bacground process can recover from an error better than the froeground process.

    I’m currently traveling, so I can’t properly investigate this, I’ll have to come back to this later.

    Thread Starter sergewebservice

    (@sergewebservice)

    2 years, 7 months ago

    A single post is easier to find later I think. Why don’t you tell us what we would need to look for to find a clue about the issue? Add a tag in the code somewhere or anything that would give a clue. The database settings? Something on our side should be helping to find where the issue is exactly. Can’t you create a trace in your plugin specifically for this issue?

    Plugin Author xnau webdesign

    (@xnau)

    2 years, 7 months ago

    Since I have already done a lot of testing with this without seeing the problem, I don’t know the specifics on what you can do at that level. Using a properly configured php error log (not the plugin debugger) may provide additional information about the problem, be sure to have verbose debugging enabled in the plugin.

    What’s really preventing me going deeper into the code is that I am currently traveling without access to my development machine. This issue is very much at the top of the list of things to investigate when I return in November.

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

The topic ‘Import csv file does not work’ is closed to new replies.