Jak zaimportować plik CSV do tabeli MySQL?

Mam nienormalizowany dziennik zdarzeń CSV od klienta, który próbuję załadować do tabeli MySQL, abym mógł przefakturować go do rozsądnego formatu. Utworzyłem tabelę o nazwie „CSVImport”, która ma jedno pole dla każdej kolumny pliku CSV. Plik CSV zawiera 99 kolumn, więc samo w sobie było to dość trudne zadanie:

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);

Tabela nie zawiera żadnych ograniczeń, a wszystkie pola zawierają wartości VARCHAR (256), z wyjątkiem kolumn zawierających liczby (reprezentowane przez INT), tak / nie (reprezentowane przez BIT), ceny (reprezentowane przez DECIMAL) i blurb tekstowy ( reprezentowany 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ły stół jest wypełniony NULL.

Myślę, że problem polega na tym, że blobty tekstowe zawierają więcej niż jedną linię, a MySQL analizuje plik tak, jakby każda nowa linia odpowiadała jednemu wierszowi bazy danych. Mogę bez problemu załadować plik do OpenOffice.

Plik clientdata.csv zawiera 2593 wierszy i 570 rekordów. Pierwszy wiersz zawiera nazwy kolumn. Myślę, że jest rozdzielany przecinkami, a tekst jest najwyraźniej rozdzielany podwójnym cudzysłowem.

AKTUALIZACJA:

W razie wątpliwości przeczytaj instrukcję: http://dev.mysql.com/doc/refman/5.0/en/load-data.html

Dodałem trochę informacji do LOAD DATAstwierdzenia, że ​​OpenOffice był wystarczająco inteligentny, aby wnioskować, a teraz ładuje prawidłową 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 NULLzapisów i żadne z załadowanych danych nie wydają się być odpowiednie.

Rdzeniem problemu wydaje się być dopasowanie kolumn w pliku CSV do kolumn w tabeli.

Wiele graficznych klientów mySQL ma bardzo ładne okna dialogowe importu do tego rodzaju rzeczy.

Moim ulubionym do pracy jest HeidiSQL oparty na systemie Windows . Daje graficzny interfejs do budowania LOAD DATApolecenia; możesz go ponownie użyć programowo później.

Zrzut ekranu: okno dialogowe „Importuj plik tekstowy”

Aby otworzyć okno dialogowe Importuj plik tekstowy ”, przejdź do Tools > Import CSV file:

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 to na http://chriseiffel.com/everything-linux/how-to-import-a-large-csv-file-to-mysql/

Aby ustawić ogranicznik jako kartę, użyj --fields-terminated-by='\t'

Najprostszym sposobem, który zaimportowałem ponad 200 wierszy jest polecenie poniżej w oknie phpmyadmin sql

Mam prostą tabelę kraju z dwiema kolumnami CountryId, CountryName

tutaj są dane .csv

oto 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

Wiem, że pytanie jest stare , ale chciałbym się tym podzielić

Użyłem tej metody do zaimportowania ponad 100 000 rekordów ( ~ 5 MB ) w 0,046 s

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 dołączenie ostatniego wiersza, 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ł swoich pól, możesz również chcieć dołączyć ten wiersz

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);

phpMyAdmin może obsłużyć import CSV. Oto kroki:

1. Przygotuj plik CSV, aby pola były w tej samej kolejności, co pola tabeli MySQL.

2. Usuń wiersz nagłówka z pliku CSV (jeśli istnieje), aby w pliku były tylko dane.

3. Przejdź do interfejsu phpMyAdmin.

4. Wybierz tabelę w menu po lewej stronie.

5. Kliknij przycisk importu u góry.

6. Przejdź do pliku CSV.

7. Wybierz opcję „CSV za pomocą LOAD DATA”.

8. Wpisz „,” w „polach zakończonych przez”.

9. Wprowadź nazwy kolumn w tej samej kolejności, w jakiej znajdują się w tabeli bazy danych.

10. Kliknij przycisk Idź i gotowe.

To jest uwaga, którą przygotowałem do mojego przyszłego użytku i dzielę się tutaj, jeśli ktoś inny może skorzystać.

Możesz to naprawić, wyświetlając kolumny w instrukcji LOAD DATA. Z instrukcji :

LOAD DATA INFILE 'persondata.txt' INTO TABLE persondata (col1,col2,...);

... więc w twoim przypadku musisz wymienić 99 kolumn w kolejności, w jakiej pojawiają się w pliku csv.

Spróbuj, to zadziałało dla mnie

    LOAD DATA LOCAL INFILE 'filename.csv' INTO TABLE table_name FIELDS TERMINATED BY ',' ENCLOSED BY '"' IGNORE 1 ROWS;

IGNORE 1 ROWS tutaj ignoruje pierwszy wiersz, który zawiera nazwy pól. Zauważ, że w nazwie pliku musisz wpisać bezwzględną ścieżkę do pliku.

Wiersz polecenia mysql jest podatny na zbyt wiele problemów podczas importowania. Oto jak to zrobić:

• użyj programu Excel do edycji nazw 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 kolumnę podstawowego numeru auto dla ID
• zmień typ kolumn według potrzeb.
• gotowy!

Jeśli używasz MySQL Workbench (obecnie wersja 6.3), możesz to zrobić przez:

1. Kliknij prawym przyciskiem myszy „Tabele”;
2. Wybierz Kreatora importu danych tabeli;
3. Wybierz plik csv i postępuj zgodnie z instrukcjami (można również użyć JSON); 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

Widzę coś dziwnego. Używasz do UCIECZKI tego samego znaku, którego używasz do ZAKOŃCZENIA. Tak więc silnik nie wie, co zrobić, gdy znajdzie „” i myślę, że dlatego wydaje się, że nic nie jest we właściwym miejscu. Myślę, że jeśli usuniesz linię ESCAPING, powinno działać świetnie.

LOAD DATA INFILE "/home/paul/clientdata.csv"
INTO TABLE CSVImport
COLUMNS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES;

Chyba że przeanalizujesz (ręcznie, wizualnie ...) swój plik CSV i znajdziesz, która postać używa do ucieczki. Czasami jest „\”. Ale jeśli go nie masz, nie używaj go.

Jeszcze innym rozwiązaniem jest użycie narzędzia csvsql z niesamowitego pakietu csvkit .

Przykład użycia:

csvsql --db mysql://$user:$password@localhost/$database --insert --tables $tablename  $file

To narzędzie może automatycznie wnioskować o typach danych (zachowanie domyślne), utworzyć tabelę i wstawić dane do utworzonej tabeli. --overwriteOpcji można użyć do usunięcia tabeli, jeśli już istnieje.--insertopcja - aby wypełnić tabelę z pliku.

Aby zainstalować pakiet

pip install csvkit

Wymagania: python-dev , libmysqlclient-dev,MySQL-python

apt-get install python-dev libmysqlclient-dev
pip install MySQL-python

Jak importować pliki csv do tabel sql

Przykładowy plik: plik Overseas_trade_indexCSV danych

Kroki:

1. Musisz utworzyć tabelę dla overseas_trade_index.

2. Musisz utworzyć kolumny związane z plikiem csv.

   Zapytanie SQL:

   ( id int not null primary key auto_increment,
   series_reference varchar (60),
   period varchar (60),
   data_value decimal(60,0),
   status varchar (60),
   units varchar (60),
   magnitude int(60),
   subject text(60),
   group text(60),
   series_title_1 varchar (60),
   series_title_2 varchar (60),
   series_title_3 varchar (60),
   series_title_4 varchar (60),
   series_title_5 varchar (60),
    );

3. Musisz podłączyć bazę danych mysql w terminalu.

   =>show databases;
   =>use database;
   =>show tables;

4. Wprowadź to polecenie, aby zaimportować dane csv do tabel mysql.

   load data infile '/home/desktop/Documents/overseas.csv' into table trade_index fields terminated by ',' lines terminated by '\n' (series_reference,period,data_value,status,units,magnitude,subject,series_title1,series_title_2,series_title_3,series_title_4,series_title_5);

5. Znajdź te dane indeksu handlu zagranicznego w sqldatabase:

   select * from trade_index;

Jeśli używasz komputera z systemem Windows z załadowanym arkuszem kalkulacyjnym Excel, nowa wtyczka mySql do programu Excel jest fenomenalna. Ludzie w Oracle naprawdę wykonali niezłą robotę przy tym oprogramowaniu. Możesz wykonać połączenie z bazą danych bezpośrednio z Excela. Wtyczka przeanalizuje Twoje dane i utworzy dla Ciebie tabele w formacie zgodnym z danymi. Miałem kilka dużych plików CSV z danymi do konwersji. To narzędzie pozwoliło zaoszczędzić dużo czasu.

http://dev.mysql.com/downloads/windows/excel/

Możesz dokonywać aktualizacji z poziomu Excela, które zostaną wprowadzone do bazy danych online. Działa to wyjątkowo dobrze z plikami mySql utworzonymi na bardzo niedrogim hostingu współdzielonym GoDaddy. (Uwaga: gdy tworzysz tabelę w GoDaddy, musisz wybrać niestandardowe ustawienia, aby umożliwić dostęp do bazy danych poza witryną ...)

Dzięki tej wtyczce masz czystą interaktywność między arkuszem kalkulacyjnym XL a internetowym magazynem danych mySql.

Używam mysql workbench do wykonania tej samej pracy.

1. utwórz nowy schemat
2. otwórz nowo utworzony schemat
3. kliknij prawym przyciskiem myszy „Tabele” i wybierz „Kreator importu danych tabeli”
4. podaj ścieżkę do pliku csv i nazwę tabeli, a następnie skonfiguruj typ kolumny, ponieważ kreator ustawia domyślny typ kolumny na podstawie ich wartości.

Uwaga: spójrz na plik dziennika mysql workbench pod kątem błędów, używając „tail -f [mysqlworkbenchpath] /log/wb*.log”

Oto przykładowy zrzut ekranu pliku Excel:

Zapisz jako i wybierz .csv.

Będziesz miał, jak pokazano poniżej, zrzut ekranu .csv, jeśli otworzysz za pomocą notatnika ++ lub innego notatnika.

Upewnij się, że usunąłeś nagłówek i wyrównywałeś kolumny w .csv jak w tabeli mysql. Zastąp folder_name nazwą swojego folderu

LOAD DATA LOCAL INFILE
„D: /folder_name/myfilename.csv„ DO TABELI Poczta POLE ZAKOŃCZONE PRZEZ ”,„ (fname, lname, email, telefon);

W przypadku dużych zbiorów danych możesz wziąć kawę i załadować ją !.

To wszystko czego potrzebujesz.

W przypadku korzystania z Intellij https://www.jetbrains.com/datagrip/features/importexport.html

Kwerenda PHP dotycząca importu 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,ChristopherKGritton@inbound.plus
Brandon Wilson,541-309-5149,BrandonMWilson@inbound.plus
Craig White,516-795-8065,CraigJWhite@inbound.plus
David Whitney,713-214-3966,DavidCWhitney@inbound.plus

Zmień nazwę serwera, nazwę użytkownika, hasło, nazwę db, ścieżkę pliku, tablename i pole znajdujące 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>'
       }
    ?>