Re: new to access, import a .txt file no delimiters to Acces
Well if you just must, go nuts here. I developed this because Access had a infuritating problem with saving specifications beyond some size (25 fields, I think it was). This may have been corrected in later versions - feedback welcome on that issue.
Just substitute your desired field names and widths into the code below, and set the constant as desired. Warning: compress after using. It simply absurdly bloats the .MDB. Feedback is welcome on a tweak to index 1 or more fields (this code does not).
Option Compare Database
Option Explicit
Sub ImportTextFile()
'import a fixed width text file into a new table called "Newtable" (you must rename it afterwards)
'LOOK BETWEEN THE ASTERISKS BELOW for variable info
Dim db As Database
Dim rs As Recordset
Dim tdfNewtable As TableDef, fldNew As Field
Dim strData As String, strTemp As String
Dim strDirFileName As String
Dim fieldnames, fieldlengths 'as is, so valid as variants for Array function
Dim i As Long, iFieldcount As Long, iRunningTotal As Long, iRecordCount As Long
'********************************************************************************************************
strDirFileName = "c:\dUMMY." 'text file source
'build these in Excel from Lotus from AS-400 download control file
fieldnames = Array( _
"MTRCST", "MTUPST", "MTDADD", "MTDOLC", "MTOPID", _
"MTCOID", "MTPLAN", "MTCODE", "MTAGE", "MTDUR", _
"MTSEX", "MTEFDT", "MTRES1", "MTRES2", "MTRES3", _
"MTRES4", "MTRES5", "MTRES6", "MTRES7", "MTRES8", _
"MTRES9", "MTRES0")
fieldlengths = Array( _
1, 1, 8, 8, 3, _
3, 5, 1, 3, 3, _
1, 10, 8, 8, 8, _
8, 8, 8, 8, 8, _
8, 8)
#Const TABLE_PREEXISTS = False
'********************************************************************************************************
iFieldcount = UBound(fieldnames)
Set db = CurrentDb
Close #1
Open strDirFileName For Input As #1 'open the file for input
#If Not TABLE_PREEXISTS Then
Set tdfNewtable = db.CreateTableDef("Newtable")
With tdfNewtable
' Create and append new Field objects to the table.
For i = 0 To iFieldcount
Set fldNew = .CreateField(fieldnames(i))
fldNew.Size = fieldlengths(i)
fldNew.Type = dbText
.Fields.Append fldNew
Next i
End With
db.TableDefs.Append tdfNewtable 'this actually creates the table described above
#End If
Set rs = db.OpenRecordset("Newtable", dbOpenDynaset)
Do Until EOF(1)
Line Input #1, strData
rs.AddNew 'get set for new data
iRunningTotal = 1 'because "Mid" function is 1-based
For i = 0 To iFieldcount
strTemp = Mid(strData, iRunningTotal, fieldlengths(i))
If strTemp = "" Then strTemp = " "
rs.Fields(i) = strTemp
iRunningTotal = iRunningTotal + fieldlengths(i)
Next i
rs.Update
iRecordCount = iRecordCount + 1
'If iRecordCount > 3 Then Stop 'debugging statement
If 0 = iRecordCount Mod 10000 Then Debug.Print iRecordCount & " records so far"
Loop
Close #1
Debug.Print iRecordCount & " records - job complete"
ProcExit:
rs.Close
Set rs = Nothing
Set db = Nothing
End Sub
------------
And as I always say, for A2000, at least:
To fix F#@$!ing Access to run NORMAL #$%%$#!! CODE:
tools/references/check Microsoft DAO 3.6 Object Library
get RID of ADO 2.1
rock on, brother
