locked
First post! CSV cleanup RRS feed

  • Question

  • First time posting so hopefully this is in the right area!
    I have a few thousand lines in a .csv that look like this:

    First Name,Last Name,Field_Name,Field_Value,Date,Order Number
    Bob,Smith,toothpaste,white,10/26/2017,1234567
    Bob,Smith,satisfaction,high,10/26/2017,1234567
    Bob,Smith,toothpaste,white,10/26/2017,7654321
    Mike,Smith,satisfaction,low,10/26/2017,1357614

    I need it to look like this:

    First Name, Last Name, Toothpaste Color,Satisfaction,Date,Order Number
    Bob,Smith,white,high,10/26/2017,1234567
    Bob,Smith,white,$null,10/26/2017,7654321
    Mike,Smith,$null,low,10/26/2017,1357614

    The results have combined the first two rows based off of identical order number.  
    I've tried some Group-Object commands based off "Order Number" but I don't know how to get the "Field_Name" values (There's 50+ unique Field_Name values) into the headers and assign the "Field_Value" to it.
    Does this make sense?  Please understand I can't post my working code due to customer privacy concerns.  I assure you, it's not even close to being workable anyway.

    Any help would be greatly greatly appreciated!

    Wednesday, February 28, 2018 6:18 PM

All replies

  • Import-CSv <file> | Select First Name, Last Name, 'Toothpaste Color', Satisfaction, Date, 'Order Number'


    \_(ツ)_/

    Wednesday, February 28, 2018 6:21 PM
  • You will have to alter the Csv to get what you need which is quite odd. You cannot use $null in a Csv.

    You also will have to write a very smart script to understand what it is you want to do.  We cannot write that for you.  We don't know what this data means or how to use it.


    \_(ツ)_/

    Wednesday, February 28, 2018 6:25 PM
  • Yeah, certainly a dumb issue to have.  1500 lines of junk in my opinion.  Thanks for taking a gander jrv.
    Wednesday, February 28, 2018 6:31 PM
  • I can recommend getting a new Csv created that is done correctly.  That would be the best and easiest way to fix this.

    If you actually know the range of value in eah bad field you can create a script that can fix this.  We can help you with questions about your script but we cannot write it for you.


    \_(ツ)_/


    • Edited by jrv Wednesday, February 28, 2018 6:52 PM
    Wednesday, February 28, 2018 6:51 PM