"Johnny Meredith" <me**************@msn.com> wrote in message
news:70**************************@posting.google.c om...
I have seven huge fixed width text file that I need to import to Access.
They contain headers, subtotals, etc. that are not needed. There is also
some corrupt data that we know about and can correct once the data is in
Access (or during the import process itself). Furthermore, the text files
are poorly set up, such that some records may be "shifted" over a few
characters, and therefore the fixed width nature of the file is corrupted.
Here's a sample of the data:
15473AB Cavity Mold D1 06/18/2002 10,000 3,500
1,000 15473A 50.17099.01
As you can see, the records are on two lines and, being fixed width, there
are no delimiters or text qualifiers present.
I've been importing these text files via Excel and using Excel VBA(which
is a long tedious process since the text files have more rows than Excel does.)
My question: is it possible to read a text file line by line and perform
logic on them in order to extract the records properly?
I can hold my own with VBA, but I don't even know where to begin on this
one. I have Access 97 and 2002. I do not have VB6, or C++, so I can't write
any utilities outside VBA.
Thanks,
Johnny Meredith
"On a quest to save time"
If you find that writing an import routine to be a less tedious than using
your current method, then this is the way to go. You need to explain how
one can be sure of what text makes up a record since I believe a record may
be spread over 2 lines, so how can you tell by looking at the data?
You also need to think carefully about error handling - what errors should
cause the import to stop and which could be handled. For example if a
required numeric field in a table is about to have the value "-", because
some Excel user typed it in to mean nothing, not applicable, zero, or
whatever. Your code simply replace this for zero without any warning, log
this error to an errors table but continue, or abort the import since the
value is not acceptable.
If you really have no idea where to start, then here is the beginning of an
import routine which has a form "frmImport" with a button "cmdImport".
****NB**** This is only to give you an idea of structure and the example
assumes that the end of a record has the character ";" (wouldn't it be nice
if it was that easy). It also does not contain code to split the record
line in to different fields but you could use functions like split, trim,
left, right, mid, etc to break up the line properly. It also does not
contain any code to log errors to an errors table, but if you can code, then
you should be able to adapt this.
Private Sub cmdImport_Click()
On Error GoTo Err_Handler
Dim strPath As String
Dim intFile As Integer
Dim strFileLine As String
Dim strRecordLine As String
Dim strTableName As String
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
strPath = "C:\Test.txt"
strTableName = "tblImport"
intFile = FreeFile()
Open strPath For Input As #intFile
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strTableName, dbOpenDynaset, dbAppendOnly)
While Not EOF(intFile)
Line Input #intFile, strFileLine
If Len(strFileLine) > 0 Then
strRecordLine = strRecordLine & strFileLine
If Right$(strRecordLine, 1) = ";" Then
rst.AddNew
rst("F1") = strRecordLine
rst.Update
strRecordLine = ""
End If
End If
Wend
Exit_Handler:
On Error Resume Next
If Not dbs Is Nothing Then
Set dbs = Nothing
End If
If Not rst Is Nothing Then
rst.Close
End If
Close #intFile
Exit Sub
Err_Handler:
Select Case Err.Number
Case 0
Resume Exit_Handler
Case Else
MsgBox Err.Description, vbExclamation, "Error No: " & Err.Number
Resume Exit_Handler
End Select
End Sub
