Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    2 Star Lounger
    Join Date
    Mar 2004
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    ADO/BAI2 File (VBA/ADO/2k3)

    What connection string can I use to pull a .BAI2 file into a recordset? Thanks.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: ADO/BAI2 File (VBA/ADO/2k3)

    I can't find the specification for .BAI2, but it seems to be some kind of comma-delimited text format. If you know the specification, you should be able to use any of the methods to read a text file.

    If you'd like a finished solution, you might contact either the contractor or the programmer of Rent A Coder - BAI2 Text file conversion using VBA or VBSCRIPT.

  3. #3
    2 Star Lounger
    Join Date
    Mar 2004
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ADO/BAI2 File (VBA/ADO/2k3)

    Okay, I need a bit of guidence here. Should either of the following work?

    cnn.Open _
    "Driver={Microsoft Text Driver (*.txt; *.csv};" & _
    "Dbq=CATA PATH" & _
    "Extensions=asc,csv,tab,txt,bai2;" & _
    "HDR = No"

    or

    cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=CATA PATH" & _
    "Extended Properties=""text;HDR=No;FMT=Delimited"""

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: ADO/BAI2 File (VBA/ADO/2k3)

    I'm sorry, I have no way of testing this - I have never seen a .BAI2 file. Have you tried it yourself?

  5. #5
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: ADO/BAI2 File (VBA/ADO/2k3)

    After the word PATH you do not have any delimiter before the word Extended. Fixing that might help.

  6. #6
    2 Star Lounger
    Join Date
    Mar 2004
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ADO/BAI2 File (VBA/ADO/2k3)

    Yes. In the first case, I get a "Data Source Name Not Found And No Default Driver Specified" error and in the second I get "Database Or Object Is Read Only". I am not sure what is going on. The BAI2 file is just a comma delimited file. If I open it in Excel it behaves just like a text file. I would include an example, but the file contains a lot of account numbers.

  7. #7
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: ADO/BAI2 File (VBA/ADO/2k3)

    <P ID="edit" class=small>(Edited by jscher2000 on 07-Jan-05 13:32. Uhhh...)</P>What if you go back to <post#=390754>post 390754</post#> and <post#=391113>post 391113</post#> in your thread from last Summer and start with that syntax. Does ADO really care that the file name is DATA.BAI2 rather than DATA.CSV?

    Added: After looking at Hans' link, the data looks quite odd. I'm not sure it really is row-and-column data after all. You might need to read each line as text, figure out what it means, and then put it in your database.

  8. #8
    2 Star Lounger
    Join Date
    Mar 2004
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ADO/BAI2 File (VBA/ADO/2k3)

    That might explain why the code differentiates between .csv and .bai2 extensions.

  9. #9
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: ADO/BAI2 File (VBA/ADO/2k3)

    > That might explain why the code differentiates between .csv and .bai2 extensions.

    Which code?

    The part the troubles me is that there are not the same number of "fields" (comma-separated values) on each line in the screen shot from the Rent-A-Coder site. This suggests that the information cannot properly be thought of as a table in the manner that ADO expects. You might want to visit that BAI web site and look at their documentation.

  10. #10
    2 Star Lounger
    Join Date
    Mar 2004
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ADO/BAI2 File (VBA/ADO/2k3)

    The ADO code above. I assume for the reason you state that I can't simply treat the bai file as a text file.

    Would it be possible for me to pull the data into an array and then pull out only the parts of the data that I need? For example:

    Function BAItoArray()
    Dim fileNum As Integer
    Dim fileContents As String

    Open ThisWorkbook.Path & "Bank" For Input As #1
    fileContents = Input$(LOF(fileNum), #1)
    Close #f1
    Debug.Print fileContents

    End Function

    This will pull the bai contents into an array. However, I don't exactly know how to pull out the particular elements or data points that I need from this array.

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: ADO/BAI2 File (VBA/ADO/2k3)

    Take a look at the Split function in the VBA help. Split can be used to create an array from a string with delimiters:

    Dim varArray As Variant
    Dim i As Integer
    varArray = Split(fileContents, ",")
    For i = LBound(varArray) To UBound(varArray)
    ' do something with varArray(i) here
    Next i

  12. #12
    2 Star Lounger
    Join Date
    Mar 2004
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ADO/BAI2 File (VBA/ADO/2k3)

    Thanks. I was working on the Split function yesterday, but what I need to do, instead of pulling the entire line of data, is to pull a specific value from a specific line of the data. For example, I can use the Left function to pull the lines out of the file.

    If Left(fileContents, 2) = "03" Then
    strArray = Split(fileContents, ",")
    For i = LBound(strArray) To UBound(strArray)
    Debug.Print strArray(i);
    Next i
    debug.print

    This works because the first two digits, for the lines I need, always start with "03". The Split function takes out the commas and makes the data one line.

    Luckly, though, the values ($ values) are the same number of commas from the start of the line (not always the same number of spaces though since the $ values can be different), and I expect this to always be the case even though other lines in the file can contain a different number of commas. Is there any way that I can specify, after the "If" statement, that the array only contain the value of the data line that's after say comma 8 for example? Or, is there some other way to code what I want to do?

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: ADO/BAI2 File (VBA/ADO/2k3)

    You don't really have to specify the number of elements of the array. You can use

    If Left(fileContents, 2) = "03" Then
    strArray = Split(fileContents, ",")
    NeededValue = strArray(8)
    ...
    End If

    or even

    If Left(fileContents, 2) = "03" Then
    NeededValue = Split(fileContents, ",")(8)
    ...
    End If

    If you ever need it, it is possible to limit the number of elements retrieved by Split - look up Split in the VBA help; you will see that it has an optional argument Limit.

  14. #14
    2 Star Lounger
    Join Date
    Mar 2004
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ADO/BAI2 File (VBA/ADO/2k3)

    Thanks. Everything works like I need it to. One last question though. The $ value that I'm pulling in needs to include a decimal in the second place from the right. That is, I'm pulling a number such as 23224 that needs to be 232.24. I've tried the Format functions, but they don't seem to work. Do you have any suggestions.?

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: ADO/BAI2 File (VBA/ADO/2k3)

    Divide the number by 100 before putting it in the spreadsheet.

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •