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:
Batch file:
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
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
