A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » General Discussion
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

populating an Access table from a text file



 
 
Thread Tools Display Modes
  #1  
Old June 4th, 2010, 02:24 AM posted to microsoft.public.access,comp.databases.ms-access
s
external usenet poster
 
Posts: 52
Default 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.
  #2  
Old June 4th, 2010, 06:56 AM posted to microsoft.public.access
KARL DEWEY
external usenet poster
 
Posts: 10,767
Default populating an Access table from a text file

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

--
Build a little, test a little.


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

  #3  
Old June 4th, 2010, 11:12 AM posted to microsoft.public.access,comp.databases.ms-access
Douglas J. Steele[_3_]
external usenet poster
 
Posts: 3,143
Default populating an Access table from a text file

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" wrote in message
...
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.



  #4  
Old June 5th, 2010, 07:09 AM posted to microsoft.public.access,comp.databases.ms-access
jbguernsey
external usenet poster
 
Posts: 2
Default populating an Access table from a text file

On Jun 4, 2:24*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 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
  #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
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 12:56 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.