MODIFY A BATCH FILE

User avatar
Michael Abrams
4StarLounger
Posts: 573
Joined: 10 Feb 2010, 17:32

MODIFY A BATCH FILE

Post by Michael Abrams »

Happy Friday All,

I have a batch file with the following line:
copy /y V:\SHP_XY_FULL_20181006.txt R:\"XY HMOs 2018\HEALTH PLUS 2018\OCT 2018\TEXT FILES\WEEKLY FULL FILES"
Works fine

The file name is being changed by adding a time stamp:
copy /y V:\SHP_XY_FULL_201810061153.txt R:\"XY HMOs 2018\HEALTH PLUS 2018\OCT 2018\TEXT FILES\WEEKLY FULL FILES"
Obviously the time stamp will change every time a file is produced.

Is there a way to incorporate wild cards to replace the time stamp so it stays consistent every week?
copy /y V:\SHP_XY_FULL_20181006****.txt R:\"XY HMOs 2018\HEALTH PLUS 2018\OCT 2018\TEXT FILES\WEEKLY FULL FILES"

Thank you for any guidance. I always appreciate it.

Michael

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: MODIFY A BATCH FILE

Post by HansV »

The copy command supports wildcards. ? stands for one character, and * for any number of characters. So you could use either

V:\SHP_XY_FULL_20181006????.txt

or

V:\SHP_XY_FULL_20181006*.txt
Best wishes,
Hans

User avatar
Michael Abrams
4StarLounger
Posts: 573
Joined: 10 Feb 2010, 17:32

Re: MODIFY A BATCH FILE

Post by Michael Abrams »

I couldn't get back fast enough - I actually got t right before I got the notification of your reply.

Thank you for looking - I have a related question, but it is concerning Access. Please feel free to move this there.

Now i need to take the file and import it into Access.

Prior to the timestamp, I used this code:
Simple and works fine.

Code: Select all

DoCmd.TransferText acImportFixed, "SHP IMPORT SPECS", "ACTIVITY", _
"R:\XY HMOS 2018\SHP 2018\" & strMonth & "\TEXT FILES\Activity Files\shp_XY_act_" & strFilename & ".txt", False, ""
How do I account for the wild card here?
\shp_XY_act_" & strFilename & ".txt

Thanks again HansV.

Michael

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: MODIFY A BATCH FILE

Post by HansV »

What would you like to happen if there are multiple files matching the pattern?
Best wishes,
Hans

User avatar
Michael Abrams
4StarLounger
Posts: 573
Joined: 10 Feb 2010, 17:32

Re: MODIFY A BATCH FILE

Post by Michael Abrams »

I actually receive 30 files (in the same folder) all different names, but same amount of characters.

The batch file copies the files from the server drive, to the individual folders for each of the clients.

All filenames are unique.

Michael

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: MODIFY A BATCH FILE

Post by HansV »

What I meant is that DoCmd.TransferText can import only one file at a time.
What if the Activity Files folder contains more than one file that matches the pattern?
- Do you want to loop through the files and import all of them?
- Or do you import only one of them? If so, which one?
Best wishes,
Hans

User avatar
Michael Abrams
4StarLounger
Posts: 573
Joined: 10 Feb 2010, 17:32

Re: MODIFY A BATCH FILE

Post by Michael Abrams »

Only one.

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: MODIFY A BATCH FILE

Post by HansV »

Sorry, what do you mean? That there will be only one file in the folder, or that you want to import only one file?
Best wishes,
Hans

User avatar
Michael Abrams
4StarLounger
Posts: 573
Joined: 10 Feb 2010, 17:32

Re: MODIFY A BATCH FILE

Post by Michael Abrams »

There are 15 clients.

Each has its own Text files folder

DoCmd.TransferText acImportFixed, "SHP IMPORT SPECS", "ACTIVITY", _
"R:\XY HMOS 2018\SHP 2018\" & strMonth & "\TEXT FILES\Activity Files\shp_XY_act_" & strFilename & ".txt", False, ""

This code imports one file only from the specified folder.

Michael

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: MODIFY A BATCH FILE

Post by HansV »

So what is the question again? I'm really confused now...
Best wishes,
Hans

User avatar
Michael Abrams
4StarLounger
Posts: 573
Joined: 10 Feb 2010, 17:32

Re: MODIFY A BATCH FILE

Post by Michael Abrams »

In Access, I have a simple import code behind a button.

The file is located here:
"R:\XY HMOS 2018\SHP 2018\" & strMonth & "\TEXT FILES\Activity Files

The file name used to be:
SHP_XY_FULL_20181006.txt

Using this code, i imported the file

Dim strFilename As String
strFilename = InputBox("Enter filename NO HYPHEN) eg: 20180508")
If Not Len(strFilename) = 8 Then
MsgBox "Invalid - try again.", vbExclamation
Exit Sub
End If
etc.
.

Code: Select all

DoCmd.TransferText acImportFixed, "SHP IMPORT SPECS", "ACTIVITY", _
"R:\XY HMOS 2018\SHP 2018\" & strMonth & "\TEXT FILES\Activity Files\shp_XY_act_" & strFilename & ".txt", False, ""
Simple code - worked fine.

They changed the filename to SHP_XY_FULL_201810061452.txt (1452 is actually a timestamp added to the end of the filename)

I need to modify the code to accommodate the 4 random numbers at the end of the filename.

Can I use a wildcard in place of the time stamp? Every week the 1st 8 characters will be the same, just the last 4 will change.

Code: Select all

DoCmd.TransferText acImportFixed, "SHP IMPORT SPECS", "ACTIVITY", _
"R:\XY HMOS 2018\SHP 2018\" & strMonth & "\TEXT FILES\Activity Files\shp_XY_act_" & [b][color=#0080FF]strFilename  & "*" & ".txt[/color][/b]", False, ""
or something similar

I hope this is clearer :crossfingers:

Michael

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: MODIFY A BATCH FILE

Post by HansV »

TransferText doesn't support wildcard characters.

Try this:

Code: Select all

    Dim strFilename As String
    strFilename = InputBox("Enter filename NO HYPHEN) eg: 20180508")
    If Not Len(strFilename) = 8 Then
        MsgBox "Invalid - try again.", vbExclamation
        Exit Sub
    End If
    ' Try to find a file matching the pattern
    strFilename = Dir("R:\XY HMOS 2018\SHP 2018\" & strMonth & "\TEXT FILES\Activity Files\shp_XY_act_" & strFilename & "????.txt")
    If strFilename = "" Then
        MsgBox "File not found - try again.", vbExclamation
        Exit Sub
    End If
    DoCmd.TransferText acImportFixed, "SHP IMPORT SPECS", "ACTIVITY", _
        "R:\XY HMOS 2018\SHP 2018\" & strMonth & "\TEXT FILES\Activity Files\" & strFilename, False, ""
Note that the DoCmd.TransferText command is slightly different now - strFilename is the complete filename including the prefix and the extension.
Best wishes,
Hans

User avatar
Michael Abrams
4StarLounger
Posts: 573
Joined: 10 Feb 2010, 17:32

Re: MODIFY A BATCH FILE

Post by Michael Abrams »

I'm on it Hans.

I will let you know by Monday.

Have a great week end !

User avatar
Michael Abrams
4StarLounger
Posts: 573
Joined: 10 Feb 2010, 17:32

Re: MODIFY A BATCH FILE

Post by Michael Abrams »

Error 31519
You cannot import this file

Highlights
DoCmd.TransferText acImportFixed, "SHP IMPORT SPECS", "ACTIVITY", _
"R:\XY HMOS 2018\SHP 2018\" & strMonth & "\TEXT FILES\Activity Files\" & strFilename, False, ""

I think a break is warranted. Let's enjoy the weekend and come back to this on MOnday.

Thank you Hans - you are a very good person.

Michael

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: MODIFY A BATCH FILE

Post by HansV »

For when you come back:

Below the line

Code: Select all

    strFilename = Dir(…)
insert

Code: Select all

    Debug.Print strFilename
Afterwards, you can see the filename in the Immediate window (press Ctrl+G if you don't see this window).
Perhaps that will provide a clue.

Have a good weekend.
Best wishes,
Hans

User avatar
Michael Abrams
4StarLounger
Posts: 573
Joined: 10 Feb 2010, 17:32

Re: MODIFY A BATCH FILE

Post by Michael Abrams »

The debug filename is exactly what it is supposed to be.

Let me poke around a bit more.

Thanks HansV

User avatar
Michael Abrams
4StarLounger
Posts: 573
Joined: 10 Feb 2010, 17:32

Re: MODIFY A BATCH FILE

Post by Michael Abrams »

Found it. A comma got left out (even though I copied/pasted)

All's well - again - thanks Hans.

Michael

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: MODIFY A BATCH FILE

Post by HansV »

Good to hear that!
Best wishes,
Hans