CSV Datei auslesen und in SQL importieren

Coldgamer

Grünschnabel
Hallo zusammen,

ich bin relativ unerfahren in PHP und sitze aktuell vor einer Aufgabe, bei der ich mir nicht sicher bin, dass ich sie
mit PHP lösen kann.

Folgende Problemstellung:

Ich habe eine SQL Datenbank mit einer Tabelle. In dieser Tabelle sind unterschiedliche "Merkmalsnamen",
die als Spalten definiert sind (diese Spalten beschreiben die Eigenschaften eines Produktes z.B. Höhe).
In den Zeilen dieser Spalten befindet sich aktuell noch keine Daten, diese befinden sich aktuell in einer Excel-Tabelle.

Ich habe diese schon als csv exportiert und per PHP eingelesen, das Problem ist, das die Spalten in der
Excel nicht mit den Spalten der SQL Tabelle übereinstimmen. Manchmal sind diese vertauscht, daher kann
ich die CSV-Datei nicht einfach importieren.

Ist es möglich, per PHP den Spaltennamen der SQL Tabelle zu suchen und in der CSV und wenn dieser in
beiden vorhanden ist, soll der Wert aus der CSV in SQL gespeichert werden.

Vielen Dank im Voraus und schöne Grüße
Daniel
 
Ja, das ist möglich. Für genauere Antworten ist die Frage zu unkonkret.

Ein Anfang wäre wohl, eine Variante der Query SHOW COLUMNS FROM tabellenname per PHP auszuführen. So kommst du an die Feld-/Spaltennamen einer Tabelle.
 
Hi, danke für deine Rückmeldung, ich versuche mal mein Problem besser darzustellen.

Ich habe eine SQL Tabelle "Produktgruppe", in dieser Tabelle sind ca. 150 Spalten (bsp. "ID", "Name", "Bildlegende", ....).

Innerhalb dieser Tabelle gibt es noch keine Daten, diese befinden sich aktuell in einer Excel-Datei.
Normalerweise würde ich jetzt die Excel als CSV exportieren und in die SQL Tabelle importieren,
jedoch hat die Excel eine andere Anzahl Spalten. So hat die Excel z.B. nur 100 Spalten.

Jetzt möchte ich über PHP eine Abfrage formulieren, der Theoretisch den Spaltenname der Tabelle "Produktgruppe" abfragt
und anschließend in der Excel nachschaut und dort aus einer Spalte mit dem selben Spaltennamen die Daten abfragt und importiert.

Leider bin ich beim durchstöbern im Internet auf keinen wirklichen Lösungsansatz gestoßen und hatte teilweise das Gefühl, dass das
was ich vorhabe, so nicht funktioniert.

Gruß Daniel
 
Nun CSV oder Excel? CSV wäre einfacher zu handhaben. Du hast im Grunde aber deinen ersten Beitrag noch mal anders formuliert geschrieben. ;) Daher bleibt auch meine Antwort im Grunde gleich. Es ist möglich. Du musst halt die Feldnamen aus DB-Tabelle und CSV-Datei auslesen und vergleichen und so die entsprechenden INSERT-Queries generieren.

Edit: Konkretere Antworten sind ohne konkretere Fragen (zum Beispiel mit Code) nicht so einfach, wenn man es nicht gerade vollständig für dich programmieren will. (Ohne damit sagen zu wollen, dass das verboten wäre. Aber muss man halt dann auch erst mal machen. ;))
 
Zuletzt bearbeitet:
Hi, vielen Dank für deine Rückmeldung :)

Da, wie du schon erwähnt hast, CSV einfacher ist, würde ich die Excel-Tabellen als CSV exportieren und dann über eine Abfrage in die SQL Datenbank importieren.

Aktuell habe ich eine Abfrage, wo ich die CSV Datei in einen Array einlese (Zeile für Zeile) und ich frage die Spaltennamen in SQL ab.
Das funktioniert soweit schon ganz gut, nur ich weiß jetzt nicht, wie ich prüfen kann ob eine Spalte in der CSV vorkommt und wenn ja, schreibe diese in SQL.

PHP:
//CSV in Array einlesen
$spalte1= array();
$handle = fopen ("meineCSV.csv","r");
while ($data = fgetcsv ($handle))
{
    $spalte1[] = $data[0];
}
fclose ($handle); 

// Datenbank Spaltenname abfragen
$pdo = new PDO('mysql:host=localhost;dbname=db', 'root', 'pw');

$rs = $pdo->query('SELECT * FROM meineTabelle LIMIT 0');
for ($i = 0; $i < $rs->columnCount(); $i++) {
    $col = $rs->getColumnMeta($i);
    $columns[] = $col['name'];
}

Ich versuche das mal am besten an einem Beispiel zu zeigen:

Die CSV besteht aus 5 Zeilen, die erste Zeile sind die Spaltennamen und diese sind mit einem Semikolon getrennt,
in den folgenden Spalten kommen dann Werte (oder auch nicht):
----
ID;Name;Einstufung;Zusatz
1;Höhe;Hoch;;
2;Breite;Hoch;;
3;Maße;Mittel;Zusammengestzter Wert;
---
Jetzt habe ich dem gegenüber meine SQL Tabelle, die nicht alle oder andere Spaltennamen hat:

---
ID:Name:Werte:Zusatz
---

Wie formuliere ich nur in PHP die Abfrage so, das ich die Spalten und die Werte der CSV in SQL bekomme.
Ein Ansatz würde mir schon viel helfen, ich erwarte keinen vollständigen Code (auch Englische Seiten mit Tipps sind gern gesehen).

Vielen Dank und Gruß
Daniel
 
Weil mich das Thema interessiert, habe ich mal etwas ausgearbeitet:
PHP:
$result=mysqli_query($con, 'DROP TABLE `TEST`');
$queryCreateUsersTable = "CREATE TABLE IF NOT EXISTS `TEST` (
    `ID` int(11) unsigned NOT NULL,
    `NAME` varchar(30) NOT NULL,
    `WERT` varchar(30) NOT NULL,
    `A` varchar(30) NOT NULL,
    `B` varchar(30) NOT NULL,
    `X` varchar(30),
    `Y` varchar(30)
)";
$result=mysqli_query($con, $queryCreateUsersTable);

$sql="SHOW COLUMNS FROM `TEST`";
$result=mysqli_query($con, $sql);
$columns = array();
while ($row=mysqli_fetch_assoc($result)) {
    $columns[] = $row["Field"];
}
echo 'columns in table: ';
var_dump($columns);
echo('<br>');

$mcolumns = array();
if (($handle = fopen("testcsv.csv", "r")) !== FALSE) {
   $colscsv = fgetcsv($handle, 1000, ";");
    for ($i = 0; $i < count($colscsv); $i++) {
        if (in_array($colscsv[$i], $columns)) {
           $mcolumns[] = array("idx" => $i, "name" => $colscsv[$i]);
        }
    }
    echo 'matching columns: ';
    var_dump($mcolumns); // Hier stehen jetzt die übereinstimmenden Spaltennamen
                    // und ihre Positionen in der CSV-Datei

    while (($data = fgetcsv($handle, 1000, ";")) !== FALSE) {
        echo '<br>';
        var_dump($data);
        // Hier kannst Du die CSV-Daten aus $data
        // mit Hilfe von $mcolumns in deine Tabelle eintragen
    }
    fclose($handle);
}
Ich habe mit diesem CSV getestet:
Code:
ID;NAME;WERT;A;B;C;D
1;name1;wert1;a1;b1;c1;d1
2;name2;wert2;a2;b2;c2;d2
3;name3;wert3;a3;b3;c3;d3
 
Zuletzt bearbeitet:
Habe es noch mal ein wenig erweitert. Hoffe, das stört nicht.

PHP:
<?php

/**
 *
 */
class CsvReader
{
    /**
     * @var string
     */
    private $delimiter;

    /**
     * @var string
     */
    private $enclosure;

    /**
     * @var string
     */
    private $escape;

    /**
     * @param string $delimiter
     * @param string $enclosure
     * @param string $escape
     */
    public function __construct($delimiter = ',', $enclosure = '"', $escape = '\\')
    {
        $this->delimiter = $delimiter;
        $this->enclosure = $enclosure;
        $this->escape    = $escape;
    }

    /**
     * @param $filename
     * @param array $keys If not empty, use as array keys. Otherwise read keys
     *                    from first line of input data
     * @return array
     * @throws Exception
     */
    public function fetchAllAssoc($filename, array $keys = array())
    {
        $stream = fopen($filename, 'rb');

        if (!is_resource($stream)) {
            throw new Exception('CSV file couldn\'t be opened');
        }

        $headerFlag = true;
        $keysCount = count($keys);

        if ($keysCount > 0) {
            $headerFlag = false;
        }

        $data = array();

        while ($row = fgetcsv($stream, 0, $this->delimiter, $this->enclosure, $this->escape)) {
            if ($headerFlag) {
                $headerFlag = false;
                $keys = $row;
                $keysCount = count($keys);
                continue;
            }

            if (count($row) !== $keysCount) {
                throw new Exception('Malformed line in CSV input');
            }

            $data[] = array_combine($keys, $row);
        }

        fclose($stream);

        return $data;
    }
}

/**
 *
 */
class MySqlPartialDataImporter
{
    /**
     * @var mysqli
     */
    private $db;

    /**
     * @param mysqli $connection
     */
    public function __construct(mysqli $connection)
    {
        $this->db = $connection;
    }

    /**
     * @param string $table
     * @return array
     */
    private function getColumnNames($table)
    {
        $sql = sprintf("SHOW COLUMNS FROM %s", $this->quoteIdentifier($table));

        $result = $this->db->query($sql);

        $columns = array();

        while ($row = $result->fetch_assoc()) {
            $columns[] = $row['Field'];
        }

        return $columns;
    }

    /**
     * @param $identifier
     * @return string
     */
    private function quoteIdentifier($identifier)
    {
        return '`' . str_replace('`', '``', $identifier) . '`';
    }

    /**
     * @param $value
     * @return string
     */
    private function quote($value)
    {
        return "'" . $this->db->real_escape_string($value) . "'";
    }

    /**
     * @param string $tableName
     * @param array $entry
     * @param array $excludedColumns
     * @return string
     * @throws Exception
     */
    public function buildInsertQuery($tableName, array $entry, array $excludedColumns = array())
    {
        $columns = $this->getColumnNames($tableName);

        $columnsFiltered = array();

        foreach ($columns as $column) {
            if (in_array($column, $excludedColumns, true)) {
                continue;
            }

            $columnsFiltered[] = $column;
        }

        $values = array();

        foreach ($columnsFiltered as $key) {
            if (array_key_exists($key, $entry)) {
                $values[$key] = $entry[$key];
            }
        }

        if (0 === count($values)) {
            throw new Exception('Resulting INSERT would be empty');
        }

        $keys = array_keys($values);

        $myself = $this;

        array_walk($values, function (&$value) use ($myself) {
            $value = $myself->quote($value);
        });

        array_walk($keys, function (&$value) use ($myself) {
            $value = $myself->quoteIdentifier($value);
        });

        $query = sprintf(
            'INSERT INTO %s (%s) VALUES (%s)',
            $this->quoteIdentifier($tableName),
            implode(', ', $keys),
            implode(', ', $values)
        );

        return $query;
    }
}

/**
 * @param mysqli $con
 */
function debugResetTable(mysqli $con)
{
    $queryDrop = "
        DROP TABLE IF EXISTS `TEST`
    ";

    mysqli_query($con, $queryDrop);

    $queryCreateUsersTable = "
        CREATE TABLE IF NOT EXISTS `TEST` (
            `ID` int(11) unsigned NOT NULL,
            `NAME` varchar(30) NOT NULL,
            `WERT` varchar(30) NOT NULL,
            `D` varchar(30) NOT NULL,
            `A` varchar(30) NOT NULL,
            `foo bar` varchar(30),
            `quz``qiz` varchar(30)
        )
    ";

    mysqli_query($con, $queryCreateUsersTable);
}



$con       = mysqli_connect('localhost', 'root', 'root', 'fiddle');
$tableName = 'TEST';

debugResetTable($con);

$csvReader = new CsvReader(';');
$importer  = new MySqlPartialDataImporter($con);

$entries = $csvReader->fetchAllAssoc(__DIR__ . '/testcsv.csv');

foreach ($entries as $entry) {
    $query = $importer->buildInsertQuery($tableName, $entry);
    var_dump($query);
}
 
Sorry für Doppelpost und halbes OT. Habe noch ein wenig am CsvReader gebastelt. Ich habe die Klasse mal als Gist angelegt, damit ich sie vielleicht irgendwann noch mal updaten kann, ohne dass hier eine alte Version steht.

- (aktuell) Der Reader ist jetzt Teil von meinem kaloa/filesystem-package. https://github.com/mermshaus/kaloa-filesystem Die Klasse kann aber einfach rauskopiert werden. Sie hat keine Abhängigkeiten.

- (veraltet) https://gist.github.com/mermshaus/e9a01de0e57ac8b18306c24b69e13669

Neu ist eine „Streaming“-Unterstützung, also ein sukzessives Abrufen von CSV-Datensätzen. So müssen nicht alle Daten zeitgleich im Speicher gehalten werden, was bei großen CSV-Dateien ein Problem sein könnte.

Beispiel dazu:

demo.csv:

Code:
name,team,position
"Thomas Müller","FC Bayern München",Sturm
"Julian Weigl","Borussia Dortmund",Mittelfeld
"Serge Gnabry","Werder Bremen",Sturm
"Gianluigi Buffon",Juventus,Tor
"Shkodran Mustafi",Arsenal,Abwehr

Code:

PHP:
$reader = new \mermshaus\CsvReader(__DIR__ . '/demo.csv');

while ($row = $reader->fetchAssoc()) {
    echo $row['name'] . ' (' . $row['position'] . ')' . "\n";
}

Ausgabe:

Code:
Thomas Müller (Sturm)
Julian Weigl (Mittelfeld)
Serge Gnabry (Sturm)
Gianluigi Buffon (Tor)
Shkodran Mustafi (Abwehr)

Enthält die CSV-Datei keine Header-Zeile mit Spaltennamen, können die Keys auch manuell hinzugefügt werden:

CSV-Daten wie oben, nur ohne die erste Zeile.

Veränderte Codezeilen:

PHP:
while ($row = $reader->fetchAssoc(array('name', 'team', 'position'))) {
    // ...
}

Auch das liefert Einträge wie:

Code:
array(3) {
  ["name"]=>
  string(14) "Thomas Müller"
  ["team"]=>
  string(18) "FC Bayern München"
  ["position"]=>
  string(5) "Sturm"
}

Ausgabe also wie oben.

Der normale Anwendungsfall, alle Daten der Reihe nach auszulesen, sollte einwandfrei funktionieren. Abseits davon gibt es diverse Corner-Cases, weil es etwas Gebastel mit fopen und dergleichen ist. (Habe es derzeit noch so, dass die verknüpfte CSV-Datei nicht permanent offen ist und dass die Klasse versucht, fopen und fclose „schlau“ automatisch auszuführen.) Auch mit dem API bin ich noch nicht wirklich zufrieden, weil man etwa theoretisch das Array mit den Schlüsseln, das an fetchAssoc übergeben werden kann, zwischen zwei Aufrufen austauschen könnte. (Derzeit geht es nicht, weil ich die Schlüssel nach dem ersten Aufruf erst mal zwischenspeichere.) Ich bin mir noch nicht sicher, ob das grundsätzlich eher Bug oder Feature ist.

Na ja, letztlich vielleicht auch nicht so wichtig, weil es solche Reader sicher sowieso schon in besser gibt. ;)
 
Zuletzt bearbeitet:
Jungs, Ihr seid der absolute Hammer :D

Mermshaus, ich hab eine Weile gebraucht deinen Code zu verstehen und für meine Bedürfnisse anzupassen aber jetzt läuft alles bei mir!

Absolut genial von euch, nochmal vielen Dank, das ist 100000x mehr als ich erwartet habe und vielen Dank auch an euch, dass ich
so einiges lernen konnte!

Schöne Grüße
Daniel
 
Zurück