Inserting CSV data into SQL

import data from csv to sql server using query
import csv to existing sql table
sql import csv into temp table
import csv file into sql server management studio
import csv file into sql server using stored procedure
sql query to insert a file into table
insert data from text file to sql table
automated csv import to sql server

I want to insert data from csv file, in to my sql table excel, the code is working, but it's not working properly.

EXAMPLE:

Data In Excel file

kier

kier@Noemail@gmail.com

output in table excel

excel_name: kier;kier@Noemail@gmail.com

excel_email: blank

 <?php
 $connect = mysqli_connect('localhost','root','P@ssw0rd@sql','voters');

if (isset($_POST['submit'])) {
  if ($_FILES['file']['name']) {
    $filename = explode(".",$_FILES['file']['name']);
    if ($filename[1] == 'csv') {
      $handle = fopen($_FILES['file']['tmp_name'], "r");
      while ($data= fgetcsv($handle)) {
        $item1 = mysqli_real_escape_string($connect, $data[0]);
        $item2 = mysqli_real_escape_string($connect, $data[1]);
        $sql = "INSERT INTO excel (excel_name, excel_email) VALUES ('$item1','$item2')";
        mysqli_query($connect, $sql);
      }
      fclose($handle);

      print "Rois x Dave!";
    }
  }
}

?>
<!DOCTYPE html>
<html>
<head>
  <title></title>
</head>
<body>
  <form method="POST" enctype="multipart/form-data">
    <div align="center">
      <p>upload<input type="file" name="file"></p>
      <p><input type="submit" name="submit" value="import"></p>
    </div>
  </form>
</body>
</html>

The problem is the excel_name and excel_email is concatenating in one column which is excel_name, please help me out of this problem. Tyia!

read the document: http://php.net/manual/en/function.fgetcsv.php

array fgetcsv ( resource $handle [, int $length = 0 [, string $delimiter = "," [, string $enclosure = '"' [, string $escape = "\\" ]]]] )

modify

$data= fgetcsv($handle)

to

$data = fgetcsv($handle, $length = 0, $delimiter = ';')

the delemeter of csv file you uploaded is ; not ,

Importing and Working with CSV Files in SQL Server, Based SQL Server CSV Import. 1) The CSV file data may have , (comma) in between (Ex: description), so how can I make import handling  In SQL Server, you can simply import and convert CSV files into data tables with a few mouse clicks using SSMS (SQL Server Management Studio). In this article, we will see how we can import CSV data into SQL server using SSMS, convert it into data tables, and execute SQL queries on the tables that we generate. Importing a CSV file

According to value kier;kier@Noemail@gmail.com your delimiter is ;. Default delimiter for fgetcsv is ,. So, you should pass ; as a delimiter argument:

while ($data = fgetcsv($handle, 4096, ';')) {

Also, it is always useful to debug your variables to see what value they have:

var_dump($item1, $item2);

Import CSV file into SQL Server, Import Flat File Wizard is a simple way to copy data from a flat file (.csv, .txt) to a new table in your database. This overview describes the reasons  After the table is created, follow the steps below. • Log into your database using SQL Server Management Studio. • Right click on your database and select Tasks -> Import Data • Click the Next > button. • For the Data Source, select Flat File Source. Then use the Browse button to select the CSV file.

Looks like the $data[0] contains your information that you need. I would just use explode again and separate the data before you move it to your DB.

Try changing your while loop to this:

while ($data= fgetcsv($handle)) {

  $items = explode(';', $data[0]);
  $sql = "INSERT INTO excel (excel_name, excel_email) VALUES ('$items[0]','$items[1]')";
  mysqli_query($connect, $sql);

}

Import CSV file to SQL Server Database, Transact-SQL reference for the BULK INSERT statement. table_name Is the name of the table or view to bulk import data into. Only views in Orders FROM '\​\SystemX\DiskZ\Sales\data\orders.csv' WITH ( FORMAT='CSV');. Use MorganDB GO Create Table StudentsData (UserName VARCHAR (250), City VARCHAR (250), MailID VARCHAR (250),); The data we are going to load into SQL Server using Bulk Insert is stored in the CSV File – UserData.CSV. The below image shows sample CSV content. Now, to read CSV file, you can use following SQL Script with Bulk Insert command

Import Flat File to SQL, There are ways in SQL Server to import data from CSV formatted file to the database. The approaches getting the data into the database are  Here is the code I am executing. cur=cnxn.cursor() # Get the cursorcsv_data = csv.reader(file(Samplefile.csv')) # Read the csvfor rows in csv_data: # Iterate through csv cur.execute("INSERT INTO MyTable(Col1,Col2,Col3,Col4) VALUES (?,?,?,?)",rows)cnxn.commit()

BULK INSERT (Transact-SQL), When you want to import the data from a .csv file. When you add rows to your new SQL table that are the results of another SQL query. Note: This  In Oracle SQL developer you can simply go to the list of tables on the left which should be under connections. Then Double click the table > actions > import data. You can then use the import wizard with your desired settings. I'm not sure if the feature was present in Oracle SQL developer when you asked this question, but it is available now.

How to Import a CSV File into Your Database with SQL Server , A CSV file with data that matches with the number of columns of the table and “​Apply SQL Script to Database”, click Apply button to insert data into the table. Click Import button, choose a CSV file and click Open button Review the data, click Apply button. MySQL workbench will display a dialog “Apply SQL Script to Database”, click Apply button to insert data into the table. We have shown you how to import CSV into MySQL table using LOAD DATA LOCAL and using MySQL Workbench.

Comments
  • thankyou so much! @ryan!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! you saved my life, you're truly grateful! <3
  • There's no , in $data[0].
  • I'm getting old, I didn't see the ;. Answer updated.