Importing text files to access(2007)

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

wvrider - 18 Dec 2007 04:35 GMT

I have a text file(.txt) that is has 20,000 plus records, and each record
contains about 30 columns of information. I have 55 of these files. The
problem with a simple import is that there is a couple lines of information
that belong together(ie there are two lines of information that should be on
one line). Below is an example of my txt file.

20071217 Tom Jane
20071217 Bob Bobby
20071218 Kim Bob
20071218 Mike Ike

What i am needing is to get the files into access, and have the database
look like this:

Col1 Col2 Col3 Col4 Col5
20071217 Tom Jane Bob Bobby
20071218 Kim Bob Mike Ike

I know this is probably an easy thing, but my access skills and database
skills are very primative. I know some Vb.net, so I don't know if can do it
via VSTA, or a sql statement. Most of the problems i run into is how much
information I have.

Thanks for all the help.

Reply to this Message

boblarson - 18 Dec 2007 06:30 GMT

Looking at your example, I hope it isn't actually like that. Your result
would appear, at first glance, to be non-normalized. Are you just planning
on getting the information into the database and then transforming the data
so it is normalized, or are you really going to have repeating fields like
that?

It is possible to write a custom function to import the text in the way you
want it, or to import as is and iterate through using a recordset to put the
data in that format.

Signature

Bob Larson
Access World Forums Super Moderator
Utter Access VIP
Tutorials at http://www.btabdevelopment.com
If my post was helpful to you, please rate the post.
__________________________________

> I have a text file(.txt) that is has 20,000 plus records, and each record
> contains about 30 columns of information. I have 55 of these files. The
[quoted text clipped - 20 lines]
>
> Thanks for all the help.

Reply to this Message

wvrider - 18 Dec 2007 12:43 GMT

thanks bob. The files are an output from sql server I think, not sure. I
think the reason why there is two lines is because the lines of data were to
wide for notepad. if that makes any sense, That is the only reason why I
think that there is two lines. If i can just get it into the database then I
can begin breaking it down.

thanks

> Looking at your example, I hope it isn't actually like that. Your result
> would appear, at first glance, to be non-normalized. Are you just planning
[quoted text clipped - 30 lines]
> >
> > Thanks for all the help.

Reply to this Message

boblarson - 18 Dec 2007 19:55 GMT

Check out my post her
http://www.access-programmers.co.uk/forums/showthread.php?t=137660&highlight=Ope
n+strfile+input

and see if you can tweak it to work for you. The code I am using there was
for someone who was importing text which was sometimes two lines, sometimes
one. If you modify the test so that it cycles based on whether this:

20071217

is the same as the line above so put that line into the same row.

See what you can do with it.

Signature

Bob Larson
Access World Forums Super Moderator
Utter Access VIP
Tutorials at http://www.btabdevelopment.com
If my post was helpful to you, please rate the post.
__________________________________

> thanks bob. The files are an output from sql server I think, not sure. I
> think the reason why there is two lines is because the lines of data were to
[quoted text clipped - 38 lines]
> > >
> > > Thanks for all the help.

Reply to this Message

wvrider - 19 Dec 2007 02:11 GMT

thanks bob, I will try to persuade it into what i need. I'll post up my
success or failures. Thanks.

> Check out my post here
> http://www.access-programmers.co.uk/forums/showthread.php?t=137660&highlight=Ope
n+strfile+input
[quoted text clipped - 51 lines]
> > > >
> > > > Thanks for all the help.

Reply to this Message