transform the value of a line into column

hello team,
I need to have a .bat withe VBS that transforms the value of a line into column
And to do this I need to verify the column named country in my original file , for each county on my file i have 12 line and for those line i have 12 values and each value represent a month
for example in my original file
for the country "CHINA " i have 3 line for  each month( column named "date")
and i need to transform the value from line to column ..
in PJ my original file and an example of file after a manual transformation
thank you for your help .
transformed_file.csv
original_file.csv
mell lian90Asked:
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.

Bill PrewIT / Software Engineering ConsultantCommented:
verify the column named country

What do you mean by that?


»bp
mell lian90Author Commented:
hi Bill Prew,
Because in my origianl file i have many country so for each country in need to do the transformation
For example if country = CHINA
for CHINA I need to transform  the 12 values into column
I need to to this transformation for each country  .
Bill PrewIT / Software Engineering ConsultantCommented:
Is is enough to group the incoming data based on column C (country) and column G (op code) or do other columns need to be added to that?  I assume this is only a subset of the data, so wondering if other things like column A, or B, or H etc need to be used when determine the Values to group together for a single output line?

B;2018.12;CHINA;;;2018.01;CHINA;OP10;MTD;0LIA01;;;CNY;ORIG07-02;0000.PACKAGE;;;;;;;;52225,94


»bp
Starting with Angular 5

Learn the essential features and functions of the popular JavaScript framework for building mobile, desktop and web applications.

mell lian90Author Commented:
hello ,

Just i need the column C (country) and column Date to regroupe the data
below another explication to my problem and i hope that can help you to understand what i need,
i will give you all the steps that i do to have the file after manual transformation
1/ I keep the first line of my column source
2/ verify the name of the column country in may second line :
__>if i have the same name of country i check the column of the date ( if i have the same date ==> i keep all the line )
__> if i have another date (the month was change so i have 2018.02 instead of 2018.01) , i copy the value of the column "value" and i past it in the same line after the
value of the previous month and i add ";" to separate data  then i delate this line
Bill PrewIT / Software Engineering ConsultantCommented:
Okay, give this a try, seems to work here.  Save as a VBS file and then run from a command prompt as follows:

cscript EE29087291.vbs input-file.csv output-file.csv

' Define needed constants
Const ForReading = 1
Const ForWriting = 2
Const TriStateUseDefault = -2
Const Delim = ";"

' Data structure for grouping
Class Group
    Public Data
    Public Amount(12)
End Class

' Header line for output file
strOutHeader = "A;B;C;D;E;G;H;I;J;K;L;M;N;O;P;Q;R;S;T;U;V;Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec"

' Columns to copy from input line to output line (zero based)
arrOutCols = Array(0,1,2,3,4,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21)

' Create filesystem object 
Set objFSO = CreateObject("Scripting.FileSystemObject")
 
' Get input and output file names from command line
If (WScript.Arguments.Count < 2) Then
    WScript.Echo "Usage: " & Wscript.ScriptName & " <input-file1> <output-file>"
    WScript.Quit
Else
    strInFile = objFSO.GetAbsolutePathname(WScript.Arguments(0))
    strOutFile = objFSO.GetAbsolutePathname(WScript.Arguments(1))
End If

' Make sure input file exists
If Not objFSO.FileExists(strInFile) Then
   Wscript.Echo "Error: " & Wscript.ScriptName & " - input file not found """ & strInFile & """."
   Wscript.Quit
End If

' Create dictionary object to store groups
Set dicGroup = CreateObject("Scripting.Dictionary")

' Read input file into an array
Set objFile = objFSO.OpenTextFile(strInFile, ForReading, False, TriStateUseDefault)
arrLine = Split(objFile.ReadAll, VbCrLf)
objFile.Close

' Loop through all lines of the file, load into a dictionary (skip index 0 which is the input header line)
For i = 1 To UBound(arrLine)

    ' Skip any blank lines
    If arrLine(i) <> "" Then

        ' Split columns by delimiter, gather needed values
        arrField = Split(arrLine(i), Delim)
        strKey = arrField(2) & "_" & arrField(7)
        strDate = arrField(5) 
        strAmount = arrField(22)
        intMonth = CInt(Split(strDate, ".")(1))

        ' If we have seen this group just add this months value to the array
        If dicGroup.Exists(strKey) Then
            dicGroup.Item(strKey).Amount(intMonth) = strAmount
        Else
           ' New group add a new entry to dictionary
           Set objGroup = New Group
           objGroup.Data = arrLine(i)
           objGroup.Amount(intMonth) = strAmount
           dicGroup.Add strKey, objGroup
        End If
    End If

Next

' Output file by groups
Set objFile = objFSO.OpenTextFile(strOutFile, ForWriting, True)

' Write header line
objFile.WriteLine strOutHeader

' Process each group and write as a single line to output file
For Each strKey In dicGroup.Keys
    ' Clear output line
    strOutput = ""

    ' Access data for this group
    Set objGroup = dicGroup.Item(strKey)

    ' Add each column from input line that we want in output line
    arrCols = Split(objGroup.Data, Delim)
    For Each i In arrOutCols
        If strOutput = "" Then
            strOutput = arrCols(i)
        Else
            strOutput = strOutput & Delim & arrCols(i)
        End If
    Next

    ' Add the 12 months of data
    For i = 1 To 12
        strOutput = strOutput & Delim &  objGroup.Amount(i)
    Next

    ' Write the complete output line to file
    objFile.WriteLine strOutput
Next

' Close output file
objFile.Close

Open in new window


»bp

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
mell lian90Author Commented:
thanks for your help Bill Prew ,
you are amazing :)
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
VB Script

From novice to tech pro — start learning today.