#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2012
    Posts
    6
    Rep Power
    0

    Import text file to access using VBScript


    Hi,
    I am new to vbscript and need some help with importing text file to access. Please can anyone check my code and let me know if I am missing anything?



    Dim aFile, FSO1, OpenFile, X,conn,Odbcconn, rs, sql, line
    Dim arr(7)
    aFile = "C:\temp\Transaction.txt"
    Set FSO1 = CreateObject("Scripting.FileSystemObject")
    If FSO1.FileExists(aFile) Then
    Set x = FSO1.OpenTextFile(aFile,1, True)
    msgbox "Yes!!"
    else
    msgbox "File not Found"
    End If
    Odbcconn = "DSN=sales;UID=;PWD="
    Set conn= CreateObject("ADODB.Connection")
    conn.Open Odbcconn
    SET rs = createObject("adodb.recordset")
    rs.Open "SELECT * FROM sales_data", conn, 3, 3
    Do Until x.AtEndOfLine
    line = x.ReadLine
    arr(7) = Split(line, " ")
    rs.AddNew
    rs("Date") = arr(0)
    rs("Customer_Name") = arr(1)
    rs("Location") = arr(2)
    rs("Invoice#") = arr(3)
    rs("Shipment#") = arr(4)
    rs("Item#") = arr(5)
    rs("Amount") = arr(6)
    rs.update
    Loop
    rs.Close
    conn.Close
    Set conn = Nothing
    msgbox "completed"
    WScript.Quit(0)

    Thank you!
  2. #2
  3. No Profile Picture
    Grumpier old Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Jun 2003
    Posts
    14,439
    Rep Power
    4539
    Does the code not work? Do you get errors?
    ======
    Doug G
    ======
    Bartender to Rene Descartes "have another beer?" Descartes: "I think not" and he vanished.
    --Alfred Bester
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2012
    Posts
    6
    Rep Power
    0
    Originally Posted by Doug G
    Does the code not work? Do you get errors?
    It works but it inserts empty lines instead of data in the text file. But if you write the value instead of arr(0)… it will work fine..

    Eg: rs("Date") = 06/01./2012
    rs("Customer_Name") = “Angel”

    It will work fine but if I replace date and name with arr(0) and arr(1) it will insert empty lines…

    No, I don’t get any errors
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2011
    Posts
    289
    Rep Power
    45
    Your problem is with the line:
    arr(7) = Split(line, " ")
    The array does not need to be dimensioned:
    Dim arr()
    arr = Split(line, " ")

    It will dimension itself. I also recommend using a better separator than a space unless you are error checking the text file for spaces before processing. Encountering a space in the text words will cause problems.

    J.A. Coutts

    Comments on this post

    • medialint agrees : Good eye
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2012
    Posts
    6
    Rep Power
    0
    Hi,
    If I use Dim arr()
    arr = Split(line, " ")
    I get the error Type Mismatch… on line arr = Split(line, " ")

    Please advise…

    Below is how my Text file looks like that’s why I was using space as a separator. Please advise…


    6/02/2012 11.1 11.1 111111 AAAAAD23DD AAAAAD23DD AA11111 11111
    N 1 2491669 0/02/2012 1 100101DD I I 10.8 0 0 0 1.11 0 0 0 0 0 11.11

    Thank you!
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2011
    Posts
    289
    Rep Power
    45
    I duplicated your code in VB6:
    Code:
    Private Sub Command1_Click()
        Dim aFile As String
        Dim FSO1 As Object
        Dim OpenFile As String
        Dim x As Object
        Dim conn As Object
        Dim Odbcconn As String
        Dim rs As Object
        Dim sql As String
        Dim line As String
        Dim arr() As String
        aFile = "C:\temp\Transaction.txt"
        Set FSO1 = CreateObject("Scripting.FileSystemObject")
        If FSO1.FileExists(aFile) Then
        Set x = FSO1.OpenTextFile(aFile, 1, True)
        MsgBox "Yes!!"
        Else
        MsgBox "File not Found"
        End If
        Odbcconn = "DSN=sales;UID=;PWD="
        Set conn = CreateObject("ADODB.Connection")
        conn.Open Odbcconn
        Set rs = CreateObject("adodb.recordset")
        rs.Open "SELECT * FROM sales_data", conn, 3, 3
        Do Until x.AtEndOfLine
            line = x.ReadLine
            arr = Split(line, " ")
            rs.AddNew
            rs("Date") = arr(0)
            rs("Customer_Name") = arr(1)
            rs("Location") = arr(2)
            rs("Invoice#") = arr(3)
            rs("Shipment#") = arr(4)
            rs("Item#") = arr(5)
            rs("Amount") = arr(6)
            rs.Update
        Loop
        rs.Close
        conn.Close
        Set conn = Nothing
        MsgBox "completed"
        'WScript.Quit (0)
    End Sub
    VBScript works with variants, so try dimensioning arr without the brackets.

    I only mentioned the space separator because you can get in trouble using it. The comma is more commonly used, but it can also cause trouble if you don't error check for it when creating the text file. I often use the "|" character, or a control character such as "&H1F".

    J.A. Coutts

    Edit: You also might want to check the validity of the text line by checking the number of elements in the array "ubound(arr)".
    Last edited by couttsj; June 3rd, 2012 at 01:33 AM.
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2011
    Posts
    289
    Rep Power
    45
    Yup! When I tried your code as a script, it worked just fine after I removed the brackets on both the dimension statement and the split command. Variants are stored as a pointer using 16 bytes. It can be any type of variable, and it doesn't have to be determined until it is used. The split command simply changes the the pointer type to a string array and assigns a new memory location for it. Not very efficient, but easy to use. Scripts are generally not very complex, so efficiency is not a major concern.

    J.A. Coutts
  14. #8
  15. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2012
    Posts
    6
    Rep Power
    0
    Nope! Its not working for me... I remove the brackets i get the error message saying type mismatch...

    I tried using TransferText Method and it does the same thing... inserts blank rows. Please can you check my code and let me know if I am missing anything?


    Code:
    Set A = CreateObject("Access.Application")  
    A.OpenCurrentDatabase ("C:\test.mdb")  
    A.DoCmd.TransferText acImportFixed, "Spec name", "test", "C:\\test.txt"
    Set A = Nothing 
    msgbox "Done!"
    Thank you!
  16. #9
  17. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2011
    Posts
    289
    Rep Power
    45
    I am not familiar with the Transfertext command, and I get an error saying "Spec name does not exist". But the real issue is the fact that you are not filling the array. Try this simple script:
    Code:
    Dim aFile, FSO1, X, line
    Dim arr
    aFile = "C:\temp\Transaction.txt"
    Set FSO1 = CreateObject("Scripting.FileSystemObject")
    If FSO1.FileExists(aFile) Then
      Set x = FSO1.OpenTextFile(aFile,1, True)
      line = x.ReadLine
      arr = Split(line, " ")
      Msgbox ubound(arr) + 1 & " variables found!"
    else
      Msgbox "File not Found"
    End If
    WScript.Quit(0)
    It works on my system, so if it doesn't work for you, there must be a difference in the scripting engine.

    J.A. Coutts
  18. #10
  19. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2012
    Posts
    6
    Rep Power
    0
    It worked!! I think you're correct i'm not filling the array... But then how do i do that. You said earlier that you tried my code in vbscript without Brackets and it worked... Can you please post the code so that i can compare...

    Thank you so much for all your help...
  20. #11
  21. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2011
    Posts
    289
    Rep Power
    45
    I took the liberty of adding a few things:
    Code:
    Dim aFile, FSO1, X, Odbcconn, conn, rs
    Dim arr
    aFile = "C:\temp\Transaction.txt"
    Set FSO1 = CreateObject("Scripting.FileSystemObject")
    If FSO1.FileExists(aFile) Then
      Set x = FSO1.OpenTextFile(aFile,1, True)
    else
      Msgbox "File not Found"
    End If
    Odbcconn = "DSN=sales;UID=;PWD="
    Set conn = CreateObject("ADODB.Connection")
    conn.Open Odbcconn
    SET rs = createObject("adodb.recordset")
    rs.Open "SELECT * FROM sales_data", conn, 3, 3
    Do Until x.AtEndOfLine
      arr = Split(trim(x.ReadLine), " ")
      if ubound(arr) = 6 then
        rs.AddNew
        rs("Date") = arr(0)
        rs("Customer_Name") = arr(1)
        rs("Location") = arr(2)
        rs("Invoice#") = arr(3)
        rs("Shipment#") = arr(4)
        rs("Item#") = arr(5)
        rs("Amount") = arr(6)
        rs.update
        Msgbox arr(1) & " added to database!"
      else
        Msgbox ubound(arr) + 1 & " variables found!"
      end if
    loop
    WScript.Quit(0)
    I added the trim command to take care of any leading or trailing spaces, and code to verify the number of variables found.

    J.A. Coutts
  22. #12
  23. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2012
    Posts
    6
    Rep Power
    0
    It’s working but not adding any data in Access database… I think it has something to do with my text file format - fixed length…

    Let me know what do you think?
  24. #13
  25. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2011
    Posts
    289
    Rep Power
    45
    Originally Posted by angel007
    It’s working but not adding any data in Access database… I think it has something to do with my text file format - fixed length…

    Let me know what do you think?
    Are you getting the message:
    X variables found!
    If so, then it is not finding the correct number of variables in the text file. If 7 variables are found, then it will display the customer name and add the record to the database. This is the test file I was using:
    06/01/2012 Customer Anywhere 12345 123 10 10.00

    with the second line containing a single space to test both scenarios.

    J.A. Coutts

IMN logo majestic logo threadwatch logo seochat tools logo