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