1 Replies - 13552 Views - Last Post: 02 February 2010 - 07:31 PM Rate Topic: -----

#1 kayatri  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 216
  • Joined: 05-May 09

read text file and insert data to sql server database

Posted 02 February 2010 - 04:11 AM

i want to read the text file and select data from it and insert in to the sql database.

this should happen automatically when user run the program. the path of the text file will be stored in config file. now the programm is running but it not write data 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

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 fso As New FileSystemObject
'Private fldr As Folder
'Private subfldr As Folder

Private Sub cmdImport_Click()
Dim cn As ADODB.Connection
Dim rs As New ADODB.Recordset
Dim strSQL As String

    
strFileName = Dir(strLogPath & "*.*")

If strFileName = "" Then Exit Sub


While (strFileName <> "")

    Set cn = New ADODB.Connection
    cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                        "Data Source=" & strLogPath & ";" & _
                        "Extended Properties=""text;HDR=YES;FMT=FixedLength"""
    cn.Open
    
    
    rs.Open "Select * from " & strFileName, cn, adOpenStatic, adLockReadOnly, adCmdText
     
     
    If rs.RecordCount > 0 Then
        Do While Not rs.EOF
                    
            strSQL = "Insert into tb_X65 (date_test,time_test,test_time,spec,serial_no,cavity,high_voltage_dc,v_15v_supply,v_5v_supply, " _
                    & " standby_power,software_variant,software_version,S4_pressed_voltage,S4_released_voltage,LED_D7_intensity, " _
                    & " LED_D7_hue_value,S2_ccw_stage1,S2_ccw_stage2,S2_ccw_stage3,S2_ccw_stage4,BLDC_motor_speed_min,S2_cw_stage1, " _
                    & " S2_cw_stage2,S2_cw_stage3,S2_cw_stage4,BLDC_motor_speed_max,input_current,S5_pressed_voltage,S5_released_voltage, " _
                    & " osc_motor_on_volt,osc_motor_on_current,osc_motor_off_volt,osc_motor_off_current,ir_header,ir_command,ocp_trip_time,status) values " _
                    & " ('" & Trim(rs("Date")) & "', '" & Trim$(rs("Time")) & "', '" & Trim$(rs("Test Time")) & "', '" & Trim$(rs!Spec) & "', '" & Trim(rs!SN) & "', " _
                    & " '" & Trim(rs!Cavity) & "', '" & (rs("High Voltage DC (V)")) & "', '" & (rs("15V Supply (V)")) & "', '" & (rs("5V Supply (V)")) & "', " _
                    & " '" & (rs("Standby Power (W)")) & "', '" & Trim$(rs("Software Variant")) & "', '" & Trim$(rs("Software Version")) & "', " _
                    & " '" & (rs("S4 Pressed Voltage (V)")) & "', '" & (rs("S4 Released Voltage (V)")) & "', '" & (rs("LED (D7) Intensity")) & "', " _
                    & " '" & (rs("LED (D7) HUE Value")) & "', '" & (rs("S2 CCW - Stage1 (V)")) & "', '" & (rs("S2 CCW - Stage2 (V)")) & "', " _
                    & " '" & (rs("S2 CCW - Stage3 (V)")) & "', '" & (rs("S2 CCW - Stage4 (V)")) & "', '" & (rs("BLDC Motor Speed Min (rpm)")) & "', " _
                    & " '" & (rs("S2 CW - Stage1 (V)")) & "', '" & (rs("S2 CW - Stage2 (V)")) & "', '" & (rs("S2 CW - Stage3 (V)")) & "', " _
                    & " '" & (rs("S2 CW - Stage4 (V)")) & "', '" & (rs("BLDC Motor Speed Max (rpm)")) & "', '" & (rs("Input Current (A)")) & "', " _
                    & " '" & (rs("S5 Pressed Voltage (V)")) & "', '" & (rs("S5 Released Voltage (V)")) & "', '" & (rs("Oscillation Motor On Voltage (V)")) & "', " _
                    & " '" & (rs("Oscillation Motor On Current (mA)")) & "', '" & (rs("Oscillation Motor Off Voltage (V)")) & "', '" & (rs("Oscillation Motor Off Current (mA)")) & "', " _
                    & " '" & (rs("IR Header")) & "', '" & (rs("IR Command")) & "', '" & (rs("OCP Trip Time (ms)")) & "', '" & Trim$(rs("Status")) & "')"
                    
            my_db1.Execute strSQL
    
            rs.MoveNext
        Loop
    End If
    
    rs.Close
    cn.Close
    Set cn = Nothing
    
    strFileName = Dir$()
    
    Call housekeep
Wend


End Sub

Private Sub Form_Load()
Dim FN As Long


Call DatabaseConn2

today_date = Format(Now, "ddMMyyyy")

'==== Read from *.ini file =====
FN = FreeFile
Open App.Path & "\config.ini" For Input As FN
  
Line Input #FN, strLogPath
'===============================

Call Timer1_Timer

End Sub

Private Sub Timer1_Timer()
    
cmdImport_Click

End Sub

Private Sub housekeep()

Dim sTemp As String

On Error GoTo error1
strFileName = Dir(strLogPath & "*.*")
intFileNum = FreeFile

If strFileName = "" Then
    Exit Sub
End If


strField() = Split(strFileName, ".")

f_year = Trim$(Right(strField(0), 2))
f_date = Trim$(Right(strField(0), 6))
f_day_month = Trim$(Left(f_date, 4))

new_date = f_day_month + "20" + f_year


Set fldr = fso.GetFolder(strLogPath)
For Each subfldr In fldr.SubFolders

    Call FileCopy(strLogPath & strFileName, strLogPath & "Uploaded\" & strFileName)
    Kill strLogPath & strFileName
    strFileName = Dir$()
    Exit Sub

Next

strFileName = Dir$()

error1:

End Sub



databse connection defined in module

Public my_db1 As New ADODB.Connection

Public Sub DatabaseConn2()

    Set my_db1 = New ADODB.Connection
    my_db1.ConnectionString = "driver={SQL Server};" & _
        "server=dflvck1s;uid=;pwd=;database=DYSON"
    my_db1.CursorLocation = adUseClient
    my_db1.ConnectionTimeout = 0
    my_db1.CommandTimeout = 0
    my_db1.Open
    
End Sub



my text file is attach here

Attached File(s)



Is This A Good Question/Topic? 0
  • +

Replies To: read text file and insert data to sql server database

#2 thava  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 180
  • View blog
  • Posts: 1,607
  • Joined: 17-April 07

Re: read text file and insert data to sql server database

Posted 02 February 2010 - 07:31 PM

what about your config file
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1