Reading text file to the database

[kayatri]

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:

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

[HanneSThEGreaT]

Welcome to the forums!

Please remember when posting code, you have to place it inside [CODE] and [/CODE] Tags.

[WoF]

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.

[kayatri]

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

[DataMiser]

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.

[DataMiser]

For Example using the bit of code you provided.

 
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.

[WoF]

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.

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:

  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