Import multiple rows of text into one field

[thedragonman]

Greetings,

I have searched and searched for the best way to do this and I am stumped so here goes. I will try to be as detailed as I can with my explaination.

I have a text file with data I want to import into access.
here is what it looks like.

00100378435
NOTE: 01/13/05 07:27 Cat: Entered by: JOSH
JOSH 730 CSR/JOSH TALKED W/FSM 1013. FSM ADVISES THAT
SOFTWARE ISSUE THAT DD CANNOT CORRECT IN THE FIELD.
ADVISE CUST THAT NEW SOFTWARE IS DUE OUT SOON.
JTM/DD

00100378436
NOTE: 01/13/05 08:30 Cat: Entered by: JOSH
830 CSR/JOSH TALKED W/FSM 1013. FSM ADVISES THAT
SOFTWARE ISSUE IS OUT.
JTM/DD

Each 11 character number field represents an account and the notes go with that account.

When I import it into access i can get it into 2 columns.

The first column has the numbers and the second column has the notes but each line of notes creates a seperate row instead of all the notes in 1 row.

So I end up with Column 1 having in the first row the number then several empty rows of cells (depending on how many lines of text there are) and in Column 2 I get multiple rows each with one line of text.

I do have an index file that lists the account number with the start and end row of the text file that is such as this:

Start,End,Account
1,33,00100378435
34,61,00100388570
62,67,00100409643
68,69,00100425683
70,94,00100434326

I thought I could use the GetRows command to pull the rows into one field but I havent been able to figure out how to do this.

Just to let you know the text file of information has over 4 million lines of text in it so I cant just edit the file. Each line has a carriage return as well as spaces.

I am not a stranger to Access but I am not by any means an expert.

Any help on how to accomplish this would be most helpful.

Thank you in advance.

 

[Treason]

Using the TransferText method is probaly not the way to go.. I would write a Function to manually put the data into a table the way I want it. The question of the day is, Is every "record" formatted the same way?

ID#
Note: "" Entered by: ""
then some notes following

If this is true then it shouldn't be too hard.

here's a basic example of how to do it:

Function TestMe()
    Dim MyLine as String
    Dim MyID as String
    Dim MyNote as String
    Dim rst as recordset

    Set rst = CurrentDb.OpenRecordset("MyTable", dbOpenDynaset)
    Open "C:\MyFile.txt" For Input As #1
    Input #fn, MyLine
    Do While Not EOF(1)
             'This checks if it is a new ID#
             If IsNumeric(MyLine) then
                 'Stores the ID#
                  MyID = MyLine
                  'Skips the next 2 lines
                  Input #fn, MyLine
                  Input #fn, MyLine

               Do Until IsNumeric(MyLine) 
                  'Now it will loop until the next record and concatenates the notes together
                  MyNote = MyNote & MyLine
                  Input #fn, MyLine
               Loop
               'Sigh, now it stores into the database
                  With rst
                       .AddNew
                          rst("ID") = MyID
                          rst("Note") = MyNote
                       .Update
                  End With

                  MyID = ""
                  MyNote = ""
             Else
                 Input #fn, MyLine
             End If
        Loop
    Close #fn
    rst.Close
End Function

I didn't test it... but thats one way to go... Open the text file manually, loop through the text, pull what you want... yadda yadda yadda

good luck

 

[thedragonman]

Here is a sample

That is the basic structure.

I have attached a sample of the data and the index file.

Thanks for the quick reply.

 

[richary]

Treason's sub slightly modified and quickly tested with sample file

Function TestMe()
Dim MyLine As String
Dim MyID As String
Dim MyNote As String
Dim rst As Recordset

Set rst = CurrentDb.OpenRecordset("MyTable", dbOpenDynaset)
Open "C:\MyFile.txt" For Input As #1
Input #1, MyLine
Do While Not EOF(1)
'This checks if it is a new ID#
If IsNumeric(MyLine) Then
'Stores the ID#
MyID = MyLine
'read next line - should be start of text
Input #1, MyLine

Do Until IsNumeric(MyLine)
'Now it will loop until the next record and concatenates the notes together
MyNote = MyNote & MyLine
Input #1, MyLine
Loop
'Sigh, now it stores into the database
With rst
.AddNew
rst("ID") = MyID
rst("Note") = MyNote
.Update
End With

MyID = ""
MyNote = ""
Else
Input #fn, MyLine
End If
Loop
Close #fn
rst.Close
End Function

As it stands it falls over on the last record, but if you append any number to the last line in your file, it works OK.

Thanks to Treason who supplied the original.

The index file is not needed with this solution.

 

[thedragonman]

Humm..keep getting error 13 type mismatch on this line

Set rst = CurrentDb.OpenRecordset("MyTable", dbOpenDynaset)

I am using Access 2000, ADO 2.9, Jet 4

I made the references to DAO3.6 objects in the VB Editor.


How are you calling the function if you don't mind me asking?

I made a module then called it with a Macro.

 

[thedragonman]

Fixed a few errors but ...

I have worked through a few errors but that last one has me stumped.

I used the exact code above with a few modifications till I get it to at least run.

Here is my exact code.

Function TestMe()
Dim MyLine As String
Dim MyID As String
Dim MyNote As String
Dim rst As Recordset

Set rst = CurrentDb.OpenRecordset("MyTable", dbOpenDynaset)
	
	Open "\allnotes.txt" For Input As #1
		Input #1, MyLine
			Do While Not EOF(1)
			'This checks if it is a new ID#
		If IsNumeric(MyLine) Then
		'Stores the ID#
			MyID = MyLine
			'read next line - should be start of text
	Input #1, MyLine

	Do Until IsNumeric(MyLine)
	'Now it will loop until the next record and concatenates the notes together
		MyNote = MyNote & MyLine
			Input #1, MyLine
				Loop
				'Sigh, now it stores into the database
			With rst
			.AddNew
				rst("ID") = MyID
				rst("Note") = MyNote
			.Update
	End With

		MyID = ""
		MyNote = ""
	Else
		Input #fn, MyLine
	End If
		Loop
	Close #fn
	rst.Close
End Function

thanks again for all the help.

 

[thedragonman]

Got it so far ...

I had to add DAO to the begining of the Recordset DIM statement as this:

Dim rst As DAO.Recordset

Now, If I can I would like to do 2 things.

1. fix it from erroring out when it reaches the last record.
2. put in a space when it hits the word NOTE

Thanks for all the help.

 

[Treason]

Throw in a resume next clause... it's the lazy way out but it works

After Dim rst As DAO.Recordset

add..

On Error Resume Next