Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 4 of 4
  1. #1
    New Coder
    Join Date
    Mar 2006
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Importing from csv

    Hi i am using the following code to import records from a csv file into mysql
    Code:
    connect_db();
    $fname = $_FILES['userfile'];
    
     $fcontents = file ('./Productivity.txt');
    
    //Displays Data from file
    echo "<pre>";
    print_r($arr);
    echo "</pre>";
    
    
      for($i=0; $i<sizeof($fcontents); $i++) {
              $line = trim($fcontents[$i], '\t');
          $arr = explode("\t", $line);
    
    $sql = "insert into qcheck (qdate, empno, ename, jobno, activity, chapter, pages, time, dataerr, tagerr)values ('".implode("','", $arr) ."')";
    $result = mysql_query ($sql) or die ("Query failed");
    }
    This code inports the data into the database but there is a minor glitch
    The tab delimited data is set like this

    Date orderno quantity team amount

    When the print_r($arr) is executed it shows the data as it is

    eg: 20-06-06 5585 50 TeamA 25000

    But when the data is added into the database its added's a number 20 before the date and the data is stored like this

    2020-06-06 5585 50 TeamA 25000

    Can anyone throw light on why this is happening?

  • #2
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    Assuming that "qdate" field is a date and is going into a date field in the database, it's assuming it's a date of 2020 and making it so. A "date" field type in mySQL will always store the full date.

    If you are trying to put the entire value "20-06-06 5585 50 TeamA 25000" into a date field, then that's not good either.

  • #3
    Regular Coder
    Join Date
    Sep 2005
    Posts
    394
    Thanks
    1
    Thanked 0 Times in 0 Posts
    You don't need to do that. It is a slow way to do things. Use the ultra fast mysql function LOAD. An example would be...

    Code:
    LOAD DATA LOCAL INFILE '/tmp/mycsv.txt' INTO TABLE `my_table` FIELDS TERMINATED BY ' ' ENCLOSED BY '' ESCAPED BY '\\' LINES TERMINATED BY '\r\n'
    Clearly, I don't know your CSV layout, so you would have to modify the above query, or click "Insert data from a text file into the table" in phpmyadmin if you have it.

    ~Phil~

  • #4
    New Coder
    Join Date
    Mar 2006
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Since i'm not going to do any date manipulation i changed the field type to varchar, and yet i'm getting the same result.

    Thanks lavinpj1 i'll try your suggestion


  •  

    Posting Permissions

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