importing txt file

[deekras]

i am trying to import a txt file into access. normally i don't have a problem with importing. but this file is strange. there are 2 or 3 or 4 rows for each record. basically each record starts with the same digit - no other row begins with that digit, but depending on how many and which changes must be made, there is more or less information. once i get it all onto 1 row, i think i can work with column widths. is there a way that i can work with such a file. i cannot get this file in any other format.

 

[deekras]

in word, using edit & replace, i was able to make this work and then import easily into access. but since i get many of these files a day, it is not practical to first make the changes in word.

 

[WayneRyan]

deekras,

Can you post a small sample of the file?

Wayne

 

[deekras]

this is what i did and it works although i am sure there is a better way.

i made a macro in Word - called it macro 1. in it i replace all paragraph marks(^p) with tab marks(^t) and then i replace all ^t6 (6 being the digit that marks new record) with ^p6. then i save the file. (the code is very long, so i am just summarizing it here)

in access i wrote this code to open Word and run the macro. then i import it into access.


Dim appWord As Object
Set appWord = CreateObject("Word.Application")
appWord.Visible = False
appWord.displayalerts = False
appWord.Documents.Open filename:="c:\file.txt"
appWord.Run "macro1"
appWord.Quit

is there a better way?

 

[WayneRyan]

deekras,

I'd point word at the file, save it as an MS-DOS textfile with
line breaks, and parse that with VBA for inclusion into your
table(s).

If you post a sample of your file, we can map it to your table
quite easily.

Wayne