Text File Import

[osmethod]

Hello,

I'd appreciate any help which is offered to my problem. I'm using
Acess 97.

I have a flat text file which lists names and addresses. Sometimes,
these can be 4 lines and sometimes 5 lines, with a blank line
separating them...
e.g.

Michael Wilde
Foxrock
Coolagh
Dublin

Tommy Williams
Ratoath
Lucan Drive
Finglas
Meath

etc...

I need to import this to a table whaich has 5 fields. When it is a 4
line address i need to leave the fifth field blank, and start back at
field 1 for the next record...

The following code works to a point. It does what i require but:
1. after each 5 line address it inserts a blank line
2. it omits the last entry of name and address
3. i get an error 62 - "input past end of file"
Function ImportFile()

Dim intImportFile As Integer
Dim strImportFile As String
Dim strTableName As String
Dim strInputLine As String
Dim dbs As Database
Dim rst As Recordset

strImportFile = "Y:\databases\milk\suppliers.txt"
strTableName = "TempTable"

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strTableName, dbOpenDynaset)

intImportFile = FreeFile
Open strImportFile For Input As #intImportFile

'Do Until EOF(intImportFile)
While Not EOF(intImportFile)
rst.AddNew
Line Input #intImportFile, strInputLine
If Len(strInputLine) = 0 Then
GoTo cont
Else
rst!Line1 = strInputLine
End If
Line Input #intImportFile, strInputLine
If Len(strInputLine) = 0 Then
GoTo cont
Else
rst!Line2 = strInputLine
End If
Line Input #intImportFile, strInputLine
If Len(strInputLine) = 0 Then
GoTo cont
Else
rst!Line3 = strInputLine
End If
Line Input #intImportFile, strInputLine
If Len(strInputLine) = 0 Then
GoTo cont
Else
rst!Line4 = strInputLine
End If
Line Input #intImportFile, strInputLine
If Len(strInputLine) = 0 Then
GoTo cont
Else
rst!Line5 = strInputLine
End If
cont:
If EOF(intImportFile) Then GoTo end1
rst.Update

Wend
'loop
end1:
Close intImportFile
End Function
Any help would be sincerely appreciated...

Thank you....

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

I'd do it like this (untested):

' use the same code you used to open the file & the recordset

Dim strLine(1 to 5) As String
Dim bytLine as Byte
Dim i as byte

' initialize
bytLine = 1
Line Input #intImportFile, strLine(bytLine)

Do While Not EOF(intImportFile)
' Check for blank line, which means add record
if len(strLine(bytLine))= 0 then
rs.AddNew
for i = 1 to bytLine - 1
rs("Line" & i) = strLine(i)
next i
rs.Update
bytLine = 0 ' reset for next record lines
end if
bytLine = bytLine + 1 ' increment for next line
Line Input #intImportFile, strLine(bytLine)
Loop

' Catch any fields from last record which didn't end on blank line
If bytLine > 1 then
rs.AddNew
for i = 1 to bytLine - 1
rs("Line" & i) = strLine(i)
next i
rs.Update
end if

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQRvDb4echKqOuFEgEQLCpgCgzrb61ql9ALOTLJGQokZX19 +aLjIAnick
V9QDrvdqqg+Z6tuUZV5Jn5Je
=EXkG
-----END PGP SIGNATURE-----

osmethod wrote:

Hello,

I'd appreciate any help which is offered to my problem. I'm using
Acess 97.

I have a flat text file which lists names and addresses. Sometimes,
these can be 4 lines and sometimes 5 lines, with a blank line
separating them...
e.g.

Michael Wilde
Foxrock
Coolagh
Dublin

Tommy Williams
Ratoath
Lucan Drive
Finglas
Meath

etc...

I need to import this to a table whaich has 5 fields. When it is a 4
line address i need to leave the fifth field blank, and start back at
field 1 for the next record...

The following code works to a point. It does what i require but:
1. after each 5 line address it inserts a blank line
2. it omits the last entry of name and address
3. i get an error 62 - "input past end of file"
Function ImportFile()

Dim intImportFile As Integer
Dim strImportFile As String
Dim strTableName As String
Dim strInputLine As String
Dim dbs As Database
Dim rst As Recordset

strImportFile = "Y:\databases\milk\suppliers.txt"
strTableName = "TempTable"

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strTableName, dbOpenDynaset)

intImportFile = FreeFile
Open strImportFile For Input As #intImportFile

'Do Until EOF(intImportFile)
While Not EOF(intImportFile)
rst.AddNew
Line Input #intImportFile, strInputLine
If Len(strInputLine) = 0 Then
GoTo cont
Else
rst!Line1 = strInputLine
End If
Line Input #intImportFile, strInputLine
If Len(strInputLine) = 0 Then
GoTo cont
Else
rst!Line2 = strInputLine
End If
Line Input #intImportFile, strInputLine
If Len(strInputLine) = 0 Then
GoTo cont
Else
rst!Line3 = strInputLine
End If
Line Input #intImportFile, strInputLine
If Len(strInputLine) = 0 Then
GoTo cont
Else
rst!Line4 = strInputLine
End If
Line Input #intImportFile, strInputLine
If Len(strInputLine) = 0 Then
GoTo cont
Else
rst!Line5 = strInputLine
End If
cont:
If EOF(intImportFile) Then GoTo end1
rst.Update

Wend
'loop
end1:
Close intImportFile
End Function
Any help would be sincerely appreciated...

Thank you....

[Os Method]

Thank you very much - MGFoster

Dim strLine(1 to 5) As String - once I changed the 5 to 6 it work
perfectly.

Thank you for sharing your coding with me.....

osmethod
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Pieter Linden]

Fixed it... I wrote everything to a variant array first and then wrote
it to the table... (Hey James Fortune, could ya make this pretty for
me? You know, fix it so it's *really* right, instead of just
functional, add error trapping, etc)

Option Compare Database
Option Explicit

Public Sub ImportTextFile(ByVal strFileName As String)
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim varAddress(0 To 4) As Variant
Dim strTextLine As String
Dim intCounter As Integer
Dim intFieldsCounter As Integer

Dim intFileNo As Integer

Set db = CurrentDb
Set rs = db.OpenRecordset("Table1", dbOpenTable, dbAppendOnly)
intFileNo = FreeFile
Open strFileName For Input As #intFileNo

Do Until EOF(intFileNo)
'Do While Not EOF(intFileNo) ' Loop until end of
file.
Line Input #intFileNo, strTextLine ' Read line into
variable.
strTextLine = Trim(strTextLine) ' strip off blanks

'--if the line isn't blank, write to the array.
'--if it IS blank, assume end of record and write array values
to table.
If Len(strTextLine) > 0 Then
varAddress(intCounter) = strTextLine
intCounter = intCounter + 1
Else
'--reset the counter
intCounter = 0
'--write the record to the table
'--add a new record
rs.AddNew

'--fill in the values from the array
For intFieldsCounter = 0 To 4
Debug.Print
rs.Fields(intFieldsCounter).Value =
varAddress(intFieldsCounter)
Next intFieldsCounter

'--save the data to the record
rs.Update

'--clear the array
For intFieldsCounter = 0 To 4
varAddress(intFieldsCounter) = Null
Next intFieldsCounter

End If

Loop

Close #intFileNo ' Close file.
rs.Close
Set rs = Nothing
Set db = Nothing

End Sub

Form level test code....

Private Sub Command0_Click()
Dim varFileName As Variant
varFileName = GetOpenFile("C:\", "Pick a file, any file!")

If IsNull(varFileName) Then
MsgBox "uhh... you like gotta choose a file, man!"
Else
ImportTextFile varFileName
End If
End Sub

All the GetOpenFile code is right here (so your user can browse for
the file, if you want...)
http://www.mvps.org/access/api/api0001.htm

[James Fortune]

os******@eircom.net (osmethod) wrote in message news:<9c**************************@posting.google. com>...

Hello,

I'd appreciate any help which is offered to my problem. I'm using
Acess 97.

I have a flat text file which lists names and addresses. Sometimes,
these can be 4 lines and sometimes 5 lines, with a blank line
separating them...
e.g.

Michael Wilde
Foxrock
Coolagh
Dublin

Tommy Williams
Ratoath
Lucan Drive
Finglas
Meath

etc...

I need to import this to a table whaich has 5 fields. When it is a 4
line address i need to leave the fifth field blank, and start back at
field 1 for the next record...

The following code works to a point. It does what i require but:
1. after each 5 line address it inserts a blank line
2. it omits the last entry of name and address
3. i get an error 62 - "input past end of file"
Function ImportFile()

Dim intImportFile As Integer
Dim strImportFile As String
Dim strTableName As String
Dim strInputLine As String
Dim dbs As Database
Dim rst As Recordset

strImportFile = "Y:\databases\milk\suppliers.txt"
strTableName = "TempTable"

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strTableName, dbOpenDynaset)

intImportFile = FreeFile
Open strImportFile For Input As #intImportFile

'Do Until EOF(intImportFile)
While Not EOF(intImportFile)
rst.AddNew
Line Input #intImportFile, strInputLine
If Len(strInputLine) = 0 Then
GoTo cont
Else
rst!Line1 = strInputLine
End If
Line Input #intImportFile, strInputLine
If Len(strInputLine) = 0 Then
GoTo cont
Else
rst!Line2 = strInputLine
End If
Line Input #intImportFile, strInputLine
If Len(strInputLine) = 0 Then
GoTo cont
Else
rst!Line3 = strInputLine
End If
Line Input #intImportFile, strInputLine
If Len(strInputLine) = 0 Then
GoTo cont
Else
rst!Line4 = strInputLine
End If
Line Input #intImportFile, strInputLine
If Len(strInputLine) = 0 Then
GoTo cont
Else
rst!Line5 = strInputLine
End If
cont:
If EOF(intImportFile) Then GoTo end1
rst.Update

Wend
'loop
end1:
Close intImportFile
End Function
Any help would be sincerely appreciated...

Thank you....

Close. Try:

Dim intImportFile As Integer
Dim strImportFile As String
Dim strTableName As String
Dim varInputLine As Variant
Dim dbs As Database
Dim rst As Recordset
Dim i As Integer

strImportFile = "C:\Input.txt"
strTableName = "TempTable"

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strTableName, dbOpenDynaset)

intImportFile = FreeFile
Open strImportFile For Input As #intImportFile

Do Until EOF(intImportFile)
rst.AddNew
i = 1
varInputLine = Null
Do Until varInputLine = "" Or EOF(intImportFile)
Line Input #intImportFile, varInputLine
If Trim(varInputLine) <> "" Then
varInputLine = Trim(varInputLine)
rst("Line" & i) = varInputLine
i = i + 1
End If
Loop
rst.Update
Loop
Close intImportFile

This took me less than 10 minutes to write so be kind.

James A. Fortune

[James Fortune]

pi********@hotmail.com (Pieter Linden) wrote in message news:<bf**************************@posting.google. com>...

Fixed it... I wrote everything to a variant array first and then wrote
it to the table... (Hey James Fortune, could ya make this pretty for
me? You know, fix it so it's *really* right, instead of just
functional, add error trapping, etc)

Perhaps the following code will help:

'--------begin form code
Private Sub cmdSelectImportDirectory_Click()
'Code adapted from post by Emile Besseling
Dim BInfo As MyBrowseInfo
Dim strDir As String
Dim strFile As String
Dim BrowseView As Long
Dim lngID As Long
Dim strFolder As String

With BInfo
.pidlRoot = 0
.lpszTitle = "Please choose an import directory."
.lpfn = 0
.lParam = 0
.iImage = 0
End With
lngID = SHBrowseForFolder(BInfo)
strDir = Space(255)
If lngID <> 0 Then
If SHGetPathFromIDList(ByVal lngID, strDir) Then
strFolder = Left(strDir, InStr(strDir, Chr(0)) - 1)
End If
End If
strDir = strFolder
cbxShowFiles.RowSourceType = "Value List"
cbxShowFiles.RowSource = ""
'Only look for files ending with .txt
strFile = Dir(strDir & "\*.txt")
If strFile = "" Then
MsgBox ("No .txt files found in " & strDir)
Exit Sub
End If
Do While strFile <> ""
If cbxShowFiles.RowSource = "" Then
cbxShowFiles.RowSource = strFile
Else
cbxShowFiles.RowSource = cbxShowFiles.RowSource & strFile
strFile = Dir()
If strFile <> "" Then cbxShowFiles.RowSource =
cbxShowFiles.RowSource & ";"
End If
Loop
cbxShowFiles.Value = Null
MsgBox ("Please select a file to import.")
End Sub
'--------end form code

'--------begin module code
Option Compare Database
Option Explicit

Type MyBrowseInfo
hwndOwner As Long
pidlRoot As Long
pszDisplayName As String
lpszTitle As String
ulFlags As Long
lpfn As Long
lParam As Long
iImage As Long
End Type

Declare Function SHBrowseForFolder Lib "shell32.dll" (ByRef lpbi As
MyBrowseInfo) As Long
Declare Function SHGetPathFromIDList Lib "shell32.dll" (pidl As Long,
ByVal sPath As String) As Long
'--------end module code

I'm trying not to use On Error unless I'm using a .mde or writing
something commercial so I'll let you do that. I didn't look at your
code but it's probably "really" right already.

James A. Fortune