Import z Excelu do MySQL databázy

Naplniť MySQL databázu tisíckami záznamov je zdĺhavé a komplikované, no vo väčšine prípadov nevyhnutné. Často sa potýkam s chaoticky formátovanými Excelovskými dokumentami a požiadavkou od klienta – spracujte ich, chceme ich mať na webe.
Import z Excelu, resp. z delimitovaného CSV súboru, vygenerovaného Excelom je síce v MySQL štandardne podporovaný, ale vyžaduje si samozrejme ako tak naformátovaný dokument s jasnou štruktúrou a aj nejaký ten zásah zo strany PHP. Dá sá síce využiť natívna MySQL funkcie „LOAD DATA INFILE“: http://dev.mysql.com/…ad-data.html, ale riešenie s pomocou PHP ponúka trochu viac, i keď nie je také výkonné (to je však prakticky jedno, keďže tie tisíce záznamov sa spracujú len raz…).

Ponúkam vám náčrt jednoduchej PHP triedy, ktorá na základe nejakých špecifikácií spracuje CSV súbor a vloží jeho štruktúrovaný obsah do databázy.

PHP TRIEDA: CSV DO MYSQL DATABÁZY

Začnem od konca, aby ste si rovno mohli povedať, či takúto triedu chcete alebo nie. Tak teda uvádzam zápis spracovania jedného CSV súboru.

<?
$csv =new importCSV;
 
$csv->directory = 'tmp'; // adresár, kde je CSVčko uložené
$csv->file = 'firmy.csv'; // názov súboru
$csv->table = 'firmy'; // názov mysql tabuľky, do ktorej sú dáta vkladané
$csv->csv_columns = array('firma','login'); // nazvy stlpcov v CSV súbore - zhodné s názvami v MySQL
$csv->specific_table_columns = array('aktivna'=>'1'); // špeciálne spracované stĺpce v MySQL - nefigurujúce v CSV
$csv->specific_csv_columns = array('login'=>'optimizeInput'); // špeciálne spracované stĺpce - figurujúce v CSV
?>

Vysvetlenie

Predstavme si situáciu – máme naplniť databázu firiem z Excelovského súboru. MySQL tabuľka vyzerá takto:

Štruktúra CSV súboru vyzerá takto:

nazov_firmy ; login ; heslo
druha_firma ; login ; heslo

Vysvetlím teraz jednotlivé premenné. Prvé tri snáď sú jasné, tak prejdem k ďalším.

Pole csv_columns obsahuje názvy stĺpcov v CSV súbore, ktoré musia byť zhodné so stĺpcami v MySQL tabuľke. Na základe tohto poľa trieda dokáže vyskladať korektnú mysql query – priradí hodnoty stĺpcov k názvom.

Pole specific_table_co­lumns môže a nemusí obsahovať pravidlá pre niektoré špecifické stĺpce z tabuľky. V našom prípade vidíme, že v CSVčku máme zadaný len názov a login firmy. V tabuľke je však ešte stĺpec aktivna (ENUM 1/0), kde je zaznačené, či je firma aktívna (1) alebo neaktívna (0). Tento príznak treba vyšpecifikovať v poli specific_table columns. Hodnota tu zadaná sa prejaví v každom zázname – teda každá firma bude vložená s príznakom aktivna = 1.

<?
$csv->specific_table_columns = array('aktivna'=>'1');
?>

Pole specific_csv_co­lumns obsahuje pravidlá na špecificky spracúvané stĺpce z CSV. Predstavme si, že v stĺpec login v CSVčku máme chaoticky spracovaný a potrebujeme z neho spraviť string bez diakritiky, medzier a pod., aby sa dal bez problémov použiť ako prihlasovacie meno.

<?
$csv->specific_csv_columns = array('login'=>'optimizeInput','heslo'=>'md5password');
?>

Takýto zápis znamená, že každá položka zo stĺpca login sa najprv preženie metódou optimizeInput. Druhý stĺpec – heslo – zasa zakódujeme metódou md5password. Trieda predpoklada, že všetky metódy tu použité sú v zdedenej triede csvMethods (class importCSV extends csvMethods).

Celý zdrojový kód triedy

class importCSV extends csvMethods {
 
        var $directory;
        var $file;
        var $table;
        var $specific_table_columns = array();
        var $csv_columns = array();
 
        private function createArrayFromCSV() {
 
                //nahrá csvčko
                $handle = fopen ($this->directory . '/' . $this->file,"r");
 
                //prechádza riadkami, z každého riadku vytvorí pole
                while ($data = fgetcsv ($handle, 1000, "\n\r")) {
 
                        //explodne data na základe delimitera
                        $line_array = explode(";", $data[0]);
 
                        $i=0;
 
                        //vytvorí pole s názvami kľúčov zhodnými s názvami stĺpcov v tabuľke
                        foreach($this->csv_columns as $key) {
                                $ret[$key][]            =       $line_array[$i];
                                $i++;
                        }
 
                }
 
                return $ret;
 
        }
 
        private function createArrayForMySQL() {
 
                // nahrá obsah CSVčka ako pole
                $data = $this->createArrayFromCSV();
 
                //spočíta počet riadkov
                $size = sizeof($data[$this->csv_columns[0]]);
 
                foreach ($this->specific_table_columns as $key => $value) {
 
                                for($i=0;$i<$size;$i++) {
                                        $data[$key][] = $value;
                                }
 
                }
 
                return $data;
 
        }
 
        public function generateQueries() {
 
                $data = $this->createArrayForMySQL();
 
                $size = sizeof($data[$this->csv_columns[0]]);
 
                for($i=0;$i<$size;$i++) {
                        unset($r);
                        foreach($this->csv_columns as $key) {
 
                                //spracuje špecifické stĺpce
                                if(array_key_exists($key,$this->specific_csv_columns)){
                                        $method_name = $this->specific_csv_columns[$key];
                                        $data[$key][$i] = $this->$method_name($data[$key][$i]);
                                }
 
                                $r[$i][] = "'" . $data[$key][$i] ."'";
                        }
 
                        if(is_array($this->specific_table_columns)) {
                           foreach ($this->specific_table_columns as $key) {
 
                                $r[$i][] = "'" . $key ."'";
                           }
                        }
 
                        $ret[$i] = implode(',',$r[$i]);
 
                }
 
                $i=1;
 
                foreach($ret as $key) {
 
                        if(is_array($this->specific_table_columns)) {
 
                                unset($sp_cl);
                                foreach ($this->specific_table_columns as $s_key => $s_value) {
                                        $sp_cl[] = $s_key;
                                }
 
                                $cols = implode(',',$this->csv_columns).','.implode(',',$sp_cl);
                        } else {
                                $cols = implode(',',$this->csv_columns);
                        }
 
                        $q = "INSERT INTO $this->table (".$cols.") VALUES ($key)";
 
                        mysql_query($q);
 
                        $i++;
                }
 
                return 'Processed <b>' . $i . '</b> lines from CSV file.';
 
        }
 
}

Tomáš Mašek