asked on

Batch file to powershell scripting

I have a batch file that I run to load data to the server. Its a csv file. Sometimes the file gets too big. As per one of the expert here "Chances are too big that Batch will choke on the ^ or other special characters, not to mention that some fields are not filled, which prevents the use of "for /f"."

So need help in powershell scripting. I am new to this concept

Batch file code

@echo off
setlocal enabledelayedexpansion
set FtpFile=%~dp0upload.ftp
set CsvFolder=F:\Purple_Top
set logfile=upload.log

set CsvFile=
for /f "delims=" %%a in ('dir /o:-d /b "%CsvFolder%\*.csv" ^| findstr.exe "^[0-9][0-9]_[0-9][0-9]_[0-9][0-9][0-9][0-9]_[0-9][0-9]_[0-9][0-9]\.csv$"') do (set CsvFile=%CsvFolder%\%%a)
if not defined CsvFile (
	echo No csv file found in '%CsvFolder%'!
	exit /b 1
)
echo Using '%CsvFile%' as file to upload.
call :ExportDataSection FTP "%FtpFile%"

echo Content of the ftp file:
type "%FtpFile%"

ftp.exe -i -v -s:"%FtpFile%" >"%logfile%"



REM ================================================================================
REM Only functions after this line!
REM ================================================================================
goto :eof
:ExportDataSection
REM *** Reads all lines listed in %1 (section) and writes them to %2 (file name).
REM *** Environment variables will be expanded.
set Section=%~1
set FileName=%~2
if exist "%FileName%" del "%FileName%"
for /f "tokens=1 delims=[]" %%a in ('type "%~f0" ^| C:\Windows\system32\find.exe /n "[%Section%]"') do set DataStart=%%a
for /f "skip=%DataStart% delims=" %%a in ('type "%~f0"') do (
	call :Expand Line "%%a"
	if "!Line:~0,1!"=="[" (goto :eof) else (>>"%FileName%" echo !Line!)
)
goto :eof

:Expand
set %1=%~2
goto :eof

REM ================================================================================
REM Only data sections after this line!
REM ================================================================================
[FTP]
open heart1
hbc_data
hbc_data
cd /home/hbc_data/data/purple_top_data
mput %CsvFile%
bye

Open in new window

Also from the csv file I need three columns oly

1. Sample ID No
2. HGB(g/L)
3. PLT(10^3/uL)

Help is appreciated.
05-14-2015-11-17.csv

ASKER CERTIFIED SOLUTION

oBdA

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

anumoses

ASKER

@echo off
setlocal enabledelayedexpansion
set FtpFile=%~dp0upload.ftp
set CsvFolder=F:\Purple_Top
set CsvFolder=C:\Temp
set logfile=upload.log

set CsvFile=
for /f "delims=" %%a in ('dir /o:d /b "%CsvFolder%\*.csv" ^| findstr.exe "^[0-9][0-9]_[0-9][0-9]_[0-9][0-9][0-9][0-9]_[0-9][0-9]_[0-9][0-9]\.csv$"') do (set CsvFile=%CsvFolder%\%%a)
if not defined CsvFile (
	echo No csv file found in '%CsvFolder%'.
	exit /b 1
)
echo Using '%CsvFile%' as input file.
for %%a in ("%CsvFile%") do (set UploadFile=%%~dpna-converted.csv)

echo Filtering unused columns, writing output to '%UploadFile%'.
powershell.exe -ExecutionPolicy RemoteSigned -Command "& {Import-Csv -Path '%CsvFile%' | Select-Object -Property 'Sample ID No', 'HGB(g/L)', 'PLT(10^3/uL)' | ConvertTo-Csv -NoTypeInformation | ForEach-Object {$_.Replace([char]34 + ',' + [char]34 , ',').Trim([char]34)} | Out-File -FilePath '%UploadFile%' -Encoding UTF8}"

call :ExportDataSection FTP "%FtpFile%"

echo Content of the ftp file:
type "%FtpFile%"

ftp.exe -i -v -s:"%FtpFile%" >"%logfile%"



REM ================================================================================
REM Only functions after this line!
REM ================================================================================
goto :eof
:ExportDataSection
REM *** Reads all lines listed in %1 (section) and writes them to %2 (file name).
REM *** Environment variables will be expanded.
set Section=%~1
set FileName=%~2
if exist "%FileName%" del "%FileName%"
for /f "tokens=1 delims=[]" %%a in ('type "%~f0" ^| C:\Windows\system32\find.exe /n "[%Section%]"') do set DataStart=%%a
for /f "skip=%DataStart% delims=" %%a in ('type "%~f0"') do (
	call :Expand Line "%%a"
	if "!Line:~0,1!"=="[" (goto :eof) else (>>"%FileName%" echo !Line!)
)
goto :eof

:Expand
set %1=%~2
goto :eof

REM ================================================================================
REM Only data sections after this line!
REM ================================================================================
[FTP]
open heart1
hbc_data
hbc_data
cd /home/hbc_data/data/purple_top_data
mput %UploadFile%
bye

Open in new window

removed the ECHO. It should transfer the file correct? It did not. Let me know. Thanks for the help.

SOLUTION

oBdA

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

anumoses

ASKER

ASCII did not work. No files created.

06_02_2015_15_45.csv

This is how the name of file is.

@echo off
setlocal enabledelayedexpansion
set FtpFile=%~dp0upload.ftp
set CsvFolder=F:\Purple_Top
set CsvFolder=C:\Temp
set logfile=upload.log

set CsvFile=
for /f "delims=" %%a in ('dir /o:d /b "%CsvFolder%\*.csv" ^| findstr.exe "^[0-9][0-9]_[0-9][0-9]_[0-9][0-9][0-9][0-9]_[0-9][0-9]_[0-9][0-9]\.csv$"') do (set CsvFile=%CsvFolder%\%%a)
if not defined CsvFile (
	echo No csv file found in '%CsvFolder%'.
	exit /b 1
)
echo Using '%CsvFile%' as input file.
for %%a in ("%CsvFile%") do (set UploadFile=%%~dpna-converted.csv)

echo Filtering unused columns, writing output to '%UploadFile%'.
powershell.exe -ExecutionPolicy RemoteSigned -Command "& {Import-Csv -Path '%CsvFile%' | Select-Object -Property 'Sample ID No', 'HGB(g/L)', 'PLT(10^3/uL)' | ConvertTo-Csv -NoTypeInformation | ForEach-Object {$_.Replace([char]34 + ',' + [char]34 , ',').Trim([char]34)} | Out-File -FilePath '%UploadFile%' -Encoding ASCII}"

call :ExportDataSection FTP "%FtpFile%"

echo Content of the ftp file:
type "%FtpFile%"

ftp.exe -i -v -s:"%FtpFile%" >"%logfile%"



REM ================================================================================
REM Only functions after this line!
REM ================================================================================
goto :eof
:ExportDataSection
REM *** Reads all lines listed in %1 (section) and writes them to %2 (file name).
REM *** Environment variables will be expanded.
set Section=%~1
set FileName=%~2
if exist "%FileName%" del "%FileName%"
for /f "tokens=1 delims=[]" %%a in ('type "%~f0" ^| C:\Windows\system32\find.exe /n "[%Section%]"') do set DataStart=%%a
for /f "skip=%DataStart% delims=" %%a in ('type "%~f0"') do (
	call :Expand Line "%%a"
	if "!Line:~0,1!"=="[" (goto :eof) else (>>"%FileName%" echo !Line!)
)
goto :eof

:Expand
set %1=%~2
goto :eof

REM ================================================================================
REM Only data sections after this line!
REM ================================================================================
[FTP]
open heart1
hbc_data
hbc_data
cd /home/hbc_data/data/purple_top_data
mput %UploadFile%
bye

Open in new window

oBdA

Any errors displayed in the console or in the upload.log?
I'll be offline for the next hours; maybe someone else will chime in in the meantime.

anumoses

ASKER

log file

ftp> User (heart1.heartlandbc.org:(none)): open heart1
ftp>

ftp> cd /home/hbc_data/data/purple_top_data
ftp> mput F:\Purple_Top\06_02_2015_15_45.csv
bye

anumoses

ASKER

New csv file with underscores removed. File not being transferred no errors in log file.
06022015.csv

anumoses

ASKER

It worked. Thanks

anumoses

ASKER

I remembered you saying take the latest file. Now I have 2 files

06_02_2015_15_45.csv

06_03_2015_14_49.csv

Script is only loading

06_02_2015_15_45-converted.csv

How do we fix it.

oBdA

It's currently going by "modified" date, because a time stamp in mm_dd_yyyy format doesn't really lend itself for alphabetic sort.
Could it be that the 06_02_2015_15_45.csv has (maybe from testing) a more recent modified date than 06_03_2015_14_49.csv?
I could work around that, but how many files will there be in the CSV folder at most?

anumoses

ASKER

The user will put one file everyday in the morning. 1 File a day by date and time. Thanks

anumoses

ASKER

Also can we ftp file only with data? No headers. From the csv file I am using another programming language(oracle) to load the data into our database table. So headers were causing a problem. I edited the csv and removed the headers

Sample ID No,HGB(g/L),PLT(10^3/uL)
W039715056673,156,218
W039715056535,170,172
W039715056704,168,197
W039715060054,150,217
W039715060904,150,248
W039715060144,135,307
W039715056566,158,309
W039715060049,152,211
W039715060996,126,195
W039715056565,147,254

TO

W039715056673,156,218
W039715056535,170,172
W039715056704,168,197
W039715060054,150,217
W039715060904,150,248
W039715060144,135,307
W039715056566,158,309
W039715060049,152,211
W039715060996,126,195
W039715056565,147,254

Can you help me with that along with date concept to take the latest file? Thanks,

anumoses

ASKER

It's currently going by "modified" date, because a time stamp in mm_dd_yyyy format doesn't really lend itself for alphabetic sort.

As you mentioned the file in purple_top directory

1. 06_03_2015_14_49.csv Modified date - 6/4/2015 9:13 AM

2. 06_02_2015_15_45.csv Modified date - 6/3/2015 7:37 AM

As per your code when I run the batch file should it not ftp No 1 file? Please confirm. I am asking this as it did not ftp based on modified date.