f



populating an Access table from a text file

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.
0
s
6/4/2010 1:24:14 AM
comp.databases.ms-access 42670 articles. 0 followers. Post Follow

4 Replies
935 Views

Similar Articles

[PageSpeed] 36

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

-- 
Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/DJSteele
Co-author: Access 2010 Solutions, published by Wiley
(no e-mails, please!)

"s" <s@mailinator.com> wrote in message 
news:d07736b2-e9a5-4a11-abb8-784778c95eaf@r5g2000yqr.googlegroups.com...
>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. 


0
Douglas
6/4/2010 10:12:13 AM
On Jun 4, 2:24=A0am, s <s...@mailinator.com> 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 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.h=
tm, 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 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0Field 2 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0Field
> 3...................Field N of the Access table
> Cell 1 data =A0 =A0 =A0 =A0 =A0Cell 2 data =A0 =A0 =A0 =A0Cell 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
0
jbguernsey
6/5/2010 6:09:47 AM
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
0
Marshall
6/5/2010 3:03:41 PM
On 6/5/2010 11:03 AM, Marshall Barton wrote:

>
> 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

Thanks to all posters who responded. I used VBA code and it worked fine 
with the test files.
0
r
6/8/2010 1:15:04 PM
Reply: