uploading csv file into mysql through php

i know how to upload a csv file into mysql database but only through cmd. i want to know how to upload csv file into mysql database using php form and will disregard some information on the excel and will only start importing starting from a certain line. ? kindly help me.

-------------Problems Reply------------

(PHP 4, PHP 5)

fgetcsv

See php manual http://php.net/manual/en/function.fgetcsv.php

<?php
$row = 1;
if (($handle = fopen("test.csv", "r")) !== FALSE) {
while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
$num = count($data);
echo "<p> $num fields in line $row: <br /></p>\n";
$row++;
for ($c=0; $c < $num; $c++) {
echo $data[$c] . "<br />\n";
}
}
fclose($handle);
}
?>

Try this it's working well, you can add as many values as possible depending on the number of columns you have in the CSV file. Then in the HTML code put the uploading syntax in the tag.

**

$fname = $_FILES['csv_file']['name'];
$chk_ext = explode(".",$fname);

$filename = $_FILES['csv_file']['tmp_ name'];
$handle = fopen($filename, "r");
if(!$handle){
die ('Cannot open file for reading');
}
while (($data = fgetcsv($handle, 10000, ",")) !== FALSE)
{
$query = "INSERT INTO tablename (col1_csv, col2_csv)
values ('$data[0]', '$data[1]');
mysql_query($query) or die(mysql_error ());
}
fclose($handle);
?>

**

You can use the MySQL LOAD DATA INFILE statement to bulk-insert thousands of records at once. PHP can handle the file upload. The PHP code would be something similar to:

$query = sprintf("
LOAD DATA LOCAL INFILE '%s'
INTO TABLE `table1`
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"'
LINES TERMINATED BY '\\r\\n'
IGNORE 1 LINES
",
mysql_real_escape_string($FILES["file1"]["tmp_name"])
);

The LOCAL keyword should allow you to workaround some security restrictions. Change the FIELDS TERMINATED BY and LINES TERMINATED BY parameter to match the separators used by excel while exporting. IGNORE 1 LINES tells MySQL to skip the header row(s).

Note: Excel does not seem to use an escape character; but it will (i) enclose the fields that contain , and " with " (ii) use "" to escape a single " inside data. I believe MySQL will understand this encoding and import the data correctly.

try this:

$filename=$_FILES["upload_file"]["name"];
$extension = end(explode(".",$filename));
if ($extension=='csv') {
$tmp_file=$_FILES["upload_file"]["tmp_name"];
$handle = @fopen($tmp_file, "r");
//specify your own database connection parameter
$db = new PDO('mysql:host=localhost;dbname=demo','user','password');
$stmt = $db->prepare("INSERT INTO writers (writer_name, writer_email) VALUES (?, ?)");
if ($handle) {
while (($buffer = fgets($handle, 4096)) !== false) {
$array=explode(",",$buffer);
$count=1;
foreach ($array as $value) {
$stmt->bindParam($count, $value);
$count++;
}
$stmt->execute();
}
if (!feof($handle)) {
echo "Error: unexpected fgets() fail\n";
}
fclose($handle);
}
$db = null;
echo "<p>Success</p>";
}
else {
$error="<p style='color:red;'>Invalid file type</p>";
}

Refer to http://pradipchitrakar.com.np/programming/upload-csv-mysql-php/

You could use the "LOAD DATA INFILE " statement with the " IGNORE ... LINES " option which you can use from the command line as well as from PHP.

Category:php Views:2 Time:2012-03-14
Tags: php mysql csv

Related post

  • perl, mysql - fasting way to upload a csv file into mysql? 2012-03-02

    what is the fastest method to upload a csv file into mysql via perl, mysql? The csv file is probably about 20Gs. thanks --------------Solutions------------- consider using mysql LOAD DATA INFILE http://dev.mysql.com/doc/refman/5.0/en/load-data.html Y

  • How to check before upload a CSV file to MySQL db if user has selected one 2013-12-22

    I have simple script that uploads CSV files to a MySQL database. It works ok but if user misses selecting some CSV file to upload and pushes the UPLOAD button, the script tries to insert thousand of 0 lines in the data base. I wonder if there is a wa

  • How to import CSV file to MySQL table 2010-09-03

    I have an unnormalized events-diary CSV from a client that I'm trying to load into a MySQL table so that I can refactor into a sane format. I created a table called 'CSVImport' that has one field for every column of the CSV file. The CSV contains 99

  • Java Desktop Application to Upload CSV Files to Database 2011-10-11

    I'm working on desktop application (Extract Transform Load type of app) that requires uploading of large CSV files (about 6 GB) to a database using Java as front end and Oracle as a backend but I'm open to other databases and tools as well (i.e. SQLi

  • How to import data from CSV file into MySQL database 2011-12-01

    I have to import data from a CSV file into MySQL database using JSP/Servlet. While searching I got this link! where it is said that we can use MySQL command "LOAD DATA INFILE ..." in hibernates createSQLQuery. But in my case the table in which I need

  • I want to upload .csv file To servlet and parse it, but dont know how to open uploaded file 2009-03-19

    I have a form that allows user to select a .csv file and post it to a servlet. However I am having dificullty getting a handle on the file from the Java Servet on the server any ideas? --------------Solutions------------- I recommend using Apache Com

  • How to import Data from CSV file to MySQL database without using LOAD DATA INFILE Syntax? 2011-04-19

    How to import Data from CSV file to MySQL database without using LOAD DATA INFILE Syntax? --------------Solutions------------- Most pedestrian way: open the .csv as text file, parse/split each line into a list of values, feed them to a prepared comma

  • Creating CSV files via MySQL - Any limits imposed? 2011-05-07

    I am going to be creating some CSV files via MySQL with code like the below for example.. SELECT id, name, email INTO OUTFILE '/tmp/result.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY ‘\\’ LINES TERMINATED BY '\n' FROM users WH

  • HOW-TO Import the MaxMind GeoIP Free Country CSV file into MySQL and save diskspace 2011-08-10

    HOW-TO Import the MaxMind GeoIP Free Country CSV file into MySQL and save diskspace, anyone have SQL file? --------------Solutions------------- You can use Navicat. It is a GUI That allows you to easily import CSV files; look it up on google. Otherwi

  • mysql exception while importing csv file to mysql database 2011-08-31

    i would like to import csv file in mysql database. my query would be like the following. query = "LOAD DATA INFILE '"+filename+"' INTO TABLE testtable FIELDS TERMINATED BY ',' (text,price)"; But i got the following error while importing file. java.sq

  • How to query CSV file from mysql? 2011-09-06

    How to query csv file using mysql? i have a .csv file and i want to query it so that i could get distinct record value from csv file. Thanks Yugal --------------Solutions------------- You can't. You need to import it into mySQL. mySQL's command for t

  • what is best way to return data to javascript from uploaded CSV file? 2011-12-08

    the scenario needs to run like this: client uploads CSV file which then dynamically populates a table with the data from the uploaded CSV file. i can upload the CSV file with a simple form. no problem. i can parse the data with PHP on the server side

  • how to import data from csv file to mysql table? 2012-03-08

    I'm trying to load data from csv file to mysql table(already created). But loading failed. I tried directly without using query too "it shows error like 23 rows skipped". I used the query: LOAD DATA INFILE 'C:\\Users\UserName\Documents\FILE.CSV' INTO

  • Uploaded CSV file of contact only uploaded last name 2013-06-22

    I am trying to upload a csv file from constant contact into my contacts in outlook.com people...but only last name is uploaded. Can anyone help? --------------Solutions------------- Hi, I'm happy to assist you with your concern regarding uploading CS

  • Upload 95 GB CSV file into MySQL MyISAM table via Load data infile: CSV engine an alternative? 2009-11-20

    I'm trying to upload a 95 GB CSV file into a MySQL database (MySQL 5.1.36) via the following command: CREATE TABLE MOD13Q1 ( rid INT UNSIGNED NOT NULL AUTO_INCREMENT, gid MEDIUMINT(6) UNSIGNED NOT NULL , yr SMALLINT(4) UNSIGNED NOT NULL , dyyr SMALLI

  • Uploading Large CSV File into Mysql Database 2011-08-02

    I want to upload large CSV document into mysql database can anyone help me out, the table consist of 10 fields but i want to upload into only 5 fields of the table without a heading. I want this done with php in the browser $filename = $_FILES['sel_f

  • Parsing a user uploaded CSV file from ASP.NET into MySQL DB. Security Issues 2012-04-10

    I am writing an ASP.NET web site where users will be able to upload a .CSV file from which i want to extract the data into a table on a MySQL Database. I was wondering what would be the safest way to do this by protecting against SQL injection or oth

  • Upload CSV file into to 2 mySQL table using php 2015-01-26

    I have CSV file want to upload it to upload it to MySQL. My CSV file has structure the first line include refernace information like name,date and time. starting form second line there is information. i want to insert the first line to table named "f

  • How to check duplicate and upload the new data when I use PHP to upload the CSV file into MySQL database 2014-11-24

    I have a CSV file which have ten column: ID, fromDate, endDate, address, price1, price2, column1, column2, column3, column4 but I just want to insert the first six columns into the MySQL database, and most important, if there exist a tuple in the dat

Copyright (C) dskims.com, All Rights Reserved.

processed in 0.152 (s). 11 q(s)