Generic Text Import

  • Thread starter Thread starter Peter Hill
  • Start date Start date
P

Peter Hill

Hello - (using Access2000)

Is there a way (without using the Import Text Wizard) of
importing a text file into Access so that it comes in as
if I had just pasted it into a datasheet table. ie. with
default field names (Field1, Field2 ... etc) and just text
as the data type for each field.
The only way I can think of doing this is to get the user
to define the file delimiter (generally, this will only be
a comma or a pipe), then using a text file reader to read
the first line of the file and then creating a new
tabledef and appending a new field everytime the delimiter
appears on that first line. They can change the fields
data type later on.
Is there a more direct approach than this such as using
transfertext or something? From memory, it always
required specification name (but I want to keep it generic
so the spec is useless!)

Regards,
Peter Hill
 
Peter Hill said:
Hello - (using Access2000)

Is there a way (without using the Import Text Wizard) of
importing a text file into Access so that it comes in as
if I had just pasted it into a datasheet table. ie. with
default field names (Field1, Field2 ... etc) and just text
as the data type for each field.
The only way I can think of doing this is to get the user
to define the file delimiter (generally, this will only be
a comma or a pipe), then using a text file reader to read
the first line of the file and then creating a new
tabledef and appending a new field everytime the delimiter
appears on that first line. They can change the fields
data type later on.
Is there a more direct approach than this such as using
transfertext or something? From memory, it always
required specification name (but I want to keep it generic
so the spec is useless!)
Click to expand...

TransferText requires a specification name. If you are importing information
with a variety of fields you might set up several such on a menu.
If it is a random thing then it will require somebody to make decisions.
 
-----Original Message-----



TransferText requires a specification name. If you are importing information
with a variety of fields you might set up several such on a menu.
If it is a random thing then it will require somebody to make decisions.


.
Click to expand...
Am I correct though in saying that to create a
specification name, you need to first import using the
wizard then saving that import specification so you can
use it later in code using the transertext?

What I'm trying to do is completely skip the import wizard
because the data the user is importing will not always be
in the same format or have the some number of columns etc.
So - what I thought was - just get the data in there, the
play with it once it is in there.
 
Transfer Text just maps to a SQL append query.

You may use SQL directly to import text (you just
need to specify the file and file type as well as
the table in your SQL statement)

The Text import ISAM uses a file called Schema.ini
to define stuff like the field types and file delimiter.

However, if you google for code, you will see examples
of VB code that reads a file and writes it to a table.

This is even better: you can completely customize your
control of the format, and it is the only way to get
really good error handling and messages ("import failed
on line 2034 because Surname field was blank")

(david)
 
Peter Hill said:
Am I correct though in saying that to create a
specification name, you need to first import using the
wizard then saving that import specification so you can
use it later in code using the transertext?

What I'm trying to do is completely skip the import wizard
because the data the user is importing will not always be
in the same format or have the some number of columns etc.
So - what I thought was - just get the data in there, the
play with it once it is in there.
Click to expand...

Probably a *lot* more work but you can do that with a generic fixed length
import.
Import anything, go to the advance section, add a few fields of 255 bytes
each and save it.
Then you can go back and write code to parse the input.
It is not hard to do but tends to take a lot of time getting it right.
The split function will make it a bit easier if comma delimited.
 
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