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!
# 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:email@example.com/ -hostkey="ssh-rsa 2048 xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx"
#open sftp://3m.ftp@Epicshd.mdc.musc.edu/ -hostkey="*"
open sftp://3m.ftp:firstname.lastname@example.org/ -hostkey="ssh-ed25519 256 bpPVqjd1/QQgssl7XYBGmUGE6SsDsdLBbpyV1+bMIlQ=" -rawsettings FSProtocol=2
# Change remote directory
# Force binary mode transfer
option transfer binary
# Download file to the local directory d:\
get 3Mdocmaster.txt D:\3MHIS\Physicians\Processing\
# Exit WinSCP
Echo New Variable: %_myPWD%>> EpicMFLProcess.log
Set _date=%myDate: =%
REM Echo New Variable: %_date%>> EpicMFLProcess.log
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
MOVE /Y %_myPWD%\doc_3M_%_date%_%_time%.csv %CD%
REM PING -n 11 127.0.0.1>nul
ECHO MFL File Moved to Load folder Completed @ %date% - %time% >> EpicMFLProcess.log