使用PDO加载包含特定列的csv文件 [英] Load a csv file with specific columns using PDO

问题描述

我有这样的表结构：

  id 
 assigned_by 
 assigned_to_user_id 
 vendor_id 
 recipient_no 
 purchase_order_no 
 destination 
 po_status 
 invoice_oount 
 invoice_amount 
 delivery_date 
 datetime_done 
 latest_jda_sync_date 
 latest_mobile_sync_date 
 created_at 
 updated_at 
 deleted_at 
  

csv文件是这样的：

  vendor_id receiver_no purchase_order_no destination po_status 
 30105 20110 10151 9005 3 
 50015 20114 10155 9005 3 
  

现在我可以加载csv文件并将它们插入我的数据库。

  $ columns =（@dummy，@dummy，@dummy，vendor_id，receiver_no，purchase_order_no，目标，po_status，@dummy，@dummy，@dummy，@dummy，@dummy，@dummy，@dummy，@dummy，@dummy） 
 $ affectedRows = $ pdo-> exec（
 LOAD DATA LOCAL INFILE。$ pdo-> quote（$ csvfile）。INTO TABLE`$ databasetable` 
 FIELDS TERMINATED BY $ pdo-> quote（$ fieldseparator）。
 LINES TERMINATED BY。$ pdo-> quote（$ lineseparator）。 
  

我遇到的唯一问题是将数据插入到正确的列。任何人都可以帮助我。

解决方案

如 LOAD DATA INFILE 语法：

< >

处理输入行时， LOAD DATA 将它分成字段，并根据列/变量列表和 SET 子句使用值

（或用户变量）

（或用户变量）。 其中应该分配每个输入字段（而不是描述可以在哪个输入字段中找到每个数据库列）。这当然是明显的，当我们意识到输入文件不需要包含字段名，因此在所有情况下都不可能采用后一种方法。

，您需要：

  $ columns ='（vendor_id，receiver_no，purchase_order_no，destination，po_status） 
  

您还需要添加 IGNORE 1 LINES 到命令，以跳过第一行（带有字段名）：

  $ affectedRows = $ pdo-> exec （
 LOAD DATA LOCAL INFILE）。$ pdo-> quote（$ csvfile）。INTO TABLE`$ databasetable` 
 FIELDS TERMINATED BY。$ pdo-> quote（$ fieldseparator）。 
 LINES TERMINATED BY。$ pdo-> quote（$ lineseparator）。
 IGNORE 1 LINES。 
  

I have a table structure like this:

id
assigned_by
assigned_to_user_id
vendor_id
receiver_no
purchase_order_no
destination
po_status
invoice_no
invoice_amount
delivery_date
datetime_done
latest_jda_sync_date
latest_mobile_sync_date
created_at
updated_at
deleted_at

And the content of my csv file is like this:

vendor_id receiver_no purchase_order_no destination po_status
30105   20110   10151   9005    3
50015   20114   10155   9005    3

And right now I'm able to load the csv file and insert them in my database. I have this script below.

$columns = "(@dummy, @dummy, @dummy, vendor_id, receiver_no, purchase_order_no, destination, po_status, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy)";
$affectedRows = $pdo->exec("
    LOAD DATA LOCAL INFILE ".$pdo->quote($csvfile)." INTO TABLE `$databasetable`
      FIELDS TERMINATED BY ".$pdo->quote($fieldseparator)."
      LINES TERMINATED BY ".$pdo->quote($lineseparator)." ". $columns);

The only problem I'm having is inserting the data to the correct column. Can anyone help me.

解决方案

As documented under LOAD DATA INFILE Syntax:

When processing an input line, LOAD DATA splits it into fields and uses the values according to the column/variable list and the SET clause, if they are present.

In other words, the column list should describe the database columns (or user variables) to which each the input fields should be assigned (rather than describing in which input field each database column can be found). This is, of course, obvious when one realises that the input file need not contain field names and thus it would be impossible to adopt the latter approach in all circumstances.

Therefore, you want:

$columns = '(vendor_id, receiver_no, purchase_order_no, destination, po_status)';

You will also need to add IGNORE 1 LINES to the command in order to skip the first line (with field names):

$affectedRows = $pdo->exec("
    LOAD DATA LOCAL INFILE ".$pdo->quote($csvfile)." INTO TABLE `$databasetable`
      FIELDS TERMINATED BY ".$pdo->quote($fieldseparator)."
      LINES TERMINATED BY ".$pdo->quote($lineseparator)."
      IGNORE 1 LINES ". $columns);

这篇关于使用PDO加载包含特定列的csv文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！