blog.kompromis.net

Problem polega na przeniesieniu dużej ilości danych zgromadzonych w kilku plikach arkusza kalkulacyjnego do bazy MySQL. Ważne aby w poszczególnych plikach dane były zorganizowane w taki sam sposób (kolumny w poszczególnych zbiorach muszą być rozmieszczone w taki sam sposób).

W moim przykładzie korzystam z pakietu biurowego OpenOffice

Import z arkusza do CSV

Będę importować siedem kolumn nazwanych kolejno:

• pole_a [typ znakowy]
• pole_b [typ numeryczny]
• pole_c [typ numeryczny]
• pole_d [typ numeryczny]
• pole_e [typ numeryczny]
• pole_f [typ numeryczny]
• pole_g [typ znakowy]

Po otwarciu pliku arkusza kalkulacyjnego (w tym wypadku *.xls) zapisuje go zmieniając format zapisu na Tekst CSV. Zaznaczam jednocześnie opcję Edytuj ustawienia filtra (zobacz zrzut ekranu poniżej).

Następnie pokazuje się okno dialogowe Eksport plików tekstowych w którym zaznaczam, że separatorem pola będzie przecinek, natomiast separatorem tekstu znaki cudzysłowia.
Poniżej fragment tak zapisanego pliku, jak widać każde pole jest ujęte w znaki apostrofu, a pola są porozdzielane przecinkami.

"1","3","0","1","0","1","A4"
"67/27","3","0","1","0","1","A4"
"62a","3","0","1","0","1","A4"
"63b","3","0","1","0","1","A4"
"1524/1","3","0","1","1","1","A4"
"15","3","0","1","1","1","A4"
"15cc","3","0","1","1","1","A4"

Tak przygotowany zbiór jest gotowy aby go zaimportować np. do MySQL-a.

Przygotowanie bazy danych i tablicy pod dane.

Przejście do linii komend MySQL-a:

$ mysql -u root -p
Enter password:

Tworzę nową bazę danych (jeżeli to konieczne) pod zbiory importowane z arkuszy kalkulacyjnych o nazwie arkusz_kalkulacyjny. Tworzę jednocześnie użytkownika o nazwie arkusz i haśle arkuszhaslo, który będzie miał wszystkie prawa do tabel tworzonych w bazie danych arkusz_kalkulacyjny.

mysql> create database arkusz_kalkulacyjny;
mysql> grant all on arkusz_kalkulacyjny.* to arkusz@localhost identified by 'arkuszhaslo';

Wylogowuję się z konta administratora i loguję na nowo utworzone przeznaczone dla moich arkuszy.

$ mysql arkusz_kalkulacyjny -u arkusz -parkuszhaslo

Teraz przyszedł czas na stworzenie tabeli w której będę składował dane z przykładowego zbioru *.csv.

mysql> CREATE TABLE `arkusz` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `pole_a` varchar(10) NOT NULL,
  `pole_b` int(1) NOT NULL,
  `pole_c` int(1) NOT NULL,
  `pole_d` int(1) NOT NULL,
  `pole_e` int(1) NOT NULL,
  `pole_f` int(1) NOT NULL,
  `pole_g` varchar(3) NOT NULL
  PRIMARY KEY  (`id`),
);

Do listy pól, które będę importować z arkusza kalkulacyjnego (7 pól), w tablicy bazy danych dodaje jedno o identyfikatorze id. Jest to pole będące kluczem głównym tablicy, a dzięki fladze auto_increment będzie jednoznacznie identyfikowało każdy wprowadzony rekord.

Ładowanie danych

Nadszedł czas aby załadować dane ze zbiorów CSV, nasz zbiór (101.csv) został zapisany w katalogu /tmp. Jesteśmy cały czas w linii komend MySQL-a:

mysql>  LOAD DATA INFILE '/tmp/101.csv' INTO TABLE `arkusz` FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '\\' LINES TERMINATED BY '\n' (pole_a, pole_b, pole_c, pole_d, pole_e, pole_f, pole_g);

Tak załadowane dane już są dostępne w SQL-u, a po co to robić ?
W moim przypadku to była konieczność ja musiałem załadować ponad 150 tys. rekordów były one rozrzucone w kilkunastu zbiorach arkusza kalkulacyjnego.
Teraz można napisać jakąś aplikację przeszukującą (tak prawdę mówiąc jedną prostą, ale zawierającą dużo danych, tablicę).