Importing A Custom Text File

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Good eveing all,

I am new to Access so please excuse my inability to code correctly yet !

I have a large text file to import (11,279,000 rows) it is basically set-out as per the table below

Name John
Surname Smith

Name Anne
Surname Franks

etc...

There are actually 46 feilds. Usually I import these into Microsoft Excel via VBA and then format them in a Workbook, when there is less than 65,536 rows that is. I have a few problems and questions, firstly when ever I try to run the RunCode Macro to run a procedure I have typed it says it can not find the procedure, when I press on the build button for the Macro I can see the Project, the module, but no procedure so that I can run it, does anyone know why this might be happening ? Secondly am I able to read values into an array just like Microsoft Excel VBA and then import them into a Table ? This would enable me to turn the data around to the standard format, ie

Name Surname
John Smith
Anne Franks

Any help you may be able to provide would be greatly appreciated, I would really like to knock this part of the project over as the rest of the data manipulation I am required to do is rather easy, I think at this stage anyway.

Yours sincerely,

Brent McIntyre
 
I have a large text file to import (11,279,000 rows) it is basically
set-out as per the table below

Name John
Surname Smith

Name Anne
Surname Franks

etc...

There are actually 46 feilds.
Click to expand...


Do While True
' parse the input one line at a time
Input Line #wInFile, strInLine

' You're not clear how the text lines are punctuated
' fixed length or what -- so you'll have to write
' this procedure yourself to split out the two parts
GetTokensFrom strInLine, strPrefix, strData

' look for an empty line as end-of-record
If Len(strPrefix)=0 Then
' wrap up the old record and start a new one
rs.Update
rs.AddNew

Else
' put in the new field
' strPrefix = "Surname", strData = "Franks"
rs.Fields(strPrefix) = strData

End If

Loop


Actually, this won't work on its own because the record AddNew and Update
need to be better managed at the start and end; and there is no error
trapping: but you get the picture.


Hope that helps a bit


Tim F
 
Tim,

Thanks for your help with the Import Code, I got the drift and will be able to work with it from here.

Did you have any ideas about the second part of the question ?

I have a few problems and questions, firstly when ever I try to run the RunCode Macro to run a procedure I have typed it says it can not find the procedure, when I press on the build button for the Macro I can see the Project, the module, but no procedure so that I can run it, does anyone know why this might be happening ?

Any further help would be greatly appreciated.

Yours sincerely,

Brent McIntyre
 
Tim,

Thanks for your post, it answered the second part of my enquiry, I got the idea of the code and will be able to complete that section from here, I hope.

Just to be a further annoyance did you have any ideas about the first part of the question ?

I have a few problems and questions, firstly when ever I try to run the RunCode Macro to run a procedure I have typed it says it can not find the procedure, when I press on the build button for the Macro I can see the Project, the module, but no procedure so that I can run it, does anyone know why this might be happening ?

Any further help would be greatly appreciated.

Yours sincerely,

Brent McIntyre
 
Brent McIntyre said:
I have a few problems and questions, firstly when ever I try to run
the RunCode Macro to run a procedure I have typed it says it can not
find the procedure, when I press on the build button for the Macro I
can see the Project, the module, but no procedure so that I can run
it, does anyone know why this might be happening ?
Click to expand...

RunCode can only execute Function procedures, not Sub procedures. If
you have an existing Sub procedure that you want to run with RunCode,
and you don't want to change it into a Function, create a "wrapper"
function that calls the Sub, and use RunCode to to execute *that*.
 
Dirk,

Thanks for your assistance, I consequently found this out by reading a few different help files, who knew they can actually be informative ! Your answer was much more succinct though.

Yours sincerely,

Brent McIntyre
 
Did you have any ideas about the second part of the question ?
Click to expand...

I see you got the answer to this already. Yes, the help files do help, but
you have to persevere with them!

B Wishes


Tim F
 
Back
Top