I am wondering if there is a way to import text files with a format as 

follows



16.    *2 x 500 gall Fuel Tank



17.    *1 x 1000 gall Fuel Tank



18.    *250mm Wavin Perforated PVC Drain Pipes

       Horseshoe shape



19.    *Pallet of Hoses



I am having no problems with the majority of rows, however alot occupy 2 

lines and subsequently create 2 records.



Is there a way to do this? There are hundreds of these files and each has 

thousands of records so obviously we don't want to have to go through each 

one converting it manually.



Thanks for any help



Tony

Tony, you could write some code which concatenates adjacent rows unless it

begins with 'xx.' where xx is the number of the row.



I guess you could do this separately before importing (ie creating a new

text file with the changes) or you could read the file into a recordset on

the fly.



My string coding's a bit scratchy at the mo. so hopefully someone else may

be able to give you some hints...



HTH,



Andrew











-----Original Message-----

From: Tony DiNucci [mailto:tony@m...]

Sent: Wednesday, 20 February 2002 7:29 AM

To: Access

Subject: [access] Importing Text files with line breaks?





I am wondering if there is a way to import text files with a format as 

follows



16.    *2 x 500 gall Fuel Tank



17.    *1 x 1000 gall Fuel Tank



18.    *250mm Wavin Perforated PVC Drain Pipes

       Horseshoe shape



19.    *Pallet of Hoses



I am having no problems with the majority of rows, however alot occupy 2 

lines and subsequently create 2 records.



Is there a way to do this? There are hundreds of these files and each has 

thousands of records so obviously we don't want to have to go through each 

one converting it manually.



Thanks for any help



Tony

Here's a rough guide to one method:



<Pseudo Code - Untested>

	Open text file



	Move to start of text file

	strPreviousLine = CurrentLine

	Move to second line	



	Do Until End of File

		Get CurrentLine

		If CurrentLine begins with a number then

			Write strPreviousLine to new record

			strPreviousLine = CurrentLine

		else

			Add CurrentLine to strPreviousLine

		end if

		Move to next line in text file.

	Loop

</Pseudo Cose>



Need to check for boundary conditions etc, but this would be one option. 



HTH,



Andrew

	



-----Original Message-----

From: Haslett, Andrew [mailto:andrew.haslett@i...]

Sent: Wednesday, 20 February 2002 9:08 AM

To: Access

Subject: [access] RE: Importing Text files with line breaks?





Tony, you could write some code which concatenates adjacent rows unless it

begins with 'xx.' where xx is the number of the row.



I guess you could do this separately before importing (ie creating a new

text file with the changes) or you could read the file into a recordset on

the fly.



My string coding's a bit scratchy at the mo. so hopefully someone else may

be able to give you some hints...



HTH,



Andrew











-----Original Message-----

From: Tony DiNucci [mailto:tony@m...]

Sent: Wednesday, 20 February 2002 7:29 AM

To: Access

Subject: [access] Importing Text files with line breaks?





I am wondering if there is a way to import text files with a format as 

follows



16.    *2 x 500 gall Fuel Tank



17.    *1 x 1000 gall Fuel Tank



18.    *250mm Wavin Perforated PVC Drain Pipes

       Horseshoe shape



19.    *Pallet of Hoses



I am having no problems with the majority of rows, however alot occupy 2 

lines and subsequently create 2 records.



Is there a way to do this? There are hundreds of these files and each has 

thousands of records so obviously we don't want to have to go through each 

one converting it manually.



Thanks for any help



Tony

Tony,



Assuming that you are use a FileSystemObject and  TextStream to import your

file:



Private Sub ImportFile()



	Dim fso As Scripting.FileSystemObject

      Dim ts As Scripting.TextStream

	Dim strTemp as String



	Set fso = new Scripting.FileSystemObject



      Set ts = fso.OpenTextFile("c:\InputFiles\MyFile.txt")



      Do While Not ts.AtEndOfStream

	    strTemp = ts.Readline '(Also moves to next line)



	    if len(strTemp) > 0 then



		  '...Put the string in strTemp where you want it.....



          end if

	Loop



	ts.Close



	Set fso = Nothing



end sub



Ian Ashton



-----Original Message-----

From: Tony DiNucci [mailto:tony@m...]

Sent: Tuesday, February 19, 2002 8:59 PM

To: Access

Subject: [access] Importing Text files with line breaks?





I am wondering if there is a way to import text files with a format as

follows



16.    *2 x 500 gall Fuel Tank



17.    *1 x 1000 gall Fuel Tank



18.    *250mm Wavin Perforated PVC Drain Pipes

       Horseshoe shape



19.    *Pallet of Hoses



I am having no problems with the majority of rows, however alot occupy 2

lines and subsequently create 2 records.



Is there a way to do this? There are hundreds of these files and each has

thousands of records so obviously we don't want to have to go through each

one converting it manually.



Thanks for any help



Tony

Thanks for the help and speedy respose :^)

Tony,



If the pattern persists of an ASTERISK (*) marking the beginning of each

data item and the line number is not needed you could test for with InStr

and discard up to the (*) and read until encountering the next one.



Just a thought.



Walt



16.    *2 x 500 gall Fuel Tank



17.    *1 x 1000 gall Fuel Tank



18.    *250mm Wavin Perforated PVC Drain Pipes

       Horseshoe shape



19.    *Pallet of Hoses

This is a multi-part message in MIME format.



------=_NextPart_000_0009_01C1B966.FA9954C0

Content-Type: text/plain;

	charset="us-ascii"

Content-Transfer-Encoding: 7bit



Expounding on Ian's ImportFile routine.  Here is code that will perform

the function you want.  I've created an array to store the data from the

text file and then add the data in the array to a table.



Private Sub ImportFile()



    Dim fso As FileSystemObject

    Dim ts As TextStream

    Dim rs As DAO.Recordset

    Dim strTemp As String

    Dim strRcd() As String

    Dim intRcdCount As Integer

    Dim i As Integer

    Dim intFindPeriod As Integer

    Dim strPath As String

    Dim strFile As String

    Dim strTable As String

   

    ' Change the path to the path where your text file is located

    strPath = "C:\Documents and Settings\John\My Documents\Work\AccessXP

Tests\"

    ' Name of your text file

    strFile = "MyFile.txt"

    ' name of table the data is to go into

    strTable = "tblFileSystem"

   

    Set fso = New FileSystemObject

    Set rs = CurrentDb.OpenRecordset(strTable)

    Set ts = fso.OpenTextFile(strPath & strFile)



    Do While Not ts.AtEndOfStream

        strTemp = ts.ReadLine '(Also moves to next line)



        If Len(strTemp) > 0 Then

            ' Find the period after the record number

            intFindPeriod = InStr(1, strTemp, ".  *")

           

            ' If intFindPeriod is greater than 0 then _

                this is the first line of the record

            If intFindPeriod > 0 Then

                ' Keep track of the number of records

                intRcdCount = intRcdCount + 1

                ' Redim the strRcd array so that the new _

                    record can be added

                ReDim Preserve strRcd(intRcdCount)

                ' Strip the Record Number, period, and _

                    first asterisk from the record _

                    (eg 1.  #, 2.  #)

                strRcd(intRcdCount) = Right(strTemp, Len(strTemp) -

(intFindPeriod + 3))

                ' Reset the intFindPeriod to zero

                intFindPeriod = 0

            Else

                ' This is the next line of the record (intRcdCount). _

                    concatenate it to the strRce(intRcdCount) array. _

                    trim all leading spaces.

                strRcd(intRcdCount) = strRcd(intRcdCount) & " " &

Trim(strTemp)

            End If

        End If

    Loop



    ' Loop through the array and _

        add each record to the table

    For i = 1 To intRcdCount

        rs.AddNew

            rs!descr = strRcd(i)

        rs.Update

    Next i

   

    ts.Close

    rs.Close

   

    Set rs = Nothing

    Set fso = Nothing



End Sub









John Ruff - The Eternal Optimist J



Always looking for Contract Opportunities



 



9306 Farwest Dr SW



Lakewood, WA 98498



papparuff@c...





-----Original Message-----

From: Ian Ashton [mailto:ian@c...]

Sent: Tuesday, February 19, 2002 3:51 PM

To: Access

Subject: [access] RE: Importing Text files with line breaks?





Tony,



Assuming that you are use a FileSystemObject and  TextStream to import

your

file:



Private Sub ImportFile()



        Dim fso As Scripting.FileSystemObject

      Dim ts As Scripting.TextStream

        Dim strTemp as String



        Set fso = new Scripting.FileSystemObject



      Set ts = fso.OpenTextFile("c:\InputFiles\MyFile.txt")



      Do While Not ts.AtEndOfStream

            strTemp = ts.Readline '(Also moves to next line)



            if len(strTemp) > 0 then



                  '...Put the string in strTemp where you want it.....



          end if

        Loop



        ts.Close



        Set fso = Nothing



end sub



Ian Ashton



-----Original Message-----

From: Tony DiNucci [mailto:tony@m...]

Sent: Tuesday, February 19, 2002 8:59 PM

To: Access

Subject: [access] Importing Text files with line breaks?





I am wondering if there is a way to import text files with a format as

follows



16.    *2 x 500 gall Fuel Tank



17.    *1 x 1000 gall Fuel Tank



18.    *250mm Wavin Perforated PVC Drain Pipes

       Horseshoe shape



19.    *Pallet of Hoses



I am having no problems with the majority of rows, however alot occupy 2

lines and subsequently create 2 records.



Is there a way to do this? There are hundreds of these files and each

has thousands of records so obviously we don't want to have to go

through each one converting it manually.



Thanks for any help



Tony

---

Change your mail options at http://p2p.wrox.com/manager.asp or to

unsubscribe send a blank email to $subst('Email.Unsub').

Thnaks for the help mate.