Solved copy Specific columns by headers batch script

February 7, 2013 at 22:21:09
Specs: Windows 7
Hi ,
I need to copy specific columns by header from csv file and copy it to another csv file.

e.g. if columnname in ('COMPANY_NO','ERP_ID') then
copy it to another file.

Can you please help me for above ?


See More: copy Specific columns by headers batch script

Report •

✔ Best Answer
March 1, 2013 at 11:31:53
Ok, first the swapping to be addressed (since the second part has me confused):

@echo off & setlocal
set company_no=
set erp_id=
set a=a
set b=b
set inp=%~dpn1
if not defined inp set /p inp=file to process (no extension .csv assumed):
set /p x=<%inp%.csv
set c=1
call :split %x%
if %company_no% gtr %erp_id% (
set a=b
set b=a)
:debug
echo company_no:%company_no% : erp_id:%erp_id%
for /f "tokens=%erp_id%,%company_no% delims=," %%a in (%inp%.csv) do (
echo %%%a%,%%%b%)>%inp%_proc.csv
goto :eof

:split
if "%~1" equ "" goto :eof
set %~1=%c%
set /a c+=1
shift
goto :split
::======= end
I tried to make it easier to use by including commandline specs for input-file-name.
As for the second problem, I'm not sure why, unless the var value is persisting in the global environment. I tried "zeroing" the two variables at the beginning. Notice the :debug label, you might try putting line following that into your code to see what it's picking up.



#1
February 8, 2013 at 10:31:27
PowerShell one-liner:

Import-Csv .\test.csv | Select-Object COMPANY_NO, ERP_ID | Export-Csv out.csv -NoTypeInformation

Tony


Report •

#2
February 28, 2013 at 02:18:36
Actually, i need a batch script for this. That i have to use in informatica cloud command process.

Report •

#3
February 28, 2013 at 12:32:15
@echo off & setlocal
set /p x=<csv
set c=1
call :split %x%
rem echo %company_no% : %erp_id%
(for /f "tokens=%company_no%,%erp_id% delims=," %%a in (csv) do echo %%a,%%b)>newcsv
goto :eof

:split
if "%~1" equ "" goto :eof
set %~1=%c%
set /a c+=1
shift
goto :split


Report •

Related Solutions

#4
February 28, 2013 at 23:41:48
Thank you so much.. This work perfect for me. But just one problem here.

If the csv file contains column company_no before erp_id it shows result as

Company_no ERP_ID
170 CH-GH-170

and if ERP_ID column in csv before column company_no it shows as

ERP_ID Company_no

CH-GH-170 170

I want every time my newscv file shows column sequence as
Company_no,ERP_ID.


Report •

#5
March 1, 2013 at 03:25:09
Hi ,

Above script has another problem. When i used below condition in my batch file it picks up wrong column.

if %filename% ==SANDBOX_SALES_ORDER_Error.csv (

echo into logdirectory backup1
@echo off & setlocal
set /p x=<C:\batchfiles\error_processing\SANDBOX_SALES_ORDER_Error.csv
set c=1
echo %efile%
call :split %x%
echo %ERP_ID%
(for /f "tokens=%ERP_ID% delims=," %%a in (C:\batchfiles\error_processing\SANDBOX_SALES_ORDER_Error.csv) do echo %%a)>C:\batchfiles\error_processing\SANDBOX_Sales_Order_Err_Proc.csv
echo %efilename%
goto :eof
echo %efile%
:split
if "%~1" equ "" goto :eof
set %~1=%c%
echo %~1
set /a c+=1
shift
goto :split
)

if %filename% ==SANDBOX_INSTALLATION_Error.csv (

@echo off & setlocal
set /p x=<C:\batchfiles\error_processing\SANDBOX_INSTALLATION_Error.csv
set c=1
echo %efile%
call :split %x%
echo %ERP_ID%
(for /f "tokens=%ERP_ID% delims=," %%a in (C:\batchfiles\error_processing\SANDBOX_INSTALLATION_Error.csv) do echo %%a)>C:\batchfiles\error_processing\SANDBOX_INSTALLATION_Err_Proc.csv
goto :eof
:split
if "%~1" equ "" goto :eof
set %~1=%c%
echo %~1
set /a c+=1
shift
goto :split
)

when i ran this script for sales order codition it shows ERP_ID value where ERP_ID has second column. and if i ran this for installation it shows me second column of installation.csv file instead of ERP_ID.


Report •

#6
March 1, 2013 at 11:31:53
✔ Best Answer
Ok, first the swapping to be addressed (since the second part has me confused):

@echo off & setlocal
set company_no=
set erp_id=
set a=a
set b=b
set inp=%~dpn1
if not defined inp set /p inp=file to process (no extension .csv assumed):
set /p x=<%inp%.csv
set c=1
call :split %x%
if %company_no% gtr %erp_id% (
set a=b
set b=a)
:debug
echo company_no:%company_no% : erp_id:%erp_id%
for /f "tokens=%erp_id%,%company_no% delims=," %%a in (%inp%.csv) do (
echo %%%a%,%%%b%)>%inp%_proc.csv
goto :eof

:split
if "%~1" equ "" goto :eof
set %~1=%c%
set /a c+=1
shift
goto :split
::======= end
I tried to make it easier to use by including commandline specs for input-file-name.
As for the second problem, I'm not sure why, unless the var value is persisting in the global environment. I tried "zeroing" the two variables at the beginning. Notice the :debug label, you might try putting line following that into your code to see what it's picking up.


Report •

#7
March 3, 2013 at 20:49:31
Thank a lot... When i ran this script from command prompt it execute successfully.

But i dont know why it showing below error in informatica cloud,

CMN_1949 Error: [Pre/Post Session Command] Process id 6872. The shell command failed with exit code 255.


Report •

#8
March 4, 2013 at 09:15:56
tonysathre: PowerShell one-liner:
nileshhajare: i need a batch script for this
powershell -Command "Import-Csv test.csv | Select-Object COMPANY_NO, ERP_ID | Export-Csv out.csv -NoTypeInformation"

PROBLEM SOLVED!

How To Ask Questions The Smart Way


Report •

#9
March 18, 2013 at 22:04:59
Thanks for the solution..

I just wondering, that above script is working fine for me but in my csv file contains special characters in headers e.g. CUSTOMER@NUMBER, PROJECT£NUMBER.

I want to remove that special characters from headers and replace it with underscorew "_".

Thanks in advance.


Report •

Ask Question