Hi,
I'm having a problem when using Load Data where the single and double quotes
in the text file are getting nuked. It appears that the escape character is
not being respected but I'm not sure why. It is also knocking out
characters near the quotes.
Your single and double quotes come out
Hi, Harpreet:
You should use MySQL C API mysql_escape_string(...) to convert that
description field into a legal SQL string before load data infile.
B.R.
budingc
- Original Message -
From: Harpreet Kaur [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Wednesday, March 27, 2002 4:41
At 20:41 + 3/26/02, Harpreet Kaur wrote:
I want to transfer data from a table in sql server to mysql. I use
C:\ BCP dbname.dbo.tblname out tblname.txt -S srvrname -U sa P sapwd -c
-t \t -r \n
to write the data to a text file and then use
load data infile /var/www/html/tblname.txt' into
If the table has a unique key then add the REPLACE keyword.
This will update the columns with new values if the key already exists
and add the row if it doesnt.
--Mark
Michael Kaiser wrote:
Using the following inserts data from a text file into a particular MySQL
table:
LOAD DATA LOCAL
Hi!
Thomas == Thomas Birchmire [EMAIL PROTECTED] writes:
Thomas I tried putting --local-infile in my server script and local-infile in the
Thomas /etc/my.cnf file. I also placed a number or logging cammands in the startup
Thomas script hoping to catch lower level messages. There appears to
16:33
To: Richard Bolen; MySQL Mailing List (E-mail)
Subject: Re: LOAD DATA INFILE and how to ignore garbage lines at end
ofload file?
At 10:58 -0500 3/4/02, Richard Bolen wrote:
I'm exporting data from Oracle and importing it into MySQL. The
problem is Oracle puts garbage lines at the end
Thomas Birchmire writes:
I have the same problem using Linux RedHat 7.2. Just what do I have to
configure my binary MySQL : Ver 8.23 Distrib 3.23.49a, for pc-linux-gnu on
i686?
The rest of MySQL works as advertised.
Regards Tom Birchmire
loca-infile is not a variable.
I tried putting --local-infile in my server script and local-infile in the
/etc/my.cnf file. I also placed a number or logging cammands in the startup
script hoping to catch lower level messages. There appears to be little
effect on anything.
Regards, Tom Birchmire
-- script and log stuff
Thomas Birchmire writes:
I tried putting --local-infile in my server script and local-infile in the
/etc/my.cnf file. I also placed a number or logging cammands in the startup
script hoping to catch lower level messages. There appears to be little
effect on anything.
Regards, Tom Birchmire
At 10:58 -0500 3/4/02, Richard Bolen wrote:
I'm exporting data from Oracle and importing it into MySQL. The
problem is Oracle puts garbage lines at the end of it's output files.
As you've noted, the problem is Oracle.
If you're using Unix, you could use tail to see how many of these lines
: Re: LOAD DATA INFILE and how to ignore garbage lines at end
ofload file?
At 10:58 -0500 3/4/02, Richard Bolen wrote:
I'm exporting data from Oracle and importing it into MySQL. The
problem is Oracle puts garbage lines at the end of it's output files.
As you've noted, the problem is Oracle
Hi!
Sinisa == Sinisa Milivojevic [EMAIL PROTECTED] writes:
Sinisa [EMAIL PROTECTED] writes:
Description:
LOAD DATA LOCAL INFILE ...
leads to 'The used command is not allowed with this MySQL version'
This happens regardless of whether I have a
local-infile = 1
in my /etc/my.cnf.
Is this a true export? Because I've never had garbage lines in my files.
Dave
On Mon, Mar 04, 2002 at 10:33:14AM -0600, Paul DuBois wrote:
At 10:58 -0500 3/4/02, Richard Bolen wrote:
I'm exporting data from Oracle and importing it into MySQL. The
problem is Oracle puts garbage lines at the
I have the same problem using Linux RedHat 7.2. Just what do I have to
configure my binary MySQL : Ver 8.23 Distrib 3.23.49a, for pc-linux-gnu on
i686?
The rest of MySQL works as advertised.
Regards Tom Birchmire
Michael Widenius [EMAIL PROTECTED] wrote:
Hi!
Sinisa == Sinisa
Rob Steele writes:
Thanks. I don't think this feature is working as the documentation says
it should. Section 4.2.4 of the manual says:
By default all MySQL clients and libraries are compiled with |
--enable-local-infile|, to be compatible with MySQL 3.23.48 and before.
That
sorry for my fault: I forgot $ (dollar sign) before sql variable.
Sommai
At 09:58 28/2/2002 +0700, Sommai Fongnamthip wrote:
Hi,
Please tell me why this mysql in php error:
sql = LOAD DATA LOCAL INFILE \/home/httpds/htdocs/srg/data/tct.csv\
INTO TABLE tct_temp FIELDS TERMINATED BY
At 12:43 -0500 2/26/02, Philip Mak wrote:
I made an Excel spreadsheet containing grades of students in my class,
like this:
(name) (email) (assignment 1 grade)(assignment 2 grade)
For when a student did not submit an assignment, I just left the field
blank.
I'm trying to import the
On Tue, Feb 26, 2002 at 12:03:25PM -0600, Paul DuBois wrote:
it set the integer columns to 0 if they were blank in the text file.
How can I make it set those to NULL instead?
You'll have to preprocess the file to convert empty fields to \N.
Damn, that's what I thought. I guess I'll have to
At 13:08 -0500 2/26/02, Philip Mak wrote:
On Tue, Feb 26, 2002 at 12:03:25PM -0600, Paul DuBois wrote:
it set the integer columns to 0 if they were blank in the text file.
How can I make it set those to NULL instead?
You'll have to preprocess the file to convert empty fields to \N.
Damn,
This worked for me:
create table tblZips(
id int unsigned not null auto_increment primary key,
city char(24) not null default '',
state char(2) not null default '',
zip char(5) not null default ''
) Type=InnoDB;
Saved CSV file as a UNIX (instad of DOS format file)
LOAD DATA
But you could already read any file that the web server can read, if you can
execute scripts which are run as the webserver. Only way to prevent that is
suEXEC. How is this specific to MySQL?
1) Webserver must be able to read HTML files of every WebUsers
2) MySQL functions are called with
-
From: Bogdan Stancescu [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: 25 January 2002 00:22
Subject: Re: LOAD DATA INFILE - Duplicate entry '' for key...
Just for the record, found the glitch: somehow, somebody on the way decided
to change LF's into CRLF's (I remotely dumped the data and e
Just for the record, found the glitch: somehow, somebody on the way decided
to change LF's into CRLF's (I remotely dumped the data and e-mailed it
locally where I test the setup process, so maybe the mail agent?). I just had
to replace 0x0d0a's into 0x0a's in a hex editor and everything works
Sorry, my fault, realised the file I was writing for VB was cocking it up.
I was adding chr(13) at the end, but the FileSystemObject write method
seemed to be putting the carriage return in too, so MYSQL didn't get the
newline character.
I changed to use the Print command in VB instead just
Yes the data is consistent firstname, lastname, address, city... The
data is sent to me in eight different files every day. I want to import
the data into a mysql database and set a field that is not included in
the txt file. The files are sent to me already organized by region and
would like
Hi,
Not quite sure what you mean, but you can use
load data infile 'file' into table mytab (col1, col3, col2)
to specify in to which columns the data should go.
HTH
Quentin
-Original Message-
From: Douglas Potter [mailto:[EMAIL PROTECTED]]
Sent: Friday, 4 January 2002 12:27 p.m.
remove your line termination and it will not look for this non-existing data
and run through the whole file.
john
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
Sent: Monday, December 03, 2001 10:11 PM
To: [EMAIL PROTECTED]
Subject: Load data infile
Be struggling
Thanks john,
After some further checking, the files that I was trying to load back into the
db where sent to me from a MAC, and had some strange C/R's in it, causing the
file be formatted improperly! After fixing that, all the files loaded into the
db fine! :)
thx's
On Tue, 4 Dec 2001
http://www.mysql.com/doc/L/O/LOAD_DATA.html
Elm
database,sql,query,table
Hi guys,
Can any one told me - with my great appreciate - how to load a
data
from txt file into a specific column?
Thanks.
Hytham Shehab
_
This message
M. A. Alves writes:
mysql database $!#$#%$
I know the feeling. :-P
On Sun, 11 Nov 2001, Michael Conley wrote:
. . . even though the text files that I am importing don't have the
customer number of the person who submitted it, if I know the customer
number . . .
Where from do
Use optionally:
mysql LOAD DATA INFILE '/home/sql/data.csv' INTO TABLE csvdata
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\' LINES TERMINATED BY
'\n';
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, October 31, 2001 9:33 AM
To: [EMAIL
Hi
I didn't see any difference between using optionally or not.
Any solutions to make the two that fails work? (see test case below)
Using 3.23.41.
Thanks
test.csv:
Record 1,some data,Works
Record 2,some data,Works
Record 3,some 'data',Works
Record 4,some data,Works
Record 5,somedata,Fails
PROTECTED]]
Sent: Wednesday, October 31, 2001 12:48 PM
To: [EMAIL PROTECTED]
Subject: Re: Load data infile
Hi
I didn't see any difference between using optionally or not.
Any solutions to make the two that fails work? (see test case below)
Using 3.23.41.
Thanks
test.csv:
Record 1,some
I didn't see any difference between using optionally or not. Any
solutions to make the two that fails work? (see test case below) Using
3.23.41.
That's a problem with your input data. You can't have the character that
you're using to surround the fields within the field,
At 2:47 PM -0500 10/31/01, [EMAIL PROTECTED] wrote:
Hi
I didn't see any difference between using optionally or not.
Any solutions to make the two that fails work? (see test case below)
Using 3.23.41.
Thanks
The problem is that your CSV file isn't legal CSV. You'll need to
tell your
At 10:32 AM -0500 10/31/01, [EMAIL PROTECTED] wrote:
Hi
I'm working on importing csv datafile returns Warnings, after carefully
checking data I noticed that there are double quotes () inside the
records.
mysql LOAD DATA INFILE '/home/sql/data.csv' INTO TABLE csvdata
FIELDS TERMINATED BY ','
Chris Book writes:
My actual data has represented as , so I need to set it up this way. I
would prefer to work with the files the way they are now rather than
re-export my data a different way, since that machine is inconvenient right
now.
I can export data using the same params, and
i had similar problem until used line delimiter different than default \n
try this:
load data local infile 'file.txt' into table mytable fields optionally
enclosed by '' escaped by '' terminated by ',' LINES TERMINATED BY '-= aNy
we1Rd cHar 0r Str1Ng =-'
:)
-Original Message-
Chris Book writes:
load data local infile 'file.txt' into table mytable fields optionally
enclosed by '' escaped by '' terminated by ','
I'm not sure how mysql handles the case where the escape
character is the same as the field enclosing character, and
I can see a problem here if you have a
That would be a tough one - you'd first have to make sure every single record has no
more than two words in that field. there's no guarantee that a field name would
actually always contain only two names, or even two names at all. When I get stuffy, I
spell my name as A. Marjolein Katsma -
I have a tab delimited file from a spreadsheet that
has a field Name (includes first and last name).
I am importing the file into a table that has
firstName and lastName as seperate fields.
Is there a way to split the field from the text file
Name into two different fields in the database,
Brad Stockdale writes:
Hello all,
I'm new to the list... Been using MySQL for various things for a year
now, but have never ran into the following situation...
I have a flat text file that I have exported from FileMaker Pro. I want
to import that into MySQL, but there's a
Hello all,
I'm new to the list... Been using MySQL for various things for a year
now, but have never ran into the following situation...
I have a flat text file that I have exported from FileMaker Pro. I want
to import that into MySQL, but there's a catch... There are two tables I am
Hello all,
I'm new to the list... Been using MySQL for various things for a year
now, but have never ran into the following situation...
I have a flat text file that I have exported from FileMaker Pro. I want
to import that into MySQL, but there's a catch... There are two tables I am
On Tue, 4 Sep 2001, Curtis Spencer wrote:
I have a text file with around 25 fields but I only want 5 of them.
They are not the first 5 fields that I want. Is there a way to skip
fields using LOAD DATA INFILE so I don't have to build a 25 field table
and then cut it down?
Read the manual
At 12:45 PM -0700 9/4/01, Curtis Spencer wrote:
I have a text file with around 25 fields but I only want 5 of them.
They are not the first 5 fields that I want. Is there a way to skip
fields using LOAD DATA INFILE so I don't have to build a 25 field table
and then cut it down?
Thanks,
Curtis
Rudy,
there is an explanation to this behaviour:
on escaped data you could un-escape almost everything, one can escape almost any
character if wanted.. .. so \\ gets \ and \* gets *, \% gets % and \N gets... N.
mysql select \A\B\C\J\N;
+-+
|ABCJN|
+-+
|ABCJN|
+-+
1 row in set
Hi!
Sinisa == Sinisa Milivojevic [EMAIL PROTECTED] writes:
Sinisa Jeff Tanner writes:
I running a test on mysql to test its performance of doing a bulk insert
into a table using LOAD DATA INFILE.
The table is simple:
CREAT TABLE test (
valueCHAR(32) NOT NULL PRIMARY KEY
)
Jeff Tanner writes:
I running a test on mysql to test its performance of doing a bulk insert
into a table using LOAD DATA INFILE.
The table is simple:
CREAT TABLE test (
valueCHAR(32) NOT NULL PRIMARY KEY
)
The test is simple:
a) clear table
b) time
DELAY_KEY_WRITE=1
Tim.
On Mon, Aug 06, 2001 at 07:36:17AM -0700, Jeff Tanner wrote:
I running a test on mysql to test its performance of doing a bulk insert
into a table using LOAD DATA INFILE.
The table is simple:
CREAT TABLE test (
valueCHAR(32) NOT NULL PRIMARY KEY
)
Tanner
Cc: '[EMAIL PROTECTED]'
Subject: Re: LOAD DATA INFILE performance testing
DELAY_KEY_WRITE=1
Tim.
On Mon, Aug 06, 2001 at 07:36:17AM -0700, Jeff Tanner wrote:
I running a test on mysql to test its performance of doing a bulk insert
into a table using LOAD DATA INFILE.
The table
PROTECTED]]
Sent: Monday, August 06, 2001 8:33 AM
To: [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Subject: Re: LOAD DATA INFILE performance testing
Jeff Tanner writes:
I running a test on mysql to test its performance of doing a bulk insert
into a table using LOAD DATA INFILE.
The table
DELAY_KEY_WRITE=ON by default
Jeff
-Original Message-
From: Tim Bunce [mailto:[EMAIL PROTECTED]]
Sent: Monday, August 06, 2001 9:00 AM
To: Jeff Tanner
Cc: '[EMAIL PROTECTED]'
Subject: Re: LOAD DATA INFILE performance testing
DELAY_KEY_WRITE=1
Tim.
On Mon, Aug 06, 2001 at 07:36:17AM
Curtis Spencer writes:
Is this possible with 2.0 version (type 4 I think) MM driver found on
www.gjt.org? Also how do the file reading privileges for this work because
I tried doing it and it gave me an authorization error even though my mysql
user has FILE on it's privileges. Also, the
At 8:19 AM +0800 7/17/01, Erymuzuan Mustapa wrote:
I have an Excel worksheet saved as text file(tab delimited),
Load data works fine on my Win2k(MySQL 3.23.38) for bulk loading but on
MacOS X , MySQL 3.23.39 the same same file doesn't seem to work right, all I
got was malformed data. Using
From my experience, if you want to import data into a date field, you have to make
sure that the date format in your csv file conforms to a date format required by
MySQL. This part is covered in MySQL documentation. So, your 20.12.1999 date
should become 2001-12-20 and so on.
P.S. Don't
: Re: LOAD DATA INFILE
From my experience, if you want to import data into a date field, you have to make
sure that the date format in your csv file conforms to a date format required by
MySQL. This part is covered in MySQL documentation. So, your 20.12.1999 date
should become 2001-12-20
.'-'.$day;
return $date;
} // dbDate
Maybe able to run a similar function?
John (*\*)
-Original Message-
From: Bernhard Doebler [mailto:[EMAIL PROTECTED]]
Sent: 05 July 2001 12:46
To: [EMAIL PROTECTED]
Subject: Re: LOAD DATA INFILE
Hi,
thanks for your answer. You never have problems
far
Bernhard Doebler
- Original Message -
From: John (*\*) [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Cc: Bernhard Doebler [EMAIL PROTECTED]
Sent: Thursday, July 05, 2001 2:13 PM
Subject: RE: LOAD DATA INFILE
This is what i wrote to convert dates in PHP before putting them into db
:[EMAIL PROTECTED]]
Sent: 05 July 2001 13:35
To: [EMAIL PROTECTED]
Subject: Re: LOAD DATA INFILE
Hi,
it seems to me I have to do something like this. It originally was my goal
to only use SQL but this does not seem to be possible. Or do you think it's
possible to reformat the string using left
Drop the quotes (') arround the NULL
e.g.: 7,'Markovic Stevo',NULL,NULL
Clive Smart
WEBServ
TomazSa wrote:
I get *.csv file like this (1 string) :
7,'Markovic Stevo','NULL','NULL'
When I use LOAD DATA INFILE syntax I get word NULL in field (MySQL table)
q: I want field to be empty (in
wow, that was fast, tnx (new on this m. list)
why was mysql.com down for 2 days (or more)?
lp, tomaz
At 2:00 AM +0200 6/25/01, c.smart wrote:
Drop the quotes (') arround the NULL
e.g.: 7,'Markovic Stevo',NULL,NULL
No.
To load a NULL value into a column using LOAD DATA, you must use \N.
NULL, with or without quotes, will insert as the word NULL.
Clive Smart
WEBServ
TomazSa wrote:
I get
On 09-May-01 Darla Baker wrote:
I have a text file which is extracted from a non-sql
database each night and then a cron sql script runs to
insert the text data into the mysql database tables.
My problem is that the date data in the text file is
formatted incosistently (12/31/00 or
The server needs read and execute permissions all the way to the data
file, and read
permissions on the file.
I you are on the server, try LOAD DATA LOCAL instead.
Kalvin Kag Khetsavanh wrote:
I keep getting the following error everytime i tried to use Load Data
mysql load data infile
If it were me, I'd create a new table in the database with enough columns
to handle all the data in the file. Then I'd do insert . . . . select
into the table that need the data.
Hope this helps,
Michael Ridinger
On Thu, 22 Mar 2001, Daniel Newman wrote:
I've been looking into the help
Richard,
My suggestions is to use this command:
LOAD DATA INFILE '/home/fpo/htdocs/secure/upload/export-customers.csv'
REPLACE INTO TABLE tempCustomer FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED
BY '"' (customerid, custLastName, custFirstName, custEmail, deliveryDay,
storeid)
The only
401 - 467 of 467 matches
Mail list logo
