Importing Data Multi Line

[DMS500Tech]

Is it possible to import a multi line record into an Access table. Specifically I have a text file and each record contains 11 lines of data and there is just shy of 99,000 records to import so a find and replace that I usually do in word equells about 11 million replacements before I can make the data be on one line. Quite time consuming, looking for a better way.

 

[Remou]

You can use the FileSystemObject to read in the data and append it line by line to a table; still time consuming, but faster than Word.

 

[CautionMP]

DMS500Tech,
Here is a rough shell that should get you going in the right direction. It is typed and untested and will need to be updated to reflect the real names of the input file (i.e. [tt]C:\Temp\MyFile.txt[/tt]) and output table.

Sub MultiLine_to_Table()
On Error GoTo MultiLine_to_Table_Error
Const cstFileIn As String = "[b][i]Your File name & path here[/i][/b]"
Const cstOutputTable As String = "[b][i]Your 9 field output table name[/i][/b]"
[b]'You will need to make sure you have a reference
'in your project to an ADODB library or change the
'code to use DAO[/b]
Dim rstOut As New ADODB.Recordset
Dim intFileIn As Integer, intLineIn As Integer
Dim strTemp As String

'open the file
intFileIn = FreeFile
Open cstFileIn For Input As #intFileIn
'Open the recordset
rstOut.Open cstOutputTable, CurrentProject.Connection, adOpenDynamic, adLockOptimistic

Do
  'Get the next (or first) line
  Line Input #intFileIn, strTemp
  'for first field create a new record
  If intLineIn = 0 Then
    rstOut.AddNew
  End If
  
  'write field data and index
  rstOut.Fields(intLineIn) = strTemp
  intLineIn = intLineIn + 1
  
  'check if record is complete
  If intLineIn > 9 Then
    'it is so update and reset field index
    rstOut.Update
    intLineIn = 0
  End If
Loop Until EOF(intFileIn)

MultiLine_to_Table_Exit:
Close intFileIn
rstOut.Close
Set rstOut = Nothing
Exit Sub

MultiLine_to_Table_Error:
Debug.Print Now, "MultiLine_to_Table", Err.Number, Err.Description
Stop
Resume MultiLine_to_Table_Exit
End Sub

Things to think about if you go this route (this is NOT a complete list):[ol]
[li]You may want to check if the file is valid using [tt]Dir()[/tt].[/li]
[li]This routine assumes your output table has only 9 fields.[/li]
[li]If you are appending to the table and encounter a dupicate record you will need to handle that error.[/li]
[li]You may need to prep [tt]strTemp[/tt] before you dump the data in your table ([tt]Trim()...[/tt]).[/li]
[li]If there are lines between records you will need to account for that.[/li][/ol]

Hope this helps,
CMP

[small]Look ma' no [tt]FileSystemObject[/tt][/small]