Solved

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

Posted on 2016-09-16
23
74 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
  • 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 23

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 51

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
 

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 23

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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

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

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 23

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 23

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 23

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Not long ago I saw a question in the VB Script forum that I thought would not take much time. You can read that question (Question ID  (http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/Q_28455246.html)28455246) Here (http…
Introduction: Recently, I got a requirement to zip all files individually with batch file script in Windows OS. I don't know much about scripting, but I searched Google and found a lot of examples and websites to complete my task. Finally, I was ab…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

762 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now