MySQL ładuje wartości NULL z danych CSV


167

Mam plik, który może zawierać od 3 do 4 kolumn wartości liczbowych oddzielonych przecinkami. Puste pola są definiowane z wyjątkiem sytuacji, gdy znajdują się na końcu wiersza:

1,2,3,4,5
1,2,3,,5
1,2,3

Poniższa tabela została utworzona w MySQL:

+ ------- + -------- + ------ + ----- + --------- + ------- +
| Pole | Wpisz | Null | Klucz | Domyślnie | Extra |
+ ------- + -------- + ------ + ----- + --------- + ------- +
| jeden | int (1) | TAK | | NULL | |
| dwa | int (1) | TAK | | NULL | |
| trzy | int (1) | TAK | | NULL | |
| cztery | int (1) | TAK | | NULL | |
| pięć | int (1) | TAK | | NULL | |
+ ------- + -------- + ------ + ----- + --------- + ------- +

Próbuję załadować dane za pomocą polecenia MySQL LOAD:

LOAD DATA INFILE '/tmp/testdata.txt' INTO TABLE moo FIELDS 
TERMINATED BY "," LINES TERMINATED BY "\n";

Wynikowa tabela:

+ ------ + ------ + ------- + ------ + ------ +
| jeden | dwa | trzy | cztery | pięć |
+ ------ + ------ + ------- + ------ + ------ +
| 1 | 2 | 3 | 4 | 5 |
| 1 | 2 | 3 | 0 | 5 |
| 1 | 2 | 3 | NULL | NULL |
+ ------ + ------ + ------- + ------ + ------ +

Problem polega na tym, że gdy pole jest puste w danych surowych i nie jest zdefiniowane, MySQL z jakiegoś powodu nie używa domyślnej wartości kolumny (która jest równa NULL) i używa zera. NULL jest używane poprawnie, gdy brakuje całego pola.

Niestety na tym etapie muszę umieć odróżnić NULL od 0, więc każda pomoc będzie mile widziana.

Dzięki S.

edytować

Wyjście POKAŻ OSTRZEŻENIA:

+ --------- + ------ + -------------------------------- ------------------------ +
| Poziom | Kod | Wiadomość |
+ --------- + ------ + -------------------------------- ------------------------ +
| Ostrzeżenie | 1366 | Nieprawidłowa liczba całkowita: „” dla kolumny „cztery” w wierszu 2 |
| Ostrzeżenie | 1261 | Wiersz 3 nie zawiera danych dla wszystkich kolumn |
| Ostrzeżenie | 1261 | Wiersz 3 nie zawiera danych dla wszystkich kolumn |
+ --------- + ------ + -------------------------------- ------------------------ +

Przy takich zmianach schematu danych użyłbym d6tstack, który wyrównuje wszystkie kolumny przed uruchomieniem LOAD DATA. Zobacz sekcję z przykładami SQL d6tstack na temat zmian schematu danych.
citynorman

Odpowiedzi:


193

To zrobi, co chcesz. Wczytuje czwarte pole do zmiennej lokalnej, a następnie ustawia rzeczywistą wartość pola na NULL, jeśli zmienna lokalna zawiera pusty ciąg:

LOAD DATA INFILE '/tmp/testdata.txt'
INTO TABLE moo
FIELDS TERMINATED BY ","
LINES TERMINATED BY "\n"
(one, two, three, @vfour, five)
SET four = NULLIF(@vfour,'')
;

Jeśli wszystkie są prawdopodobnie puste, wczytujesz je wszystkie do zmiennych i masz wiele instrukcji SET, na przykład:

LOAD DATA INFILE '/tmp/testdata.txt'
INTO TABLE moo
FIELDS TERMINATED BY ","
LINES TERMINATED BY "\n"
(@vone, @vtwo, @vthree, @vfour, @vfive)
SET
one = NULLIF(@vone,''),
two = NULLIF(@vtwo,''),
three = NULLIF(@vthree,''),
four = NULLIF(@vfour,'')
;

Przypuszczam, że teoretycznie - ale wszystko jest w pamięci i zawiera tylko niewielkie ilości danych na wiersz, więc wyobrażałbym sobie, że byłoby to nieskończenie małe; ale powinieneś to przetestować, jeśli uważasz, że może to być problem.
Duncan Lock

4
Bardzo podoba mi się ta odpowiedź. Użytkownicy mogą zobaczyć puste ciągi, ''gdy pobierają plik csv (używając IFNULL(Col,'')w SELECT INTO OUTFILEzapytaniu) dla programu Excel, ale następnie przesyłają akceptując je jako puste, zamiast zajmować się nimi \Nw pliku csv. Dzięki!
chrisan

9
dla dat użyłem „NULLIF (STR_TO_DATE (@ date1,„% d /% m /% Y ”),„ 0000-00-00 ”)”
Joaquín L. Robles,

1
Mam plik csv zawierający zera, na 0które należy przekonwertować NULL(ponieważ nie jest możliwe uzyskanie wartości zerowej dla danych, o których mowa), a także puste ciągi. Jak upewnić się, że zarówno zera, jak i puste ciągi są konwertowane na NULL?
Paul Rougieux,

Jeśli wartości zerowe i puste łańcuchy są w oddzielnych kolumnach, a potem po prostu wykonaj powyższe pustych strun, a coś takiego dla zer: nullif(@vone, 0).
Duncan Lock,

136

Podręcznik MySQL mówi:

Podczas odczytu danych za pomocą LOAD DATA INFILE puste lub brakujące kolumny są aktualizowane za pomocą „”. Jeśli chcesz mieć wartość NULL w kolumnie, powinieneś użyć \ N w pliku danych. W pewnych okolicznościach można również użyć dosłownego słowa „NULL”.

Więc musisz zamienić puste miejsca na \ N w ten sposób:

1,2,3,4,5
1,2,3,\N,5
1,2,3

3
Dzięki za wskazówkę - jestem sceptyczny, jeśli chodzi o edycję surowych danych źródłowych, ale jeśli jest to jedyny sposób, spróbuję.
Spiros

7
Rozumiem twój sceptycyzm, nikt nie lubi edytować surowych danych, po prostu nie wydaje się to właściwe. Jeśli jednak pomyślisz o tym przez chwilę, musi istnieć sposób na odróżnienie NULL od pustego łańcucha. Gdyby puste wpisy były tłumaczone na wartości NULL, potrzebna byłaby specjalna sekwencja dla pustego ciągu. Byłoby miło mieć sposób, jak powiedzieć MySQL, jak traktować puste wpisy, coś w rodzaju LOAD DATA INFILE '/tmp/testdata.txt' INTO TABLE moo TREAT BLANKS AS NULL ...
Janci

2
OK, ale jeśli Fields enclosed by: "jest to, że "\N"od"name",\N,"stuff"
Jonathon

3
Mogę zweryfikować, że przynajmniej dla „phpMyAdmin 3.5.5” żaden styl nie \Njest akceptowany jako oznaczający NULL. Zamiast tego użyj NULL, jak w tym przykładzie:"name","age",NULL,"other","stuff"
Jonathon

1
Mamy MySQL 5.5.46-0 + deb8u1. Wypróbowałem zarówno NULL, jak i \ N i tylko \ N działało dla nas.
raphael75

6

Zachowanie jest różne w zależności od konfiguracji bazy danych. W trybie ścisłym spowodowałoby to błąd, w przeciwnym razie ostrzeżenie. Poniższe zapytanie może posłużyć do określenia konfiguracji bazy danych.

mysql> show variables like 'sql_mode';

Dzięki! Drapałem się po głowie, próbując zrozumieć, dlaczego import pliku CSV z pustymi kolumnami, które z powodzeniem zaimportowałem wczoraj na serwer produkcyjny, nie działa na mojej nowej instalacji lokalnej - taka była odpowiedź w moim przypadku!
Emma Burrows

3

Przetwórz wstępnie wprowadzony plik CSV, aby zastąpić puste wpisy znakiem \ N.

Spróbuj na wyrażeniu regularnym: s / ,, /, \ n, / g i s /, $ /, \ N / g

Powodzenia.


1
To wyrażenie regularne częściowo działa, nie rozwiązuje sekwencyjnych pustych wpisów, na przykład ,,,, będzie, \ n ,, \ n, powinno być użyteczne, jeśli uruchomisz je dwukrotnie
ievgen

1
Podsumuje odpowiedź i poprzedni komentarz. Następujące pracowało dla mnie, w kolejności: sed -i 's / ,, /, \ N / g' $ plik, sed -i 's / ,, /, / g' $ plik, sed -i 's / \ N, $ / \ N / g '$ plik,
Omar Khazamov

Chciałbym to zrobić, ale nie wiem, jak używasz tego wyrażenia regularnego. Jeśli używasz MySQL do uruchomienia tego na pliku, byłoby to najlepsze rozwiązanie. Ale ty nie mówisz, a ja nie chcę spędzać czasu na szukaniu w Google, jak zrobić coś, co może nie być możliwe.
DonkeyKong,

1

(zmienna1, @ zmienna2, ..) SET zmienna2 = nullif (@ zmienna2, '' lub '') >> możesz wstawić dowolny warunek


0

pokaż zmienne

Show variables like "`secure_file_priv`";

Uwaga: zachowaj plik csv w lokalizacji podanej przez powyższe polecenie.

create table assessments (course_code varchar(5),batch_code varchar(7),id_assessment int, assessment_type varchar(10), date int , weight int);

Uwaga: tutaj datekolumna „ ” zawiera puste wartości w pliku csv.

LOAD DATA INFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/assessments.csv' 
INTO TABLE assessments
FIELDS TERMINATED BY ',' 
OPTIONALLY ENCLOSED BY '' 
LINES TERMINATED BY '\n' 
IGNORE 1 ROWS 
(course_code,batch_code,id_assessment,assessment_type,@date,weight)
SET date = IF(@date = '', NULL, @date);
Korzystając z naszej strony potwierdzasz, że przeczytałeś(-aś) i rozumiesz nasze zasady używania plików cookie i zasady ochrony prywatności.
Licensed under cc by-sa 3.0 with attribution required.