import text file over 255 fields

[belmontj]

I’m reading in a comma delimited text file using the Open [filename]for Input. No problem reading this into new tables

My problem is: How to append the fields as a new record (or row) rather than a new field.


Here is an illustration of how it reads now; keep in mind this goes on for over 1000 fields, but are groups of 10 (each 10 can be its own Field)

Fld1 Fld2 Fld3 Fld4 Fld5Fld6 Fld7 Fld8 Fld9 Fld10…Fld1400
str1, str2, str3, str4, str5, str6, str7, str8, str9, str10,…str1400

So my code now is something like this, which treats each comma as a new field, but how can I take groups of 10 (commas) and treat it as one field and repeat this until the last comma in the row, then append.

Const strDelimiter As String = ","
Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset("tst", dbOpenDynaset)
Open "filename" For Input As #1
While EOF(1) = False
Line Input #1, strLine
' split the line into an array
varArray =Split(strLine, strDelimiter)

rst.AddNew

i = 0
j = 1
Do Until i = (somenumber)
rst.Fields("Field" & j).Value= varArray(i)
i = i + 1
j = j + 1
Loop

rst.Update

Wend
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing
Close #1

[June7]

Try a nested loop

For k = 1 to 10
rst.Fields("Field" & j) = rst.Fields("Field" & j) & varArray(i)
i = i + 1
Next

[ssanfu]

Interesting problem. I would try and devise a different table structure, but it was interesting.

Here is the code I came up with:

Option Compare Database
Option Explicit
Option Base 1

Public Sub test()
   Const strDelimiter As String = ","
   Const Groups As Integer = 10

   Dim dbs As DAO.Database
   Dim rst As DAO.Recordset

   Dim TF As Integer   'text file file number
   Dim varArray         'array name
   Dim UB As Integer   ' array upper bound
   Dim GroupNum As Integer  'how many groups
   Dim i As Integer  'index for array
   Dim j As Integer  'index for array
   Dim x As Integer  'for looping
   Dim k As Integer  'which field number
   Dim sString As String
   Dim sfilename As String
   Dim rsName As String   'recordset name or SQL
   Dim strInput As String
   '-----------------------

   'path to text file
   sfilename = "C:\Documents and Settings\usernamePath\Desktop\TextFileName.txt"
   rsName = "tst"
   '-----------------------
   
   Set dbs = CurrentDb()

   'open recordset
   Set rst = dbs.OpenRecordset(rsName, dbOpenDynaset)

   '  open text file
   '  Open "sfilename" For Input As #TF
   TF = FreeFile
   Open sfilename For Input As #TF

   'loop thru text file
   While Not EOF(TF)
      sString = ""
      '  read line from text file
      Line Input #TF, strInput
      ' split the line into an array
      varArray = Split(strInput, strDelimiter)
      UB = UBound(varArray)
      GroupNum = UB \ Groups + 1

      j = 0
      k = 1
      rst.AddNew
      For x = 1 To GroupNum
         i = 0
         sString = ""
         'build string
         Do Until i = (Groups)
            If (j + i) < (UB + 1) Then
               sString = sString & varArray(j + i) & ","
            End If
            i = i + 1
         Loop
         sString = Left(sString, Len(sString) - 1)
         rst.Fields(k) = sString
         j = j + 10
         k = k + 1
      Next
      rst.Update

   Wend
   rst.Close
   Set rst = Nothing
   Set dbs = Nothing
   Close #TF
   MsgBox "Done"
End Sub

You need the blue line "Option Base 1"
The RED text is what you can/should change.