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

    Malfoy

    Joined:
    Nov 1, 2005
    #1
    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

    Joined:
    Jun 7, 2005
    #2
    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

    Malfoy

    Joined:
    Nov 1, 2005
    #3
    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:
    [​IMG]

    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

    Joined:
    Jun 7, 2005
    #4
    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

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

    Malfoy

    Joined:
    Nov 1, 2005
    #6
    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

    angelwatt

    Joined:
    Aug 16, 2005
    Location:
    USA
    #7
    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:
    Code:
    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
    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:
    Code:
    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

    Malfoy

    Joined:
    Nov 1, 2005
    #8
    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
    334-749-1255
    3360 Ross Clark Circle
    Dothan, AL, 36303
    334-792-8777

    to

    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

    angelwatt

    Joined:
    Aug 16, 2005
    Location:
    USA
    #9
    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