Need help updating an existing script to move a CSV file

Advertisement

Alex_MUSC
Joined:
Posts:
3
Location:
Charleston SC

Need help updating an existing script to move a CSV file

Hello,
I currently have a Batch File and an SFTP script that I need help updating to fix an issue with an application importing a CSV file. This script was written by someone else 5 years ago and I'm not very familiar with scripting. The current script downloads a 3Mdocmaster TXT file from WinSCP and then uses a VBS script to reformat the TXT file to a CSV file so that another application picks it up in a folder to process it. The vendor has changed their file requirement which now requires a header in the file and the current script doesn't work.

I need to update this existing script (and process) so that it downloads a 3Mdocmaster CSV file from WinSCP and puts it in the correct folder to process on the Windows server. I also want to eliminate the process of reformatting the TXT to CSV.

Is there a simple and efficient way to update the script to transfer the CSV file? Below is the SFTP script and Batch file used to do this. I attached the files if that helps. Any help is appreciated!

Thanks,
Alex

FTP Script:
# Automatically abort script on errors
option batch abort
# Disable overwrite confirmations that conflict with the previous
option confirm off
# Connect using a password
# open sftp://user:password@example.com/ -hostkey="ssh-rsa 2048 xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx"
# Connect
#open sftp://3m.ftp@Epicshd.mdc.musc.edu/ -hostkey="*"
open sftp://3m.ftp:junkm%40n@epicshd.musc.edu/ -hostkey="ssh-ed25519 256 bpPVqjd1/QQgssl7XYBGmUGE6SsDsdLBbpyV1+bMIlQ=" -rawsettings FSProtocol=2
# Change remote directory
cd /download/3m
# Force binary mode transfer
option transfer binary
# Download file to the local directory d:\
get 3Mdocmaster.txt D:\3MHIS\Physicians\Processing\
# Disconnect
close
# Exit WinSCP
exit
Batch file:
CLS
 
set _myPWD=%CD%
Echo New Variable: %_myPWD%>> EpicMFLProcess.log
 
Set myStartDate=%date%
Set myDate=%myStartDate:/=%
Set myDate=%myDate:~-8%
Set _date=%myDate: =%
REM Echo New Variable: %_date%>> EpicMFLProcess.log
 
Set myStartTime=%time%
Set myTime=%myStartTime::=%
SET myTime=%myTime:~0,-3%
Set _time=%myTime: =%
REM Echo New Variable: %_time%>> EpicMFLProcess.log
 
START "" /wait "C:\Program Files (x86)\WinSCP\winscp.exe" /console /log=WinSCP_%_date%_%_time%.log /script=EpicSFTPScript.txt 
ECHO Download Completed @ %date% - %time% >> EpicMFLProcess.log
 
START "" /wait %_myPWD%\DocReformat.vbs 3Mdocmaster.txt 3Mdocmaster_reformatted.csv
ECHO DocReformat Completed @ %date% - %time% >> EpicMFLProcess.log
 
ECHO MOVE /Y 3Mdocmaster_reformatted.csv doc_3M_%_date%_%_time%.csv >> EpicMFLProcess.log
MOVE /Y %_myPWD%\3Mdocmaster_reformatted.csv %_myPWD%\doc_3M_%_date%_%_time%.csv
ECHO File Rename Completed (3Mdocmaster_reformatted.csv TO doc_3M_%_date%_%_time%.csv) @ %date% - %time% >> EpicMFLProcess.log
 
ECHO MOVE /Y %_myPWD%\doc_3M_%_date%_%_time%.csv %CD% >> EpicMFLProcess.log
CD ..
CD Load
MOVE /Y %_myPWD%\doc_3M_%_date%_%_time%.csv %CD%
 
CD %_myPWD%
REM PING -n 11 127.0.0.1>nul
ECHO MFL File Moved to Load folder Completed @ %date% - %time% >> EpicMFLProcess.log
  • FTP files.zip (13.12 KB, Private file)

Reply with quote E-mail

Advertisement

martin
Site Admin
martin avatar
Joined:
Posts:
34,115
Location:
Prague, Czechia

Re: Need help updating an existing script to move a CSV file

Sorry, but I do not understand, what you ask us for. There's no difference between transferring TXT and CSV files. Just give the correct filename name in the WinSCP ("FTP") script.

And (re)formatting of a file is not a WinSCP question.

Reply with quote

Alex_MUSC
Joined:
Posts:
3
Location:
Charleston SC

Re: Need help updating an existing script to move a CSV file

Martin,

Thanks for the quick reply. What I need to do is change the existing script from downloading a TXT file to downloading a CSV file directly from WinSCP.

What do I need to change in the Batch file and FTP script to download and move the CSV file from WinSCP to the correct folder location?

Thanks,
Alex

Reply with quote E-mail

martin
Site Admin
martin avatar
Joined:
Posts:
34,115
Location:
Prague, Czechia

Re: Need help updating an existing script to move a CSV file

Alex_MUSC wrote:

Thanks for the quick reply. What I need to do is change the existing script from downloading a TXT file to downloading a CSV file directly from WinSCP.
Once again: There's no difference between transferring TXT and CSV files. Just give the correct filename name in the WinSCP ("FTP") script.

You have this command for downloading .txt file:
get 3Mdocmaster.txt D:\3MHIS\Physicians\Processing\
If you want to download .csv file, just download a .csv file....
get 3Mdocmaster.csv D:\3MHIS\Physicians\Processing\

Reply with quote

Advertisement

Guest

Re: Need help updating an existing script to move a CSV file

Martin,
Thanks for the clarification and feedback. I will change the file extension in the script and test it out.
Alex

Reply with quote

Advertisement

You can post new topics in this forum