Prev: Resource Booking System in Access
Next: How to build the relation of "1" to"N"
From: s on 3 Jun 2010 21:24
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.
From: Douglas J. Steele on 4 Jun 2010 06:12
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(a)mailinator.com> wrote in message
news:d07736b2-e9a5-4a11-abb8-784778c95eaf(a)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.


From: jbguernsey on 5 Jun 2010 02:09
On Jun 4, 2:24 am, s <s...(a)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.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
From: r on 8 Jun 2010 09:15
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.
 | 
Pages: 1
Prev: Resource Booking System in Access
Next: How to build the relation of "1" to"N"