View Single Post
  #5  
Old June 5th, 2010, 04:03 PM posted to microsoft.public.access,comp.databases.ms-access
Marshall Barton
external usenet poster
 
Posts: 5,361
Default populating an Access table from a text file

s wrote:

A text file has data in a format like the below:

Title1
Cell 1 data
Title 2
Cell 2 data
Title 3
Cell 3 data
.
Title N
Cell N data

I guess I can read this using some VBA code posted at
http://www.mvps.org/access/modules/mdl0057.htm and
http://www.granite.ab.ca/access/readtextfile.htm, add an Instr
function to it so that when I read the text file, I know when to
insert the data properly in the table.

1. Is there another way I can directly import the data into an Access
2007 table without VBA code? I know Access allows import from data,
but my data is not as formatted as I want so the data might end up
being in the table in a way such as

Field 1 of Access Table

Title 1
Cell 1
Title 2
Cell 2
.
Title N
Cell N

whereas I want it to be like
Field 1 Field 2 Field
3...................Field N of the Access table
Cell 1 data Cell 2 data Cell 3 data

2. If I have to use VBA, are the methods I linked to proper(Am i going
in the right direction) and advisable or are there better/easier ways
to do what I want to achieve.



No, you want the result to be a normalized table:
ID Title Data
1 aaa xxx
2 bbb yyy
. . .
n kkk zzz

Then you will be able to do interesting things with the
table. A table that looks like you said you want will be
near unusable anywhere other than printing a spreadsheet.
If a spreadsheet is all you want then use a spreadsheet
program instead of a database.

Note that a table can have up to 255 fields so you N must be
less than that. Also, a table has no inherent order to the
records it contains so you will need a field such as the ID
field above to preserve the order of the information in the
text file. (A query is the ONLY way to present sorted
records.)

And, yes you will need to use a fairly simple VBA procedure
to load the data into whatever kind of table you decide to
use. Chuck Grimsby's class is a good solution to importing
a complex text file, but may be overkill for your fairly
simple text file. The other one is closer to what I think
you can use, but it does not deal with the every other line
distinction.

OTOH, I think you can just import the text file into a
spreadsheet and transpose it to get what you said you want.

--
Marsh