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

[ectrimm]

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

[HansV]

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.

[ectrimm]

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"""

[HansV]

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

[jscher2000]

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

[ectrimm]

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.

[jscher2000]

<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.

[ectrimm]

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

[jscher2000]

> 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.

[ectrimm]

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.

[HansV]

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

[ectrimm]

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?

[HansV]

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.

[ectrimm]

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.?

[HansV]

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