使用PDO加载包含特定列的csv文件

本文介绍了使用PDO加载包含特定列的csv文件的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述 我有这样的表结构： 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:idassigned_byassigned_to_user_idvendor_idreceiver_nopurchase_order_nodestinationpo_statusinvoice_noinvoice_amountdelivery_datedatetime_donelatest_jda_sync_datelatest_mobile_sync_datecreated_atupdated_atdeleted_atAnd the content of my csv file is like this:vendor_id receiver_no purchase_order_no destination po_status30105 20110 10151 9005 350015 20114 10155 9005 3And 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文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！