populating an Access table from a text file

[s]

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 10 Cell 10 data

Title 11
Cell 11 data
..
..
..
Title 15 Cell 15 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.

Any advice would be welcome.

 

[KARL DEWEY]

If you could post some actual data with puncuation someone might have an idea
on how to proceed.

 

[Douglas J. Steele]

I can't think of anyway to import that other than to use VBA.

 

[jbguernsey]

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 10 Cell 10 data

Title 11
Cell 11 data
.
.
.
Title 15 Cell 15 data
.
.
.
Title N
Cell N data

I guess I can read this using some VBA code posted athttp://www.mvps.org/access/modules/mdl0057.htmandhttp://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.

Any advice would be welcome.

The first problem is in rows like > Title 10 Cell 10 data

Is there any way of determining where Title N finishes and Cell N
Data starts?

If there is you're in business. If there is no consistent end to
Title N or start to Cell N then I can think of nothing (other than by
hand) that can be done ...

JB

 

[r]

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.

Thanks to all posters who responded. I used VBA code and it worked fine
with the test files.