Can anyone find me a solution.
I want to import csv file into mysql database. I have the code to insert.But the rows that contains comma in data field is not getting inserted and is skipped.All the other rows are inserted.
This is the code that i have..
[php]@mysql_close($c on);
$con = mysql_connect(" $hostname","$us er","$pass") or die("did'nt connect to mysql");
mysql_select_db ("test") or die ("no database");
$databasetable = "$file3";
$lines = 0;
$queries = "";
$linearray = array();
foreach(split($ lineseparator,$ csvcontent) as $line) {
$count = $count + 1;
$lines++;
$line = trim($line," \t");
$expr="/,(?=(?:[^\"]*\"[^\"]*\")*(?![^\"]*\"))/";
$results=preg_s plit($expr,trim ($line));
$results1= preg_replace("/^\"(.*)\"$/","$1",$results );
$linearray = explode($fields eparator,$resul ts1);
$linemysql = implode("','",$ linearray);
if($addauto)
{
$query = "insert into $databasetable VALUES('$count' ,'$linemysql'); ";
$result1 = mysql_query($qu ery) or die("didn't query for insert");
}
else
$query = "insert into $databasetable values('$linemy sql');";
$queries .= $query . "\n";
@mysql_query($q uery);
}
echo " CSV File imported to the database";
@mysql_close($c on);
[/php]
It is inserting all the rows except the row that has comma in it.I couldn rectify it.Can anyone help me out please.. thank u.
3 2689
Welcome to The Scripts!
Please enclose your posted code in [code] tags (See How to Ask a Question).
This makes it easier for our Experts to read and understand it. Failing to do so creates extra work for the moderators, thus wasting resources, otherwise available to answer the members' questions.
Please use [code] tags in future.
MODERATOR
Your code if very difficult to follow, without any comments. And whatever happened to the good old (standard PHP) fgetcsv() function?
To get some insight into the code, what I miss is what is the content of the reg.ex in the splits, i.e. $lineseparator and $fieldseparator .
Since the record is not inserted into the db, it would be handy to know how the sql statement looks like, so, for debug reasons, add an echo to each of the SQL statements before you execute it.
(The last mysql_query is not even tested for errors, so how do you know what happens?)
Ronald
This is the full coding -
$path = $fileloc;
-
$file = basename($path); // $file is set to "index.php"
-
-
$databasehost = "localhost";
-
$databasename = "test";
-
-
$databaseusername ="test";
-
$databasepassword = "";
-
$fieldseparator = ",";
-
$lineseparator = "\n";
-
$csvfile = $fileloc;
-
-
-
$addauto =1;
-
-
if(!file_exists($csvfile)) {
-
echo "File not found. Make sure you specified the correct path.\n";
-
exit;
-
}
-
-
-
$file = fopen($csvfile,"r");
-
-
if(!$file) {
-
echo "Error opening data file.\n";
-
exit;
-
}
-
-
$size = filesize($csvfile);
-
-
if(!$size) {
-
echo "File is empty.\n";
-
exit;
-
}
-
-
$csvcontent = fread($file,$size);
-
-
fclose($file);
-
-
$con = mysql_connect("$hostname","$user","$pass") or die("did'nt connect to mysql");
-
mysql_select_db("test") or die ("no database");
-
$result = mysql_query("SELECT * FROM ex order by count");
-
$count=0;
-
while($row = mysql_fetch_array($result))
-
{
-
$count= $row['count'];
-
}
-
if($count==0)
-
$count=0;
-
else
-
$count=$count;
-
-
-
@mysql_close($con);
-
$con = mysql_connect("$hostname","$user","$pass") or die("did'nt connect to mysql");
-
mysql_select_db("test") or die ("no database");
-
-
$databasetable = "$file3";
-
$lines = 0;
-
$queries = "";
-
$linearray = array();
-
-
foreach(split($lineseparator,$csvcontent) as $line) {
-
$count = $count + 1;
-
-
$lines++;
-
-
$line = trim($line," \t");
-
-
$expr="/,(?=(?:[^\"]*\"[^\"]*\")*(?![^\"]*\"))/";
-
-
$results=preg_split($expr,trim($line));
-
-
$results1= preg_replace("/^\"(.*)\"$/","$1",$results);
-
-
$linearray = explode($fieldseparator,$results1);
-
-
$linemysql = implode("','",$linearray);
-
-
if($addauto)
-
{
-
$query = "insert into $databasetable VALUES('$count','$linemysql');";
-
$result1 = mysql_query($query) or die("didn't query for insert");
-
}
-
else
-
$query = "insert into $databasetable values('$linemysql');";
-
$queries .= $query . "\n";
-
@mysql_query($query);
-
}
-
@mysql_close($con);
-
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: David Berry |
last post by:
Hi All. I'm looking for any help or sample code that can show me how to make a file import wizard in ASP.NET (VB preferred) like the one that MS Access uses. I'm working on a web site where the user has the ability to upload a file (.txt or .csv). The data in the file may be comma delaminated, tab delaminated, fixed width etc (we don't know).
What I'd like to create is something like MS Access uses to import an Excel file into the...
|
by: DCM Fan |
last post by:
Access 2K, SP3 on Windows 2K, SP4
All,
I have an import spec set up with quoted Identifiers and comma-separated
values. The text file is produced by a 3rd-party program of which I have no
control. It outputs all text fields surrounded by quotes, and all numeric
fields w/o quotes. All fields are separated with commas.
This has been working for 2 years, until today, when one of the data fields
|
by: Steemer |
last post by:
Okay, I checked with usual suspects and I'm still frustrated.
..txt file, fixed width. About 88 fields of varying length. All text
fields. It will only let me add seperators to about the 410th position but
there are 420 positions.
What's going on?
|
by: orange |
last post by:
I've got a database in .txt file similar to this:
BookName;Author;Year;ReviewedBy;Rating;Pages
Nemesis;Isaac Asimov;1989;13,31,24;good;110
Sense & Sensibility;Jane Austen;1970;45,32;great;120
The Bicentennial Man;Isaac Asimov;1965;14;excellent;124
...
the data in field 'ReviewedBy' should be connected to names of people
that read the book. I'd like to have a separate table in Access like
|
by: holdemfoldem |
last post by:
Hi. I'm new to this board and have a few questions about using the
method referred to in the topic of this message.
I have manually transferred over 1/2 million records from a text file
into my Access table in under 1 minute, using File>Get External
Data>Import, then specifying .txt as the data source, selecting
the text file, and walking through the resulting Wizard.
I'm thinking I can do the same thing with DoCmd.TransferText, but...
| |
by: ninrulz |
last post by:
I will try to explain my situation. I know that it is hard to offers
solutions without fully understanding what people would like to
achieve.
I receive 2 csv files every month. The csv files change name each
month. Each file contains 13 columns and around 20k rows. In each file,
the first row is the heading.
I have created a database that helps me sort out the data. Prior to
importing into the database, I have been copying the csv...
|
by: Someone |
last post by:
Hello
Using the c file io functions if reading a csv file there is a problem if a
field contains an embedded carriage return. So I can check for apostrophe
pairs but processing does seem a bit of a struggle. Has anyone got a robust
algorithm or functions to use to reliably get a whole row of text from a csv
file even if some fields contain embedded carriage returns?
Angus
|
by: cabrenner |
last post by:
I am new to SQL Server, and migrating part of an Access application to
SSE. I am trying to insert a comma delimited file into SSE 2005. I am
able to run a BULK INSERT statement on a simple file, specifying the
field (,) and row (\n) terminators. I can also do the same with a
format file.
Here is the problem. My csv file has 185 columns, with a mixture of
datatypes. Sometimes, a text field will contain the field delimiter as
part of...
|
by: GarryJones |
last post by:
(I am sorry if this is the wrong group for this posting, I cant find a
group on usenet for phpmyadmin, but maybe someone would be nice enough
to answer me anyway....)
I need to import data into a simple table The table has three fields.
The first is an auto increment value, the second the current time and
date (the 14 character version), and a field with some data.
This works okay in SQL mode
INSERT INTO all_epst VALUES...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
| |
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |