SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Evangelist vhogarth's Avatar
    Join Date
    Nov 2003
    Location
    Taxachussets
    Posts
    415
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    3 column excel spreadsheet into mysql db??

    I just got handed the task of auditing an excel spreedsheet with a ton of info on it spread over three columns. I was wondering if there is a way i can take the data in those columns and recreate those columns in mysql and populate it accordingly? You may ask how will putting it in mysql make it easier for me? I'm going to automate the way we do the audit process (ITS A PAIN IN THE A$$ the way it is right now... way too many emails going back and forth.. keeping track is a bear)

    Anyhow so how should i go about doing it. My skillz are nubile at best. Well i guess i should give myself a little more credit, i'm not quite a nubie anymore but i'm not close to the next plateau. I have a functional knowledge of php and mysql.

    Thanks for your time.

  2. #2
    SitePoint Zealot manoloweb's Avatar
    Join Date
    Aug 2003
    Location
    Mexico
    Posts
    148
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    1.- You can name the range in excel, then make it an ODBC data source and finally import data from mysqlFront or similar programs.

    or

    2.- Save it as CSV and import the data from myAdmin or MySQLFront

    or

    3.- Make an excel formula that constructs a valid insert statement using those 3 columns...

    Let's say your columns are A, B and C, so you type in D something like this:

    ="insert into mytable (field1,field2,field3) values ("&A1&","&B1&","&C1&");"

    Then copy that formula down, and copy-paste the entire D column into a text file, naming it "query.sql", that way you can create the table, and then run that file into it.

    ;-)

  3. #3
    SitePoint Evangelist vhogarth's Avatar
    Join Date
    Nov 2003
    Location
    Taxachussets
    Posts
    415
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by manoloweb
    1.- You can name the range in excel, then make it an ODBC data source and finally import data from mysqlFront or similar programs.

    or

    2.- Save it as CSV and import the data from myAdmin or MySQLFront

    or

    3.- Make an excel formula that constructs a valid insert statement using those 3 columns...

    Let's say your columns are A, B and C, so you type in D something like this:

    ="insert into mytable (field1,field2,field3) values ("&A1&","&B1&","&C1&");"

    Then copy that formula down, and copy-paste the entire D column into a text file, naming it "query.sql", that way you can create the table, and then run that file into it.

    ;-)
    Thanks for the quick response, those are some good ways. The last one i found most intriguing, but seeing that I want to do it with the least amount of effort. I think i may just use myAdmin. I've already exported it to the csv file. Lets hope the import works. Hmm.. Does the myadmin import create the columns in the DB according to the headings in the CSV file or do I have to specify which goes where?

  4. #4
    SitePoint Evangelist vhogarth's Avatar
    Join Date
    Nov 2003
    Location
    Taxachussets
    Posts
    415
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    not working

    I tried going into myAdmin and importing the CSV file, but i got this error:
    Error

    SQL-query :

    LOAD DATA LOCAL INFILE 'C:\\WINDOWS\\TEMP\\php187A.tmp' INTO TABLE `access_list` FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '\\' LINES TERMINATED BY '\r\n'

    MySQL said:


    #1148 - The used command is not allowed with this MySQL version

    I noticed that when I opened the .CSV file in text pad that everything was seperated by just a comma and everything is in rows as it should be. How would a dump of some sort know which columns the data should go into if they're all just seperated by commas? How does it know when to return to the next row when there isnt some sort of indicator at the end of each line?

  5. #5
    SitePoint Zealot
    Join Date
    Apr 2004
    Location
    Michigan
    Posts
    140
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Here is a short script to add the data from the csv file to a database table. You will need to create the table beforehand.

    Code:
    <?PHP
    
    // connect to your database
    
    include ('db.php');
    
    // put the entire csv file into an array
    $all_lines = file('file.csv'); 
    
    // count the number of lines in the file and store it in a variable
    $how_many_lines = count($all_lines);
    
    // 1. separate each line into its individual elements 
    // (aka fields aka pieces of information)
    //  using the EXPLODE () function.
    
    for ($i = 0; $i < $how_many_lines; $i++) { 
      $all_fields[$i] = explode(',', $all_lines[$i]); 
      $col1 = $all_fields[$i] [0];
      $col2 = $all_fields[$i] [1];
      $col3 = $all_fields[$i] [2];
    
    // insert the data into the databse table
    
    $sql = "INSERT INTO table_name (col1_name,col2_name,col3_) VALUES ('$col1','$col2','col3')";
    $result2 = mysql_query($sql);
    }
    
    ?>
    Lite...

  6. #6
    SitePoint Evangelist vhogarth's Avatar
    Join Date
    Nov 2003
    Location
    Taxachussets
    Posts
    415
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Umm... it worked.

    Quote Originally Posted by litebearer2
    Here is a short script to add the data from the csv file to a database table. You will need to create the table beforehand.

    Code:
    <?PHP
    
    // connect to your database
    
    include ('db.php');
    
    // put the entire csv file into an array
    $all_lines = file('file.csv'); 
    
    // count the number of lines in the file and store it in a variable
    $how_many_lines = count($all_lines);
    
    // 1. separate each line into its individual elements 
    // (aka fields aka pieces of information)
    //  using the EXPLODE () function.
    
    for ($i = 0; $i < $how_many_lines; $i++) { 
      $all_fields[$i] = explode(',', $all_lines[$i]); 
      $col1 = $all_fields[$i] [0];
      $col2 = $all_fields[$i] [1];
      $col3 = $all_fields[$i] [2];
    
    // insert the data into the databse table
    
    $sql = "INSERT INTO table_name (col1_name,col2_name,col3_) VALUES ('$col1','$col2','col3')";
    $result2 = mysql_query($sql);
    }
    
    ?>
    Lite...
    Hey Lite

    Thanks for the code, it makes sense to me. THe only question I have about it is how the explode function knows when it gets to the end of the row? this is how the data was saved in the csv file:

    data for column 1 row 1, data for column 2 row 1, data for column 3 row 1
    data for column 1 row 2, data for column 2 row 2, data for column 2 row 3
    etc..,

    My question is how does it know where the line break is? There is nothing to differentiate the end of one row and the beginning of another. I'm thinking that when I'm going to explode the function it will combine

    date for column 3 row 1 (with) date for column 1 row 2

    because thats where the two commas seperate the rest of the info? Am I wrong? Should I add a special character like a semi colon ; to indicate the end of a row and somehow specify that means to return to a new row.

    So far though, thanks a lot for your help i really appreciate it.

    EDITED:
    *What I said above was what I was thinking would happen. I just ran that script and it worked perfectly. Instead of inserting the data, i made it echo the stuff just to see it.. Wow man, thanks again.

    HOw does the echo function know the end of a line? the white space?

  7. #7
    SitePoint Zealot
    Join Date
    Apr 2004
    Location
    Michigan
    Posts
    140
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    when you export a file from excel, it automatically 'attaches/inserts' an end-of-row character (could be a \n or a \r. Not being a php guru, all I know is that its there and it works ). This character may not be 'visible', but it is there (kind of like air - we breathe but don't see it with the visible eye). Then when you read the file into your array, it automatically recognizes the end-of-row marker.


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
  •