Solved

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

Posted on 2016-09-16
23
86 Views
Last Modified: 2016-11-15
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.
0
Comment
Question by:sXmont1j6
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 11
  • 6
  • 2
  • +1
23 Comments
 
LVL 11

Expert Comment

by:loftyworm
ID: 41801699
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
 
LVL 24

Expert Comment

by:NVIT
ID: 41801910
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
 
LVL 54

Expert Comment

by:Bill Prew
ID: 41801978
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
How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

 

Author Comment

by:sXmont1j6
ID: 41806618
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
 

Author Comment

by:sXmont1j6
ID: 41806727
The folder name is already in existence, it does not need to be created.
0
 
LVL 11

Expert Comment

by:loftyworm
ID: 41807224
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
 

Author Comment

by:sXmont1j6
ID: 41807234
I believe so too, I just need some more assistance on the code.  Hopefully NVIT has the time
0
 
LVL 24

Expert Comment

by:NVIT
ID: 41807426
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
 

Author Comment

by:sXmont1j6
ID: 41807610
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
 

Author Comment

by:sXmont1j6
ID: 41807616
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
 

Author Comment

by:sXmont1j6
ID: 41807621
...And it also stripped everything else from the file name...  Not a desired result.
0
 
LVL 24

Expert Comment

by:NVIT
ID: 41807643
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
 

Author Comment

by:sXmont1j6
ID: 41807670
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
 
LVL 24

Accepted Solution

by:
NVIT earned 500 total points
ID: 41807755
@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
 

Author Comment

by:sXmont1j6
ID: 41807775
I am offsite now, but I will check this first thing tmw morning...
0
 

Author Comment

by:sXmont1j6
ID: 41808950
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
 
LVL 24

Expert Comment

by:NVIT
ID: 41809147
Have you tried my last post? I tested it before posting. Can you post specific errors, if any?
0
 

Author Comment

by:sXmont1j6
ID: 41811808
NVIT,  I apologize, it was a busy day.  I will try first thing tomorrow
0
 
LVL 24

Expert Comment

by:NVIT
ID: 41864649
Based on communication between NVIT and OP, post ID: https:#a41807755 works.
0
 

Author Comment

by:sXmont1j6
ID: 41864652
I am sorry, I have been out of touch on this.. can we revisit on Monday?
0

Featured Post

Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you receive another warning that your shared drive is almost full and you have asked your users to clean out old files again and again, here is a single command that may help. This command will place all the files that have not been used rec…
If like me you are one who spends a lot of time working and scripting with cmd.exe, sometimes it is handy to be able to quickly view a calendar for a given month and year. This script will quickly do just that!  Save the code posted below to a .bat …
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question