php - 如何在codeigniter中将包含html标签的csv文件导入MYSQL

Question

我想使用 codeigniter 将 csv 导入 mysql。

这是我的源代码。

我有一个来自客户端的非标准化事件日记 CSV，我正在尝试将其加载到 MySQL 表中，以便可以重构为正常的格式。

我创建了一个名为“CSVImport”的表，其中 CSV 文件的每一列都有一个字段

function import_questions(){


    $question_type=$_POST['question_type'];
    $main_category_name=$_POST['main_category_name'];
    $sub_category_name=$_POST['sub_category_name'];
    $exam_name_list=$_POST['exam_name_list'];
    $chapter_name=$_POST['chapter_name'];
    $lesson_name=$_POST['lesson_name'];
    $difficult_level=$_POST['difficult_level'];
    $input_type=$_POST['input_type'];


    $config = array();
    $config['upload_path'] = './uploads/question_upload/';
    $config['allowed_types'] = '*';
    $config['max_size']      = '0';
    $config['overwrite']     = FALSE;
    $this->load->library('upload',$config);

    $tb_name=$_FILES['userfile']['name'];
    $tb_type=$_FILES['userfile']['type'];

    if (!$this->upload->do_upload())
    {
    $error = array('error' => $this->upload->display_errors());
    $this->session->set_flashdata('failure', 'File Uploaded Error!.');
    header('Location:'.$this->data['base'].'cbulkupload/');
    }
    else
    {
    $datas = array('upload_data' => $this->upload->data());
    $filename = $datas['upload_data']['full_path'];

    require_once './excel/excel_reader2.php';


    if($tb_type == 'application/vnd.ms-excel')
    {

    $data = new Spreadsheet_Excel_Reader($filename);

        for($i=0;$i<=count($data->sheets);$i++) // Loop to get all sheets in a file.
        {   
            //echo "count :".count($data->sheets);
            //echo "<pre>";print_r($data->sheets[$i][cells]);
            if(count($data->sheets[$i][cells])>0) // checking sheet not empty
            {


                for($j=1;$j<=count($data->sheets[$i][cells]);$j++) // loop used to get each row of the sheet
                { 
                    //echo "<pre>";print_r($data->sheets[$i][cells][$j]);

                    if($j != 1){

                    $QUESTION_AREA = mysql_real_escape_string(preg_replace("/\s+/"," ",$data->sheets[$i][cells][$j][1]));
                    $TEXT_OPTION1 = mysql_real_escape_string(preg_replace("/\s+/"," ",$data->sheets[$i][cells][$j][2]));
                    $TEXT_OPTION2 = mysql_real_escape_string(preg_replace("/\s+/"," ",$data->sheets[$i][cells][$j][3])); 
                    $TEXT_OPTION3 = mysql_real_escape_string(preg_replace("/\s+/"," ",$data->sheets[$i][cells][$j][4]));
                    $TEXT_OPTION4 = mysql_real_escape_string(preg_replace("/\s+/"," ",$data->sheets[$i][cells][$j][5]));
                    $TEXT_OPTION5 = mysql_real_escape_string(preg_replace("/\s+/"," ",$data->sheets[$i][cells][$j][6]));
                    $RESULT_OPTION = $data->sheets[$i][cells][$j][7];
                    $ANSWER_DESCRIPTION = mysql_real_escape_string(preg_replace("/\s+/"," ",$data->sheets[$i][cells][$j][8]));
                   $query = "insert into add_question(MAIN_CATEGORY_ID,SUB_CATEGORY_ID,EXAM_NAME_ID,CHAPTER_ID,LESSON_ID,INPUT_TYPE,DIFFICULT_LEVEL,DIAGNOSIS_VALUE,CREATED_DATE,ACTIVE_STATUS,QUESTION_AREA,TEXT_OPTION1,TEXT_OPTION2,TEXT_OPTION3,TEXT_OPTION4,TEXT_OPTION5,RESULT_OPTION,ANSWER_DESCRIPTION) values(
        '".$main_category_name."','".$sub_category_name."','".$exam_name_list."','".$chapter_name."','".$lesson_name."','".$input_type."','".$difficult_level."','".$question_type."',NOW(),'Y','".$QUESTION_AREA."','".$TEXT_OPTION1."','".$TEXT_OPTION2."','".$TEXT_OPTION3."','".$TEXT_OPTION4."','".$TEXT_OPTION5."','".$RESULT_OPTION."','".$ANSWER_DESCRIPTION."')";
                    mysql_query($query);

                }
                }
            }

        }
}

请帮助我找到更好的解决方案。

上传 csv 后发现以下错误:

file 502 Bad Gateway

Answer 1

Once your file is uploaded then u should to fetch a row from uploaded using fgetcsv function of php
My Working Solution:

$p_Filepath ="your file path"
$file = fopen($p_Filepath, 'r');       
    $data = array();
    $i=0;

    while (! feof($file)) {
      $data[$i] = (fgetcsv($file));
      $i++;
    }

    $data1 = array();
    for($j=1;$j<count($data);$j++){
      $data1[$j] = array (
                  "date"          =>  $data[$j][0],
                  "time"          =>  $data[$j][1],
                  "type"          =>  $data[$j][2],
                  "source"        =>  $data[$j][3],
                  "contactName"   =>  $data[$j][4],
                  "phone"         =>  $data[$j][5],                        
                  "email"         =>  $data[$j][6],                        
                  "address"       =>  $data[$j][7],
                  "city"          =>  $data[$j][8],
                  "state"         =>  $data[$j][9],
                  "zip"           =>  $data[$j][10],
                  "language"      =>  $data[$j][11],
                  "practiceArea"  =>  $data[$j][12],
                  "practiceCategory"=>  $data[$j][13],
                  "actionable"    =>  $data[$j][14],
                  "billable"      =>  $data[$j][15],
                  "destNum"       =>  $data[$j][16],
                  "callDuration"  =>  $data[$j][17],
                  "pageUrl"       =>  $data[$j][18],
                  "emailRec"      =>  $data[$j][19]
                  );
      $this->db->insert("contactReport",$data1[$j]);      
    }