[AccessD] Reading DIF format (A97 etc)

On 12 Jul 2005 at 11:16, Griffiths, Richard wrote:

> Does anyone have experience of processing/importing DIF (large) files
> into MS Access, Excel, SQL or other?
> 

 Never tried it before, but the DIF format is quite simple and is available 
at http://www.wotsit.org.

Looking at the 2KB DIF specification file from there and saving a simple 
tabular spreadsheet as DIF to check exactly what it looks like, I knocked 
this routine up in a few minutes.

This one is very basic.
1.  You need to define a table with the appropriate fields first.  If you 
wanted, you could extend the function to actually create the table based on 
the Table (name) and Tuple (columns) headers.  You could also make an 
initial pass through the file to try and determine the field types.

2.  This function  just imports into numeric and text fields, but a bit of 
playing with it would allow for dates etc (you'd just need to check 
rs(lngColPointer).Type and parse the data appropriately.


Function ImportDIF(DIFFilename As String, Tablename As String) 
Dim strTemp1 As String
Dim strTemp2 As String
Dim lngColumns As Long
Dim lngFields As Long
Dim lngColPointer As Long
Dim rs As DAO.Recordset
Dim blnFirstRecord As Boolean

Set rs = CurrentDb.OpenRecordset(Tablename)
lngFields = rs.Fields.Count

Open DIFFilename For Input As #1
'headers
Line Input #1, strTemp1
While strTemp1 <> "DATA"
Line Input #1, strTemp1
Select Case strTemp1
   Case "TABLE"
      Line Input #1, strTemp1 '0, 1
     Line Input #1, strTemp1 'Table Name
   Case "VECTORS"  'rows topic
      Line Input #1, strTemp1 '0, row count
      Line Input #1, strTemp1 ' blank
   Case "TUPLES" 'columns topic
      Line Input #1, strTemp1 '0, column count
      lngColumns = Split(strTemp1, ",")(1)
      If lngColumns <> lngFields Then
         MsgBox "Table has " & lngFields & " fields, but DIF field has " & 
lngColumns & " Columns!"
         Close #1
         Exit Function
      End If
      Line Input #1, strTemp1 ' blank
   Case Else ' optional headers
End Select
Line Input #1, strTemp1
Wend
Line Input #1, strTemp1 '0,0
Line Input #1, strTemp1 ' blank

'Data
blnFirstRecord = True
Do
    Line Input #1, strTemp1 'type, numerical value
    Line Input #1, strTemp2 'string value
    Select Case Split(strTemp1, ",")(1)
        Case -1  ' special value
           If strTemp2 = "BOT" Then
              If Not blnFirstRecord Then
                rs.Update
                blnFirstRecord = False
              End If
              rs.AddNew
              lngColPointer = 0 ' new row
           End If
           If strTemp = "EOD" Then Exit Function
        Case 0 ' numeric value in strTemp1
            rs(lngColPointer) = Split(strTemp1, ",")(2)
            lngColPointer = lngColPointer + 1
        Case 1 ' string value in strTemp2
            'trim leading and trailing quotes from string
            strTemp2 = Mid$(strTemp2, 2, Len(strTemp2) - 2)
            rs(lngColPointer) = strTemp2
            lngColPointer = lngColPointer + 1
    End Select
Loop
Close #1
End Function
-- 
Stuart