asked on

Convert CSV

Hi,

i've a csv it looks like that:

UserId;OrderIdent;OrderID;Action;Product;Version
Z528517;2397277;ZP145676-001;YES;MAIL;MSC
Z528516;2397278;ZP145676-002;YES;MAIL;MSC
Z528515;2397279;ZP145676-003;YES;MAIL;MSC
Z528514;2397281;ZP145676-004;YES;MAIL;MS2003
Z528484;2397284;ZP145677-001;YES;MAIL;MSC
Z528474;2397285;ZP145677-002;YES;MAIL;MSC
Z528396;2397345;ZP145687-003;YES;MAIL;MS2003
Z130561;2397436;ZP145706-001;YES;MAIL;MSC
Z527465;2397632;ZP145766-001;YES;AdA
Z527465;2397633;ZP145766-002;YES;MAIL;MSXC2010
Z831113;2397637;ZP145767-001;YES;AdA
Z831113;2397638;ZP145767-002;YES;MAIL

Open in new window

I want to convert and sort it to this form (The last lines should be "AdA" and "MS2003"
to use it for some automatic exchange and AD coding

UserId,OrderIdent,OrderID,Action,Product,Version
Z528517,2397277,ZP145676-001,YES,MAIL,MSC
Z528516,2397278,ZP145676-002,YES,MAIL,MSC
Z528515,2397279,ZP145676-003,YES,MAIL,MSC
Z528484,2397284,ZP145677-001,YES,MAIL,MSC
Z528474,2397285,ZP145677-002,YES,MAIL,MSC
Z130561,2397436,ZP145706-001,YES,MAIL,MSC
Z527465,2397633,ZP145766-002,YES,MAIL,MSC
Z831113,2397638,ZP145767-002,YES,MAIL,MSC
Z528514,2397281,ZP145676-004,YES,MAIL,MS2003
Z528396,2397345,ZP145687-003,YES,MAIL,MS2003
Z527465,2397632,ZP145766-001,YES,AdA
Z831113,2397637,ZP145767-001,YES,AdA

Open in new window

Who can help here?

Thank you so much

Raheman M. Abdul

Try the following:
import-csv H:\filecsv.csv | Sort-Object version -Descending | ft -AutoSize

your source data does not match with the resultant data what you require as per your question.

Or try: import-csv H:\filecsv.csv | Sort-Object Product, version -Descending | ft -AutoSize

Raheman M. Abdul

import-csv H:\filecsv.csv | Sort-Object version -Descending | ft -AutoSize | export-csv H:\ filecsv2.csv

Raheman M. Abdul

The above code works if you replace all the ; with commas

ASKER CERTIFIED SOLUTION

Qlemo

membership

This solution is only available to members.

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

Start Free Trial

Mandy_

ASKER

hi,

thanks. sorry so far not working for me

What exactly did ft -AutoSize

Raheman M. Abdul

It's short form for format-table
Autosize displays the data in columns with automatic width according to the data

I guess your input data sample is not matching with the output you require. There must be error in typo etc.
Eg. Msxc2010 it's not in your output

Qlemo

Mandy_,

If something does not work for you, please tell us what did not work - e.g. list the expected result and what you got instead. And it is always a good idea to mention which post you refer to.

Mandy_

ASKER

Thank you for your help. this one below is working fine.

(import-csv -delimter ';' file.csv | sort product, version -descending |
converto-csv -notype -delimiter ';') -replace '"' | out-file result.csv

Mandy_

ASKER

very good . thank you