[AccessD] Reading DIF format (A97 etc)

Actually, Split(), Join(),etc., are VBA 6 functions, which makes them available to Access 2000 and later.

Charlotte Foust


-----Original Message-----
From: Heenan, Lambert [mailto:Lambert.Heenan at aig.com] 
Sent: Tuesday, July 12, 2005 8:06 AM
To: 'Access Developers discussion and problem solving'
Subject: RE: [AccessD] Reading DIF format (A97 etc)


"Split()" is an Access 2000+ function which takes a string as its first parameter and a second string parameter which is a delimiter used to split the first string into components. The split function breaks the whole string up and returns all the parts in an array. So the "(1)" at the end is in fact an array index, retrieving element 1 from the array returned by Split(). This is piece of shorthand code writing (possibly the author learned to code in the terse world of C/C++ ???), but I would not use it personally, simply because it is a little confusing to read. I'd explicitly assign the returned array to a Variant and then retrieve element 1 from that.

Dim StrParts as Variant
...
	StrParts = Split(strTemp1, ",")
	lngColumns = StrParts(1)
...

My 2¢.

Lambert


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Griffiths, Richard
Sent: Tuesday, July 12, 2005 10:37 AM
To: Access Developers discussion and problem solving
Subject: RE: [AccessD] Reading DIF format (A97 etc)


Stuart

Thanks I will look, can you help me on the line of code

lngColumns = Split(strTemp1, ",")(1)

Split - is this your user def function na dwhat about the (1) bit??

Richard

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Stuart McLachlan
Sent: 12 July 2005 14:11
To: Access Developers discussion and problem solving
Subject: Re: [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


-- 
AccessD mailing list
AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

-- 
AccessD mailing list
AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
-- 
AccessD mailing list
AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com