Insert data during Import !!!!

  • Thread starter Thread starter Greg
  • Start date Start date
G

Greg

HI All

I have a a pile of txt files to import into access (200) each file has
stock information (share market) one of the problems i have is that that
each sto code is a seperate file but does not have the stock code with
each record.

I need to find a way on how to insert the stock code as it is imported
into access .

I have thought about opening the file doing an array for each file and
adding the extra field closing the file etc and then import the text
files into the required table.

If anyone has any better ideas i would be more than gratefull

Thanks in advance
Olly
 
One idea is to bring both files into Access tables and then write update
queries to add the information from one to the other.

The key question is how will you know this info?

If you know it for the text files then you should know it for the Access
tables.

Just depends on which you prefer working with.
Guess which one I like better?
 
HI Joe
Each file is named by code eg CML.txt i wiould get the name from the
file, iam looking for a way of inserting the code during a procedure
and maybe runing an a query based on the code name.

Thats the thought but how to implement it is causing some grief
Olly
 
It can't be done during an import using the wizard.
But if you write your own code you could do it.

This may give you the idea:

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.
'add the file name during this process!

If rs.BOF = True Then
rs.AddNew
Else
rs.Edit
End If
rs.Update
Loop
End Sub
 
Thanks Joe

Finally worked it out , but your idea was good, i ended up going the
other way and found that i could change the fields included in data text
file.

The code is not elegent but it works and practise makes perfect

many thanks

Olly
 
Joe,

I tried your sample code & it worked, but it skipped the
first record. So I took the first read out & put it at
the bottom of the DO While loop. A problem that I have
that Greg didn't mention, was that my file was comma
delimited. I used the instr() function to parse the 71
fields into an array, then updated the Access Table in
whatever sequence & with whatever "manipulated" data I
chose. My files were named 02391281.ext. This would be
for store 0239, day 128, sequence 1. Greg, if you want a
copy, just email me.

Thanks.
-Bill.
 
Great!
The code is just a sample hack to give you the idea.
Obviously, each situation is different and you need to test your code fully
to be sure it does what you think it does.

Glad to know the idea was useful.
 
Back
Top