Direction to online idiot guide to custom text importing

  • Thread starter Thread starter Dale
  • Start date Start date
D

Dale

Hi,

I hope someone will be able to point me in the right direction. I've
searched the net for an idiots guide to using VBA to import text into
Access 97. I know there are commands to do that, which will import
delimited data, but I'm looking more for custom imports.

I can find a lot of courses that train this but I don't have the couple
of thousand £'s to do it. Maybe my search string isn't descriptive
enough, but I just can't seem to find any online resource for this sort
of thing.

Is anyone able to point me to a good resource? I'm not looking for
someone to go through it with me, nor to help me with a project as I'm
more than happy to learn from a written resource - but I just can't seem
to find one.

Thanks in advance!
 
Here is an outline for how to code your own import routine.

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
 
Here is some stripped down code I use for importing text files.
(MixedCase is a function that processes the string from upper case to mixed
case, I left it in to show that you can add
functions to reformat the information if needed)

Public Sub CnvAddress()

Dim InputData As String, i, RecPtr
Dim db As Database, rst As Recordset
Dim OwnrName As String, OwnrAdd1 As String, OwnrAdd2 As String, AddValid As
String

Set db = CurrentDb()
Set rst = db.OpenRecordset("CnvAddress", dbOpenDynaset)
Open "c:\convert\CnvAddress.txt" For Input As #1

Do While Not EOF(1)

Line Input #1, InputData

RecPtr = 1
i = 60: OwnrName = Mid(InputData, RecPtr, i): RecPtr = RecPtr + i
i = 40: OwnrAdd1 = Mid(InputData, RecPtr, i): RecPtr = RecPtr + i
i = 40: OwnrAdd2 = Mid(InputData, RecPtr, i): RecPtr = RecPtr + i
i = 1: AddValid = Mid(InputData, RecPtr, i): RecPtr = RecPtr + i

With rst
.AddNew
!OwnrName = MixedCase(OwnrName)
!OwnrAddress = MixedCase(OwnrAdd1) & vbCrLf &
MixedCase(OwnrAdd2)
If AddValid = "Y" Then !Valid = True
.Update
End With

Close #1

rst.Close
Set rst = Nothing

End Sub

--
John... Visio MVP

Need stencils or ideas? http://www.mvps.org/visio/3rdparty.htm
Need VBA examples? http://www.mvps.org/visio/VBA.htm
Common Visio Questions http://www.mvps.org/visio/common_questions.htm
 
Here is an outline for how to code your own import routine.
Click to expand...

Thank you, and John, for the snips of code. What I'm looking for,
though, is a basic introduction to text importing. I'd like to be able
to take any text document, that has some form of consistency in format
what ever that may be, and be able to import it. I deal with a lot of
stats in my current role and I'd like to be able to import them
automatically - however, the format of the stats change so at the moment
I'm importing them into Excel, modifying them and exporting them again
which just takes up too much time.

I'm not shy of learning, but am shy of the cash for the courses to do
this, so I hoped that someone may know of an online idiots guide to it.
However, your code gives me the starting block to mess around with for
now.

Cheers!
 
Back
Top