通过PHP将CSV文件导入SQLite数据库 [英] Import CSV File into a SQLite Database via PHP
本文介绍了通过PHP将CSV文件导入SQLite数据库的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我想通过一个可以手动运行以更新数据的PHP脚本将表格从CSV文件导入到SQLite数据库中。
I want to import a table from a CSV file into a SQLite DB via a PHP script that I can manually run to update the data.
此处列出了我要实现的目标:
Heres a list of what I want to achieve:
- 将旧表(称为 produkte)重命名为product-currentdate(或删除表)
- 然后从CSV文件中导入文件(
;
分隔并使用ISO 8859-1字符集/ CSV文件的第一行包含表标题) - 将日期保存在表 product中
- Rename old table (which is called "produkte") into product-currentdate (Or drop the table)
- Then import the files from the CSV File (
;
separated and ISO 8859-1 charset / The first row of the CSV-file contains the table header) - Save the date in the table "product"
我已经发现出于某种原因不起作用的脚本:
I've found a script which for some reason does not work:
<?php
$dir = 'sqlite:test.sqlite';
$dbh = new PDO($dir) or die("cannot open the database");
$query = <<<eof
LOAD DATA LOCAL INFILE 'produkte.csv'
INTO TABLE produkte
FIELDS TERMINATED BY ';'
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(id, Hauptmenue, Produktgruppe, Beschreibung, Text, Bild, Shop, Info)
eof;
$dbh->query($query);
?>
我希望有人知道如何解决我的问题...
I hope someone knows how to solve my problem...
最诚挚的问候Dave
Best regards Dave
推荐答案
Federico Cingolani 在 Github 上发布了一个满足您需求的php脚本
Federico Cingolani has posted a php script at Github that meets your needs
<?php
function import_csv_to_sqlite(&$pdo, $csv_path, $options = array())
{
extract($options);
if (($csv_handle = fopen($csv_path, "r")) === FALSE)
throw new Exception('Cannot open CSV file');
if(!$delimiter)
$delimiter = ',';
if(!$table)
$table = preg_replace("/[^A-Z0-9]/i", '', basename($csv_path));
if(!$fields){
$fields = array_map(function ($field){
return strtolower(preg_replace("/[^A-Z0-9]/i", '', $field));
}, fgetcsv($csv_handle, 0, $delimiter));
}
$create_fields_str = join(', ', array_map(function ($field){
return "$field TEXT NULL";
}, $fields));
$pdo->beginTransaction();
$create_table_sql = "CREATE TABLE IF NOT EXISTS $table ($create_fields_str)";
$pdo->exec($create_table_sql);
$insert_fields_str = join(', ', $fields);
$insert_values_str = join(', ', array_fill(0, count($fields), '?'));
$insert_sql = "INSERT INTO $table ($insert_fields_str) VALUES ($insert_values_str)";
$insert_sth = $pdo->prepare($insert_sql);
$inserted_rows = 0;
while (($data = fgetcsv($csv_handle, 0, $delimiter)) !== FALSE) {
$insert_sth->execute($data);
$inserted_rows++;
}
$pdo->commit();
fclose($csv_handle);
return array(
'table' => $table,
'fields' => $fields,
'insert' => $insert_sth,
'inserted_rows' => $inserted_rows
);
}
这篇关于通过PHP将CSV文件导入SQLite数据库的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文