Import Specification

[Guest]

Thanks for taking the time to read my question.

I have to import text (.csv) files to tables in my database. The trick is,
the user can select what file they want to import into what table. It's ok
if the files to import are speradsheets as docmd.TransferSpreadsheet takes
care of it, but if they are text files (docmd.TransferText) you need an
Import Specification (found when you import a file and then click on the
'Advanced' button).

DoCmd.TransferText acImportDelim, ImportSpecificationName, tblName,
FileName, True

If I could buid this on the fly for the table that would be great. If I
could build it once through code, and save it, then check if the table has an
Import Specification associated to it, that would be fine too. The user can
make and delete some tables. That is why I need this part to be flexible. I
can't have the user import Excel files because of the 65000 row limit. Some
of the files to be imported will be larger than that.

Thanks again for your help,

Brad

 

[Chriske911]

Thanks for taking the time to read my question.

I have to import text (.csv) files to tables in my database. The trick is,
the user can select what file they want to import into what table. It's ok
if the files to import are speradsheets as docmd.TransferSpreadsheet takes
care of it, but if they are text files (docmd.TransferText) you need an
Import Specification (found when you import a file and then click on the
'Advanced' button).

DoCmd.TransferText acImportDelim, ImportSpecificationName, tblName,
FileName, True

If I could buid this on the fly for the table that would be great. If I
could build it once through code, and save it, then check if the table has an
Import Specification associated to it, that would be fine too. The user can
make and delete some tables. That is why I need this part to be flexible. I
can't have the user import Excel files because of the 65000 row limit. Some
of the files to be imported will be larger than that.

Thanks again for your help,

Brad

almost anything is possible using code
off course you will have to do some checks before you import
otherwise you'll end up with a FU DB in no time

the most obvious way is to import in a sequential order using the open
file command
and reading line by line using char(59) as delimiter (most commonly
used)
and updating the recordset as long as there are lines to be read (until
EOF)

you can write code so it is dynamic in number of fields and allowing
any recordset to be passed along

create your own import wizard for this to keep it all under control and
use error trapping all the way

put this all in a commit transaction to be able to rollback on errors

you will not find cut and paste code for this but I suggest you give
google a try with sequential import vba code (or alike)

grtz

 

[Marshall Barton]

I have to import text (.csv) files to tables in my database. The trick is,
the user can select what file they want to import into what table. It's ok
if the files to import are speradsheets as docmd.TransferSpreadsheet takes
care of it, but if they are text files (docmd.TransferText) you need an
Import Specification (found when you import a file and then click on the
'Advanced' button).

DoCmd.TransferText acImportDelim, ImportSpecificationName, tblName,
FileName, True

If I could buid this on the fly for the table that would be great. If I
could build it once through code, and save it, then check if the table has an
Import Specification associated to it, that would be fine too. The user can
make and delete some tables. That is why I need this part to be flexible. I
can't have the user import Excel files because of the 65000 row limit. Some
of the files to be imported will be larger than that.

I agree with Chris that using the direct File I/O statements
is the most versatile approach to this issue. To that end
there is an excellent class module to manage most of the
details at:
http://www.mvps.org/access/modules/mdl0057.htm

 

[Guest]

Excellent Suggestion! I hadn't thought of that.

Thanks for the tips, and thanks to Marshall for the link

Have a great day

Brad

 

[Guest]

Hi Marshall,

I'm having a bit of trouble with the code. Do you know anything about it?

Brad

 

[Marshall Barton]

Only in a broad sense. I used it once several years ago in
a simple test, but not in a real situation.

I remember that at the time I thought the release notes and
the comments in the module were pretty decent. I also
thought that anything that powerful would require an
inexperienced programmer to spend a fair amount of time
learning how to use it well.

You can try posting a specific question about it and maybe
there's someone who has used it a lot that might answer.
It's even possible that Chuck might see it and provide some
author's insight if you posted the question to the newsgroup
comp.databases.ms-access

 

[Guest]

Thanks for the reply Marshall.

How do I get to that newsgroup.

Also I searched the web and found this:

http://www.developersdex.com/vb/message.asp?p=2899&r=4273557&Page=1

It may help someone else too.

Search: clsReadTextFile

Thanks,

Brad

Only in a broad sense. I used it once several years ago in
a simple test, but not in a real situation.

I remember that at the time I thought the release notes and
the comments in the module were pretty decent. I also
thought that anything that powerful would require an
inexperienced programmer to spend a fair amount of time
learning how to use it well.

You can try posting a specific question about it and maybe
there's someone who has used it a lot that might answer.
It's even possible that Chuck might see it and provide some
author's insight if you posted the question to the newsgroup
comp.databases.ms-access
--
Marsh
MVP [MS Access]

Hi Marshall,

I'm having a bit of trouble with the code. Do you know anything about it?


"Marshall Barton" wrote:>> I agree with Chris that using the direct File I/O statements

 

[Marshall Barton]

You get a newsgroup the same way you get to any newsgroup
(as long as your (ISP?) server subscribes to it). The
details depend on your news/email reader program, but
generally you look at the all groups selection, click on the
one you want and select Subscribe.

You won't have a clue what I'm talking about if you are
using the Microsoft web interface to this newsgroup. In
this case, you're probably using Outlook Express for your
email. If so, check the menus, there is one that allows you
to use OE as your newsgroup reader too. (I don't use OE, so
I can't be specific.)
--
Marsh
MVP [MS Access]

How do I get to that newsgroup.

Also I searched the web and found this:
http://www.developersdex.com/vb/message.asp?p=2899&r=4273557&Page=1
It may help someone else too.
Search: clsReadTextFile

Only in a broad sense. I used it once several years ago in
a simple test, but not in a real situation.

I remember that at the time I thought the release notes and
the comments in the module were pretty decent. I also
thought that anything that powerful would require an
inexperienced programmer to spend a fair amount of time
learning how to use it well.

You can try posting a specific question about it and maybe
there's someone who has used it a lot that might answer.
It's even possible that Chuck might see it and provide some
author's insight if you posted the question to the newsgroup
comp.databases.ms-access
--
Marsh
MVP [MS Access]

Hi Marshall,

I'm having a bit of trouble with the code. Do you know anything about it?


Brad wrote:
I have to import text (.csv) files to tables in my database. The trick is,
the user can select what file they want to import into what table. It's ok
if the files to import are speradsheets as docmd.TransferSpreadsheet takes
care of it, but if they are text files (docmd.TransferText) you need an
Import Specification (found when you import a file and then click on the
'Advanced' button).

DoCmd.TransferText acImportDelim, ImportSpecificationName, tblName,
FileName, True

If I could buid this on the fly for the table that would be great. If I
could build it once through code, and save it, then check if the table has an
Import Specification associated to it, that would be fine too. The user can
make and delete some tables. That is why I need this part to be flexible. I
can't have the user import Excel files because of the 65000 row limit. Some
of the files to be imported will be larger than that.


:>> I agree with Chris that using the direct File I/O statements
is the most versatile approach to this issue. To that end
there is an excellent class module to manage most of the
details at:
http://www.mvps.org/access/modules/mdl0057.htm

 

[Guest]

Thanks Marshall,

I am at http://support.microsoft.com/newsgroups/default.aspx so I don't
think I can see that group. I actuall got lucky and found
http://www.developersdex.com/vb/default.asp?p=2899 which has the newsgroup
you wrote about.

Thanks,

Brad

You get a newsgroup the same way you get to any newsgroup
(as long as your (ISP?) server subscribes to it). The
details depend on your news/email reader program, but
generally you look at the all groups selection, click on the
one you want and select Subscribe.

You won't have a clue what I'm talking about if you are
using the Microsoft web interface to this newsgroup. In
this case, you're probably using Outlook Express for your
email. If so, check the menus, there is one that allows you
to use OE as your newsgroup reader too. (I don't use OE, so
I can't be specific.)
--
Marsh
MVP [MS Access]

How do I get to that newsgroup.

Also I searched the web and found this:
http://www.developersdex.com/vb/message.asp?p=2899&r=4273557&Page=1
It may help someone else too.
Search: clsReadTextFile

Only in a broad sense. I used it once several years ago in
a simple test, but not in a real situation.

I remember that at the time I thought the release notes and
the comments in the module were pretty decent. I also
thought that anything that powerful would require an
inexperienced programmer to spend a fair amount of time
learning how to use it well.

You can try posting a specific question about it and maybe
there's someone who has used it a lot that might answer.
It's even possible that Chuck might see it and provide some
author's insight if you posted the question to the newsgroup
comp.databases.ms-access
--
Marsh
MVP [MS Access]


Brad wrote:
Hi Marshall,

I'm having a bit of trouble with the code. Do you know anything about it?


Brad wrote:
I have to import text (.csv) files to tables in my database. The trick is,
the user can select what file they want to import into what table. It's ok
if the files to import are speradsheets as docmd.TransferSpreadsheet takes
care of it, but if they are text files (docmd.TransferText) you need an
Import Specification (found when you import a file and then click on the
'Advanced' button).

DoCmd.TransferText acImportDelim, ImportSpecificationName, tblName,
FileName, True

If I could buid this on the fly for the table that would be great. If I
could build it once through code, and save it, then check if the table has an
Import Specification associated to it, that would be fine too. The user can
make and delete some tables. That is why I need this part to be flexible. I
can't have the user import Excel files because of the 65000 row limit. Some
of the files to be imported will be larger than that.


:>> I agree with Chris that using the direct File I/O statements
is the most versatile approach to this issue. To that end
there is an excellent class module to manage most of the
details at:
http://www.mvps.org/access/modules/mdl0057.htm

 

[Marshall Barton]

OK, you are using a web interface. Personally, I find them
rather clumsy.

I was not aware of that interface and obviously don't use
it, but if you don't want to use a real news reader program,
that will probably work for you.

You can learn a whole lot of stuff by searching that group
along with the appropriate MS groups for just about any
question you have. It's hard for me to imagine a question
that hasn't been asked at least once sometime or other.
Although it seems you may have come up with a new one, you
did find someting related to your topic.

I don't know why you are having trouble creating the Class
module using the instrctions for LoadFromText???

 

[Guest]

Someone actually replied to my post on the other newsgroup.

Close the DB, copy the contents of the .txt file, delete the .txt file,
paste the contents into a class module in Access2000 and save it. save the
class module as clsReadTextFile.

Now it works. No idea why it didn't take before...

Thanks for sticking with this one. I truly appreciate it.

What newsgroup reader do you use?

Brad

link to other help:
http://www.developersdex.com/vb/message.asp?p=2899&ID=<KX%[email protected]>

OK, you are using a web interface. Personally, I find them
rather clumsy.

I was not aware of that interface and obviously don't use
it, but if you don't want to use a real news reader program,
that will probably work for you.

You can learn a whole lot of stuff by searching that group
along with the appropriate MS groups for just about any
question you have. It's hard for me to imagine a question
that hasn't been asked at least once sometime or other.
Although it seems you may have come up with a new one, you
did find someting related to your topic.

I don't know why you are having trouble creating the Class
module using the instrctions for LoadFromText???
--
Marsh
MVP [MS Access]

I am at http://support.microsoft.com/newsgroups/default.aspx so I don't
think I can see that group. I actuall got lucky and found
http://www.developersdex.com/vb/default.asp?p=2899 which has the newsgroup
you wrote about.

 

[Marshall Barton]

Glad to hear that you're past the step of getting the code
into your app. Now, all you have to do is figure out how to
use it so you can write the code to do the job you were
originally asking about ;-)

I gave up on Outlook Express years ago and have been using
Forte Agent ever since. A key reason for this is that Agent
is a text only mail program so it is impossible for it to
run a malicious script in the body of an email message.

 

[Guest]

Thanks for the tip on Forte Agent. I'll look into it.

Thanks again for all your help.

Have a great day,

Brad

Glad to hear that you're past the step of getting the code
into your app. Now, all you have to do is figure out how to
use it so you can write the code to do the job you were
originally asking about ;-)

I gave up on Outlook Express years ago and have been using
Forte Agent ever since. A key reason for this is that Agent
is a text only mail program so it is impossible for it to
run a malicious script in the body of an email message.
--
Marsh
MVP [MS Access]

Someone actually replied to my post on the other newsgroup.

Close the DB, copy the contents of the .txt file, delete the .txt file,
paste the contents into a class module in Access2000 and save it. save the
class module as clsReadTextFile.

Now it works. No idea why it didn't take before...

Thanks for sticking with this one. I truly appreciate it.

What newsgroup reader do you use?

Brad

link to other help:
http://www.developersdex.com/vb/message.asp?p=2899&ID=<KX%[email protected]>