CSV import woes

  • Barry Callahan

    Barry Callahan - 2005-11-27

    We have been using the released version of OSC 2.0.5 for a while.

    We recently hired someone to enter all of the paper membership records our organization has been maintaining into a spreadsheet for import into OSC.

    We first attempted the import, and were greeted with the following message:

    Cannot execute query.

    INSERT INTO `person_custom` (`per_ID`, `chu_Church_ID`) VALUES ('58',9)

    Duplicate entry '58' for key 1


    Thinking that OSC was complaining about attempting to import records from the CSV file that already existed in the database, we deleted all person records from the database (except the administrator account) and the import was reattempted.

    The first attempt resulted in an SQL error.  Upon inspection, the date fields were "all sorts of munged" and some of the digits were carried into the later fields of the SQL query.

    I THINK that the cause of that error was that the wrong date format was specified - we reattempted the import, being careful to ensure that everything was selected correctly, and that error did not recur.

    We now get this error:

    Cannot execute query.

    INSERT INTO `person_custom` (`per_ID`, `chu_Church_ID`) VALUES ('1',9)

    Duplicate entry '1' for key 1


    Using the command-line MySql client, I examined the database. The first record in the CSV file now exists 5 times in the person_per table but does not show up at all when we use the OSC "View All Persons" feature.

    • Steve McAtee

      Steve McAtee - 2005-11-28

      Per your other topic, is this one related?  Should I merge them. 

      Thanks and please advise.

      • Barry Callahan

        Barry Callahan - 2005-12-02

        I posted the other topic first because it was shorter. Less typing and less thinking to try providing as complete a report as possible.

        I attempted to use the CVS version after doing everything I could think of (withoug spending a LOT of quality time with the source) to get the release to import.

        It may be worthwhile to merge the topics. I don't know.

    • Barry Callahan

      Barry Callahan - 2005-12-12

      Ok.  After spending some quality time with OSC, and comparing the release version of OSC that I've been working with to the CVS version that I can't get working, I fixed the problem I was having with not being able to import CSV files.... mostly.

      First, there was a '"' missing from a file (I don't remember which one) - I actually did this before I made my initial post.

      The thing I finally did to allow processing of CSV files is that I replaced the version of CSVImport.php I had with the version in the current CVS.

      I qualified my statement above with "mostly" because I noticed that if I'm importing a "Birth Date" column in the CSV file and there's a row in the CSV where that field is blank, the SQL query to insert that row fails. The reason being that the birth date gets broken into separate columns for insertion, and when that field is empty, you end up getting ',,,,' in your query, causing mysql to puke.

      I'll try to get to adding some sanity checking to processing birth dates (default to 'NULL' instead of leaving the fields empty) in the next day or two, and I'll post a diff.


Log in to post a comment.