VB question
-
Hi all, I would like to insert datas from a txt file (into a table) which has delimeter (coma) in it. Can some one help me here. Eg: Txt file: Ram,10/12/72,M Harish Chandra,19/06/77,M Vanitha,20/01/73,F Table name : Test has the following fields Name Text(50) DOB Date Sex Text(1) I would like to insert these records into a test table using a do while loop. Tnx
-
Hi all, I would like to insert datas from a txt file (into a table) which has delimeter (coma) in it. Can some one help me here. Eg: Txt file: Ram,10/12/72,M Harish Chandra,19/06/77,M Vanitha,20/01/73,F Table name : Test has the following fields Name Text(50) DOB Date Sex Text(1) I would like to insert these records into a test table using a do while loop. Tnx
This 3 Functions Would Help ya To Do what ever you wnat. please Pay attention To Remarks ! you know The Sign of The End of String (Data into Text File) Must be "$" ! For Example : Ram,10/12/72,M Harish Chandra,19/06/77,M Vanitha,20/01/73,F $ OR Ram,10/12/72,MHarish Chandra,19/06/77,MVanitha,20/01/73,F$ '******************************************************** If you Wana To See a Compiled Version of this Application Check it Out : http://www.honarejavan.com/upload/convertor.exe 'Read Data From TextFile Dim total As String Public Function WRD(mypath As String) Dim MyLines As String Open mypath For Input As #1 Do While Not EOF(1) Line Input #1, MyLines$ WRD = WRD + MyLines total = total + MyLines Loop Close #1 End Function 'Create a DB ConnectionString For Access DB Function ACC(PTH As String) ACC = "Provider=Microsoft.Jet.OLEDB.4.0.1;" & _ "Persist Security Info=False;" & _ "Data Source=" & PTH End Function 'Create a DB ConnectionString For SQL DB Function fSQL(DBname As String, DBADR As String, UID As String, PWD As String) fSQL = "Provider=MSDASQL;Driver={SQL SERVER};Server=" & DBADR & ";UID=" & UID & ";PWD=" & PWD & ";Database=" & DBname End Function 'processing and insertion Function insert_2DB(STR As String, connstr As String) Dim i, j As Integer 'Counter 'TWord Hold ALL characters Dim TWord(10000), DB_name, DB_Date, DB_gender As String '******************************** Dim Conn As New ADODB.Connection Dim RS As New ADODB.Recordset 'SQL Query Dim strSQL As String strSQL = "SELECT * FROM test" 'Opening DataBase Conn.Open (connstr) With RS .CursorType = 2 .LockType = 3 End With RS.Open strSQL, Conn '******************************** 'Loop For insert characters into arrey For i = 1 To Len(total) TWord(i) = Mid(total, i, 1) Next j = 1 ' First Value of j must Be 1 Do Until (TWord(j) = "$" And Not STR = "") 'Catch The Name From Each Line Do Until TWord(j) = "," DB_name = DB_name + TWord(j) j = j + 1 Loop 'Jump of "," j = j + 1 'Catch The Date From Each Line Do Until TWord(j) = "," DB_Date = DB_Date + TWord(j) j = j + 1 Loop 'Jump of "," j = j + 1 DB_gender = TWord(j) If DB_gender = "m" Or DB_gender = "M" Then DB_gender = "Male" Else DB_gender = "Female" End If 'Ready To Count New Line j = j + 1 'Inserting Data into Database RS
-
This 3 Functions Would Help ya To Do what ever you wnat. please Pay attention To Remarks ! you know The Sign of The End of String (Data into Text File) Must be "$" ! For Example : Ram,10/12/72,M Harish Chandra,19/06/77,M Vanitha,20/01/73,F $ OR Ram,10/12/72,MHarish Chandra,19/06/77,MVanitha,20/01/73,F$ '******************************************************** If you Wana To See a Compiled Version of this Application Check it Out : http://www.honarejavan.com/upload/convertor.exe 'Read Data From TextFile Dim total As String Public Function WRD(mypath As String) Dim MyLines As String Open mypath For Input As #1 Do While Not EOF(1) Line Input #1, MyLines$ WRD = WRD + MyLines total = total + MyLines Loop Close #1 End Function 'Create a DB ConnectionString For Access DB Function ACC(PTH As String) ACC = "Provider=Microsoft.Jet.OLEDB.4.0.1;" & _ "Persist Security Info=False;" & _ "Data Source=" & PTH End Function 'Create a DB ConnectionString For SQL DB Function fSQL(DBname As String, DBADR As String, UID As String, PWD As String) fSQL = "Provider=MSDASQL;Driver={SQL SERVER};Server=" & DBADR & ";UID=" & UID & ";PWD=" & PWD & ";Database=" & DBname End Function 'processing and insertion Function insert_2DB(STR As String, connstr As String) Dim i, j As Integer 'Counter 'TWord Hold ALL characters Dim TWord(10000), DB_name, DB_Date, DB_gender As String '******************************** Dim Conn As New ADODB.Connection Dim RS As New ADODB.Recordset 'SQL Query Dim strSQL As String strSQL = "SELECT * FROM test" 'Opening DataBase Conn.Open (connstr) With RS .CursorType = 2 .LockType = 3 End With RS.Open strSQL, Conn '******************************** 'Loop For insert characters into arrey For i = 1 To Len(total) TWord(i) = Mid(total, i, 1) Next j = 1 ' First Value of j must Be 1 Do Until (TWord(j) = "$" And Not STR = "") 'Catch The Name From Each Line Do Until TWord(j) = "," DB_name = DB_name + TWord(j) j = j + 1 Loop 'Jump of "," j = j + 1 'Catch The Date From Each Line Do Until TWord(j) = "," DB_Date = DB_Date + TWord(j) j = j + 1 Loop 'Jump of "," j = j + 1 DB_gender = TWord(j) If DB_gender = "m" Or DB_gender = "M" Then DB_gender = "Male" Else DB_gender = "Female" End If 'Ready To Count New Line j = j + 1 'Inserting Data into Database RS