Closed. This question needs details or clarity. It is not currently accepting answers. Want to improve this question? Add details and clarify the problem by editing this post. Closed last month. Improve this question i need to import a csv file to a mysql database, the problem is: The csv file cointains too many days horizontally, that means that i

Import csv file to MariaDB without knowing the exact number of columns [closed]

i need to import a csv file to a mysql database, the problem is: The csv file cointains too many days horizontally, that means that i need to create fields in my DB according to the fields that exist in the excel file.

I found this script and was trying to adpat it to my case but i keep getting the following error:

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ‘;Turno;10/06/2022;11/06/2022;12/06/2022;

I will put an image so that you have a clue off what im doingenter image description here

Import.php
<?php
require_once('config.php');
$row = 1;
if(isset($_POST["Import"])=='true'){
    $table='teste';
    $file=$_FILES["file"]["tmp_name"];
    $handle = fopen($file, "r");

    $row = 1; 
    $columns = [];
    
    while (($data = fgetcsv($handle, 1000, ",")) !== FALSE AND $row==1) {
       $columns = $data;
       $row++; 
    }
}
    //SQL string commands
    $createSQL = "CREATE TABLE IF NOT EXISTS $table 
                  (".implode(" VARCHAR(255) NOT NULL, ", $columns). " 
                   VARCHAR(255) NOT NULL);";
    
    $file = addslashes(realpath(dirname(__FILE__)).'\'.$file);
    $loadSQL = "LOAD DATA INFILE '$file' 
                INTO TABLE $table 
                FIELDS TERMINATED BY ';' 
                IGNORE 1 LINES";
    
    // Open database connection 
    try { 
       $dbh = new PDO("mysql:host=$host;dbname=$database",$username,$password); 
       $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    
       // Execute queries
       $S1TH = $dbh->query($createSQL); 
       $S2TH = $dbh->query($loadSQL);   
    } 
    
    catch(PDOException $e) { 
       echo $e->getMessage(); 
    }
    
    # Close database connection 
    $dbh = null;
    
    
?>

JavaScript
​x
 
Import.php<?phprequire_once('config.php');$row = 1;if(isset($_POST["Import"])=='true'){    $table='teste';    $file=$_FILES["file"]["tmp_name"];    $handle = fopen($file, "r");​    $row = 1;     $columns = [];        while (($data = fgetcsv($handle, 1000, ",")) !== FALSE AND $row==1) {       $columns = $data;       $row++;     }}    //SQL string commands    $createSQL = "CREATE TABLE IF NOT EXISTS $table                   (".implode(" VARCHAR(255) NOT NULL, ", $columns). "                    VARCHAR(255) NOT NULL);";        $file = addslashes(realpath(dirname(__FILE__)).'\'.$file);    $loadSQL = "LOAD DATA INFILE '$file'                 INTO TABLE $table                 FIELDS TERMINATED BY ';'                 IGNORE 1 LINES";        // Open database connection     try {        $dbh = new PDO("mysql:host=$host;dbname=$database",$username,$password);        $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);           // Execute queries       $S1TH = $dbh->query($createSQL);        $S2TH = $dbh->query($loadSQL);       }         catch(PDOException $e) {        echo $e->getMessage();     }        # Close database connection     $dbh = null;        ?>​

index.php

<!DOCTYPE html>
<html lang="en">
<head>
    <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" crossorigin="anonymous">
    <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap-theme.min.css" crossorigin="anonymous">
    <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js" crossorigin="anonymous"></script>
</head>
<body>
    <div id="wrap">
        <div class="container">
            <div class="row">
                <form class="form-horizontal" action="import.php" method="post" name="upload_excel" enctype="multipart/form-data">
                    <fieldset>
                        <!-- Form Name -->
                        <legend>Form Name</legend>
                        <!-- File Button -->
                        <div class="form-group">
                            <label class="col-md-4 control-label" for="filebutton">Select File</label>
                            <div class="col-md-4">
                                <input type="file" name="file" id="file" class="input-large">
                            </div>
                        </div>
                        <!-- Button -->
                        <div class="form-group">
                            <label class="col-md-4 control-label" for="singlebutton">Import data</label>
                            <div class="col-md-4">
                                <button type="submit" id="submit" name="Import" class="btn btn-primary button-loading" data-loading-text="Loading...">Import</button>
                            </div>
                        </div>
                    </fieldset>
                </form>
            </div>
         
        </div>
    </div>
</body>
</html>

JavaScript
 
index.php​<!DOCTYPE html><html lang="en"><head>    <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" crossorigin="anonymous">    <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap-theme.min.css" crossorigin="anonymous">    <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js" crossorigin="anonymous"></script></head><body>    <div id="wrap">        <div class="container">            <div class="row">                <form class="form-horizontal" action="import.php" method="post" name="upload_excel" enctype="multipart/form-data">                    <fieldset>                        <!-- Form Name -->                        <legend>Form Name</legend>                        <!-- File Button -->                        <div class="form-group">                            <label class="col-md-4 control-label" for="filebutton">Select File</label>                            <div class="col-md-4">                                <input type="file" name="file" id="file" class="input-large">                            </div>                        </div>                        <!-- Button -->                        <div class="form-group">                            <label class="col-md-4 control-label" for="singlebutton">Import data</label>                            <div class="col-md-4">                                <button type="submit" id="submit" name="Import" class="btn btn-primary button-loading" data-loading-text="Loading...">Import</button>                            </div>                        </div>                    </fieldset>                </form>            </div>                 </div>    </div></body></html>​

Answer

Assume you have a well formatted csv. Mainly first line should indicate each column content and also assume unique tags.

name, surname -> tags
RName1,RSurname1 -> contents, count as Line 1
RName2,RSurname2 

//if tags is missing then you can add some defaults as
//c1,c2 ... cn (n-> deductible after parsing)

JavaScript
 
name, surname -> tagsRName1,RSurname1 -> contents, count as Line 1RName2,RSurname2 ​//if tags is missing then you can add some defaults as//c1,c2 ... cn (n-> deductible after parsing)​

DB design can be:

id: Integer (autonumber, PK)
lineKey : String, not null
lineValue : String
fileName : String (assume unique), not null
lineNo : Integer, not null (on LineNo from file) 

//lineNo -> can be renamed as nextColumn 
//increment counter on added column, each time +1 start from 1.

key(lineKey, fileName, lineNo)

JavaScript
 
id: Integer (autonumber, PK)lineKey : String, not nulllineValue : StringfileName : String (assume unique), not nulllineNo : Integer, not null (on LineNo from file) ​//lineNo -> can be renamed as nextColumn //increment counter on added column, each time +1 start from 1.​key(lineKey, fileName, lineNo)​

Data can be added as:

(name,Rname1,fileName1,1)
(surname,Rsurname1,fileName1,2) 
...
on other import maybe

name , surname, dob
Rname, null  , 2000-01-01

so can be added as
(name, Rname, fileName2,1) -> another file so counter start from 1
(surname, null, fileName2,2)

JavaScript
 
(name,Rname1,fileName1,1)(surname,Rsurname1,fileName1,2) ...on other import maybe​name , surname, dobRname, null  , 2000-01-01​so can be added as(name, Rname, fileName2,1) -> another file so counter start from 1(surname, null, fileName2,2)​

One of main advantage is that storage is independent of file-contents (irrelevant how many columns). Also it’s not required each time a file is parsed to create a new table.

Notice: Db design is the key !

Imagine 100 distinct files as column numbers so need to create 100 additional tables ?!
Or maybe one table with more then 100 columns for who knows what a files can contain ?!
(hard to read and inefficient as storage -> affect query performance. Many columns can be null)

Advertisement

Answer