Batch script issue for getting dynamic column of csv file?

Question

This is script which i am using for export the data from csv and write into a .trn extension file. The code execute for certain column of csv file data right, i mean to say if the column no indexing A to Z, code execute right,but when after the Z column means for AA,AB ect the data gets wrong. Code you can see here:

Batch Script

@echo off
   set "line1=^%%a,cf,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,y,,,,,,,,,,,,,"
   set "line2=^%%a,$,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,y,qbconid,,,^%%b,,,,,,,,,"
   .......................................................................................
   .......................................................................................
   set "line26=^%%a,$,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,y,txtstate,,,^%%z,,,,,,,,,"
   set "line27=^%%a,$,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,y,pmtper,,,^%%aa,,,,,,,,,"
   set "line28=^%%a,#,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,y,minfee,^%%ab,,,,,,,,,"
(for /F "tokens=1-28 skip=1 delims=," %%a in ('type "NewPortfolios.csv"') do ( 
        echo %line1%
        echo %line2%
        ............
        ............
        echo %line26%
    echo %line27%
    echo %line28%

    )) >"file.trn"

And the csv file data is:

 A               B             C               AA           AB
$portcode   $qbconId    $name     ....... $pmtper   #minfee
asingh12    123456789   Ajay Singh....... ajay           123

Output is:

asingh12,cf,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,y,,,,,,,,,,,,,
asingh12,$,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,y,qbconid,,,123456789,,,,,,,,,
...................................................................................
asingh12,$,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,y,pmtper,,,asingh12a,,,,,,,,,
asingh12,#,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,y,minfee,asingh12b,,,,,,,,,

In the above 2 lines you can see that the column value is coming wrong. As you can see CSV data the last 2 column value should be ajay and 123 but it is coming as asingh12a and asingh12b.These are the values of a and b.It should come from AA and AB.

Can someone please help me in this.?

                            **EDITED HERE**

Hi Aacini - Hope you are doing well. I am facing again an issues with .csv file convert in to .txt file. Now this time getting a data from .csv with Quotes(") that's why the batch script is not running properly. Please find the sample data from .csv in to .txt format- "_portcode","_qbconId","_name","_address","_city","_state","_zip","_bmeth","_ffee","_brak1","_rate0","_brak2","_rate1","_brak3","_rate2","_brak4","_rate3","_brak5","_rate4","_rate5","_bre","_custact","_custody","_qbmgrid","_refby","_txstate","_pmtper","_minfee"

"schorira","001c000000WtrL1AAJ",,"9232 Collegeview Cir","Bloomington","MN","55437","T",,"1000000.0","1.25",,,,,,,,,"1.0","Robert M. Schofield IRA Rollover",,"Schwab","JTB","Patrick Stephens","MN","Quarterly","0". Previous time in .csv data there is no Quotes. Please help me out. If you want more clarification from my side please let me know. I appreciate your help in advance.

Hi Acini,

Till now we are waiting for your reply. Please help me out.

This question appears to be off-topic because it is homework! See stackoverflow.com/q/21090192/2128947 and stackoverflow.com/q/21019174/2128947 — Magoo
– Magoo, Commented Jan 15, 2014 at 10:25

Aacini · Accepted Answer · 2014-01-16 17:17:40Z

2

EDIT: I fixed a couple details, it should correctly run now.

EDIT #2: Subtle bug fixed, and an example added

@echo off
set "letter=abcdefghijklmnopqrstuvwxyz"
   set "line1=!a!,cf,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,y,,,,,,,,,,,,,"
   set "line2=!a!,$,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,y,qbconid,,,!b!,,,,,,,,,"

   set "line26=!a!,$,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,y,txtstate,,,!z!,,,,,,,,,"
   set "line27=!a!,$,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,y,pmtper,,,!aa!,,,,,,,,,"
   set "line28=!a!,#,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,y,minfee,!ab!,,,,,,,,,"
setlocal EnableDelayedExpansion
(for /F "usebackq skip=1 delims=" %%a in ("NewPortfolios.csv") do (
   set i1=0
   set "line=%%a"
   for %%b in ("!line:,=" "!") do for /F "tokens=1,2" %%i in ("!i1! !i2!") do (
      if %%i lss 26 (
         set var=!letter:~%%i,1!
      ) else (
         set var=a!letter:~%%j,1!
      )
      set "!var!=%%~b!
      set /A i1+=1, i2=i1-26
   )
   echo %line1%
   echo %line2%

   echo %line26%
   echo %line27%
   echo %line28%
)) > file.trn

Previous Batch program can output up to a maximum of 52 columns (a..z aa..az), but it is very easy to increment this range. With this input data:

 A,        B,       C,        D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z, AA,    AB
$portcode,$qbconId,$name,     D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z,$pmtper,#minfee
asingh12,123456789,Ajay Singh,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z,ajay,123

the output is:

asingh12,cf,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,y,,,,,,,,,,,,,
asingh12,$,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,y,qbconid,,,123456789,,,,,,,,,
asingh12,$,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,y,txtstate,,,Z,,,,,,,,,
asingh12,$,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,y,pmtper,,,ajay,,,,,,,,,
asingh12,#,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,y,minfee,123,,,,,,,,,

edited Jan 16, 2014 at 17:17

answered Jan 15, 2014 at 16:18

Aacini

67.7k12 gold badges74 silver badges114 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Ajay-Salesforce Expert Over a year ago

There are couple of points here: 1. We need to have more than 30 columns in the CSV file. So, we need to generate a .txt file while will display the data as given above. Here, we are not able to use more than 25 column variables. Please suggest how to do that. It will very helpful. 2. Whatever variable we are taking in the for loop (as we have taken z - see the above code) then starting from z column the data is coming blank. Please suggest how to take the variable for porting the data in a txt file for more than 30 columns. Appreciate your support and quick response.

Aacini Over a year ago

@Ajay-SalesforceExpert: There was a subtle bug in the code; see the second edit...

Ajay-Salesforce Expert Over a year ago

Hi Aacini Hope you are doing well. I have a doubt i have edited my questions Please see question and Help me.

Collectives™ on Stack Overflow

Batch script issue for getting dynamic column of csv file?

Can someone please help me in this.?

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Can someone please help me in this.?

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related