#1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2000
    Location
    San Juan, Argentina
    Posts
    6
    Rep Power
    0
    Hi.
    i got an excel file with a table which contains 3 columns, ARTIST TITLE & GENRE.. for example:

    METALLICA LOAD ROCK
    METALLICA RELOAD ROCK
    MEGADETH YOUTHANASIA HEAVY

    the thing is that i wanna pass the 11450 files to a table in mysql database. i've latest phpmyadmin and it has an option 4 making that... the thing is that i copy the text contained in all files and i paste in a text file but when i exec command in phpmyadmin all the row enters in one field... i've learn that it has an option which allows phpmyadmin to recognize which data to insert in which field.. it's a char separator. So, if i specify a ";" as a separator my table must be like...

    METALLICA; LOAD; ROCK;
    METALLICA; RELOAD; ROCK;
    MEGADETH; YOUTHANASIA; HEAVY;

    I know that if i do this the data will be inserted right, but how can i do to put a ";" at the end of each data (of each column) in each row ? There're 11000 rows more or less... i dunno anything about excel.. but....

    IS THERE A BETTER WAY TO TRANSFER THIS DATA FROM EXCEL TO A TABLE IN MYSQL ?

    Thanks in advance
    Yo gotta help me plz... i'm becoming MAD !!

    ------------------
    Henry - Argentina
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2000
    Posts
    300
    Rep Power
    15
    Well, there has to be some way to export the cells inside one datasheet to a text file. Try under the "File" menu. You didn't specify which version of Excel you are using, so I really cannot help you that much.

    On a side note, if you have Excel, then you probably have Access as well. Why don't you try importing the table into Access and the using Access' wizards to export an ordinary char-delimited text file??
  4. #3
  5. No Profile Picture
    The Dude Abides
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Feb 2000
    Location
    grass valley,ca
    Posts
    1,062
    Rep Power
    16
    I use the export function in excel to export a tab or comma delimited text file. Once I have that I go to the phpmyadmin "insert text files". For the fields terminated by use your delimiter (tab t, comma ,), the others I leave blank except for the lines terminated by which I leave at n

    Something else I've run into on different mysql server setups is that you have to modify the phpmyadmin file ldi_check.php3 there is a command "stripslashes" that is part of the Load data infile command. On some servers this doesn't seem to matter, on others this command causes mysql not to understand the data it is receiving and it gets corrupted. If you remove this command from the script the data loads with no problem.

    If your problem continues try the second solution.

    good luck
    chris
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2000
    Location
    London/UK
    Posts
    91
    Rep Power
    14
    I can't speak about phpMyAdmin as I've never used it, but if none of the above works, you could export from excel into a delimited file and then hack up a small php script that reads the file in line by line and builds up the SQL inserts.

    hth

    Bealers

    ------------------
    http://back-end.org
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2000
    Posts
    51
    Rep Power
    14
    the above replies are correct, as I've done this a million times.

    1. Save or export Excel spreadsheet as a tab/pipe delimited text file. whatever, as long as its delimited.

    2. write the php script to open the file, and read each line in, then use something like split() to break each line up into an array.

    3. do unto the array elements what you will!

    /wj
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2000
    Location
    Seaside, CA
    Posts
    84
    Rep Power
    15
    You could also try using the pipe character as your delimiter when you export from Excel. (Usually, all my rows have commas, semicolons, etc. in the fields so that seems to mess things up when I import. The pipe character works every time.) "|"

    ------------------
    From the day we're born, we're running out of time.

Similar Threads

  1. help with tables and wordwrapping
    By junkedBrian613 in forum PHP Development
    Replies: 2
    Last Post: February 15th, 2004, 05:42 PM
  2. extracting text from pdf files online
    By igiveadamn in forum Perl Programming
    Replies: 2
    Last Post: February 13th, 2004, 04:09 PM
  3. Loading data from text files
    By Manso in forum MySQL Help
    Replies: 1
    Last Post: January 28th, 2004, 04:53 AM
  4. How do I extract data form multiple files?
    By cols2910 in forum Visual Basic Programming
    Replies: 1
    Last Post: January 21st, 2004, 06:44 AM
  5. how to keep table on top, not centered??
    By vinyl in forum HTML Programming
    Replies: 2
    Last Post: January 20th, 2004, 06:19 PM

IMN logo majestic logo threadwatch logo seochat tools logo