predefine fixed width import

[Guest]

Hello,

I have a fixed width text file that contains demographic info plus 30 sets
of exam result (for each demographic record). I would like to break exam into
records.

I was thinking of breaking the the exam result into small tables and
merge/append them into one big one. Question is is there anyway I can import
data into the little table by predefine which the starting position each exam
result?

example
Text file
Apple 1 2
Orange 4 5 6 7

Small table 1
Apple 1
Apple 2

Small table 2
Orange 4
Orange 5
Orange 6
Orange 7

Big table
Apple 1
Apple 2
Orange 4
Orange 5
Orange 6
Orange 7


Any input or comment is appreciated!

Thanks!

 

[Guest]

Create an Import Spec and use it to import your files. For a fixed width
file, you can define the length of each field. Saves you having to code it
in.

 

[Joe Fallon]

Not quite sure what you are tyring to do but it may be easier to do it in
code.
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

 

[Pat Hartman\(MVP\)]

There is no easy or automated way to do this. You'll need to do it the old
fashion way - with code. Use the import wizard to import the text file,
don't forget to save the import spec (press the advanced button to get to
the option) so you can use it again if you need to. Once the table is
imported, you can normalize the data in one of two ways.
1. Run a series of append queries (one for each column you need to "pivot".
In this case it will be 30)
2. Write a code loop to read the imported table and create the correct
normalized records by looping through the columns.

Method 1 is pretty easy and shouldn't take more than a half hour to create
the append query run it and change it 30 times. However, if you need to do
this more than once, you will need to go with method 2.