[RESOLVED] Data From Text File To Database Table

[Paytor]

Hello again!

I have a problem. I have an adodc that is connected to a a table in the database. CommandType -> adCmdTabe...... and then i select the table that i want! The problem is that i have a text file which has important data that i want to store to a table in the database. The order inside the txt file is like this:

1st row-> 05/29/2007 14:43:36 242 6.58 0.98 1660 31.3 2.50 2.58 2.64 2.68

2nd row-> 05/29/2007 14:45:40 240 3.03 0.85 668 31.3 1.19 1.22 1.22 1.23

3rd row->....................


740 row ->...................

I have made a table in SQL server with 11 fields. Can anybody post some kind of code so i can read the file(EOF) and save every data into the proper field without using any array in the client(VB 6)? The fields in the database are with the same order as in the file(date time ........)!

Thanks!

[RhinoBull]

Something like sample below may work for you - just kim that it is not tested.

Private Sub Command1_Click()
'================================
Dim strConString As String
Dim adoConn As ADODB.Connection
Dim strLine As String
Dim arValues() As String
Dim i As Integer
Dim strSql As String

    strConString = "valid connection string goes here"
    Set adoConn = New ADODB.Connection
    adoConn.Open strConString
    
    Open some_file For Input As #1
        Line Input #1, strLine
        arValues() = Split(strLine, Space(1))
        
        strSql = "Insert Into MyTable" & vbNewLine
        strSql = strSql & "    (Field1, Field2, Field3, Field4)" & vbNewLine 'and so on for each remaining field
        strSql = strSql & "Values" & vbNewLine
        strSql = strSql & "    (" & vbNewLine
        strSql = strSql & "    '" & arValues(0) & "'," & vbNewLine 'date
        strSql = strSql & "    '" & arValues(1) & "'," & vbNewLine 'time
        strSql = strSql & "     " & arValues(2) & "," & vbNewLine 'first numeric value
        strSql = strSql & "     " & arValues(3) & "" & vbNewLine  'next numeric value
        'and so on for each remaining value
        strSql = strSql & "    )"
        
        adoConn.Execute strSql
        
    Close #1
    
    adoConn.Close
    Set adoConn = Nothing

End Sub

[Paytor]

Thanks i will try it and let you know! If someone has another example please post it.

[gavio]

That's quite good, what RB posted

Except, you should be using FreeFile() instead of fixed numbers when working with files. Eg:

Dim intFF As Integer
 
intFF = FreeFile
 
Open some_file For Input As #intFF
'...

[Paytor]

I have some problems with the code you posted. In the arValues(0) i have the first date ->05/29/2007 but then in the arValues(1) i have all the rest ->14:43:36 242 6.58 0.98 1660 31.3 2.50 2.58 2.64 2.68. And then i also get a message subscript out of range in the arValues(2). I aslo tryed to store only the date without any other number but that didn't workef either.

What do you think should i change in the code??? Any ideas??

This is how it should be stored in the database table:

Date Time Voltage Amber.................and so on
05/29/2007 14:43:36 242 6.58

Every data in it's proper field!

Thanks!

[RhinoBull]

If data in the file is as you presented (all fields are separated by 1 space character) then it should work.
However, I cannot guarantee for anything other than what was presented in this thread.

[Paytor]

I undertand. The date and time is seperated by 1 space character but all other are separated by 4 and 5 space characters. Isn't there any way that i can make it work?

Thanks!

[RhinoBull]

What creates that file? Layout must be consistent or you will need to parse each line individually.

[Paytor]

I'm converting a hexadecimal packet of data into decimal. I'm trying to make the space bettwen the fields with 1 space character. If you have any other idea please tell me. i 'll let you know!

Thanks!

[Paytor]

I HAve created a file with eleven numbers (1 2 3 4 5 6 7 8 9 10 11) and made it this way. Why does it not work??? I get some strange error"multiple steps error or something" Strange?? Any ideas?

Dim strLine As String
Dim arValues() As String
Dim i As Integer
 
ReDim arValues(0 To 10)
 
MsgBox (Adodc1.Recordset.Fields.Count)
    
    Open "C://One.txt" For Input As #1
        Line Input #1, strLine
        arValues() = Split(strLine, Space(1))
    
    With Adodc1
       .Recordset.AddNew
      
      For i = 1 To 11
        .Recordset.Fields(i) = arValues(i - 1)
      Next i
    
       .Recordset.Update
    End With
    
    Close #1

[RhinoBull]

Why does it not work???

Because you are not doing it the same way I showed you.
And I never use data controls.

[Paytor]

So the way i tryed to do it is wrong. Ok let's leave it. I used your code with the simple text file(1 2 3 4 5 6 7 8 9 10 11) that i made and it still doesn't work. All have one space character between them. What is there to change?

Thanks!

[RhinoBull]

What is there to change?

I don't know unless you show us what you did...

[Paytor]

Nothing much because i don't undertand the code very well. This is what i did:

Dim strConString As String
Dim adoConn As ADODB.Connection
Dim strLine As String
Dim arValues() As String
Dim i As Integer
Dim strSql As String

    strConString = Adodc1.ConnectionString
    Set adoConn = New ADODB.Connection
    adoConn.Open strConString
    
    Open "C://One.txt" For Input As #1
        Line Input #1, strLine
        arValues() = Split(strLine, Space(1))
        
        strSql = "Insert Into MyTable" & vbNewLine
        strSql = strSql & "    (Field1, Field2, Field3, Field4)" & vbNewLine 'and so on for each remaining field
        strSql = strSql & "Values" & vbNewLine
        strSql = strSql & "    (" & vbNewLine
        strSql = strSql & "    '" & arValues(0) & "'," & vbNewLine 'date
        strSql = strSql & "    '" & arValues(1) & "'," & vbNewLine 'time
        strSql = strSql & "     " & arValues(2) & "," & vbNewLine 'first numeric value
        strSql = strSql & "     " & arValues(3) & "" & vbNewLine  'next numeric value
        strSql = strSql & "     " & arValues(4) & "," & vbNewLine
        strSql = strSql & "     " & arValues(5) & "," & vbNewLine
        strSql = strSql & "     " & arValues(6) & "," & vbNewLine
        strSql = strSql & "     " & arValues(7) & "," & vbNewLine
        strSql = strSql & "     " & arValues(8) & "," & vbNewLine
        strSql = strSql & "     " & arValues(9) & "," & vbNewLine
        strSql = strSql & "     " & arValues(10) & "," & vbNewLine
        'and so on for each remaining value
        strSql = strSql & "    )"
        
        adoConn.Execute strSql
        
    Close #1
    
    adoConn.Close
    Set adoConn = Nothing

[RhinoBull]

OK,
Field1, Field2, Field3, Field4 must be replaced with actual fields names that you created (you said you have 11 of them).

arValues(3) & "" >> should really be arValues(3) & ","

The very last value in the sql should not end with comma so replace what you have with this:

arValues(10) & vbNewLine

Although vbNewLine is not necessry I personally prefer to have it - it makes final sql much more readable when it needs to be debugged.

[Paytor]

I will make the changes and see what happens! I'll let you know!

Thanks!

[Paytor]

Yes it works with the simple text file! Very Good! Now i will see with the other one that has 4 and 5 spaces between the fields. Let's see what happens.

[Paytor]

Yes the problem is now with the spaces. It doesn't work with the important text file. Is there any way to replace spaces of 2 or bigger with 1 space character in the strLine so that the strLine contains it in the proper form???

[RhinoBull]

You can try using Replace function to replace Space(2) (and so Space(3 or 4)) with Space(1) few times before string finally gets splitted on a single space.
You can even do that in the loop:

Dim i%

        Line Input #1, strLine
        
        For i = 1 To 10
            strLine = Replace(strLine, Space(2), Space(1))
        Next i
        
        arValues() = Split(strLine, Space(1))
        '...

[Paytor]

Oh i made a mistake. Between the date and time there is 1 space character. But between all others there is a Tab space between them and not space characters! Sorry! How can i make it work???

[RhinoBull]

As I said in my previous post use Replace function:

Line Input #1, strLine
strLine = Replace(strLine, vbTab, Space(1))

arValues() = Split(strLine, Space(1))

[Paytor]

Ok it works! If i have any other problem i will post it here!

Thanks a lot for your help!

[RhinoBull]

No problem. Do you feel like it's time to resolve this thread?