Вопрос по mysql, import, load-data-infile, csv, database-table – Импортируйте CSV в таблицу MySQL

93

Каков наилучший / самый быстрый способ загрузить CSV-файл в таблицу MySQL? Я хотел бы, чтобы первая строка данных использовалась в качестве имен столбцов.

Нашел это:

Как импортировать файл CSV в таблицу MySQL

Но единственным ответом было использование графического интерфейса, а не оболочки?

ответ, принятый на вопрос, на который вы ссылаетесь, использует графический интерфейс. Ответ, на который вы ссылаетесь, был дан вчера, тогда как этот вопрос (ответ) относится к 2012 году. lcm
И даже решение с графическим интерфейсом не берет имена столбцов из CSV ... вам нужно создать всю таблицу до импорта- Dominique
На этот вопрос уже есть ответstackoverflow.com/questions/3635166/… David

Ваш Ответ

12   ответов
-2

включая & quot; загрузить данные в файл & quot ;, использовать инструментальные средства mysql и т. д.

когда я использую кнопку импорта mysql workbench, сначала вам нужно создать пустую таблицу самостоятельно, установить каждый тип столбца самостоятельно. Примечание: вы должны добавить столбец идентификатора в конце как первичный ключ, а не null и auto_increment, иначе кнопка импорта не будет видна позже. Однако, когда я начинаю загружать CSV-файл, ничего не загружается, похоже на ошибку. Я сдаюсь.

К счастью, лучший простой способ, который я нашел, - это использовать Oracle MySQL для Excel. Вы можете скачать его здесьMySQL для Excel

Это то, что вы собираетесь сделать: Откройте CSV-файл в Excel, на вкладке Данные, найдите кнопку MySQL для Excel

выберите все данные, нажмите кнопку экспорта в MySQL. Обратите внимание, чтобы установить столбец идентификатора в качестве первичного ключа.

когда закончите, перейдите в рабочий стол mysql, чтобы изменить таблицу, такой как тип валюты должен быть десятичным (19,4) для большой десятичной суммы (10,2) для регулярного использования. другой тип поля может быть установлен на varchar (255).

3

mysql -u -p --local-infile & quot ;, он будет работать нормально

0

загрузка данных локального файла работает просто отлично. Я попробовал скрипт php, который опубликовал Hawkee, но у меня не получилось. Вместо того, чтобы отлаживать это, вот что я сделал:

1) скопируйте / вставьте строку заголовка CSV-файла в текстовый файл и отредактируйте его с помощью emacs. добавьте запятую и CR между каждым полем, чтобы включить каждое в его собственную строку.
2) Сохраните этот файл как FieldList.txt
3) отредактируйте файл, включив в него defns для каждого поля (большинство из них были varchar, но довольно много были int (x). Добавить таблицу созданияtablename (в начало файла и) в конец файла. Сохраните его как CreateTable.sql
4) запустить клиент mysql с вводом из файла Createtable.sql для создания таблицы
5) запустить клиент mysql, скопировать / вставить в большинстве «LOAD DATA INFILE»; команда подстановки моего имени таблицы и имени файла CSV. Вставьте в файл FieldList.txt. Обязательно укажите «IGNORE 1 LINES»; перед вставкой в список полей

Похоже, много работы, но легко с Emacs .....

4

которые есть в файле CSV.

Тогда используйте следующий запрос

LOAD DATA INFILE 'D:/Projects/testImport.csv' INTO TABLE cardinfo
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
@ Marci-man попробуй этоstackoverflow.com/q/2139069
Как насчет, если я хотел бы игнорировать столбец в CSV?
23

который будет делать то, что вам нужно:

<?php

$host = 'localhost';
$user = 'root';
$pass = '';
$database = 'database';

$db = mysql_connect($host, $user, $pass);
mysql_query("use $database", $db);

/********************************************************************************/
// Parameters: filename.csv table_name

$argv = $_SERVER[argv];

if($argv[1]) { $file = $argv[1]; }
else {
    echo "Please provide a file name\n"; exit; 
}
if($argv[2]) { $table = $argv[2]; }
else {
    $table = pathinfo($file);
    $table = $table['filename'];
}

/********************************************************************************/
// Get the first row to create the column headings

$fp = fopen($file, 'r');
$frow = fgetcsv($fp);

foreach($frow as $column) {
    if($columns) $columns .= ', ';
    $columns .= "`$column` varchar(250)";
}

$create = "create table if not exists $table ($columns);";
mysql_query($create, $db);

/********************************************************************************/
// Import the data into the newly created table.

$file = $_SERVER['PWD'].'/'.$file;
$q = "load data infile '$file' into table $table fields terminated by ',' ignore 1 lines";
mysql_query($q, $db);

?>

Он создаст таблицу на основе первой строки и импортирует в нее оставшиеся строки. Вот синтаксис командной строки:

php csv_import.php csv_file.csv table_name
Это никогда не работает. Исправьте это в коде сайта.
Awesome script. Для тех, у кого есть файлы CSV с двойными кавычками (читайте большинство людей), добавьте `ENCASED IN & apos; \ & quot; & apos;` кfields terminated by ',' ... он даже работает с частично двойными кавычками.
Этот ответ намного лучше, чем принятый ответ. В частности, он позволяет то, что запрашивал OP, и я также хочу: «первая строка данных будет использоваться в качестве имен столбцов». (Я бы предпочел скрипт на Python, поэтому мне не нужно устанавливать PHP, но его не должно быть сложно портировать.)
@YumYumYum Можете ли вы подробнее рассказать о проблеме, с которой вы столкнулись?
Я думаю ты имеешь ввидуENCLOSED BY '\"'... также многим людям понадобитсяLINES TERMINATED BY '\r\n' если использовать CSV из Windows. И, наконец, целесообразно избегать имен полей с помощью обратных кавычек, если есть пробелы:$columns .= "`$column` varchar(250)";
0

LOAD DATA LOCAL INFILE 'd:\\Site.csv' INTO TABLE `siteurl` FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n';

Character   Escape Sequence
\0      An ASCII NUL (0x00) character
\b      A backspace character
\n      A newline (linefeed) character
\r      A carriage return character
\t      A tab character.
\Z      ASCII 26 (Control+Z)
\N      NULL

посещения: http://www.webslessons.com/2014/02/import-csv-files-using-php-and-mysql.html

3

load data local infile 'file-name.csv'
into table table-name
fields terminated by '' enclosed by '' lines terminated by '\n' (column-name);

В приведенной выше команде в моем случае загружается только один столбец, поэтому «не завершается» и "заключен в" поэтому я оставил это поле пустым, иначе программист может ввести разделительный символ. например, , (запятая) или & quot; или же ; или что-нибудь.

** для людей, которые используют MySQL версии 5 и выше **

Перед загрузкой файла в MySQL необходимо убедиться, что ниже линии буксировки добавлены в сторонуetc/mysql/my.cnf

редактировать команду my.cnf

sudo vi /etc/mysql/my.cnf

[mysqld]  
local-infile

[mysql]  
local-infile  
1

CSV иMySQL Connector:

import csv
import mysql.connector

credentials = dict(user='...', password='...', database='...', host='...')
connection = mysql.connector.connect(**credentials)
cursor = connection.cursor(prepared=True)
stream = open('filename.csv', 'rb')
csv_file = csv.DictReader(stream, skipinitialspace=True)

query = 'CREATE TABLE t ('
query += ','.join('`{}` VARCHAR(255)'.format(column) for column in csv_file.fieldnames)
query += ')'
cursor.execute(query)
for row in csv_file:
    query = 'INSERT INTO t SET '
    query += ','.join('`{}` = ?'.format(column) for column in row.keys())
    cursor.execute(query, row.values())

stream.close()
cursor.close()
connection.close()

Ключевые моменты

Use prepared statements for the INSERT Open the file.csv in 'rb' binary Some CSV files may need tweaking, such as the skipinitialspace option. If 255 isn't wide enough you'll get errors on INSERT and have to start over. Adjust column types, e.g. ALTER TABLE t MODIFY `Amount` DECIMAL(11,2); Add a primary key, e.g. ALTER TABLE t ADD `id` INT PRIMARY KEY AUTO_INCREMENT;
2

в том, как загрузить данные, а в том, как построить таблицу для ее хранения. Вы должны сгенерировать оператор DDL для построения таблицы перед импортом данных.

Особенно сложно, если в таблице большое количество столбцов.

Вот скрипт Python, который (почти) выполняет эту работу:

#!/usr/bin/python    
import sys
import csv

# get file name (and hence table name) from command line
# exit with usage if no suitable argument   
if len(sys.argv) < 2:
   sys.exit('Usage: ' + sys.argv[0] + ': input CSV filename')
ifile = sys.argv[1]

# emit the standard invocation
print 'create table ' + ifile + ' ('

with open(ifile + '.csv') as inputfile:
   reader = csv.DictReader(inputfile)
   for row in reader:
      k = row.keys()
      for item in k:
         print '`' + item + '` TEXT,'
      break
   print ')\n'

Проблема, которую он оставляет для решения, заключается в том, что окончательное объявление имени поля и типа данных завершается запятой, и синтаксический анализатор mySQL не допустит этого.

Конечно, проблема в том, что он использует тип данных TEXT для каждого поля. Если в таблице несколько сотен столбцов, то VARCHAR (64) сделает таблицу слишком большой.

Это также, кажется, ломается при максимальном количестве столбцов для MySQL. Это когда пора переходить в Hive или HBase, если вы можете.

2

чтобы сделать это, я добавлю несколько фрагментов:

$dir = getcwd(); // Get current working directory where this .php script lives
$fileList = scandir($dir); // scan the directory where this .php lives and make array of file names

Затем получите заголовки CSV, чтобы вы могли указать mysql, как импортировать (примечание: убедитесь, что ваши столбцы mysql точно соответствуют столбцам csv):

//extract headers from .csv for use in import command
$headers = str_replace("\"", "`", array_shift(file($path)));
$headers = str_replace("\n", "", $headers);

Затем отправьте ваш запрос на сервер MySQL:

mysqli_query($cons, '
        LOAD DATA LOCAL INFILE "'.$path.'"
            INTO TABLE '.$dbTable.'  
            FIELDS TERMINATED by \',\' ENCLOSED BY \'"\'
            LINES TERMINATED BY \'\n\'
            IGNORE 1 LINES
            ('.$headers.')
            ;
        ')or die(mysql_error());
145

вы можете связать MYSQL непосредственно с ним и загрузить информацию, используя следующий синтаксис SQL.

Чтобы импортировать файл Excel в MySQL, сначала экспортируйте его как файл CSV. Удалите заголовки CSV из созданного файла CSV вместе с пустыми данными, которые Excel, возможно, поместил в конец файла CSV.

Затем вы можете импортировать его в таблицу MySQL, выполнив:

load data local infile 'uniq.csv' into table tblUniq fields terminated by ','
  enclosed by '"'
  lines terminated by '\n'
    (uniqName, uniqCity, uniqComments)

как читать дальше:Импортируйте файл CSV непосредственно в MySQL

EDIT

В вашем случае вам сначала нужно будет написать интерпретатор, чтобы найти первую строку и назначить их в качестве имен столбцов.

EDIT-2

Из документов MySQLнаLOAD DATA syntax:

The IGNORE number LINES option can be used to ignore lines at the start of the file. For example, you can use IGNORE 1 LINES to skip over an initial header line containing column names:

LOAD DATA INFILE '/tmp/test.txt' INTO TABLE test IGNORE 1 LINES;

Поэтому вы можете использовать следующее утверждение:

LOAD DATA LOCAL INFILE 'uniq.csv'
INTO TABLE tblUniq
FIELDS TERMINATED BY ','
    ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(uniqName, uniqCity, uniqComments)
Спасибо ... пока все будет хорошо. lcm
Как насчет, если я хотел бы игнорировать столбец в CSV?
Знаете ли вы, если есть способ установить путь к файлу CSV?
Как отладить эту команду при сбое? я пытаюсь загрузить файл с помощью этой команды, но он ничего не делает.
Вместо удаления первой строки вы можете добавитьIGNORE 1 LINES на запрос
4

есть раздел импорта, в который вы можете импортировать csv-файлы в вашу базу данных, есть даже флажок для установки заголовка, чтобы первая строка файла содержала имена столбцов таблицы (если этот флажок не установлен, первая строка станет частью данных

Я очень удивлен, что вам нужно использовать надстройку, такую как phpadmin, чтобы получить эту функциональность, спасибо за ваш ответ
Это сделало мой день

Похожие вопросы