PHP / SQL — загрузка CSV в базу данных — разные таблицы ежедневно / несколько таблиц
Я задал вчера вопрос, который был неясен, и теперь я немного расширил его. Короче говоря, этот текущий проект требует простого веб-интерфейса, где пользователь может загрузить CSV-файл (эта веб-страница уже создана). Я изменил свой PHP для тестового файла, но моя ситуация требует чего-то другого. Каждый день пользователь будет загружать от 1 до 5 различных отчетов CSV. Эти отчеты содержат около 110 полей / столбцов, хотя не все поля будут заполнены в каждом отчете. Я создал базу данных с 5 таблицами, каждая из которых покрывает различные поля из 110. Например, одна таблица содержит информацию о счетчиках воды (25 полей), а другая таблица содержит информацию о тестах, выполненных на счетчиках (45 полей). ). Мне трудно найти способ взять CSV, после загрузки, и разбить данные на разные таблицы. Я слышал о том, как поместить весь CSV в одну таблицу и разделить ее с помощью операторов INSERT, но у меня есть вопросы по этому поводу:
-
Есть ли способ поместить CSV со 110 полями в одну таблицу без создания полей? Или мне нужно создать 110 полей в MYSQL Workbench, а затем создать переменную для каждого в PHP?
-
Если нет, смогу ли я объявить переменные из дампа таблицы, чтобы правильные данные затем вошли в правильную таблицу?
Я не настолько знаком с CSV с точки зрения загрузки, как эта, обычно просто извлекаю CSV из папки с известным именем файла, так что вот откуда у меня путаница. Вот PHP, который я использовал как простой тест с 10 столбцами. Это было сделано, чтобы убедиться, что загрузка CSV работает, что она и делает.
<?php
$server = "localhost";
$user = "root";
$pw = "root";
$db = "uwstest";
$connect = mysqli_connect($server, $user, $pw, $db);
if ($connect->connect_error) {
die("Connection failed: " . $conn->connect_error);
}if(isset($_POST['submit']))
{
$file = $_FILES['file']['tmp_name'];
$handle = fopen($file, "r");
$c = 0;
while(($filesop = fgetcsv($handle, 1000, ",")) !== false)
{
$one = $filesop[0];
$two = $filesop[1];
$three = $filesop[2];
$four = $filesop[3];
$five = $filesop[4];
$six = $filesop[5];
$seven = $filesop[6];
$eight = $filesop[7];
$nine = $filesop[8];
$ten = $filesop[9];
$sql = "INSERT INTO staging (One, Two, Three, Four, Five, Six, Seven, Eight, Nine, Ten) VALUES ('$one','$two', '$three','$four','$five','$six','$seven','$eight','$nine','$ten')";
}
if ($connect->query($sql) === TRUE) {
echo "You database has imported successfully";
} else {
echo "Error: " . $sql . "<br>" . $conn->error;
}
}
}?>
Решение
В зависимости от размера CSV, вы можете рассмотреть возможность использования встроенной функции импорта CSV в MySQL, так как она работает в 10-100 раз быстрее.
Если вы настаиваете на импорте строки за строкой, то вы можете сделать что-то подобное с PDO (или адаптировать его к mysqli).
Если вы хотите сопоставить столбцы, то либо сохраните свой CSV как ассоциативный массив, либо проанализируйте первую строку и сохраните ее в массиве, например, $ cols.
в этом случае $ results является ассоциативным массивом, в котором хранится строка csv с column_name => column_value
$cols=implode(',',array_keys($result));
$vals=':'.str_replace(",",",:",$cols);
$inserter = $pdo->prepare("INSERT INTO `mydb`.`mytable`($cols) VALUES($vals);");
foreach ($result as $k => $v) {
$result[':' . $k] = utf8_encode($v);
if(is_null($v))
$result[':' . $k] = null;
unset($result[$k]);
}
$inserter->execute($result);
надеюсь это поможет.
Я предлагаю пойти с PDO, чтобы избежать всех странностей, с которыми вы можете столкнуться в данных CSV.
Вот так я бы создал столбцы / значения.
$is_first=true;
$cols='';
$vals='';
$cols_array=array();
while (($csv = fgetcsv($handle)) !== false) {
if($is_first)
{
$cols_array=$csv;
$cols=implode(',',$csv);
$is_first=false;
$vals=':'.str_replace(",",",:",$cols);
continue;
}
foreach ($result as $k => $v) {
$result[':' . $cols_array[$k]] = utf8_encode($v);
if(is_null($v))
$result[':' . $cols_array[$k]] = null;
unset($result[$k]);
}
$inserter->execute($result);
}
Вот код, который я использую для импорта CSV.
$file='data/data.csv';
$handle = fopen($file, "r");
$path=realpath(dirname(__FILE__));
$full_path=$path."/../../$file";
$cnt = 0;
$is_first = true;
$headers=array();
$bind=array();
$csv = fgetcsv($handle, 10000, ",");
$headers=$csv;
$alt_query='LOAD DATA LOCAL INFILE \''.$full_path.'\' INTO TABLE mytable
FIELDS TERMINATED BY \',\'
ENCLOSED BY \'\"\'
LINES TERMINATED BY \'\r\n\'
IGNORE 1 LINES
(' . implode(',',$headers).')';
echo exec("mysql -e \"USE mydb;$alt_query;\"",$output,$code);
Другие решения
Предполагая, что отношение между таблицами и CSV является произвольным, но пока единообразным, вам просто нужно один раз установить индекс индекса соответствия -> столбец таблицы.