Import Text file

  • Thread starter Thread starter Dean
  • Start date Start date
D

Dean

I have a text file that is created each day by a program, I wish to import
this into a DB. Is there an easy way.
1st problem is that the file name is textdate.txt where text is a filename
that is constant)
date is obviously the date of the file but it is in the format of
07.07.2004, this is not importable by access!
Next problem is that the first three lines of the file are always the same
and will not be needed, (junk text).

File directory is constant
file length will vary
I have previous experiance of VBA and importing text but not this.

Can anyone soggest a short simple query that I can use?

Thanks in advance
Dean

(e-mail address removed)

http://www.dkso.co.uk/

http://homepage.ntlworld.com/dkso
 
This generic code should get you started (assumes each text file is
comma-delimited
and that the order of data are the same as the field order in the table):


Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim intFile As Integer, intFields As Integer
Dim strFile As String, strLine As String
Dim varArray As Variant
Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset("TableToWhichDataWillBeAppended", _
dbOpenDynaset, dbAppendOnly)
strFile = Dir("C:\MyFolderName\*.txt")
Do While strFile <> ""
intFile = FreeFile()
' open the text file in order to read the data
Open "C:\MyFolderName\" & strFile As #intFile For Input
' discard the first two lines in the text file
Line Input #intFile, strLine
Line Input #intFile, strLine
' append each record from the text file to the table
Do While EOF(intFile) = False
Line Input #intFile, strLine
varArray = Split(strLine, ",")
rst.AddNew
For intFields = LBound(varArray) To UBound(varArray)
rst.Fields(intFields - LBound(varArray)).Value = _
varArray(intFields)
Next intFields
rst.Update
Loop
Close #intFile
strFile = Dir()
Loop
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing
 
Thank you Ken. I'll give it a go later.

Dean
Ken Snell said:
This generic code should get you started (assumes each text file is
comma-delimited
and that the order of data are the same as the field order in the table):


Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim intFile As Integer, intFields As Integer
Dim strFile As String, strLine As String
Dim varArray As Variant
Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset("TableToWhichDataWillBeAppended", _
dbOpenDynaset, dbAppendOnly)
strFile = Dir("C:\MyFolderName\*.txt")
Do While strFile <> ""
intFile = FreeFile()
' open the text file in order to read the data
Open "C:\MyFolderName\" & strFile As #intFile For Input
' discard the first two lines in the text file
Line Input #intFile, strLine
Line Input #intFile, strLine
' append each record from the text file to the table
Do While EOF(intFile) = False
Line Input #intFile, strLine
varArray = Split(strLine, ",")
rst.AddNew
For intFields = LBound(varArray) To UBound(varArray)
rst.Fields(intFields - LBound(varArray)).Value = _
varArray(intFields)
Next intFields
rst.Update
Loop
Close #intFile
strFile = Dir()
Loop
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing
Click to expand...
 
Ken,

I'm going to have to pass on that one and ask for more help. I keep getting
an error "User defined type not defined".
I've tried putting in a module and currently have it on the VBA of a command
button on a form. I've changed the sub from Private to Public but keep
getting the error.

Dim "dbs As DAO.Database"
 
You need to set a reference for the DAO library (ACCESS 2000 and 2002 do not
have this reference set as a default library).

Open VBE, click Tools | References. Find the Microsoft DAO 3.x library and
check it. Close References window.
 
Thanks Ken.
Works a treat.
Scheduled it to run early in the morning and put an autostart macro in. I've
had to put the code into the LOAD routine of a FORM that is started in the
Autoexec macro but that was the easiest way I could think of.
O'h I'm sure you did it to test me, 'Open "C:\MyFolderName\" & strFile As
#intFile For Input'
has an error in it.....should be 'Open "C:\MyFolderName\" & strFile For
Input As #intFile' but I found your trap and corrected it.
I don't think I've done to badly....with a great deal of your help.

Thanks again
Dean
 
Sorry about the error in the syntax.... my memory stubbornly insists that
the logical order for that statement is the way I typed it, not the way VBA
says it is!

Good luck.
 
Dean said:
Not a problem.
I'm just so grateful for the help you and all the other MVPs offer. I've
looked around newsgroups for a while now and find most people so very
helpful and friendly.

Thanks again
Dean
Click to expand...


You're very welcome!
 
Not a problem.
I'm just so grateful for the help you and all the other MVPs offer. I've
looked around newsgroups for a while now and find most people so very
helpful and friendly.

Thanks again
Dean
 
Back
Top