how to get " into a string without it terminating it

[Ian Davies]

Hello

I have the following sql string to run as a command in my VB6 project to
update mysql table

strSQL = "LOAD DATA INFILE " & ImportFile & " INTO TABLE tPupils FIELDS
TERMINATED BY ',' ENCLOSED BY ' " ' LINES TERMINATED BY '\n';"

Problem is the double quote at position ......" ' LINES......
causes the command to fail thinking the double quote is ending the SQL but
it is infact just indicating that the file being read in has fields enclosed
by "
How do I rewrite this string so I can use the double quote in it as part of
the string and not to terminate it

Thanks
ps the sql is to update a mysql table

[Bill Karwin]

Ian Davies wrote:

How do I rewrite this string so I can use the double quote in it as part of
the string and not to terminate it

Well, how does VB allow you to put double quotes in any string? This
has little to do with SQL. It's a VB question.

I'm not a VB user, but I see from documentation that one can use any of
the following solutions:
- Enclose the whole string in single-quotes instead of double-quotes
(but then you have to worry about the literal single-quotes within the
string).
- Put two double-quotes where you want one in the string.
- Concatenate your string with CHR(34), which is the code for a
double-quote character.

See http://support.microsoft.com/kb/q147687/

Regards,
Bill K.

[Thomas Bartkus]

"Ian Davies" <ia********@virgin.net> wrote in message
news:Gt*****************@newsfe7-win.ntli.net...

Hello

I have the following sql string to run as a command in my VB6 project to
update mysql table

strSQL = "LOAD DATA INFILE " & ImportFile & " INTO TABLE tPupils FIELDS
TERMINATED BY ',' ENCLOSED BY ' " ' LINES TERMINATED BY '\n';"

Problem is the double quote at position ......" ' LINES......
causes the command to fail thinking the double quote is ending the SQL but
it is infact just indicating that the file being read in has fields enclosed by "
How do I rewrite this string so I can use the double quote in it as part of the string and not to terminate it

Thanks
ps the sql is to update a mysql table

Bill Karwin gave you the the straight story. Use Chr$(34) which equals a
double quote ["] character.

Rewrite your statement:
strSQL = "LOAD DATA INFILE " & ImportFile & " INTO TABLE tPupils FIELDS
TERMINATED BY ',' ENCLOSED BY ' " & vbDblQuote & "' LINES TERMINATED BY
'\n';"

Notice that we keep the the quote character between single quotes (not so
easy to see!).

As a further tip - I like to use the pre-declared vbLf character so that a
PRINT statement will output strSQL in the debug window nice and pretty so
that what little hair I have left doesn't fall out when I try to decipher my
own code.

strSQL = "LOAD DATA INFILE " & ImportFile & vbLf & _
"INTO TABLE tPupils" & vbLf & _
"FIELDS TERMINATED BY ','" & vbLf & _
"ENCLOSED BY '" & Chr$(34) & "'" & vbLf & _
"LINES TERMINATED BY '\n';"

That is marginally easier to read in the code.
And much easier to read when you output to the debug window:

debug.print strSQL

LOAD DATA INFILE {SomeFile}
INTO TABLE tPupils
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

MySQL doesn't mind the extra linefeeds and you can just copy/paste the
output to your favorite query tool to prove that your sql strings work as
advertised.

Notice that those double/single quote combos are unfriendly to the eyeballs!
Thomas Bartkus

[Ian Davies]

Thanks Bill

That solved that problem
Still not working though. Error message indicating a syntax error. I think
it is something to do with a variation thats needed somewhere due to the use
of mysql.
Will post again if I cant resolve it.
Thanks again
Ian
"Bill Karwin" <bi**@karwin.com> wrote in message
news:e2********@enews4.newsguy.com...

Ian Davies wrote:

How do I rewrite this string so I can use the double quote in it as part of the string and not to terminate it

Well, how does VB allow you to put double quotes in any string? This
has little to do with SQL. It's a VB question.

I'm not a VB user, but I see from documentation that one can use any of
the following solutions:
- Enclose the whole string in single-quotes instead of double-quotes
(but then you have to worry about the literal single-quotes within the
string).
- Put two double-quotes where you want one in the string.
- Concatenate your string with CHR(34), which is the code for a
double-quote character.

See http://support.microsoft.com/kb/q147687/

Regards,
Bill K.

[Ian Davies]

Thanks thomas

Thanks
That solved that problem
Still not working though. Error message indicating a syntax error. I think
it is something to do with a variation thats needed somewhere due to the use
of mysql.
Will post again if I cant resolve it.
Thanks again
Ian

Yes I agree, using vbLf does make things look neater. Will use it if I
remember
Ian

"Thomas Bartkus" <th***********@comcast.net> wrote in message
news:MJ********************@telcove.net...

"Ian Davies" <ia********@virgin.net> wrote in message
news:Gt*****************@newsfe7-win.ntli.net...

Hello

I have the following sql string to run as a command in my VB6 project to
update mysql table

strSQL = "LOAD DATA INFILE " & ImportFile & " INTO TABLE tPupils FIELDS
TERMINATED BY ',' ENCLOSED BY ' " ' LINES TERMINATED BY '\n';"

Problem is the double quote at position ......" ' LINES......
causes the command to fail thinking the double quote is ending the SQL but it is infact just indicating that the file being read in has fields enclosed

by "
How do I rewrite this string so I can use the double quote in it as part

of

the string and not to terminate it

Thanks
ps the sql is to update a mysql table

Bill Karwin gave you the the straight story. Use Chr$(34) which equals a
double quote ["] character.

Rewrite your statement:
strSQL = "LOAD DATA INFILE " & ImportFile & " INTO TABLE tPupils FIELDS
TERMINATED BY ',' ENCLOSED BY ' " & vbDblQuote & "' LINES TERMINATED

BY '\n';"

Notice that we keep the the quote character between single quotes (not so
easy to see!).

As a further tip - I like to use the pre-declared vbLf character so that a
PRINT statement will output strSQL in the debug window nice and pretty so
that what little hair I have left doesn't fall out when I try to decipher my own code.

strSQL = "LOAD DATA INFILE " & ImportFile & vbLf & _
"INTO TABLE tPupils" & vbLf & _
"FIELDS TERMINATED BY ','" & vbLf & _
"ENCLOSED BY '" & Chr$(34) & "'" & vbLf & _
"LINES TERMINATED BY '\n';"

That is marginally easier to read in the code.
And much easier to read when you output to the debug window:

debug.print strSQL

LOAD DATA INFILE {SomeFile}
INTO TABLE tPupils
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

MySQL doesn't mind the extra linefeeds and you can just copy/paste the
output to your favorite query tool to prove that your sql strings work as
advertised.

Notice that those double/single quote combos are unfriendly to the eyeballs! Thomas Bartkus

[Bill Karwin]

Ian Davies wrote:

Still not working though. Error message indicating a syntax error. I think
it is something to do with a variation thats needed somewhere due to the use
of mysql.
Will post again if I cant resolve it.

Great, either I or someone else will try to help if you need it.

When you post, please post the SQL statement, without all the VB stuff
around it. That is, get the actual SQL statement that is being
executed, _after_ any variable substitutions, concatenation, or
expressions have been evaluated by VB.

All that code laced through the SQL obscures errors. Not trying to pick
on VB -- the same thing applies when folks post their PHP, ASP, Java,
Perl, or other code that prepares the SQL, instead of the
ready-to-execute SQL.

Also any error message in its entirety would be helpful for troubleshooting.

Regards,
Bill K.

[Ian Davies]

Ive pondered over this last night and a bit this morning and still cant find
anything wrong with the syntax, which is following what is on the mySQL site
for CSV files (I think that is equivelent to xls type). Here is the link
http://dev.mysql.com/doc/refman/5.0/en/load-data.html

The sql is as follows
LOAD DATA INFILE " & ImportFile & " INTO TABLE tpupils FIELDS TERMINATED BY
',' ENCLOSED BY '" & Chr$(34) & "' LINES TERMINATED BY '/n' IGNORE 1 LINES;

and the error is

[MySQL][ODBC 3.51 Drive][mysql-5.0.18-nt]You have an error in your SQL
syntax; check the manual that coresponds to your MySQL server version for
the right syntax to use near 'E:\Ian\Book1.xls INTO TABLE tpupils FIELD
TERMINATED BY ',' ENCLOSED BY ' " ' LIN' at line 1 -2147217900

Assistance appreciated

Ian

"Bill Karwin" <bi**@karwin.com> wrote in message
news:e2*********@enews4.newsguy.com...

Ian Davies wrote:

Still not working though. Error message indicating a syntax error. I think it is something to do with a variation thats needed somewhere due to the use of mysql.
Will post again if I cant resolve it.
Great, either I or someone else will try to help if you need it.

When you post, please post the SQL statement, without all the VB stuff
around it. That is, get the actual SQL statement that is being
executed, _after_ any variable substitutions, concatenation, or
expressions have been evaluated by VB.

All that code laced through the SQL obscures errors. Not trying to pick
on VB -- the same thing applies when folks post their PHP, ASP, Java,
Perl, or other code that prepares the SQL, instead of the
ready-to-execute SQL.

Also any error message in its entirety would be helpful for

troubleshooting.
Regards,
Bill K.

[Ian Davies]

To update
I have tried the statement simplified directly in mysql

LOAD DATA INFILE 'Book1.xls' INTO TABLE tpupils IGNORE 1 LINES;

and still get an error. this time
Out of range value adjusted for column 'PupilNo' at row 1

The table structure is
PupilNo (was an autoinc but changed it to BIGINT(20) just to see if that was
problem)
FirstName (varchar(20))
Surname (varchar(20))
Gender (varchar(6))
StartYear (INT(20))

Using excel file with same fields
Tried with a txt file too but same problem
If anyone has used LOAD DATA INFILE to get excel rows into mysql table
please can I have the syntax

Thanks
Ian

"Ian Davies" <ia********@virgin.net> wrote in message
news:Lw*************@newsfe2-gui.ntli.net...

Ive pondered over this last night and a bit this morning and still cant find anything wrong with the syntax, which is following what is on the mySQL site for CSV files (I think that is equivelent to xls type). Here is the link
http://dev.mysql.com/doc/refman/5.0/en/load-data.html

The sql is as follows
LOAD DATA INFILE " & ImportFile & " INTO TABLE tpupils FIELDS TERMINATED BY ',' ENCLOSED BY '" & Chr$(34) & "' LINES TERMINATED BY '/n' IGNORE 1 LINES;
and the error is

[MySQL][ODBC 3.51 Drive][mysql-5.0.18-nt]You have an error in your SQL
syntax; check the manual that coresponds to your MySQL server version for
the right syntax to use near 'E:\Ian\Book1.xls INTO TABLE tpupils FIELD
TERMINATED BY ',' ENCLOSED BY ' " ' LIN' at line 1 -2147217900

Assistance appreciated

Ian

"Bill Karwin" <bi**@karwin.com> wrote in message
news:e2*********@enews4.newsguy.com...

Ian Davies wrote:

Still not working though. Error message indicating a syntax error. I think it is something to do with a variation thats needed somewhere due to

the

use of mysql.
Will post again if I cant resolve it.

Great, either I or someone else will try to help if you need it.

When you post, please post the SQL statement, without all the VB stuff
around it. That is, get the actual SQL statement that is being
executed, _after_ any variable substitutions, concatenation, or
expressions have been evaluated by VB.

All that code laced through the SQL obscures errors. Not trying to pick
on VB -- the same thing applies when folks post their PHP, ASP, Java,
Perl, or other code that prepares the SQL, instead of the
ready-to-execute SQL.

Also any error message in its entirety would be helpful for

troubleshooting.

Regards,
Bill K.

[Thomas Bartkus]

"Ian Davies" <ia********@virgin.net> wrote in message
news:8B***************@newsfe4-gui.ntli.net...

To update
I have tried the statement simplified directly in mysql

LOAD DATA INFILE 'Book1.xls' INTO TABLE tpupils IGNORE 1 LINES;

and still get an error. this time
Out of range value adjusted for column 'PupilNo' at row 1

The table structure is
PupilNo (was an autoinc but changed it to BIGINT(20) just to see if that was problem)
FirstName (varchar(20))
Surname (varchar(20))
Gender (varchar(6))
StartYear (INT(20))

Using excel file with same fields
Tried with a txt file too but same problem
If anyone has used LOAD DATA INFILE to get excel rows into mysql table
please can I have the syntax

First -
Back off on the BIGINT which won't solve your problem anyway. BIGINT fields
are alien to the Microsoft world and presents problems you don't need to
deal with right now.

Second -
You can't load an excel file [Book1.xls] that way. The Excel file (.xls)
format is proprietary and MySQL won't know how to deal with it.

What you *can* do with the Excel file is SaveAs "Text (Tab Delimited);
*.txt".
The following "LOAD FILE" matches the way Excel will output this text file:

LOAD DATA INFILE 'Book1.txt' # Text fomat! Not .xls.
INTO TABLE tpupils
FIELDS TERMINATED BY '\t' # The tab character separates the
fields
LINES TERMINATED BY '\r\n' # Microsoft style <cr><lf> line
termination.
IGNORE 1 LINES; # Assuming that the first
line are field (column) labels we don't need.

Make sure you have a clean Excel table without extra junk around it.

Make *especially* sure that you aren't mixing data types in your Excel
columns. This is something Excel permits and MySQL can't tolerate. I
suggest you deliberately format your Excel columns to text for the varchar
fields and to numeric (zero decimal places) for the INT field so there won't
be any misunderstandings.

Third -
Consider using linked tables in MS Access.
You can create linked tables to both your MySQL table and your Excel
worksheet inside the same Access .mdb (file) database. This makes it easy
to use Access query utilities and/or ADO to move data Excel<->MySQL.

Thomas Bartkus