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;
?>
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>
Advertisement
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)
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)
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)
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)