Script to move files with specific naming convention to a specific folder with a naming convention.

Hi All,

We are receiving files with a specific naming convention and I am looking to create a script to move them from the SFTP folder to individual case folders which already have a specific naming convention.  The file and the folder have the same policynumber in their names.  

The file name is always going to be in this format:  lastnamefirstname_providername_pgs_systemID_policynumber.pdf

The folders are always in the following format and these are preexisting, they do not need to be created:  lastname, first name_policynumber

I want to be able to move the files into the corresponding folders and run this every night.  

Any help is much appreciated.
sXmont1j6Asked:
Who is Participating?
 
NVITConnect With a Mentor Commented:
@echo off
REM Move matching policy PDFs to folders based on matching, ending number. 
REM The number is usually a policy number. e.g. the number is 2000
REM .
REM Filename: jacob,john_provider01_666_sys01_2000.pdf
REM Foldername can vary. As long as the last number is the same and follows an underscore. e.g.
REM   Foldername: C:\local\test\NewDir\jacob,john_2000
REM   or...
REM   Foldername: C:\local\test\NewDir\jacob-john_2000
REM .
REM move "C:\local\test\pdfroot\*_2000.pdf" "C:\local\test\NewDir\jacob,john_2000"
REM or...
REM move "C:\local\test\pdfroot\*_2000.pdf" "C:\local\test\NewDir\jacob-john_2000"
REM --------------------------------------------------
REM - Adjust SrcDirRoot and TgtDirRoot values per your needs
REM - PDF files must reside in SrcDirRoot
REM - TgtDirRoot are assumed to exist. They are not created

setlocal enabledelayedexpansion

set SrcDirRoot=C:\local\test\pdfroot
set TgtDirRoot=C:\local\test\NewDir

for /d %%a in ("%TgtDirRoot%\*") DO (
  set TDName=%%~na
  for /f "tokens=2 delims=_" %%d in ('echo %%a') DO (
    for /f "tokens=1-5 delims=_" %%A in ('dir /b "%SrcDirRoot%"') DO (
      set Name=%%A
      set PolicyNumPfx=%%~nE
      if /i "!PolicyNumPfx!" equ "%%d" (
        if exist "%SrcDirRoot%\*_!PolicyNumPfx!.pdf" ECHO move "%SrcDirRoot%\*_!PolicyNumPfx!.pdf" "%TgtDirRoot%\!TDName!"
      )
    )
  )
)

Open in new window

0
 
loftywormCommented:
This can be done, with some work in BATCH (which should translate to VB), and likely powershell
You need to read up on the FOR /? and pay attention to the tokens and delimes options.
0
 
NVITCommented:
This does it.
- I assume policynumber is unique for each user.
- Make a movepdf.bat file with this code
- Currently, it is in test mode. To run it for real, remove the ECHO on line 9
- Change the SrcDir to your needs.
- Since you didn't specify file locations, I assumed the pdf files are in SrcDir. This can be adjusted. Let me know.

@echo off
setlocal enabledelayedexpansion

set SrcDir=C:\local\test\users

FOR /f "tokens=1-2 delims=_" %%A in ('dir /b /ad "%SrcDir%"') DO (
  set Dir=%%A
  set PolicyNum=%%B
  ECHO move "%SrcDir%\*!PolicyNum!.pdf" "%SrcDir%\!Dir!_!PolicyNum!"
)

Open in new window


- Open a CMD window.
- Type: movepdf.bat

My example shows:
move "C:\local\test\users\*2000.pdf" "C:\local\test\users\jacob,john_2000"
move "C:\local\test\users\*1000.pdf" "C:\local\test\users\Jane,Mary_1000"

Open in new window

0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
Bill PrewCommented:
Is this a typo, or actually the case?

"The file name is always going to be in this format:  lastnamefirstname_providername_pgs_systemID_policynumber.pdf"


If that is accurate and there is no character between the lastname and the firstname, then I don't see how it would be possible to separate those two items in a script.

~bp
0
 
sXmont1j6Author Commented:
NVIT,

The source directory and the target directories are different, so I am not certain your script will work.

Bill Prew,

I don't need to separate the name, I just need to move the PDF to a folder that has the same policynumber.  the goal is the following


- Move \\util\sftp\Doe,John_medicalplace_7_12334_555555.pdf    

TO

\\file1\scanneddocuments\Doe,John_555555
0
 
sXmont1j6Author Commented:
The folder name is already in existence, it does not need to be created.
0
 
loftywormCommented:
I believe NVIT and I are correct, although I do not have time to write the code for it specifically.  The FOR loop will do the trick.  You can use multiple delimiters and set the variable, and then put them all back together however you like.  If NVIT can show you the code, then it should work.
0
 
sXmont1j6Author Commented:
I believe so too, I just need some more assistance on the code.  Hopefully NVIT has the time
0
 
NVITCommented:
Revised version.
- Please review usage notes at my first post.

@echo off
REM Move matching policy PDFs to user folders
REM e.g.
REM .
REM Filename: jacob,john_provider01_666_sys01_2000.pdf
REM Foldername: C:\local\test\NewDir\jacob,john_2000
REM .
REM move "C:\local\test\users\jacob,john_provider02_221_sys01_2000.pdf" "C:\local\test\NewDir\jacob,john_2000"
REM --------------------------------------------------
REM IMPORTANT: Firstname,Lastname must be EXACT for both filename and foldername.
REM Else, the file will be renamed instead of moved to the correct folder
REM --------------------------------------------------
REM - Adjust SrcDirRoot and TgtDirRoot values per your needs
REM - PDF files must reside in SrcDirRoot
REM - TgtDirRoot are assumed to exist. They are not created

setlocal enabledelayedexpansion

set SrcDirRoot=C:\local\test\users
set TgtDirRoot=C:\local\test\NewDir

FOR /f "tokens=1-5 delims=_" %%A in ('dir /b "%SrcDirRoot%"') DO (
  REM echo %%A-%%B-%%C-%%D-%%E
  set Name=%%A
  set Provider=%%B
  set Pgs=%%C
  set SysID=%%D
  set PolicyNumPfx=%%~nE
  set PolicyNumExt=%%~xE
  set TgtDir=!Name!_!PolicyNumPfx!
  if "!PolicyNumPfx!" neq "" ECHO move "%SrcDirRoot%\!Name!_!Provider!_!Pgs!_!SysID!_!PolicyNumPfx!!PolicyNumExt!" "%TgtDirRoot%\!TgtDir!"
)

Open in new window

0
 
sXmont1j6Author Commented:
Thank you for this, but can't I just only be concerned with the policynumber.  The policynumber is the same on the file that is to be moved to the pre-existing folder.   I'd rather not mess with the first and last name as I have no control over the source for those.
0
 
sXmont1j6Author Commented:
I got it to move the file, but it did not move the file into a subfolder that contains the policynumber, it just moved it to the root of the TgtDirRoot
0
 
sXmont1j6Author Commented:
...And it also stripped everything else from the file name...  Not a desired result.
0
 
NVITCommented:
That probably because as I mentioned - differences in the filename to foldername.

Can you give me a sample exact name of each, i.e. folder and associated file? You can change the text for privacy. I just need the exact location of text.
0
 
sXmont1j6Author Commented:
filename will be in the following format:  doe,john_medical shop_123456C_555555.pdf

folder will be preexisting and be in the following format: doe, john_555555

Without changing the name of the file, I need it to moved into the above folder.
0
 
sXmont1j6Author Commented:
I am offsite now, but I will check this first thing tmw morning...
0
 
sXmont1j6Author Commented:
You have a line REM that states that  TgtDirRoot are assumed to exist. They are not created

I believe that is where the issue is currently...

The target root folder is c:\scans and in this folder are thousands of subfolders with the format:  Doe, John_555555.  

The source folder will have many PDF's that I need to move into the subfolders of scans based on the policynumber
0
 
NVITCommented:
Have you tried my last post? I tested it before posting. Can you post specific errors, if any?
0
 
sXmont1j6Author Commented:
NVIT,  I apologize, it was a busy day.  I will try first thing tomorrow
0
 
NVITCommented:
Based on communication between NVIT and OP, post ID: https:#a41807755 works.
0
 
sXmont1j6Author Commented:
I am sorry, I have been out of touch on this.. can we revisit on Monday?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.