Trying to read in a file into mysql, only 1 set of data reads, what gives?

Discussion in 'Web Design and Development' started by Malfoy, Apr 10, 2008.

  1. Malfoy macrumors 6502a


    Nov 1, 2005
    Ok I'm working on a project and I'm trying to dump a large amount of info into the mysql db. I'm trying with a test file first and it seems no matter how I format it, the script only reads the first set. Here is the code:

    This is the source (a csv file):
    No matter how I format the csv file the code stops after reading the first phone number. Any ideas on what I need to change so it actually moves on to the next set of addresses/phone numbers? Thanks in advance.
  2. mheidt macrumors member

    Jun 7, 2005
    So you did try to put every record in a row?

    The code you provided doesn't work, because the query will fail and exists in the die()

    It only reads the "1" in the first iteration, so that $data[>0] is missing

    For debugging I would skip the mysql part first und just try to get the rows printed row by row.
  3. Malfoy thread starter macrumors 6502a


    Nov 1, 2005
    I didn't try putting the information in a row because the way the data is given to me, I'd have to manually format it to make that happen. The code works for me though so its odd it doesn't work for you.
    This is the output in phpmyadmin of what it does when ran once:

    It never starts on the 2nd set of data.

    I do a print out and it only prints the first set of information.
    I set everything to 1 line and it still only prints the first row.
    If i remove the !=false check, it still only reads 1 line.
    Sigh I'm going to bed now.

    Attached is a screenshot of my desktop showing everything. its a big pic so plan accordingly, :)

    Attached Files:

  4. mheidt macrumors member

    Jun 7, 2005
    is $data[6] happen to be "1" again?

    How shall fgetcsv know, that the new record starts after "334 -749-1255,"?

    Use print_r($data)
  5. angelneo macrumors 68000

    Jun 13, 2004
    What is your row record delimiter in your csv? line break? seems like one giant line to me.
  6. Malfoy thread starter macrumors 6502a


    Nov 1, 2005
    Why I'm not in bed yet given its 430 here is beyond me.

    Good questions and I don't know. I just assumed (first mistake I know) that it would treat the file as 0, 1, 2, 3,4, 5 new record time 0, 1, 2, 3,4,5 and repeat until finished since I told it to parse 6 things over and over until the end of file.

    How do I go about setting a row delimiter?
  7. angelwatt Moderator emeritus


    Aug 16, 2005
    To help you get the records onto one line you can use a regular expression find and replace. This can be done with TextWrangler (free) or others.

    Your CSV:
    OPELIKA, AL, 36801,
    334 -749-1255,
    DOTHAN, AL, 36303,
    334 -792-8777,
    OXFORD, AL, 36203,
    256 -831-4911
    Use this as your regular expression find: \r(?!(\d,).*$)

    You may need to change the \r at the beginning depending on what format the file is. Alternatives would be \n (unix) or \r\n (Windows).

    Then for the replace part you just put: \1

    This'll change the above input to:
    1,2254 TIGER TOWN PARKWAY,OPELIKA, AL, 36801,334 -749-1255,
    1,3360 ROSS CLARK CIRCLE,DOTHAN, AL, 36303,334 -792-8777,
    1,401 OXFORD EXCHANGE BLVD,OXFORD, AL, 36203,256 -831-4911
    Then maybe you'll have better luck with a properly formatted CSV file. Though those commas at the end of each line may cause a problem, but can be fixed with a second find and replace: find( ,\r ) replace with( \r )

    Let me know if you have any problems with that.
  8. Malfoy thread starter macrumors 6502a


    Nov 1, 2005
    Ok, after a complete rewrite of the code we got it to work with reading mulitple lines in with each address on one line. So that problem is gone. Now the last thing I need to figure out is how to convert

    2254 Tiger Town Parkway
    Opelika, AL, 36801
    3360 Ross Clark Circle
    Dothan, AL, 36303


    2254 Tiger Town Parkway, Opelika, AL, 36801, 334-749-1255
    3360 Ross Clark Circle, Dothan, AL, 36303, 334-792-8777

    I ran your expression angel on a version of the data and it works but it seems to be dependent on the number 1 being there. When the 1 is removed it makes everything 1 line. Can you try to explain to me what each part of ur expression for find is doing as well as pt me on how I can bring it all onto 1 line without the #1 being there. Muchos gracias in advance.
  9. angelwatt Moderator emeritus


    Aug 16, 2005
    Yeah, it kind of depended on that number at the beginning.

    A quick analysis of the first regular expression find: \r(?!(\d,).*$)
    \r = new lines (on Mac)
    ?!(\d,) = is not (?!) a digit (\d) followed by a comma
    .*$ = the rest of the line has miscellaneous characters

    New regular expression find for your new situation: \r((\D|\d{3}-).*$)
    \r = same as before, new lines
    \D = not a digit (address line)
    | = or
    \d{3}- = three digits followed by dash (first 4 characters of telephone number

    The replace on this is , \1

    So you end up with lines that start with non-digits or 3 digits and a dash, get rid of the newline at the beginning of that line and replace it with a comma and space. The above at least works with the two records you posted here, but be sure to check it against a number of records.

Share This Page