MODIFY A BATCH FILE
-
- 4StarLounger
- Posts: 574
- Joined: 10 Feb 2010, 17:32
MODIFY A BATCH FILE
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
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
-
- Administrator
- Posts: 78485
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: MODIFY A BATCH FILE
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
V:\SHP_XY_FULL_20181006????.txt
or
V:\SHP_XY_FULL_20181006*.txt
Best wishes,
Hans
Hans
-
- 4StarLounger
- Posts: 574
- Joined: 10 Feb 2010, 17:32
Re: MODIFY A BATCH FILE
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.
How do I account for the wild card here?
\shp_XY_act_" & strFilename & ".txt
Thanks again HansV.
Michael
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, ""
\shp_XY_act_" & strFilename & ".txt
Thanks again HansV.
Michael
-
- Administrator
- Posts: 78485
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: MODIFY A BATCH FILE
What would you like to happen if there are multiple files matching the pattern?
Best wishes,
Hans
Hans
-
- 4StarLounger
- Posts: 574
- Joined: 10 Feb 2010, 17:32
Re: MODIFY A BATCH FILE
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
The batch file copies the files from the server drive, to the individual folders for each of the clients.
All filenames are unique.
Michael
-
- Administrator
- Posts: 78485
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: MODIFY A BATCH FILE
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?
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
Hans
-
- 4StarLounger
- Posts: 574
- Joined: 10 Feb 2010, 17:32
Re: MODIFY A BATCH FILE
Only one.
-
- Administrator
- Posts: 78485
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: MODIFY A BATCH FILE
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
Hans
-
- 4StarLounger
- Posts: 574
- Joined: 10 Feb 2010, 17:32
Re: MODIFY A BATCH FILE
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
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
-
- Administrator
- Posts: 78485
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
-
- 4StarLounger
- Posts: 574
- Joined: 10 Feb 2010, 17:32
Re: MODIFY A BATCH FILE
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.
.
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.
or something similar
I hope this is clearer
Michael
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, ""
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, ""
I hope this is clearer
Michael
-
- Administrator
- Posts: 78485
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: MODIFY A BATCH FILE
TransferText doesn't support wildcard characters.
Try this:
Note that the DoCmd.TransferText command is slightly different now - strFilename is the complete filename including the prefix and the extension.
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, ""
Best wishes,
Hans
Hans
-
- 4StarLounger
- Posts: 574
- Joined: 10 Feb 2010, 17:32
Re: MODIFY A BATCH FILE
I'm on it Hans.
I will let you know by Monday.
Have a great week end !
I will let you know by Monday.
Have a great week end !
-
- 4StarLounger
- Posts: 574
- Joined: 10 Feb 2010, 17:32
Re: MODIFY A BATCH FILE
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
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
-
- Administrator
- Posts: 78485
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: MODIFY A BATCH FILE
For when you come back:
Below the line
insert
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.
Below the line
Code: Select all
strFilename = Dir(…)
Code: Select all
Debug.Print strFilename
Perhaps that will provide a clue.
Have a good weekend.
Best wishes,
Hans
Hans
-
- 4StarLounger
- Posts: 574
- Joined: 10 Feb 2010, 17:32
Re: MODIFY A BATCH FILE
The debug filename is exactly what it is supposed to be.
Let me poke around a bit more.
Thanks HansV
Let me poke around a bit more.
Thanks HansV
-
- 4StarLounger
- Posts: 574
- Joined: 10 Feb 2010, 17:32
Re: MODIFY A BATCH FILE
Found it. A comma got left out (even though I copied/pasted)
All's well - again - thanks Hans.
Michael
All's well - again - thanks Hans.
Michael
-
- Administrator
- Posts: 78485
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands