• 首页 首页 icon
  • 工具库 工具库 icon
    • IP查询 IP查询 icon
  • 内容库 内容库 icon
    • 快讯库 快讯库 icon
    • 精品库 精品库 icon
    • 问答库 问答库 icon
  • 更多 更多 icon
    • 服务条款 服务条款 icon

没办法csv的数据插入到具有未知列数的mysql数据库

用户头像
it1352
帮助1

问题说明

已经有两天了,这个我很烦的问题,我做了研究和一切工作,但它似乎比我的编程技能和知识还重要。

I'm having this very annoying problem for couple of days already, and I did research and everything, but it seems to be bigger than my programming skills and knowledge.

我遇到一种情况,用户可以在数据库的表中定义自定义列,现在我需要导入 CSV 文件到该用户的自定义表中。由于我最近发现表需要是动态的,因此这是我之前处理导入的方式:

I have a situation where user can define custom columns in tables in my database, and now I need to import CSV files into that user's custom tables. Since I recently found out that tables need to be "dynamic" this is how I handled importing before:

$file = $_FILES['file']['tmp_name'];
        $handle = fopen($file, "r");
        $name = $_FILES['file']['name'];
    if (strrpos($name, '.csv', -4)) {//handling the csv file
                    do {
                        if ($data[0]) {
                            $mysqli->query("INSERT INTO `test` (`name`, `surname`, `email`) VALUES
                            (
                                '" . addslashes($data[0]) . "',
                                '" . addslashes($data[1]) . "',
                                '" . addslashes($data[2]) . "'
                            )
                        ");

                        }
                    } while ($data = fgetcsv($handle, 1000, ",", "'"));

这与静态表完美配合。但是由于我不知道表中将有多少列,我尝试了如下操作:

And that worked perfectly with static tables. But since I dont't know how many columns table will have, I tried something like this:

构建查询方法

 if (strrpos($name, '.csv', -4)) {//ubacije vrijednosti csv fajla u bazu
                do {
                    if ($data[0]) {
                        $query = "INSERT INTO $tabela (";
                        $last = end($anotherArray);
                        foreach ($anotherArray as $something) {//$anotherArray contains names of fields in user's custom table
                            if ($something != $last) {
                                $query .= "" . $something . ",";
                            } else {
                                $query .= "" . $something . ")";
                            }
                        }

                        $query .= "VALUES (";
                        foreach($data as $val){
                       $query .= "'$val',";
}


    }while ($data = fgetcsv($handle, 1000, ",", "'"));

                $query=rtrim($query,',');
                $query.=")";
                //var_dump($query);
                $rez=$mysqli->query($query);
                //var_dump($rez);

但是此代码的问题是csv文件是否包含例如2个或更多这样的列:

But problem with this code is if csv file contains for example 2 or more colums like so:

所有内容都成为查询的 VALUES 部分的一部分。因此查询如下所示: INSERT INTO tabela12312334(user_facebook_id,ime,prezime)VALUES('123123123','Ognjen','Babic','123123123','Neven',Babic) ,当然字段数与值数不同。

everything becomes part of that VALUES part of query. So query looks like this: "INSERT INTO tabela12312334 (user_facebook_id,ime,prezime) VALUES('123123123','Ognjen','Babic','123123123','Neven',Babic)" and of course number of fields isn't same as number of values.

请帮助我解决这个问题,我很拼命。

Please help me to solve this, I'm desperate.

正确答案

#1

试试这个

LOAD DATA LOCAL INFILE  
'c:/path-to-csv-file/your-file.csv'
INTO TABLE your_awesome_table  
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS
(field_1,field_2 , field_3);

NB: 作为字段标题,您可能需要在CSV文件中包含标题

NB: the first row as the title for your fields, you might have to include a header in your CSV file

这篇好文章是转载于:学新通技术网

  • 版权申明: 本站部分内容来自互联网,仅供学习及演示用,请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系,请提供相关证据及您的身份证明,我们将在收到邮件后48小时内删除。
  • 本站站名: 学新通技术网
  • 本文地址: /reply/detail/tanhcfekhj
系列文章
更多 icon
同类精品
更多 icon
继续加载