473,729 Members | 2,102 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

does "LOAD DATA" EVER work?!? I've tried EVERYTHING!

nsh
mailing.databas e.mysql, comp.lang.php
subject: does "LOAD DATA" EVER work?!? I've tried EVERYTHING!

version info:
my isp is running my web page on a linux box with php ver. 4.4.1
according to phpinfo, the "mysql api client is ver. 4.0.25" - I have no
idea how this relates, if at all, to the mysql engine's version.
background:
I have tried literally everything I can think of, and I can NOT get
load data infile to work under php AT ALL!!!

I have scoured all the newsgroups, and found countless plea's for help
on this subject. NONE of them seem to adaquately answer all my
questions; most suggest trying stuff I've long since tried and failed.

I have actually read the freaking mysql manual, and from the
description, it SOUNDS like I SHOULD be able to transfer a csv file
from my window's pc, to the web server's mysql database, and import it
into a table in 1 single command. I do realize this is a security risk,
and therefore can't be done this way. Someone should re-write the
manual's description of the load data command. The manual refer's to
"client software" - but it never explains what the <blank> it is - so
therefore I assume it to be my web browser :) - afterall, that is the
client I'm using! (yes, there is a reference to mysql client software,
but I can't put that on my users' pc's, nor do I or should I [feel] I
should have to do this).

The csv file is exported from ms access. I manually ftp the file to the
web server.
Here's the command I've been trying hundreds of variations on:

--
LOAD DATA CONCURRENT LOCAL INFILE '/full_server_pat h/myfile.csv'
REPLACE INTO TABLE `intemp` FIELDS TERMINATED BY ',' OPTIONALLY
ENCLOSED BY '|' LINES TERMINATED BY '\n';
--

(Some fields use double quotes within the field, so I closed the fields
in the pipe symbol. My test file doesn't have any quotes, and I've
proven that the pipe works.)
The full_server_pat h is determined with php's "getcwd" function.
MY questions:
1) Is there SOME way that this can be done as I interpreted the mysql
manual? Is there a way to upload, and import a file from my pc to the
web server - WITHOUT the use of any special software? (I have users
that will update the database, and they will have nothing more than ms
access, and IE installed).

2) I'm ASSUMING the answer to #1 is No. So obviously I must get the
file to the server somehow, then load data. I've done exactly this in
my testing. after it's ftp'd, there's NO WAY I can get mysql to import
the darn file (thru php).
** - If I use phpmyadmin, and insert the above command into a sql
window & execute it - IT WORKS PERFECTLY!!
- If I use the EXACT same command in php (see sample), I GET
NOTHING! I get NO error messages, and I NEVER get a successful import.
SAMPLE:
__
$qry="LOAD DATA CONCURRENT LOCAL INFILE '/full_server_pat h/myfile.csv'
REPLACE ";
$qry.="INTO TABLE `intemp` FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED
BY '|' LINES TERMINATED BY '\n'";
$qry.="LINES TERMINATED BY '\\n';";
$rslt=mysql_que ry($qry,$db) or die(mysql_error ());
--

so I guess the question for #2 is - what am I doing wrong?

3) Considering I've spent COUNTLESS hours trying to solve this - on
MULTIPLE occasions;
I'm lead to ask, does "load data" REALLY even work, and why does it
seem so hard to find working examples?

oh - by the way - I've tried relative path's, absolute paths, using
"LOCAL", not using it; and dozens of other permutations.

I'm hoping someone can finally shed full light on the issues with LOAD
DATA...
TIA

Nov 22 '05 #1
3 2750
I wonder if you are being caught out by different sorts of single
quotes?
if you cut the code from phpMyadmin that it gives when exporting and
look _carefully_ are the quote characters identical. (I never knew
there were two single quote characters before.)

If I was you I'd write a line of data by hand 'as if' exported from
ACCESS and see if it can be LOADded. Then look at what an export of an
existing record looked like from mySQL/phpMyAdmin. In an ideal world
they should be identical. Then start knocking out the differences.


--
PETER FOX Not the same since the deckchair business folded
pe******@eminen t.demon.co.uk.n ot.this.bit.no. html
2 Tees Close, Witham, Essex.
Gravity beer in Essex <http://www.eminent.dem on.co.uk>
Nov 22 '05 #2
ns*@starnetwx.n et wrote:
LINES TERMINATED BY '\n'";
$qry.="LINES TERMINATED BY '\\n';";


This directive can appear only once. The last one with two backslashes is
correct (because the string is in double quotes).

--
E. Dronkert
Nov 22 '05 #3
<ns*@starnetwx. net> wrote in message
news:11******** **************@ g49g2000cwa.goo glegroups.com.. .
<snip>
does "LOAD DATA" EVER work?!? I've tried EVERYTHING!
Okay - Now calm down!
** - If I use phpmyadmin, and insert the above command into a sql
window & execute it - IT WORKS PERFECTLY!!


There's you - answering your own question.

It will work *every time* you throw a properly formed LOAD DATA command at
MySQL.
And obviously, you haven't tried everything everything after all!

To troubleshoot, I might suggest -

That you dump (and examine!) the exact contents of $qry as it exists just
before you pass it to mysql_query(). You might compare it *very carefully*
with the string that you know works when you pass it to mysqladmin.

Another thing that comes to mind -

Are you working with the same usr/pwd/permissions in mysqladmin that you are
working with in your PHP code. It's possible you have the FILE privelege
under mysqladmin that you lack from inside your PHP code.

Post the exact contents $qry here (not your PHP assignment code!) if
something above doesn't smack you in the face with the error of your ways
:-)

-Thomas Bartkus

Nov 22 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
4508
by: Otto | last post by:
Hello to all I have a problem how to write correctly a PHP string. I did the following import with the help of PHPMyAdmin: Nombre d'enregistrements insérés : 364 (traitement: 0.0553 sec.) requête SQL: LOAD DATA LOCAL INFILE '/tmp/phpVfO8r8' INTO TABLE `fncid`
1
2939
by: keefah | last post by:
Hi, I can't seem to get past this problem, though I've tried multiple examples off the web. I've registered integrated Crystal (VS.NET 2003 Pro). I've made sure the assemblies are all there. I've tried design-time ReportSource assignment and runtime. Why can't I get past this? Below is error page from most basic of tries (just a dumb (text only) report within a viewer on a webform. Thanks in advance, -Nick Description: An unhandled...
3
4695
by: 21novembre | last post by:
Hi all, I made a question several days before to describe my strange trouble of mysqldump. But I still can't figour it out. Well, I just want to ask another question whether I could just backup my databases by copying the data folder to some place? Then if I meet some disaster, could I just copy the backup folder back to recover my databases? Thank you. Zh.y
11
10165
by: bleedledeep | last post by:
I've been tracking down a memory leak using DevPartner 7.2 and what I am seeing is that calling XmlDocument.Load() leaks A LOT of memory. The following code is called when I click a button on my test form. (this is test code I wrote to confirm the bug, not my actual code) private void button1_Click(object sender, System.EventArgs e) { XmlDocument xd = new XmlDocument();
2
3295
by: Jeff_Mac | last post by:
Hi there. I'm a bit of a newbie, and I would appreciate any help that anyone can give me on an error I'm getting with Crystal Reports. Every time I attempt to view a report using the Crystal Report Viewer, I get a "Load Report Failed" error, and I'm not sure what I can do to resolve it. I know it's not the connection to the data -- I'm able to get a datagrid to display the data just fine after filling a dataset. I tried searching the...
2
1562
by: nsh | last post by:
mailing.database.mysql, comp.lang.php subject: does "LOAD DATA" EVER work?!? I've tried EVERYTHING! version info: my isp is running my web page on a linux box with php ver. 4.4.1 according to phpinfo, the "mysql api client is ver. 4.0.25" - I have no idea how this relates, if at all, to the mysql engine's version. background:
0
943
nidahali
by: nidahali | last post by:
Hi, I'm making a project on load balancing / load sharing among a single server and multiple clients. What my requirements are: Server Client CPU Usage
4
32551
by: vunet.us | last post by:
Hi all, I am converting my app to AJAX-based. I have a form that submits some data including images. When I use AJAX XmlHttpRequest I am unable to submit the form with ENCTYPE="multipart/form-data" (error 500). Without ENCTYPE="multipart/form-data", everything goes well. I think there is a header I must use in addition, but I cannot seem to find a good solution. Can you suggest? Thank you. Here is a part of xmlhttp request where I set...
3
3623
chunk1978
by: chunk1978 | last post by:
hi there... i'm having a new strange problem with my flash site... IE7 seems to not display my preloader but instead loads the movie with a white screen... the movie does load though... this is a new problem, as it never use to happen before... it use to show everything... does anyone know why this suddenly started to happen? i've cleared the cache... could this be an issue with Flash CS3? any help would be GREATLY appreciated, as i've been...
0
8764
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,...
0
9428
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
1
6722
isladogs
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...
0
6026
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();...
0
4534
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...
0
4798
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
3242
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
2
2694
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
2166
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.