Can I use wildcards in TransferText import?

[Shyguy]

I need to import a text file pretty much daily. I download the file
and change the name to a standard name and then run the code to import
the file into a table in my database. The problem is that the file
starts with a standard name but adds the date and some other stuff to
the end of the file name. Is there a way I could use a wildcard like
"*" so I wouldn't have to change the name?

Thanks for any help

[Michael \(michka\) Kaplan [MS]]

Re: Can I use wildcards in TransferText import?

No, you cannot -- when the filesystem opens a file it needs a legal name.
But you can write code to get the name that uses wildcards via the Dir()
function, and then use the name you get....


--
MichKa [MS]
NLS Collation/Locale/Keyboard Development
Globalization Infrastructure and Font Technologies

This posting is provided "AS IS" with
no warranties, and confers no rights.


"Shyguy" <shyguy@aol.com > wrote in message
news:o40ic0lvgg mnd9f4u2fs723le tljn9qc4r@4ax.c om...[color=blue]
> I need to import a text file pretty much daily. I download the file
> and change the name to a standard name and then run the code to import
> the file into a table in my database. The problem is that the file
> starts with a standard name but adds the date and some other stuff to
> the end of the file name. Is there a way I could use a wildcard like
> "*" so I wouldn't have to change the name?
>
> Thanks for any help
>
>[/color]

[Shyguy]

Re: Can I use wildcards in TransferText import?

I'm not sure I follow. The original filename doesn't use wildcards.
it just has different endings. Are you saying I can write code to
change the file name into a specific name?

On Thu, 10 Jun 2004 20:10:42 -0700, "Michael \(michka\) Kaplan [MS]"
<michkap@online .microsoft.com> wrote:
[color=blue]
>No, you cannot -- when the filesystem opens a file it needs a legal name.
>But you can write code to get the name that uses wildcards via the Dir()
>function, and then use the name you get....[/color]

[Michael \(michka\) Kaplan [MS]]

Re: Can I use wildcards in TransferText import?

You can write code to

(1) detect the filename (using wildcards)
(2) use that detected filename in your call to TransferText


--
MichKa [MS]
NLS Collation/Locale/Keyboard Development
Globalization Infrastructure and Font Technologies

This posting is provided "AS IS" with
no warranties, and confers no rights.


"Shyguy" <shyguy@aol.com > wrote in message
news:46mjc0h46i n5eknhutnbdhdu6 05mhrggkb@4ax.c om...[color=blue]
> I'm not sure I follow. The original filename doesn't use wildcards.
> it just has different endings. Are you saying I can write code to
> change the file name into a specific name?
>
> On Thu, 10 Jun 2004 20:10:42 -0700, "Michael \(michka\) Kaplan [MS]"
> <michkap@online .microsoft.com> wrote:
>[color=green]
> >No, you cannot -- when the filesystem opens a file it needs a legal name.
> >But you can write code to get the name that uses wildcards via the Dir()
> >function, and then use the name you get....[/color]
>[/color]

[Shyguy]

Re: Can I use wildcards in TransferText import?

Thanks for your reply and patience. Being pretty much a beginner at
this, I really have no clue as the where to start this. Could you
direct me to sample code, or a starting place to search?

Thanks again


On Fri, 11 Jun 2004 10:00:18 -0700, "Michael \(michka\) Kaplan [MS]"
<michkap@online .microsoft.com> wrote:
[color=blue]
>You can write code to
>
>(1) detect the filename (using wildcards)
>(2) use that detected filename in your call to TransferText[/color]

[Michael \(michka\) Kaplan [MS]]

Re: Can I use wildcards in TransferText import?

As I said, the Dir() function is your friend. You can say (in VBA):

Dim stFile As String

stFile = Dir$("C:\FOO\BA R\FILE??.TXT")

and stFile will contain the first file in C:\FOO\BAR\ that matches the
pattern given.

From there you can call the TransferText method, passing stFile in as the
filename. For more info, look in the online help on the Dir$ function and
the TransferText method.


--
MichKa [MS]
NLS Collation/Locale/Keyboard Development
Globalization Infrastructure and Font Technologies

This posting is provided "AS IS" with
no warranties, and confers no rights.


"Shyguy" <shyguy@aol.com > wrote in message
news:r3ujc01gfc 531ab2cafvds5sp 3k3iqi7mj@4ax.c om...[color=blue]
> Thanks for your reply and patience. Being pretty much a beginner at
> this, I really have no clue as the where to start this. Could you
> direct me to sample code, or a starting place to search?
>
> Thanks again
>
>
> On Fri, 11 Jun 2004 10:00:18 -0700, "Michael \(michka\) Kaplan [MS]"
> <michkap@online .microsoft.com> wrote:
>[color=green]
> >You can write code to
> >
> >(1) detect the filename (using wildcards)
> >(2) use that detected filename in your call to TransferText[/color]
>[/color]

[Shyguy]

Re: Can I use wildcards in TransferText import?

Thank you so much for all the help. Unfortunately I still can't get
it to work. These are the two main lines of code.

stFile = Dir$("C:\Docume nts and Settings\Me\Des ktop\Testing*.t xt")

DoCmd.TransferT ext acImportDelim, "", "TestTable" , stFile, True, ""

If I substitute stFile with the actual name of the file everything
works fine.

When I run it with the stFile, I get an error message saying that it
can't find the file, but it shows the correct file name in the error
message.


On Fri, 11 Jun 2004 21:57:14 -0700, "Michael \(michka\) Kaplan [MS]"
<michkap@online .microsoft.com> wrote:
[color=blue]
> Dim stFile As String
>
> stFile = Dir$("C:\FOO\BA R\FILE??.TXT")[/color]

[Douglas J. Steele]

Re: Can I use wildcards in TransferText import?

All the Dir function returns is the name of the file without its folder. You
need to add the folder yourself.

stFile = Dir$("C:\Docume nts and Settings\Me\Des ktop\Testing*.t xt")

DoCmd.TransferT ext acImportDelim, "", "TestTable" , "C:\Documen ts and
Settings\Me\Des ktop\" & stFile, True, ""


--
Doug Steele, Microsoft Access MVP

http://I.Am/DougSteele

(no e-mails, please!)


"Shyguy" <shyguy@aol.com > wrote in message
news:i3blc0d2ql 4upa3conm9qchm1 sojm04rjd@4ax.c om...[color=blue]
> Thank you so much for all the help. Unfortunately I still can't get
> it to work. These are the two main lines of code.
>
> stFile = Dir$("C:\Docume nts and Settings\Me\Des ktop\Testing*.t xt")
>
> DoCmd.TransferT ext acImportDelim, "", "TestTable" , stFile, True, ""
>
> If I substitute stFile with the actual name of the file everything
> works fine.
>
> When I run it with the stFile, I get an error message saying that it
> can't find the file, but it shows the correct file name in the error
> message.
>
>
> On Fri, 11 Jun 2004 21:57:14 -0700, "Michael \(michka\) Kaplan [MS]"
> <michkap@online .microsoft.com> wrote:
>[color=green]
> > Dim stFile As String
> >
> > stFile = Dir$("C:\FOO\BA R\FILE??.TXT")[/color]
>[/color]

[Shyguy]

Re: Can I use wildcards in TransferText import?

Thank you, thank you, thank you. I thought that since the Dir$ was =
to the directory and file name that stFile would be = to the same.
Obviously I don't understand Dir$. ;-(

Although I do understand it a little better now.

Thanks again.

On Sat, 12 Jun 2004 11:53:25 GMT, "Douglas J. Steele"
<NOSPAM_djsteel e@NOSPAM_canada .com> wrote:
[color=blue]
>DoCmd.Transfer Text acImportDelim, "", "TestTable" , "C:\Documen ts and
>Settings\Me\De sktop\" & stFile, True, ""[/color]

[Shyguy]

Re: Can I use wildcards in TransferText import?

I ran into a slight snag. The files come in with 2 periods in
different places within the filename. Is there a way to remove the
periods with code?



On Sat, 12 Jun 2004 11:53:25 GMT, "Douglas J. Steele"
<NOSPAM_djsteel e@NOSPAM_canada .com> wrote:
[color=blue]
>All the Dir function returns is the name of the file without its folder. You
>need to add the folder yourself.
>
>stFile = Dir$("C:\Docume nts and Settings\Me\Des ktop\Testing*.t xt")
>
> DoCmd.TransferT ext acImportDelim, "", "TestTable" , "C:\Documen ts and
>Settings\Me\De sktop\" & stFile, True, ""[/color]

[Douglas J. Steele]

Re: Can I use wildcards in TransferText import?

Even if the filename has periods in it, don't you need to keep it correct so
that you can refer to the file properly? If you remove the periods, how will
you be able to transfer the file name?

Or are you saying that you want to rename the files? If so, the Name
statement lets you.

Name "C:\Documen ts and Settings\Me\Des ktop\" & stFile As _
Replace("C:\Doc uments and Settings\Me\Des ktop\" & stFile, ".", "")

will remove the periods, while

Name "C:\Documen ts and Settings\Me\Des ktop\" & stFile As _
Replace("C:\Doc uments and Settings\Me\Des ktop\" & stFile, ".", "_")

will replace the periods with underscores

--
Doug Steele, Microsoft Access MVP

http://I.Am/DougSteele

(no e-mails, please!)


"Shyguy" <shyguy@aol.com > wrote in message
news:bb5pc0d4ik mn9r8gsvaeu0dme 7p3vuidlg@4ax.c om...[color=blue]
> I ran into a slight snag. The files come in with 2 periods in
> different places within the filename. Is there a way to remove the
> periods with code?
>
>
>
> On Sat, 12 Jun 2004 11:53:25 GMT, "Douglas J. Steele"
> <NOSPAM_djsteel e@NOSPAM_canada .com> wrote:
>[color=green]
> >All the Dir function returns is the name of the file without its folder.[/color][/color]
You[color=blue][color=green]
> >need to add the folder yourself.
> >
> >stFile = Dir$("C:\Docume nts and Settings\Me\Des ktop\Testing*.t xt")
> >
> > DoCmd.TransferT ext acImportDelim, "", "TestTable" , "C:\Documen ts and
> >Settings\Me\De sktop\" & stFile, True, ""[/color]
>[/color]

[Shyguy]

Re: Can I use wildcards in TransferText import?

Thank you again for all your help. I'm sorry I wasn't as clear as I
should have been. I was asking how to change the filename to have no
periods. I tried looking up the Name Statement in help, but couldn't
find anything. After adding your code the filename had no '.' s at
all, including the one between the name and "txt" so it wouldn't
import. I played around with it a bit and made it work. Probably not
the right way but it does work. I'm just thankful I don't do this for
a living. :-)

Thanks again for your help. Not just in this instance but in the past
as well.

On Sun, 13 Jun 2004 18:46:58 GMT, "Douglas J. Steele"
<NOSPAM_djsteel e@NOSPAM_canada .com> wrote:
[color=blue]
>Even if the filename has periods in it, don't you need to keep it correct so
>that you can refer to the file properly? If you remove the periods, how will
>you be able to transfer the file name?
>
>Or are you saying that you want to rename the files? If so, the Name
>statement lets you.
>
>Name "C:\Documen ts and Settings\Me\Des ktop\" & stFile As _
> Replace("C:\Doc uments and Settings\Me\Des ktop\" & stFile, ".", "")
>
>will remove the periods, while
>
>Name "C:\Documen ts and Settings\Me\Des ktop\" & stFile As _
> Replace("C:\Doc uments and Settings\Me\Des ktop\" & stFile, ".", "_")
>
>will replace the periods with underscores[/color]