dongyou2305
2017-02-25 09:00
浏览 60
已采纳

使用PHP和MySQLi导入CSV替代方法

I am using the code below to import a CSV textfile into my table, but my new host not allowing LOAD DATA commands and forces me to find alternative ways of importing the file. Naturally, they are useless in giving me a clear answer.

What other ways except LOAD DATA LOCAL and LOAD DATA do I have to import a CSV file into my MySQL table via PHP/MySQLi?

  // Query - import CSV file into DB, ignore top 3 lines
    $sql= "LOAD DATA LOCAL INFILE '".$import_file."' INTO TABLE `$dbtable`
       FIELDS TERMINATED BY '".$fieldseparator."'
       LINES TERMINATED BY '".$lineseparator."'
       IGNORE 3 LINES
       SET TIMESTAMP = '".$local_time."'";

Thanks

EDIT:

After looking into the suggested fgetcsv function, I came up with the below code. Theoretically, it should work, but the code seems to load until it finally times out without showing an error. The database is populated with a single row entry only.

What am I doing wrong here?

                if ($import_file) {
                  // set row counter to 0
                  $line = 0;

                  while ( ($row = fgetcsv($import_file)) !== false ) {

                    // ignore the first 3 rows of the file based on iteration of the counter
                    if ($line <= 3) {
                      $line++;
                      continue;
                    } else {

                        // if row count is higher than 3, insert into database
                        $sql = "INSERT INTO `$dbtable`
                          (`OPERA_CONF`, `CRS`, `HOLIDEX`, `ARRIVAL`)
                          VALUES (
                            '" . $row[0] . "',
                            '" . $row[1] . "',
                            '" . $row[2] . "',
                            '" . $row[3] . "'
                          )";

                        // execute query
                        $mysqli->query($sql);
                        $line++;

                    }

                  }

                } else {
                    echo "
 CSV file could not be found.";
                }
  • 写回答
  • 好问题 提建议
  • 关注问题
  • 收藏
  • 邀请回答

1条回答 默认 最新

  • dtslobe4694 2017-02-25 13:23
    已采纳

    You're going to have to use PHP to read the contents of the CSV file line by line and insert them into the table.

    Have a look at http://www.readmyviews.com/import-csv-data-mysql-using-php/

    Code pasted here for easy of use:

    $filepath = "C:\wamp\www\importdata\sample.csv"; 
    
    if (($getdata = fopen($filepath, "r")) !== FALSE) {
                   fgetcsv($getdata);   
                   while (($data = fgetcsv($getdata)) !== FALSE) {
                        $fieldCount = count($data);
                        for ($c=0; $c < $fieldCount; $c++) {
                          $columnData[$c] = $data[$c];
                        }
                 $option_name = mysqli_real_escape_string($connect ,$columnData[0]);
                 $option_value = mysqli_real_escape_string($connect ,$columnData[1]);
                 $import_data[]="('".$option_name."','".$option_value."')";
                // SQL Query to insert data into DataBase
    
                 }
                 $import_data = implode(",", $import_data);
                 $query = "INSERT INTO option_data_master(option_name,option_value) VALUES  $import_data ;";
                 $result = mysqli_query($connect ,$query);
    
    已采纳该答案
    评论
    解决 无用
    打赏 举报

相关推荐 更多相似问题