how to access txt. file to be transfer to mysql database using vb.net

[thasahema]

hi there...
i am looking for a solution to input .txt file into database using vb.net coding and mysql as the database.

plz kindly help me to get the solution...

i have done with the database connection

rgds,
hema malini

 

[cjard]

i'm amazed that mysql doesnt come with a bulk loader that can do this for you..

from here, find a code example that lets you read txt files. if the files are in CSV i recommend a free library called JHL_Tools.dll

read the text file in, line at a time to save resources. with each line, run a database query to insert the records.

You are strongly recommended to use parameterized queries. This board has covered the topic of parameterized queries many times so a brief search should turn up a good result. In short, a param query looks like this:

myOleDbCommand.CommandText = "INSERT INTO tblEmployee(name, age) VALUES (?, ?)"


the database compiles the statement knowing that the ? are variables. You then set parameter values in the .Parameters collection of the the myOleDbCommand and run the command.. change the values, run it again.

this is much better and more secure than building an sql string each time


-
note: i assumed youre using OLE, but if youre using a mysql driver, the command set might be different. for instance in ms access the statement looks like that above. A param query in oralce looks like:

"INSERT INTO tblEmployee(name, age) VALUES in_name, :in_age)"

and in sql server looks like:
"INSERT INTO tblEmployee(name, age) VALUES (@in_name, @in_age)"


so mysql might use different syntax

 

[JohnH]

You might find this seven-part VB.Net-MySql tutorial series interesting, the Connector/Net library is used for interaction, and provides objects very similar to ADO.Net and the System.Data.SqlClient namespace, ie the MySql.Data.MySqlClient namespace with MySqlConnection / MySqlCommand / MySqlDataAdapter.
http://www.vbmysql.com/articles/

 

[dellman]

You might find this seven-part VB.Net-MySql tutorial series interesting, the Connector/Net library is used for interaction, and provides objects very similar to ADO.Net and the System.Data.SqlClient namespace, ie the MySql.Data.MySqlClient namespace with MySqlConnection / MySqlCommand / MySqlDataAdapter.
http://www.vbmysql.com/articles/

Thanks for this link JohnH, I am going to take a look at this, to date I have been mainly working with SQL server and looking for a tutorial much like this one.

Cheers.

 

[zekeman]

If you can create the .txt file in your own defined format, the same

format that mysql uses to do exports and imports, your done.

 

[ramnujs]

load file to mysql

mysql provides a command for loading text file with delimeted values:
Syntax:

load data local infile 'c:\filename.txt' into table `tablename` fields escaped by '\\' terminated by ',' enclosed by '"' lines terminated by '\n' ( `column 1`, `column 2`, .....`Column n` )

 

[TechGnome]

interesting.... I read that and got a completely different view of the problem.... I assumed he wanted to know how to store the file iteself in the DB - the actual binary info.... not parsed out and into tables....

again, reinforcing a theory of mine...
-tg