SitePoint Sponsor

User Tag List

Results 1 to 19 of 19
  1. #1
    SitePoint Enthusiast
    Join Date
    Oct 2003
    Location
    Italy
    Posts
    50
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Import csv file into mysql

    I have a file (.csv) I get from an instrument which has data in two rows (not columns), which I would like to import into mysql. If the data were in columns I can do it using fgetcsv, however my data is in rows, which I need to "transpose" some way so as to import the file;
    eg. Myfile.csv

    (abs) 0.254, 0.251, 0.891, 0.201, 1.025, 0.302,
    (conc) 1.302, 1.316, 7.440, 0.692, 9.094, 1.551,

    and my table has two fields called abs and conc

    When I transpose the data, using Excel, I can import, but I have to do this step manually

    0.254,1.302
    0.251,1.316
    0.891,7.44
    0.201,0.692
    1.025,9.094
    0.302,1.551

    Any help would be appreciated

    Ger

  2. #2
    SitePoint Wizard swdev's Avatar
    Join Date
    Oct 2004
    Location
    UK
    Posts
    1,053
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Try this
    PHP Code:
     // read data file into an array
     
    $arr file('test1.csv');
     
     
    // build values line
     
    $line '';
     foreach(
    $arr as $values)
     {
       
    $line .= '(' $values '), ';
     }
     
    // remove trainling space and comma
     
    $line substr($line0, (strlen($line) - 2));
     
     
    // build query string
     
    $sql 'INSERT INTO'
          
    ' table (`abs`, conc)'
          
    ' values'
          
    ' ' $line
          
    ;
     
     echo 
    $sql;
     
    // run query
     
    $result mysql_query($sql) or die ('Failed to execute ' $sql ' due to ' mysql_error()); 

  3. #3
    SitePoint Guru arunkumar's Avatar
    Join Date
    Jan 2005
    Location
    India:Chennai
    Posts
    827
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You can use csv spliting functions in php.,search php manual
    It help you split all csv entries.

    ArunKumar
    Where there is a will there is a way
    ArunKumar

  4. #4
    SitePoint Enthusiast
    Join Date
    Oct 2003
    Location
    Italy
    Posts
    50
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks, but it did not work. I get the following;

    Failed to execute INSERT INTO standards (`abs`, conc) VALUES (1.303,13.166,7.440,0.693,9.095,1.552,8.047,8.295,1.956,0.583,0.300 2.229,17.079,11.803,1.066,13.720,2.534,13.154,14.190,2.707,0.925,0.462) due to You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '2.229,17.079,11.803,1.066,13.720,2.534,13.154,14.190,2.707,0.92

    my table is;

    CREATE TABLE standards (
    abs float(6,3) NOT NULL default '0.000',
    conc float(6,3) NOT NULL default '0.000'
    ) TYPE=MyISAM;

    and the file has the following values

    1.303,13.166,7.440,0.693,9.095,1.552,8.047,8.295,1.956,0.583,0.300,
    2.229,17.079,11.803,1.066,13.720,2.534,13.154,14.190,2.707,0.925,0.462,

  5. #5
    SitePoint Wizard swdev's Avatar
    Join Date
    Oct 2004
    Location
    UK
    Posts
    1,053
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    are all the values on a single line in the file or 2 lines?
    Your original post didn't mkae that clear, so I assumed that each pair of values was on a separate line.

    1.303,13.166,7.440,0.693,9.095,1.552,8.047,8.295,1.956,0.583,0.300,
    2.229,17.079,11.803,1.066,13.720,2.534,13.154,14.190,2.707,0.925,0.462,
    which of there values is the abs and which is the conc?
    and does the file really finish with a comma?

  6. #6
    Umm. PHP Guru....Naaaah jaswinder_rana's Avatar
    Join Date
    Jul 2004
    Location
    canada
    Posts
    3,193
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ONE MORE QUESTION in addition to what swdev already asked.
    are the values always even or there might be odd paris
    ---------------------------
    Errors = Improved Programming.
    My Site

  7. #7
    SitePoint Enthusiast
    Join Date
    Oct 2003
    Location
    Italy
    Posts
    50
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Values are on 2 lines (each line always (?) finishes with a comma), first line being abs, second conc. Values are always paired.

    Ger

  8. #8
    SitePoint Wizard swdev's Avatar
    Join Date
    Oct 2004
    Location
    UK
    Posts
    1,053
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ok -try this code
    PHP Code:
     // read data file into an array
     
    $arr file('test1.csv');
     
     
    // remove trailing spaces and comma for ABS data
     
    $line substr($arr[0], 0strlen(trim($arr[0])) - 1);
     
    $abs explode(','$line);
     
     
    // remove trailing spaces and comma for CONC data
     
    $line substr($arr[1], 0strlen(trim($arr[0])) - 1);
     
    $conc explode(','$line);
     
     
    // build array for use in the VALUES statement
     
    foreach ($abs as $key => $value)
     {
       
    $val[] = '(' $abs[$key] . ',' $conc[$key] . ')';
     }
     
     
    // build VALUES data
     
    $line implode(', '$val);
     
     
    // build query string
     
    $sql 'INSERT INTO'
          
    ' table (`abs`, conc)'
          
    ' values'
          
    ' ' $line
          
    ;
     
     echo 
    $sql;
     
    // run query
     
    $result mysql_query($sql) or die ('Failed to execute ' $sql ' due to ' mysql_error()); 
    It assumes

    • abs data is on line 1
    • conc data is on line 2
    • data is alwasy paired
    • line always ends with 1 character (+ spaces) after the final value on each line


    Hope this helps

  9. #9
    SitePoint Enthusiast
    Join Date
    Oct 2003
    Location
    Italy
    Posts
    50
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Almost there!!
    But I get some errors;
    Notice: Undefined offset: 1 in /Library/WebServer/Documents/olive/test/csttomysql.php on line 16
    Notice: Undefined offset: 1 in /Library/WebServer/Documents/olive/test/csttomysql.php on line 23
    Notice: Undefined offset: 2 in /Library/WebServer/Documents/olive/test/csttomysql.php on line 23

    etc and the query fails?

    INSERT INTO standards (`abs`, conc) values (1.303,), (13.166,), (7.440,), (0.693,), (9.095,), (1.552,), (8.047,), (8.295,), (1.956,), (0.583,), (0.300 2.229,), (17.079,), (11.803,), (1.066,), (13.720,), (2.534,), (13.154,), (14.190,), (2.707,), (0.925,), (0.46,)

  10. #10
    Umm. PHP Guru....Naaaah jaswinder_rana's Avatar
    Join Date
    Jul 2004
    Location
    canada
    Posts
    3,193
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    it worked for me. i ran swdev's very last posted code
    i got this answer

    ANSWER
    INSERT INTO table (`abs`, conc) values (0.254,1.302), ( 0.251, 1.316), ( 0.891, 7.440), ( 0.201, 0.692), ( 1.025, 9.094), ( 0.302, 1.551)
    with this DATA
    0.254, 0.251, 0.891, 0.201, 1.025, 0.302,
    1.302, 1.316, 7.440, 0.692, 9.094, 1.551,
    ---------------------------
    Errors = Improved Programming.
    My Site

  11. #11
    SitePoint Wizard swdev's Avatar
    Join Date
    Oct 2004
    Location
    UK
    Posts
    1,053
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Perhaps you can post a link to the actual data file that you are using.

  12. #12
    SitePoint Enthusiast
    Join Date
    Oct 2003
    Location
    Italy
    Posts
    50
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ok,

    seems to be some problem with the file. It's from a PC, but I using a Mac. When I save it using Unix line breaks , it works, but the last value(s) are missing after the decimal point. Sometimes, there may be ?? instead of a negative value (not often).

    There is also a space before each value (except first), which I think was giving me the offset error?

    I've included a test file (the actual files will be much bigger (and more complex), but just wanted to see it it was possible to automatically database these data.

    ger
    Attached Files Attached Files

  13. #13
    SitePoint Wizard swdev's Avatar
    Join Date
    Oct 2004
    Location
    UK
    Posts
    1,053
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    will get the file as soon as it has been approved

  14. #14
    SitePoint Evangelist DMacedo's Avatar
    Join Date
    May 2004
    Location
    Braga, Portugal
    Posts
    596
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Talking

    You will have to grant FILE permissions for your mysql user; but I think it is possible using mysql_query("LOAD DATA INFILE ...");

    But I will have to check your attachment as soon as it is approved
    ~ Daniel Macedo

  15. #15
    SitePoint Wizard swdev's Avatar
    Join Date
    Oct 2004
    Location
    UK
    Posts
    1,053
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I finally got the file and it looked fine to me.

    I ran the code (exactly as postec earlier) and it gives the right SQL syntax

    Code:
     INSERT INTO table (`abs`, conc) values (1.303,1.433), ( 1.351, 1.337), ( 7.440, 7.217), ( 0.693, 0.706), ( 9.095, 9.458), ( 1.552, 1.505), ( 8.047, 7.725), ( 8.295, 9.124), ( 1.956, 2.034), ( 0.583, 0.577), ( 3.028, 3.119), ( 1.564, 1.720), ( 2.425, 2.401), ( 3.453, 3.349), ( 0.696, 0.710), ( 0.431, 0.449), ( 4.050, 3.929), ( 3.752, 3.602), ( 2.395, 2.635), ( 3.814, 3.967), ( 1.734, 1.717), ( 1.690, 1.740), ( 1.615, 1.777), ( ??, ??), ( 1.835, 1.780), ( 2.137, 2.179), ( 1.305, 1.357), ( 7.863, 7.627)
    It might be down to line endings - check the manual for file

  16. #16
    SitePoint Enthusiast
    Join Date
    Oct 2003
    Location
    Italy
    Posts
    50
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yeah, it was the fact that I was opening it on a Mac (line endings).

    Thanks
    ger

  17. #17
    SitePoint Wizard swdev's Avatar
    Join Date
    Oct 2004
    Location
    UK
    Posts
    1,053
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Your welcome

  18. #18
    SitePoint Evangelist NokX's Avatar
    Join Date
    Feb 2003
    Location
    Knoxville, TN
    Posts
    523
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    someone might've already posted this, but there is a nifty function in php called fgetcsv(). it works a little something like this...

    PHP Code:
    $h fopen("myfile.csv""r");
       while (
    $d fgetcsv($h1000)) {
          
    $n count($d);
          for (
    $c 0$c $n$c++) {
             print 
    $d[$c]."<br />\n";
          }
       }
       
    fclose($h); 
    Quote Originally Posted by Gbowe
    Yeah, it was the fact that I was opening it on a Mac (line endings).

    Thanks
    ger
    i use a mac, too... if you're exporting your csv file from an application like excel, be sure to choose the CSV option for MS-DOS.

  19. #19
    SitePoint Enthusiast
    Join Date
    Sep 2005
    Posts
    44
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    PHP-CSV import script into MySQL table

    I think I almost got it! I simply modified the that swdev posted, to use my csv filename, and voila', well, at least it dumps it into my browser. Now my question is, how do I get it to go into my MySQL database? Do I just type the table name (i.e., product_test) or do I have to somehow tell the browser where MySQL is? Should I use a blank table or one with column headers expected from CSV (I have tried it both ways)? I'm not sure if I have everything set-up correctly, but the fact that the query is echoed by PHP is encouraging.

    <?
    // read data file into an array
    $arr = file('products.csv');

    // build values line
    $line = '';
    foreach($arr as $values)
    {
    $line .= '(' . $values . '), ';
    }
    // remove trainling space and comma
    $line = substr($line, 0, (strlen($line) - 2));

    // build query string
    $sql = 'INSERT INTO'
    . ' product_test (`abs`, conc)'
    . ' values'
    . ' ' . $line
    ;

    echo $sql;
    // run query
    $result = mysql_query($sql) or die ('Failed to execute ' . $sql . ' due to ' . mysql_error());
    ?>
    Last edited by morrie; Sep 22, 2005 at 11:55. Reason: add more detail


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
  •