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\m ilk\suppliers.t xt"
strTableName = "TempTable"

Set dbs = CurrentDb
Set rst = dbs.OpenRecords et(strTableName , dbOpenDynaset)

intImportFile = FreeFile
Open strImportFile For Input As #intImportFile

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

Wend
'loop
end1:
Close intImportFile
End Function


Any help would be sincerely appreciated...

Thank you....

[MGFoster]

Re: Text File Import

-----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(intImportFi le)
' Check for blank line, which means add record
if len(strLine(byt Line))= 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:::mgf0 0 <at> earthlink <decimal-point> net
Oakland, CA (USA)

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

iQA/AwUBQRvDb4echKq OuFEgEQLCpgCgzr b61ql9ALOTLJGQo kZX19+aLjIAnick
V9QDrvdqqg+Z6tu UZV5Jn5Je
=EXkG
-----END PGP SIGNATURE-----

osmethod wrote:
[color=blue]
> 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\m ilk\suppliers.t xt"
> strTableName = "TempTable"
>
> Set dbs = CurrentDb
> Set rst = dbs.OpenRecords et(strTableName , dbOpenDynaset)
>
> intImportFile = FreeFile
> Open strImportFile For Input As #intImportFile
>
> 'Do Until EOF(intImportFi le)
> While Not EOF(intImportFi le)
> rst.AddNew
> Line Input #intImportFile, strInputLine
> If Len(strInputLin e) = 0 Then
> GoTo cont
> Else
> rst!Line1 = strInputLine
> End If
> Line Input #intImportFile, strInputLine
> If Len(strInputLin e) = 0 Then
> GoTo cont
> Else
> rst!Line2 = strInputLine
> End If
> Line Input #intImportFile, strInputLine
> If Len(strInputLin e) = 0 Then
> GoTo cont
> Else
> rst!Line3 = strInputLine
> End If
> Line Input #intImportFile, strInputLine
> If Len(strInputLin e) = 0 Then
> GoTo cont
> Else
> rst!Line4 = strInputLine
> End If
> Line Input #intImportFile, strInputLine
> If Len(strInputLin e) = 0 Then
> GoTo cont
> Else
> rst!Line5 = strInputLine
> End If
> cont:
> If EOF(intImportFi le) Then GoTo end1
> rst.Update
>
> Wend
> 'loop
> end1:
> Close intImportFile
> End Function
>
>
> Any help would be sincerely appreciated...
>
> Thank you....[/color]

[Os Method]

Re: Text File Import

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]

Re: Text File Import

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 intFieldsCounte r As Integer

Dim intFileNo As Integer

Set db = CurrentDb
Set rs = db.OpenRecordse t("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(strTextLin e) ' 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(intC ounter) = 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 intFieldsCounte r = 0 To 4
Debug.Print
rs.Fields(intFi eldsCounter).Va lue =
varAddress(intF ieldsCounter)
Next intFieldsCounte r

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

'--clear the array
For intFieldsCounte r = 0 To 4
varAddress(intF ieldsCounter) = Null
Next intFieldsCounte r

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(varFileN ame) 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]

Re: Text File Import

osmethod@eircom .net (osmethod) wrote in message news:<9c0abefd. 0408111827.2b25 22fc@posting.go ogle.com>...[color=blue]
> 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\m ilk\suppliers.t xt"
> strTableName = "TempTable"
>
> Set dbs = CurrentDb
> Set rst = dbs.OpenRecords et(strTableName , dbOpenDynaset)
>
> intImportFile = FreeFile
> Open strImportFile For Input As #intImportFile
>
> 'Do Until EOF(intImportFi le)
> While Not EOF(intImportFi le)
> rst.AddNew
> Line Input #intImportFile, strInputLine
> If Len(strInputLin e) = 0 Then
> GoTo cont
> Else
> rst!Line1 = strInputLine
> End If
> Line Input #intImportFile, strInputLine
> If Len(strInputLin e) = 0 Then
> GoTo cont
> Else
> rst!Line2 = strInputLine
> End If
> Line Input #intImportFile, strInputLine
> If Len(strInputLin e) = 0 Then
> GoTo cont
> Else
> rst!Line3 = strInputLine
> End If
> Line Input #intImportFile, strInputLine
> If Len(strInputLin e) = 0 Then
> GoTo cont
> Else
> rst!Line4 = strInputLine
> End If
> Line Input #intImportFile, strInputLine
> If Len(strInputLin e) = 0 Then
> GoTo cont
> Else
> rst!Line5 = strInputLine
> End If
> cont:
> If EOF(intImportFi le) Then GoTo end1
> rst.Update
>
> Wend
> 'loop
> end1:
> Close intImportFile
> End Function
>
>
> Any help would be sincerely appreciated...
>
> Thank you....[/color]

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.t xt"
strTableName = "TempTable"

Set dbs = CurrentDb
Set rst = dbs.OpenRecords et(strTableName , dbOpenDynaset)

intImportFile = FreeFile
Open strImportFile For Input As #intImportFile

Do Until EOF(intImportFi le)
rst.AddNew
i = 1
varInputLine = Null
Do Until varInputLine = "" Or EOF(intImportFi le)
Line Input #intImportFile, varInputLine
If Trim(varInputLi ne) <> "" Then
varInputLine = Trim(varInputLi ne)
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]

Re: Text File Import

pietlinden@hotm ail.com (Pieter Linden) wrote in message news:<bf31e41b. 0408121640.469c 900f@posting.go ogle.com>...[color=blue]
> 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)[/color]

Perhaps the following code will help:

'--------begin form code
Private Sub cmdSelectImport Directory_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 = SHBrowseForFold er(BInfo)
strDir = Space(255)
If lngID <> 0 Then
If SHGetPathFromID List(ByVal lngID, strDir) Then
strFolder = Left(strDir, InStr(strDir, Chr(0)) - 1)
End If
End If
strDir = strFolder
cbxShowFiles.Ro wSourceType = "Value List"
cbxShowFiles.Ro wSource = ""
'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.Ro wSource = "" Then
cbxShowFiles.Ro wSource = strFile
Else
cbxShowFiles.Ro wSource = cbxShowFiles.Ro wSource & strFile
strFile = Dir()
If strFile <> "" Then cbxShowFiles.Ro wSource =
cbxShowFiles.Ro wSource & ";"
End If
Loop
cbxShowFiles.Va lue = 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 SHBrowseForFold er Lib "shell32.dl l" (ByRef lpbi As
MyBrowseInfo) As Long
Declare Function SHGetPathFromID List Lib "shell32.dl l" (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