SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    Confirmed Halfwit
    Join Date
    Oct 1999
    Location
    Vancouver, BC, Canada
    Posts
    983
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'm having trouble using the import feature of the PHPmyAdmin scripts.. I think my host screwed up with the install somehow..

    Anyway, I tried to find an alternate script that would take a single csv file and import it into a mySQL db, but couldn't seem to find one.. anyone have one kicking around? Or is there a way to get excel to output SQL?

    Thanks a lot.
    - A simple online WYSIWYG editor for HTML code snippets.
    - Managed Web Hosting - $3.95/month (resellers welcome)
    - Why pay more? $8.95 domains & $9.95 SSL certificates!

  2. #2
    Dumb PHP codin' cat
    Join Date
    Aug 2000
    Location
    San Diego, CA
    Posts
    5,460
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    login to server through telnet or ssh upload the csv file to a dir of your choice, cd into that dir login to mysql form the commandline run

    LOAD DATA INFILE 'tecsvfilename.txt' INTO TABLE tablename
    FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';


    You may to edit this a bit in order to get it to work with your csv file this exaple assumes the fileds are spearated by a comma and enclosed by ""

    example

    "shlsfh","sdjkafhasj","djafadj"
    "sf","tjuepryurew","apworwero"
    Please don't PM me with questions.
    Use the forums, that is what they are here for.

  3. #3
    Confirmed Halfwit
    Join Date
    Oct 1999
    Location
    Vancouver, BC, Canada
    Posts
    983
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    This is a great solution.. except that I don't have telnet access into this account.

    I'm thinking that maybe I might take the csv file into work with me, import it into an oracle db, then export it as SQL... I'm a little leary about that, because I don't want to mix "real work" with "home work".

    Do you know of any web scripts that would work for what I need?

    Thanks again.
    - A simple online WYSIWYG editor for HTML code snippets.
    - Managed Web Hosting - $3.95/month (resellers welcome)
    - Why pay more? $8.95 domains & $9.95 SSL certificates!

  4. #4
    Dumb PHP codin' cat
    Join Date
    Aug 2000
    Location
    San Diego, CA
    Posts
    5,460
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Assuming you exported the data from excel withour the double quotes around the fields, you may have to re export it or search and replace " with nothing, you will also need to change the field and table names to make it work with your stuff.

    <?

    $file = file("test.txt");
    foreach($file as $line) {
    $sql .= "INSERT into tablename set ";
    $data = explode(",",$line);
    $sql .= "field1 = '$data[0]', field2 = '$data[1]', field3 = '$data[2]'";
    //mysql_query($sql);
    print $sql."<br>";
    unset($sql);
    }

    ?>
    Please don't PM me with questions.
    Use the forums, that is what they are here for.

  5. #5
    Confirmed Halfwit
    Join Date
    Oct 1999
    Location
    Vancouver, BC, Canada
    Posts
    983
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I see what you mean. My only problem is that excel has given me a csv file where not everything is in brackets..

    ie:

    1,field2,"field3 and, some text, and more",field4

    You see how the field3 text is in brackets because there are also comma's in this string? But not every field is in brackets..

    I'd hate to have to figure out this logic just for this.. I'm going to just import it into Oracle and dump out the SQL. If anyone knows of a script that can handle all this, then feel free to post for future use..
    - A simple online WYSIWYG editor for HTML code snippets.
    - Managed Web Hosting - $3.95/month (resellers welcome)
    - Why pay more? $8.95 domains & $9.95 SSL certificates!

  6. #6
    Dumb PHP codin' cat
    Join Date
    Aug 2000
    Location
    San Diego, CA
    Posts
    5,460
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Reexport your data from excel choose a delimiter such as || and then simply ereg_replace the quotes out

    <?

    $file = file("test.txt");
    foreach($file as $line) {
    $line = ereg_replace('"', "", $line);
    $sql .= "INSERT into tablename set ";
    $data = explode("||",$line);

    $sql .= "field1 = '$data[0]', field2 = '$data[1]', field3 = '$data[2]'";
    //mysql_query($sql);
    print $sql."<br>";
    unset($sql);
    }

    ?>
    Please don't PM me with questions.
    Use the forums, that is what they are here for.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •