Reading text files with ADO

[Yazster]

Hi,

I'm using ADO to connect to a fixed-width(.prn) text file in order to import it into a .MDB database. I've connected to the file and everything works fine. I have my information from the text file in my recordset after performing a query, but I noticed that for each line I'm missing some data that appears at the end of each line in the text file.

Does anybody know why I'm only able to import partial lines?

Any help would be greatly appreciated.
Yazster.

 

[Yazster]

Ok, I've noticed something. For each line of the text file I read, any data after a comma isn't read into the recordset. So basically, anything after a comma is ignored.

Is there any way around this? Unfortunately the text file contains data that may contain a comma, and not necessarily at the same place for each line.

Yazster.

 

[uthamsar]

Could you explain how you are reading each line (parsing the line into different fields, how you are breaking the line). It should not ignore the comma.

 

[Yazster]

Dim cnConnection As ADODB.Connection
Dim rsRecordset As ADODB.Recordset

Set cnConnection = New ADODB.Connection

cnConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=c:\;" & _
"Extended properties='text;HDR=NO;FMT=Delimited'"

Set rsRecordset = New ADODB.Recordset
rsRecordset.Open "select * from MYTEXTFILE.prn", cnConnection, adOpenStatic, adLockReadOnly

While Not rsRecordset.EOF
FIELD1 = Mid(rsRecordset.Fields(0), 4, 7)
FIELD2 = Mid(rsRecordset.Fields(0), 60, 26)
... and this goes on for about 15 fields.
... I then update another database using an "INSERT INTO" query with the FIELD variables.
wend

 

[Chendoe]

I cannot offer much help, but I'm wrestling with the same kind of problem. However, I was using the simple File Open commands to attach to the text file.

According to VB documentation, one SHOULD be able to use Line Input to read ALL characters up to a CRLF, but even that seems to be hiccuping at the comma. I offer that suggestion, but want to add my voice to the "how can I?" chorus.

Before VB, (GWBASIC, BASICA) LINEINPUT was a dead solid way of dealing with comma delimited data, but VB seems to choke a little. Good Luck!

 

[uthamsar]

Reading text files:
I did the following to read a text file and then write into database. Have a common dialog control on the form to have a 'open file dialogbox' or in your case may be you can open the file directly - open "C:\mytextfile.prn" for input as #1

It opens the file for reading then with while condition, it reads until the end of the file, then with 'Line Input #1, sTemp' - it reads one line at a time, and then keeps the contents of that line into sTemp

Then it parses that sTemp into different fields and opens the recordset and writes the contents of those fileds into the table and then reads the next line. This goes on until it reaches the end of the file.
I did not have any problems of not reading commas and the text after that. I hope this helps you.
If you prefer to send me your file, you could do that. My e-mail is ravinuthala@un.org


cdlMain.Filter = "Text Files (*.txt)|*.txt|Data Files (*.dat)|*.dat|All Files|*.*"
cdlMain.ShowOpen
'MsgBox "You have selected " & cdlMain.FileName
Open cdlMain.FileName For Input As #1
While Not EOF(1)
Line Input #1, sTemp
If ((Left(sTemp, 2) <> &quot;14&quot) Then
'I am looking for some code 14 in the beginning
of the line. If it is not 14, then do nothing, skip to the next line
Else
ipos = 10
'sProjNo = Mid(sTemp, ipos, 7)

sFiscalShare = Val(Mid(sTemp, ipos, 2))
ipos = ipos + 2
sSeqNo = Mid(sTemp, ipos, 4)

ipos = ipos + 4
sItemNo = Mid(sTemp, ipos, 11)
sItemNoPre = Left(sItemNo, Len(sItemNo) - 6)
sItemNoPost = Right(sItemNo, 6)
sItemNo = sItemNoPre & &quot;.&quot; & sItemNoPost

ipos = ipos + 12
sUnitMeasure = Mid(sTemp, ipos, 5)

ipos = ipos + 5
sUnitPrice = Mid(sTemp, ipos, 12)
sUnitPricePre = Left(sUnitPrice, Len(sUnitPrice) - 3)
sUnitPricePost = Right(sUnitPrice, 3)
sUnitPrice = sUnitPricePre & &quot;.&quot; & sUnitPricePost
dUnitPrice = Val(sUnitPrice)


ipos = ipos + 13
sAuthQty = Mid(sTemp, ipos, 11)
sAuthQtyPre = Left(sAuthQty, Len(sAuthQty) - 2)
sAuthQtyPost = Right(sAuthQty, 2)
sAuthQty = sAuthQtyPre & &quot;.&quot; & sAuthQtyPost
dAuthQty = Val(sAuthQty)

ipos = ipos + 47
sItemDes = Mid(sTemp, ipos)
If Trim(sItemNo) <> &quot;.&quot; Then
Set rstItem = db.OpenRecordset(&quot;ITEM&quot;, dbOpenDynaset)

rstItem.AddNew
With rstItem
!Seq_Number = sSeqNo
!Item_Number = sItemNo
!Fiscal_Share = sFiscalShare

!Unit_of_Measure = sUnitMeasure
!Unit_Cost = dUnitPrice
!Orig_Auth_Quantity = dAuthQty
!Item_Descr = sItemDes
End With
rstItem.Update
End If

End If
Wend
End Sub

 

[Yazster]

I also found another way to do this, using the SCHEMA.INI file. The file allows you to break the text into separate defined fields, specifying the type and width of the text. Here's an example:

[test2joe.prn]
colnameheader=false
format=FixedLength
CharacterSet=OEM
Col1=&quot;NoGood&quot; Char Width 1
Col2=&quot;Region&quot; Char Width 3
Col3=&quot;Quote&quot; Char width 7
Col4=&quot;Submit Date&quot; Char Width 9
col5=&quot;Submit Time&quot; Char width 9
col6=&quot;NoGood2&quot; char width 3
col7=&quot;Date Quote Due&quot; char width 9
col8=&quot;Loi&quot; char width 2
col9=&quot;Quote Status&quot; char width 17
Col10=&quot;Customer Name&quot; char width 26
col11=&quot;Branch Name&quot; char width 23
col12=&quot;Total Lines&quot; Integer width 6
col13=&quot;Valid Lines&quot; Integer width 6
col14=&quot;To be processed by specialist&quot; Integer Width 7
col15=&quot;% to be processed by specialist&quot; char Width 7
col16=&quot;To be processed by Data Entry&quot; Integer Width 7
col17=&quot;% to be processed by Data Entry&quot; char Width 7
col18=&quot;YTD Sales&quot; Currency Width 12
col19=&quot;Last Year's Sales&quot; Currency Width 12
col20=&quot;CBO&quot; Currency Width 12
col21=&quot;Quote Strategy&quot; char Width 4
col22=&quot;Currency&quot; char Width 4
col23=&quot;Focus Account&quot; char width 4
col24=&quot;CT&quot; char width 2

The first line contains the name of the text file. This file should be named SCHEMA.INI and placed in the same folder as the text file being read.

After connecting to the PRN file using ADO, I run a &quot;SELECT *&quot; query. The fields are automatically created according to the schema file without having to populate them using code. Also, the field types and lengths are also created according to the schema file.

This definitely seems to be the easiest and most efficient way to import fixed-width text files into a database.

Yazster

 

[MichaelRed]

Hmmmmmmmmmmmmmmm,

Probably a bit (lot?) late. Also perhaps somewhat obscure at this level. I offer the following as an alternative to the machinations described above. You would still need to do the assignment statements for the various fields, however IF the information is truly &quot;regular&quot;, the routine returns an array with the number of dimensions the same as the data seperations. This would (aparently) return an array of N * 23, where te &quot;N&quot; is the number of records and the 23 represents the number of (ZERO basesd!!!!!) fields. So, a simple (double) loop would populate the recordset.

'________________________________________________________
'First, A sample text file. You need to &quot;Save&quot; this somewhere and somehow (or create your own thinnggggyyyyyy). This is the original request / input from another user. I saves it as &quot;C:\My Documents\MyCsvFile.Txt&quot;. If you do differently, you NEED to change the TEST program to point to YOUR sample.


123456, 08/15/2001, 2:39:40 AM, 2:41:29 AM, 49 secs
123654, 08/15/2001, 2:49:40 AM, 2:51:29 AM, 49 secs


'________________________________________________________
'Next the Test Program- so you can &quot;see&quot; what it does

Public Function basTstCSV2Array()

    Dim Idx As Long
    Dim Jdx As Long
    Dim Kdx As Long
    Dim Ldx As Long
    Dim FilNme As String
    Dim MyArray As Variant
    
    FilNme = "C:\My Documents\MyCsvFile.Txt"

    MyArray = basCSV2Array(FilNme)
    
    Idx = UBound(MyArray, 1)
    Jdx = UBound(MyArray, 2)

    For Kdx = 0 To Idx
        For Ldx = 0 To Jdx
            Debug.Print MyArray(Kdx, Ldx);
        Next Ldx
        Debug.Print
    Next Kdx

End Function
[/code

'________________________________________________________
'And Finally the Actual code/procedure/function/ ...
[code
Public Function basCSV2Array(fName As String, _
                             Optional RecSep As String = vbCrLf, _
                             Optional FldSep As String = ",") As Variant

    Dim Fil As Integer
    Dim NRecs As Long
    Dim NFlds As Long
    Dim Idx As Long
    Dim J As Long                               'indices
    Dim RawFile As String                       'Holds the entire contents of the file
    Dim RawSplit() As String                    'the file split up on a line per line basis
    Dim OneLine() As String                     'used to hold the tokens for one line
    Dim RptAry() As String                      '2d array. Holds X lines & 0 to 4 elements per line

    Fil = FreeFile                              'get the next free file number

    'This works for large files. I (Troy williams)tried it
    'with a 50 meg file on a computer with 128 Mb of ram and it worked fine.
    'open the file and dump the contents into the rawfile variable

    Open fName For Binary As #Fil               'Open file
    RawFile = String$(LOF(Fil), 32)             'Create "empty" String of Length
    Get #Fil, 1, RawFile                        'Fill "Empty Str with File
    Close #Fil                                  'Close File

    'Get the Nunber of Records and Fields
    RawSplit = Split(RawFile, RecSep)   'Split the file up by lines
    NRecs = UBound(RawSplit) - 1            'Num Lines/Records in the file

    OneLine = Split(RawSplit(0), FldSep)        'Split the first line
    NFlds = UBound(OneLine)             'Get the Number of Fields

    ReDim RptAry(0 To NRecs, 0 To NFlds)

    For Idx = 0 To NRecs
       OneLine = Split(RawSplit(Idx), FldSep)
       For Jdx = 0 To NFlds
          RptAry(Idx, Jdx) = OneLine(Jdx)
       Next Jdx
    Next Idx

    basCSV2Array = RptAry

End Function

MichaelRed
mred@att.net

There is never time to do it right but there is always time to do it over