🚀 go-pugleaf

You could import it into a single table and then run Queries to create
or append to the other tables. That is, provided the total size doesn't
exceed Access 2000's limit of 2GB per database. You can, of course, use
tables in related database so that the limit really applies only to the
size of a single table plus the necessary overhead for a one-table
database.

Without more information, I can't be more specific, but I'd presume
that the multiple tables into which you want to put the information are
related by some common information.

You could, of course, open the file as a sequential file, open all the
pertinent Access tables, read it record by record, and append the
information to appropriate Access tables as you go along. But, it seems
to me there'll be a higher probablity of someting going awry while your
update is in progress if you take this approach.

  "ASA GSI" <jbruce@asaglobalserv.com> wrote:
> Hi
>
>  I an using Access 2000, I will be given a text file that I need to
import
> into Access, the run a report from that data.  The text file contains
> 399,000 records with approx. 206 fields that is delimitated with
commas.
> How do I import this text file into access, and into different tables.
>
> For example:
> Field1 - Field36 into table1
> Field37 - Field47 into table2
> ........
>
> I would like to use code but not sure the approach to accomplish this.
>
> Thank you in advanced.
> Jordan
> jbruce@asaglobalserv.com
>
>

--
 L. M. (Larry) Linson
  http://www.ntpcug.org - North Texas PC User Group - Visit and Join
  http://www.ntmsdevsigs.homestead.com - NTPCUG Developer SIGs
  http://homestead.deja.com/user.accdevel - Access examples


Sent via Deja.com http://www.deja.com/
Before you buy.

Yes I do have a schema already in place and will be using primary and
foriegn keys


Larry Linson <accdevel@my-deja.com> wrote in message
news:8tpml6$9tm$1@nnrp1.deja.com...
> You could import it into a single table and then run Queries to create
> or append to the other tables. That is, provided the total size doesn't
> exceed Access 2000's limit of 2GB per database. You can, of course, use
> tables in related database so that the limit really applies only to the
> size of a single table plus the necessary overhead for a one-table
> database.
>
> Without more information, I can't be more specific, but I'd presume
> that the multiple tables into which you want to put the information are
> related by some common information.
>
> You could, of course, open the file as a sequential file, open all the
> pertinent Access tables, read it record by record, and append the
> information to appropriate Access tables as you go along. But, it seems
> to me there'll be a higher probablity of someting going awry while your
> update is in progress if you take this approach.
>
>   "ASA GSI" <jbruce@asaglobalserv.com> wrote:
> > Hi
> >
> >  I an using Access 2000, I will be given a text file that I need to
> import
> > into Access, the run a report from that data.  The text file contains
> > 399,000 records with approx. 206 fields that is delimitated with
> commas.
> > How do I import this text file into access, and into different tables.
> >
> > For example:
> > Field1 - Field36 into table1
> > Field37 - Field47 into table2
> > ........
> >
> > I would like to use code but not sure the approach to accomplish this.
> >
> > Thank you in advanced.
> > Jordan
> > jbruce@asaglobalserv.com
> >
> >
>
> --
>  L. M. (Larry) Linson
>   http://www.ntpcug.org - North Texas PC User Group - Visit and Join
>   http://www.ntmsdevsigs.homestead.com - NTPCUG Developer SIGs
>   http://homestead.deja.com/user.accdevel - Access examples
>
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.

When you use the Import command, and specify a text file, the Import
Text Wizard will pop up. The wizard gives you the option of "skipping"
any field you like. So you can import certain fields to one table, then
re-run it and import other fields to another table, etc.

When you get to the last step in the wizard, click the "Advanced"
button, and then you can save the import spec and be able to use it
later. You'll end up with a different spec for each table you want to
import into.

HTH,
Frank

In article <bRXL5.52866$Z2.756820@nnrp1.uunet.ca>,
  "ASA GSI" <jbruce@asaglobalserv.com> wrote:
> Hi
>
>  I an using Access 2000, I will be given a text file that I need to
import
> into Access, the run a report from that data.  The text file contains
> 399,000 records with approx. 206 fields that is delimitated with
commas.
> How do I import this text file into access, and into different tables.
>
> For example:
> Field1 - Field36 into table1
> Field37 - Field47 into table2
> ........
>
> I would like to use code but not sure the approach to accomplish this.
>
> Thank you in advanced.
> Jordan
> jbruce@asaglobalserv.com
>
>

--
Frank S Pappalardo - www.fpappalardo.com
Troop/Pack 335 CR, SS Simon & Jude Church
Bethlehem, PA - Minsi Trails Council


Sent via Deja.com http://www.deja.com/
Before you buy.

Jordan, I'd do this two different ways.

1.  First, when you are in your access database, you can right click in the
tables view and select Import from the pop up menu.  You'll get a dialog box
that lets you select the file type (select .txt) and then browse to the file
in question and select it.  In the resulting import wizard, you'll be asked
questions about your text file.  Answer them but when you get to the point
where you can tell what type each field you import will be, you can also
check an option to skip certain fields.  I'd just check the option on fields
37-206 on the first import and the next time around, check the skip option
on fields 1-37 and 48-206, etc.  Each time making a new table.  In the end,
you would have a bunch of new tables, and each would only contain the data
from the select fields you had imported.

2.  The next way is to use a really good text editor.  I like textpad
because it has a block select mode.  I'd open the entire file, when I'd copy
a series of columns to the clipboard, and paste them into a new text file.
Create as many text files as you need to split up the original file and then
import those individually.

I have used both methods to accomplish what you are wanting to do and they
both take time, but there is really no other way to do it.  Good luck and I
hope this helps.

....Brad


"ASA GSI" <jbruce@asaglobalserv.com> wrote in message
news:bRXL5.52866$Z2.756820@nnrp1.uunet.ca...
> Hi
>
>  I an using Access 2000, I will be given a text file that I need to import
> into Access, the run a report from that data.  The text file contains
> 399,000 records with approx. 206 fields that is delimitated with commas.
> How do I import this text file into access, and into different tables.
>
> For example:
> Field1 - Field36 into table1
> Field37 - Field47 into table2
> ........
>
> I would like to use code but not sure the approach to accomplish this.
>
> Thank you in advanced.
> Jordan
> jbruce@asaglobalserv.com
>
>

I would import the text file into a temp Access table.
Then I would create Append queries to add the data from the temp table to
the real ones.
Then I would code the whole procedure so it could be done with the click of
a button every day.
--
Joe
Access MVP
Check out Dev Ashish's web site for answers to common questions
http://www.mvps.org/access/

ASA GSI <jbruce@asaglobalserv.com> wrote in message
news:bRXL5.52866$Z2.756820@nnrp1.uunet.ca...
> Hi
>
>  I an using Access 2000, I will be given a text file that I need to import
> into Access, the run a report from that data.  The text file contains
> 399,000 records with approx. 206 fields that is delimitated with commas.
> How do I import this text file into access, and into different tables.
>
> For example:
> Field1 - Field36 into table1
> Field37 - Field47 into table2
> ........
>
> I would like to use code but not sure the approach to accomplish this.
>
> Thank you in advanced.
> Jordan
> jbruce@asaglobalserv.com
>
>

You can use the TransferText method of DoCmd - as in:

DoCmd.TransferText [transfertype][, specificationname], tablename, filename[,
hasfieldnames][, HTMLtablename]

by using a series of these DoCmd.TransferText commands you should be able to
write a filter which will direct the data where you want.


ASA GSI wrote:

> Hi
>
>  I an using Access 2000, I will be given a text file that I need to import
> into Access, the run a report from that data.  The text file contains
> 399,000 records with approx. 206 fields that is delimitated with commas.
> How do I import this text file into access, and into different tables.
>
> For example:
> Field1 - Field36 into table1
> Field37 - Field47 into table2
> ........
>
> I would like to use code but not sure the approach to accomplish this.
>
> Thank you in advanced.
> Jordan
> jbruce@asaglobalserv.com

"Joe Fallon" <jfallon1@nospamtwcny.rr.com> wrote:

>I would import the text file into a temp Access table.
>Then I would create Append queries to add the data from the temp table to
>the real ones.
>Then I would code the whole procedure so it could be done with the click of
>a button every day.

I would definitely agree with Joe's concept of a temp Access table.
Although I would strongly suggest using a temp Access MDB as well for
this intermediary set of tables.

I have noticed that when creating lots of records, ie 100K,  using
RecordSets and .append statements that the database bloats by ten or
thirty Mb.  A compact then gets it down to a reasonably size of only
being larger by several MB.

If doing as many records as you're talking about very often between
compacts could conceivably bloat the database past the 1 GB (A97) or 2
GB (A2K) size and thus get you into deeper trouble.

And visit my website for sample code on creating temp MDBs, tables and
linking to them.

Tony
----
Tony Toews, Microsoft Access MVP
   Please respond only in the newsgroups so that others can 
read the entire thread of messages.
   Microsoft Access Links, Hints, Tips & Accounting Systems at 
http://www.granite.ab.ca/accsmstr.htm
   VolStar http://www.volstar.com Manage hundreds or 
thousands of volunteers for special events.