how to get " into a string without it terminating it

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

Re: how to get " into a string without it terminating it

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.

Re: how to get " into a string without it terminating it

"Ian Davies" wrote in message
news:Gt84g.3528$ZB4.1850@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

Re: how to get " into a string without it terminating it

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" wrote in message
news:e2r47d05jm@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.

Re: how to get " into a string without it terminating it

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" wrote in message
news:MJydncBsoPk3rszZ4p2dnA@telcove.net...
> "Ian Davies" wrote in message
> news:Gt84g.3528$ZB4.1850@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
>
>
>
>
>

Re: how to get " into a string without it terminating it

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.

Re: how to get " into a string without it terminating it

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" wrote in message
news:e2rrr3011b6@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.

Re: how to get " into a string without it terminating it

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" wrote in message
news:Lwk4g.76$k72.11@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" wrote in message
> news:e2rrr3011b6@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.
>
>

Re: how to get " into a string without it terminating it

"Ian Davies" wrote in message
news:8Bl4g.287$mH3.202@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 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