System.Data.Odbc.OdbcCommand("LOAD DATA LOCAL INFILE 'Current_path' INTO TABLE importcsv... I need to pass the filename into this command.. its giving me a errocode 22.. Please help

System.Data.Odbc.OdbcCommand("LOAD DATA LOCAL INFILE 'Current_path' INTO TABLE importcsv... I need to pass the filename into this command.. its giving me a errocode 22.. Please help

'Current_path'  I have assigned the file name to this, The user select the file from a directory and full path gets copied to a textbox. I am trying to pass this fullpath into the LOAD data command but it says file not found.
but if i enter the same path into the LOAD DATA cmd it works!
How can I pass this dynamic value into the command?
Pavithra_SAsked:
Who is Participating?
 
Pavithra_SConnect With a Mentor Author Commented:
OdbcCom = new System.Data.Odbc.OdbcCommand("LOAD DATA LOCAL INFILE" + " '" + Current_path + " '"+ "INTO TABLE importcsv FIELDS TERMINATED BY ',' ENCLOSED BY '\"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES (PatentNo,Title,Inventor,Assignee,DateofPublication,ApplicationDate,FamilyMembers) ", OdbcCon);

this worked for me,

Thanks for your help! LOAD DATA LOCAL INFILE works for me.. my file is local on my machine and MySQL server is on another...
0
 
abelCommented:
Error 22 is in this case, file not found, as you have noticed already.

If the mysql server is not local, it will not work. If the pathname contains an unc path, you may have problems (see bug report here: http://bugs.mysql.com/bug.php?id=28520, but the report is quite old), and if the final command you create is not containing the single quotes, it will not work either.

Yet more, it might have problems with spaces in paths, I had that in the past myself. Try to use a path that is very short and on the same machine as the mysql server and see if that helps.
0
 
abelConnect With a Mentor Commented:
Btw, I forgot a major point: did you double escape the backslashes? I.e., if you would do it from the commandline you would use something like:

load data LOCAL infile 'c:\\test.txt' into tablex...
you should do the same from your command. To be on the safe side, I usually change the backslashes to forward slashes, which works fine on windows (actually, under the hood windows uses forward slashes exclusively) and you don't need to worry about any escaping business:

string currentPath = @"c:\test.txt";currentPath = currentPath.Replace("\\", "/");

0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
Pavithra_SAuthor Commented:
Can u please delete my comment right now?. I will repost this.. Thanks!
0
 
abelCommented:
I see you are quite new here. The experts do not have the power to delete comments or questions. Since this question has a solution (file is not on the right server), you, as the asker, can accept the comment that explained that principle as the solution (which is the first one above). That will close this question. If you post a new q. it is a good idea to use the "related q." button.

0
 
abelCommented:
If you have questions about closing questions and awarding points, I'll be happy to help you. But before you ask, please read this on closing questions and this on awarding points.
0
 
Pavithra_SAuthor Commented:
Thank you for your suggestions. If I am right you explained that you cannot import a file if it is not on the same machine as the server.. but I am able to do this..
 
0
 
abelCommented:
I'm sorry, I think I misunderstood you then. Your previous comment I understood of it that you could not get the file because it was on a remote server.

If you do it from the commandline, it will work, even if you use the mysql client from a local machine and your server is remote.

If you do it through odbc, you add an extra layer. The command is send to the server untouched and the server has no idea where to get the file with the local name.

What you can do about that is simple: use the absolute UNC path to your file instead, i.e.: \\mylocalcomputername\sharename\directory\file.txt (remember to double-escape).
0
 
abelCommented:
mmm, it maybe something else. According to this link: http://bugs.mysql.com/bug.php?id=14422 you should remove the word LOCAL to get it working (bottom of that post). Even though that link shows a different error it is worth trying.
0
 
abelCommented:
And... on the bottom of this thread is a hint that seems to solve some odbc problems. It doesn't really seem related, but by default, using LOCAL INFILE will be disabled for security reasons. Can you check whether your setting is correct? http://cgfa.telepac.pt/pub/mysql/doc/refman/5.1/en/load-data-local.html

// my.cnf
[odbc]
local-infile=1

or add option=65535 to the odbc connection string.
0
 
Pavithra_SAuthor Commented:
OdbcCom = new System.Data.Odbc.OdbcCommand("LOAD DATA LOCAL INFILE" + " '" + Current_path + " '"+ "INTO TABLE importcsv FIELDS TERMINATED BY ',' ENCLOSED BY '\"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES ", OdbcCon);

What I was trying to say that the line above works good and I am able to import the file successfully!! Sorry I guess I mislead you by asking to delete the comment..
I said that Coz i had my column names on that... but its ok
Your suggestion of escaping backslash did help me, thank you.. But the solution is the comment I sent... Please let me know how I can assign points to both your comment and My comment as both together gives the right solution..

sorry for having mislead you..

Thank you for your time!
0
 
abelCommented:
You can never assign points to yourself (because that would open the door for any expert assigning themselves points). But what you can do: accept your own comment as a solution and assign points to another comment. Just click "Accept as solution" on your comment and type the total amount of points into the textbox (will appear on the next page) of my comment.

the result will then be: your comment is the answer/solution, my comment is the "assisting solution".
0
 
Pavithra_SAuthor Commented:
oh ok cool Thanks!
0
 
abelCommented:
excellent. Glad it works now altogether (and I learned something along the way about the limitations and intricacies of INFILE).
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.