1 Attachment(s)
Reading text file to the database
I have a text file that need to be read to database. now i can read the text file to database. But my problem is the text file will be in the specific folder. so i must get the folder first then read the text file. it is a automatic process when i run the program it will read the text file to database. this is my code so far:
Code:
Dim blnConnect As Boolean
Const BIF_RETURNONLYFSDIRS = &H1 'Only file system directories
Const MAX_PATH = 260
Private Declare Sub CoTaskMemFree Lib "ole32.dll" (ByVal hMem As Long)
Private Declare Function lstrcat Lib "KERNEL32" Alias "lstrcatA" (ByVal lpString1 As String, ByVal lpString2 As String) As Long
'Private Declare Function SHBrowseForFolder Lib "shell32" (lpbi As BrowseInfo) As Long
Private Declare Function SHGetPathFromIDList Lib "shell32" (ByVal pidList As Long, ByVal lpBuffer As String) As Long
Dim FN As Long
Private name1 As String
Private MyPath As String
Dim strFileName As String
Dim strLogPath As String
Dim strField() As String
Dim intFileNum As Double
Dim f_year As String
Dim f_day As String
Dim f_day_month As String
Dim new_date As String
Private Sub cmdImport_Click()
Dim cn As ADODB.Connection
Dim rs As New ADODB.Recordset
Dim strSQL As String
Dim Data(11) As String
Dim Fields As Integer
Dim InFile As Integer
Dim InLine As String
InFile = FreeFile
Open App.Path & "\X65_Rectifier_UK_100110_Cav1.txt" For Input As #InFile
If Not EOF(InFile) Then
Line Input #InFile, InLine
End If
While Not EOF(InFile)
Line Input #InFile, InLine
Data(0) = Mid(InLine, 1, 10)
Data(1) = Mid(InLine, 26, 9)
Data(2) = Mid(InLine, 51, 10)
Data(3) = Mid(InLine, 76, 8)
Data(4) = Mid(InLine, 101, 12)
Data(5) = Mid(InLine, 126, 17)
Data(6) = Mid(InLine, 151, 23)
Data(7) = Mid(InLine, 176, 20)
Data(8) = Mid(InLine, 201, 21)
Data(9) = Mid(InLine, 226, 23)
Data(10) = Mid(InLine, 251, 20)
Data(11) = Mid(InLine, 276, 6)
Fields = UBound(Data())
strSQL = "Insert into tb_X65 (date_test,time_test,test_time,varient,serial_no,pre_power_up,input_current_rms,output_voltage,load_off_current,discharge_voltage,discharge_time,status) " & _
"VALUES ('" & Format(Data(0), "m/d/yyyy") & "', " & _
"'" & Format(Data(1), "hh:mm") & "', " & _
"'" & Data(2) & "', " & _
"'" & Data(3) & "', " & _
"'" & Data(4) & "', " & _
"'" & Data(5) & "', " & _
"'" & Data(6) & "', " & _
"'" & Data(7) & "', " & _
"'" & Data(8) & "', " & _
"'" & Data(9) & "', " & _
"'" & Data(10) & "', " & _
"' " & Data(11) & "')"
my_db1.Execute strSQL
Wend
Close #InFile
end sub
Private Sub Form_Load()
Call DatabaseConn2
today_date = Format(Now, "ddMMyyyy")
Call Timer1_Timer
end sub
Private Sub Timer1_Timer()
cmdImport_Click
End Sub
Module1
Public my_db1 As New ADODB.Connection
Public Sub DatabaseConn2()
Set my_db1 = New ADODB.Connection
my_db1.ConnectionString = "driver={SQL Server};" & _
"server=MISXPP16;uid=;pwd=;database=master"
my_db1.CursorLocation = adUseClient
my_db1.ConnectionTimeout = 0
my_db1.CommandTimeout = 0
my_db1.Open
End Sub
I keep the folder path in config.ini file. Now i want to call the path in form loading. something like this
InFile = FreeFile
'Open App.Path & "\config.ini" For Input As InFile
'If Not EOF(InFile) Then
' Line Input #InFile, InLine
' End If
but im not sure about this can any one help me? i attach my text file
In side config.ini file will be like this:C:\X65_Rectifier_Test_Software
Re: Reading text file to the database
Welcome to the forums! :wave:
Please remember when posting code, you have to place it inside [CODE] and [/CODE] Tags.
Re: Reading text file to the database
You can use the API function GetPrivateProfileString to read a named entry from an .ini file.
You find detailed descriptions here:
http://allapi.mentalis.org/apilist/G...leString.shtml
You declare the function GetPrivateProfileSring and then call it to read a certain string which contains your path name from the ini file.
You can read the examples how to use this function. If you don't get along with the usage, we surely can help.
Re: Reading text file to the database
Hai WoF,
You mean declare the function GetPrivateProfileSring like how i declare other function is it?
But after that i dont know how to call that function to read text file from specifie folder? Can you help me? Thank you
Re: Reading text file to the database
There should be lots of samples on the net but if you only need the one value set in the file you could use a plain text file and use the line input method to read the path from the file as well.
Re: Reading text file to the database
For Example using the bit of code you provided.
Code:
InFile=Freefile
Open App.Path & "\config.txt" For Input As InFile
If Not EOF(InFile) Then
Line Input #InFile, DataFilePathName
else
'Nothing in file
End If
close #InFile
Assuming the first line of your file contains the full path/filename and that you call this before you attempt to read the main data file you should be fine.
That said it is good to know how to use the INI Read/Write functions as they can come in quite handy from time to time.
Re: Reading text file to the database
Look at the sample code at the link I provided. This is how to declare the function and how to write a small wrapping function to simplify the use of it.
Code:
Private Declare Function GetPrivateProfileString Lib "kernel32" Alias "GetPrivateProfileStringA" ( _
ByVal lpApplicationName As String, ByVal lpKeyName As Any, _
ByVal lpDefault As String, ByVal lpReturnedString As String, _
ByVal nSize As Long, ByVal lpFileName As String _
) As Long
Public Function ReadIni(Filename As String, Section As String, Key As String) As String
Dim RetVal As String * 255, v As Long
v = GetPrivateProfileString(Section, Key, "", RetVal, 255, Filename)
ReadIni = Left(RetVal, v - 1)
End Function
What you do then is:
Code:
MyPath = ReadIni(app.path & "\config.ini","SectionName", "ThePath")
"Section name is the name of the section of the inifile. Sections are the names in [].
"ThePath" must be the string which identifies the line like
[SectionName]
...
ThePath=C:\Thispath
