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 8 2582
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" <ia********@vir gin.net> wrote in message
news:Gt******** *********@newsf e7-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
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.co m> 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.
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******** ************@te lcove.net... "Ian Davies" <ia********@vir gin.net> wrote in message news:Gt******** *********@newsf e7-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 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.
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.x ls INTO TABLE tpupils FIELD
TERMINATED BY ',' ENCLOSED BY ' " ' LIN' at line 1 -2147217900
Assistance appreciated
Ian
"Bill Karwin" <bi**@karwin.co m> wrote in message
news:e2******** *@enews4.newsgu y.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.
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********@vir gin.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.x ls INTO TABLE tpupils FIELD TERMINATED BY ',' ENCLOSED BY ' " ' LIN' at line 1 -2147217900
Assistance appreciated
Ian "Bill Karwin" <bi**@karwin.co m> wrote in message news:e2******** *@enews4.newsgu y.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" <ia********@vir gin.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 misunderstandin gs.
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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: maniac |
last post by:
Hey guys, I'm new here, just a simple question.
I'm learning to Program in C, and I was recommended a book called,
"Mastering C Pointers", just asking if any of you have read it,
and if it's worth the $25USD.
I'm just looking for a book on Pointers, because from what I've
read it's one of the toughest topics to understand.
thanks in advanced.
|
by: Alan |
last post by:
hi all,
I want to define a constant length string, say 4
then in a function at some time, I want to set the string to a constant
value, say a
below is my code but it fails
what is the correct code?
many thx!
|
by: Matt |
last post by:
I have 2 questions:
1. strlen returns an unsigned (size_t) quantity. Why is an unsigned
value more approprate than a signed value? Why is unsighned value less
appropriate?
2. Would there be any advantage in having strcat and strcpy return a
pointer to the "end" of the destination string rather than returning a
|
by: Apotheosis |
last post by:
The problem professor gave us is:
Write a program which reads two integer values. If the first is less
than the second, print the message "up". If the second is less than
the first, print the message "down" If the numbers are equal, print
the message "equal" If there is an error reading the data, print a
message containing the word "Error" and perform exit( 0 );
And this is what I wrote:
|
by: ern |
last post by:
Right now I'm using exit(0) to terminate my program. My
program is a console .exe application. After the "exit(0)" line of
code is encountered, the console application waits for an enter press,
before terminating. I want it to terminate completely without having
to press enter manually. Anybody know what I might be missing here ?
| |
by: Freaker85 |
last post by:
Hello,
I am new at programming in C and I am searching a manner to parse a
string into an integer.
I know how to do it in Java, but that doesn't work in C ;o)
I searched the internet but I didn't found it yet.
help please
thank you
Freaker85
|
by: Chris |
last post by:
Hello all...
I have a program with the following structure (all classes
mentioned are of my own creation, and none of the classes contain try
or catch blocks):
- main() consists of a large try block with several catch blocks I
will describe below. main(), within the try block, declares, and
implicitly default-constructs, a variable (object) of class AppDBConn.
|
by: arnuld |
last post by:
i have created a solutions myself. it compiles without any trouble
and runs but it prints some strange characters. i am not able to find
where is the trouble.
--------------------------------- PROGRAMME
--------------------------------
/* K&R2 section 1.9
exercise 1.19
|
by: aarklon |
last post by:
Hi all,
See:- http://www.cs.princeton.edu/introcs/faq/c2java.html
for C vs Java in number crunching
http://husnusensoy.blogspot.com/2006/06/c-vs-java-in-number-crunching.html
|
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,...
|
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...
| |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
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...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
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
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
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...
| |