Import Multiple Specification

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have one file that I grab (.txt but could be .csv if desired). I have some
code to automatically import this file in my database. The problem that I
run into is that not all of the records have the same layout. There are
three parts (D1, M1, and O1) in the flat file. I want to try to automate the
import as much as possible.

Is there a way to have MS Access use different import specs based on the
first two digits of that line?

If not, I will have to import the file three times then delete the data that
does not come over properly...

Thanks!
 
You can't import "non-tabular" data using the wizards in Access.
It needs to look like a spreadsheet in order to use the wizards.

You should use code to open the file and process 1 line at a time.
Here is an outline:

Public Sub ImportFile(strPath As String)

Dim db As Database, rs As Recordset
Dim sLine As String, sTrimmed As String
Set db = CurrentDb
Set rs = db.OpenRecordset("TableName", dbOpenTable)

Open strPath For Input As #1

'Read a single line from an open sequential file and assign it to a String
variable.
Line Input #1, sLine
'Trim the leading blanks
sTrimmed = LTrim(sLine)

Do While Not EOF(1)
'read the next line of the file
Line Input #1, sLine
sTrimmed = LTrim(sLine)

'manipulate the string if necessary, then add it to the rs table.
If rs.BOF = True Then
rs.AddNew
Else
rs.Edit
End If
rs.Update
Loop
End Sub
 
Back
Top