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
LVL 6
anumosesAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

oBdACommented:
The name of the csv sample and the name of the csv as defined in the batch differ; the file is separated with "-", the batch with "_".
Here's a version that relies on the underscore being used; as before, it's in test mode:
@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%"

ECHO 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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
anumosesAuthor Commented:
@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.
oBdACommented:
Any errors displayed in the console or in the upload.log?
At the end of line 18, try replacing "UTF8" with "ASCII".
Does the input file really have the format with the underscores?
There should now be a file with the name of the csv file and a "-converted" attached before the ".csv".
Simplify Active Directory Administration

Administration of Active Directory does not have to be hard.  Too often what should be a simple task is made more difficult than it needs to be.The solution?  Hyena from SystemTools Software.  With ease-of-use as well as powerful importing and bulk updating capabilities.

anumosesAuthor Commented:
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

oBdACommented:
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.
anumosesAuthor Commented:
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
anumosesAuthor Commented:
New csv file with underscores removed. File not being transferred no errors in log file.
06022015.csv
anumosesAuthor Commented:
It worked. Thanks
anumosesAuthor Commented:
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.
oBdACommented:
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?
anumosesAuthor Commented:
The user will put one file everyday in the morning. 1 File a day by date and time. Thanks
anumosesAuthor Commented:
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,
anumosesAuthor Commented:
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Powershell

From novice to tech pro — start learning today.