Can LOAD DATA ignorecharacters normally needing escapes inside fields?

A windows platform application generates a file consisting of comma
separated text whose fields are enclosed in double quotes. When
executing the load data infile command, the backslash characters in
field "path" cause problems. There isn't a way to have the windows
application generate escaped backslashes, i.e. \\.

I suppose I could make a separate pass through the text file doing a
text substitution, but I'd prefer to avoid that.

Is there a way to have LOAD DATA INFILE not pay attention to the
contents of a field when loading it?

The data consists of many lines of the form:
"abc","dir1\dir2\dir3","file.ext","xyz"

and my sql statement is like:
TRUNCATE TABLE table_name;
LOAD DATA INFILE 'c:/x/y/z.txt' REPLACE
INTO TABLE table_name
FIELDS ENCLOSED BY '"' TERMINATED BY ','
(col1,path,file,other_info)
;

Thanks,
Dave

--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=gcdmw-win32@m.gmane.org

Re: Can LOAD DATA ignorecharacters normally needing escapes insidefields?

DaveO wrote:
> A windows platform application generates a file consisting of comma
> separated text whose fields are enclosed in double quotes. When
> executing the load data infile command, the backslash characters in
> field "path" cause problems. There isn't a way to have the windows
> application generate escaped backslashes, i.e. \\.
>
> I suppose I could make a separate pass through the text file doing a
> text substitution, but I'd prefer to avoid that.
>
> Is there a way to have LOAD DATA INFILE not pay attention to the
> contents of a field when loading it?

Have you already tried using ... FIELDS ESCAPED BY '' ... ?

--
Todd Farmer, Support Engineer
MySQL Inc., www.mysql.com

MySQL Conference & Expo - http://www.mysqlconf.com/
April 23-26 in Santa Clara, California (US)

--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=gcdmw-win32@m.gmane.org

Re: Can LOAD DATA ignorecharacters normally needing escapes insidefields?

Todd Farmer wrote:
> DaveO wrote:
snip

>> Is there a way to have LOAD DATA INFILE not pay attention to the
>> contents of a field when loading it?
>
> Have you already tried using ... FIELDS ESCAPED BY '' ... ?
>

That makes sense, since the default is FIELDS ESCAPED BY '\'.
And it works. Thanks.

Dave

--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=gcdmw-win32@m.gmane.org