Jak zaimportować plik CSV do tabeli MySQL
Mam unnormalized events-diary CSV od klienta, który próbuję załadować do tabeli MySQL, aby móc refaktorować do zdrowego formatu. Stworzyłem tabelę o nazwie "CSVImport", która ma jedno pole dla każdej kolumny pliku CSV. Plik CSV zawiera 99 kolumn, więc było to wystarczająco trudne zadanie samo w sobie: {]}
CREATE TABLE 'CSVImport' (id INT);
ALTER TABLE CSVImport ADD COLUMN Title VARCHAR(256);
ALTER TABLE CSVImport ADD COLUMN Company VARCHAR(256);
ALTER TABLE CSVImport ADD COLUMN NumTickets VARCHAR(256);
...
ALTER TABLE CSVImport Date49 ADD COLUMN Date49 VARCHAR(256);
ALTER TABLE CSVImport Date50 ADD COLUMN Date50 VARCHAR(256);
Nie ma ograniczeń w tabeli, a wszystkie pola zawierają wartości VARCHAR( 256), z wyjątkiem kolumn, które zawierają liczby (reprezentowane przez INT), yes/no (reprezentowane przez BIT), ceny (reprezentowane przez dziesiętne) i rozmycia tekstu (reprezentowane przez tekst).
Próbowałem załadować dane do pliku:
LOAD DATA INFILE '/home/paul/clientdata.csv' INTO TABLE CSVImport;
Query OK, 2023 rows affected, 65535 warnings (0.08 sec)
Records: 2023 Deleted: 0 Skipped: 0 Warnings: 198256
SELECT * FROM CSVImport;
| NULL | NULL | NULL | NULL | NULL |
...
Cała tabela jest wypełniona NULL
.
Myślę, że problem polega na tym, że rozmycia tekstu zawierają więcej niż jedną linię, a MySQL parsuje plik tak, jakby każda nowa linia odpowiadała jednemu wierszowi bazy danych. Mogę załadować plik do OpenOffice bez problemu.
Clientdata.plik csv zawiera 2593 linie i 570 rekordów. Pierwsza linia zawiera nazwy kolumn. Myślę, że jest rozdzielany przecinkami, a tekst jest najwyraźniej rozdzielany podwójnym cytatem.
Aktualizacja:
W razie wątpliwości przeczytaj instrukcję: http://dev.mysql.com/doc/refman/5.0/en/load-data.html
Dodałem do LOAD DATA
informację, że OpenOffice był na tyle sprytny, by wywnioskować, a teraz ładuje poprawną liczbę rekordów:
LOAD DATA INFILE "/home/paul/clientdata.csv"
INTO TABLE CSVImport
COLUMNS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
ESCAPED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES;
Ale nadal istnieje wiele CAŁKOWICIE NULL
rekordów, a żadne z danych, które zostały załadowane, nie wydaje się być w we właściwe miejsce.
15 answers
Sednem problemu wydaje się dopasowanie kolumn w pliku CSV do kolumn w tabeli.
Wiele graficznych klientów mySQL ma bardzo ładne okna dialogowe importu dla tego typu rzeczy.
Moim ulubionym zadaniem jest Windows based HeidiSQL . Daje graficzny interfejs do zbudowania polecenia LOAD DATA
; można go później programowo użyć.
Zrzut ekranu: okno dialogowe "Importuj plik tekstowy"
Aby otworzyć Import textfile " okno dialogowe, przejdź do Tools > Import CSV file
:
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2015-12-24 19:20:42
Użyj mysqlimport aby załadować tabelę do bazy danych:
mysqlimport --ignore-lines=1 \
--fields-terminated-by=, \
--local -u root \
-p Database \
TableName.csv
Znalazłem w http://chriseiffel.com/everything-linux/how-to-import-a-large-csv-file-to-mysql/
Aby ogranicznik stał się tabulatorem, użyj --fields-terminated-by='\t'
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2017-04-16 22:59:46
Najprostszy sposób, w jaki zaimportowałem 200 + wierszy, znajduje się poniżej polecenia w oknie phpMyAdmin sql
Mam prostą tabelę kraju z dwoma kolumnami CountryId, CountryName
Tutaj jest .dane csvOto polecenie:
LOAD DATA INFILE 'c:/country.csv'
INTO TABLE country
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS
Pamiętaj o jednej rzeczy, nigdy nie pojawiaj się w drugiej kolumnie, w przeciwnym razie import zostanie zatrzymany
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2014-06-11 18:37:03
Wiem, że pytanie jest stare , ale chciałbym podzielić się tym
Użyłem tej metody do importowania ponad 100k rekordów (~5MB) w 0.046 sec
Oto Jak to zrobić:
LOAD DATA LOCAL INFILE
'c:/temp/some-file.csv'
INTO TABLE your_awesome_table
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
(field_1,field_2 , field_3);
Bardzo ważne jest, aby dołączyć ostatnią linię, jeśli masz więcej niż jedno pole, tzn. Zwykle pomija ostatnie pole (MySQL 5.6.17)
LINES TERMINATED BY '\n'
(field_1,field_2 , field_3);
Następnie, zakładając, że masz pierwszy wiersz jako tytuł dla swoich pól, możesz również dołączyć tę linię
IGNORE 1 ROWS
Tak to wygląda, jeśli plik ma wiersz nagłówka.
LOAD DATA LOCAL INFILE
'c:/temp/some-file.csv'
INTO TABLE your_awesome_table
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS
(field_1,field_2 , field_3);
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2018-03-21 15:56:27
PhpMyAdmin może obsługiwać import CSV. Oto kroki:
Przygotuj plik CSV, aby pola były w tej samej kolejności, co pola tabeli MySQL.
Usuń wiersz nagłówka z pliku CSV (jeśli istnieje), tak aby w pliku znajdowały się tylko dane.
Przejdź do interfejsu phpMyAdmin.
Wybierz tabelę w lewym menu.
Kliknij przycisk Importuj na górze.
Przejdź do pliku CSV plik.
Wybierz opcję "CSV za pomocą danych ładowania".
Wpisz", " w "pola zakończone przez".
Wprowadź nazwy kolumn w tej samej kolejności, w jakiej znajdują się w tabeli bazy danych.
Kliknij przycisk Idź i gotowe.
Jest to notka, którą przygotowałem do mojego przyszłego wykorzystania i dzielenie się tutaj, jeśli ktoś inny może skorzystać.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2016-07-28 04:13:09
Można to naprawić, wymieniając kolumny w instrukcji you LOAD DATA. Z instrukcji :
LOAD DATA INFILE 'persondata.txt' INTO TABLE persondata (col1,col2,...);
... więc w Twoim przypadku musisz wyświetlić listę 99 kolumn w kolejności, w jakiej pojawiają się w pliku csv.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2014-12-29 21:38:26
Linia poleceń mysql jest podatna na zbyt wiele problemów przy imporcie. Oto jak to zrobić:
- Użyj programu excel, aby edytować nazwy nagłówków, aby nie miały spacji
- Zapisz jako .csv
- Użyj darmowej przeglądarki Navicat Lite SQL do importowania i automatycznego tworzenia nowej tabeli (nadaj jej nazwę)
- Otwórz nową tabelę Wstaw główną kolumnę auto number dla ID
- Zmień typ kolumn zgodnie z potrzebami. Zrobione!
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2012-09-25 00:53:44
Spróbuj tego, zadziałało dla mnie
LOAD DATA LOCAL INFILE 'filename.csv' INTO TABLE table_name FIELDS TERMINATED BY ',' ENCLOSED BY '"' IGNORE 1 ROWS;
Ignoruj 1 wiersz ignoruje pierwszy wiersz zawierający fieldnames. Zauważ, że dla nazwy pliku musisz wpisać ścieżkę bezwzględną pliku.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2017-08-16 01:44:25
Możesz również wypróbować to narzędzie online do generowania poleceń SQL create/insert na podstawie Twojego pliku CSV. http://www.convertcsvtomysql.com/
Co lubię w tym narzędziu:
- Proste
- nie tylko generuje instrukcje INSERT, ale także tworzy instrukcje aby wygenerować tabelę
- podczas generowania instrukcji CREATE narzędzie to nie tylko tworzy wszystkie pola VARCHAR-analizuje dane w pliku CSV i na podstawie tej analizy wybiera odpowiednie dane Typ.
Wady
- Rozmiar pliku wejściowego jest ograniczony do 3mb Możesz nie chcieć przekazywać swoich danych osobom trzecim]}
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2013-11-13 16:51:18
Jeśli używasz MySQL Workbench (obecnie Wersja 6.3) możesz to zrobić przez:
- Kliknij prawym przyciskiem myszy na "tabele";
- Wybierz Kreator Importu Danych Tabeli;
- Wybierz plik csv i postępuj zgodnie z instrukcjami (JSON może być również używany); Dobrą rzeczą jest to, że możesz utworzyć nową tabelę na podstawie pliku csv, który chcesz zaimportować lub załadować dane do istniejącej tabeli
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2018-05-24 12:51:25
Jeśli używasz komputera z systemem windows z załadowanym arkuszem kalkulacyjnym Excel, nowa wtyczka mySql do Excela jest fenomenalna. Ludzie z Oracle naprawdę wykonali świetną robotę przy tym oprogramowaniu. Możesz nawiązać połączenie z bazą danych bezpośrednio z programu Excel. Ta wtyczka przeanalizuje Twoje dane i skonfiguruje tabele w formacie zgodnym z danymi. Miałem potworne duże pliki csv z danymi do konwersji. To narzędzie znacznie oszczędzało czas.
Http://dev.mysql.com/downloads/windows/excel/
Możesz wprowadzać aktualizacje z programu Excel, które zostaną przesłane do bazy danych online. To działało niezmiernie dobrze z plikami mySql utworzonymi na ultra tani hosting GoDaddy współdzielony. (Uwaga podczas tworzenia tabeli w firmie GoDaddy należy wybrać niektóre niestandardowe ustawienia, aby włączyć dostęp do bazy danych poza witryną...)
Dzięki tej wtyczce masz czystą interakcję między arkuszem kalkulacyjnym XL a mySql online przechowywanie danych.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2014-12-23 09:07:47
Zmień nazwę serwera,nazwę użytkownika, hasło, nazwę dbname, ścieżkę pliku, nazwę tabeli i pole, które znajduje się w bazie danych, którą chcesz wstawić
<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "bd_dashboard";
//For create connection
$conn = new mysqli($servername, $username, $password, $dbname);
$query = "LOAD DATA LOCAL INFILE
'C:/Users/lenovo/Desktop/my_data.csv'
INTO TABLE test_tab
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(name,mob)";
if (!$result = mysqli_query($conn, $query)){
echo '<script>alert("Oops... Some Error occured.");</script>';
exit();
//exit(mysqli_error());
}else{
echo '<script>alert("Data Inserted Successfully.");</script>'
}
?>
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2017-06-13 06:54:26
Zapytanie PHP o import pliku csv do bazy danych mysql
$query = <<<EOF
LOAD DATA LOCAL INFILE '$file'
INTO TABLE users
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(name,mobile,email)
EOF;
if (!$result = mysqli_query($this->db, $query))
{
exit(mysqli_error($this->db));
}
**przykładowe dane pliku CSV * *
name,mobile,email
Christopher Gritton,570-686-3439,[email protected]
Brandon Wilson,541-309-5149,[email protected]
Craig White,516-795-8065,[email protected]
David Whitney,713-214-3966,[email protected]
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2016-11-29 07:05:28
Oto przykładowy zrzut ekranu pliku excel:
Zapisz jako i wybierz .csv.
I będziesz miał jak pokazano poniżej .dane csv zrzut ekranu, jeśli otworzysz za pomocą Notatnika++ lub innego notatnika.
Upewnij się, że usuniesz nagłówek i wyrównasz kolumnę.csv jak w tabeli mysql. Zastąp folder_name nazwą folderu
LOAD DATA LOCAL INFILE
'D:/folder_name/myfilename.csv"
Do tabeli mail
Pola ZAKOŃCZONE PRZEZ","
(fname, lname, email, telefon);
Jeśli big data, możesz wziąć kawę i załadować ją!.
To wszystko, czego potrzebujesz.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2017-03-31 11:01:25
Kolejnym rozwiązaniem jest użycie narzędzia csvsqlz pakietu amazing csvkit.
Przykład użycia:
csvsql --db mysql://$user:$password@localhost/$database --insert --tables $tablename $file
To narzędzie może automatycznie wnioskować typy danych( zachowanie domyślne), tworzyć tabelę i wstawiać dane do utworzonej tabeli. --overwrite
opcja może być użyta do upuszczenia tabeli, jeśli już istnieje. --insert
opcja-wypełnienie tabeli z pliku.
Aby zainstalować pakiet
pip install csvkit
Warunki wstępne: python-dev
, libmysqlclient-dev
, MySQL-python
apt-get install python-dev libmysqlclient-dev
pip install MySQL-python
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2018-06-21 14:15:20