none
I have 4 different strings, how can I export them to 4 different columns as csv file in powershell ? RRS feed

  • Question

  • First I'd write here my code and what I have done so far to work this out.

    $page = invoke-WebRequest -Uri "someURL"
    $location = $page.ParsedHtml.body.getElementsByTagName('Tag0') | Where {$_.getAttributeNode('class').Value -eq 'value0'}
    $rooms = $page.ParsedHtml.body.getElementsByTagName('Tag1') | Where {$_.getAttributeNode('class').Value -eq 'value1'}
    $size = $page.ParsedHtml.body.getElementsByTagName('Tag2') | Where {$_.getAttributeNode('class').Value -eq 'value2'}
    $price = $page.ParsedHtml.body.getElementsByTagName('Tag3') | Where {$_.getAttributeNode('class').Value -eq 'value3'}
    
    $locationText=$location.innerText -replace "`n"," " -replace "`r"," "
    $roomsText=$rooms.innerText -replace "`n"," " -replace "`r"," "
    $sizeText=$size.innerText -replace "`n"," " -replace "`r"," "
    $priceText=$price.innerText -replace "`n"," " -replace "`r"," "
    
    $exportLoc= $locationText | select-object @{Name='locataion';Expression={$_}}
    
    $exportRooms = $toomsText | select-object @{Name='rooms';Expression={$_}}
    
    $exportSize = $sizeText | select-object @{Name='size';Expression={$_}}
    
    $exportPrice = $priceText | select-object @{Name='price';Expression={$_}}
    
    
    Now I tried to export $exportLoc with 
    $exportLoc | Export-Csv data.csv -Encoding Unicode
    And it worked. But I cannot append (the following line generates an error)
    $exportRooms | Export-Csv data.csv -Encoding Unicode
    So I tried to wrap them in an object and export that object at once like this
    $wrapper = @{
    'locations' = $exportLoc
    'rooms' = $exportRooms
    'size' = $exportSize
    'prices' = $exportPrice
    } 
    
    $wrapper | Export-Csv data.csv -Encoding Unicode
    The above code didn't work as well. The content of the column in CSV file appears to be 
    System.Object[]
    instead of the actual content of the value.

    How can I have 4 different columns in Excel sheet each contains one of the above objects?





    • Moved by Bill_Stewart Tuesday, November 7, 2017 10:17 PM This is not "fix/debug/rewrite my script for me" forum
    Tuesday, September 26, 2017 2:52 PM

All replies

  • Hi,

    so close:

    $wrapper = [PSCustomObject]@{
    'locations' = $exportLoc
    'rooms' = $exportRooms
    'size' = $exportSize
    'prices' = $exportPrice
    } 
    
    $wrapper | Export-Csv data.csv -Encoding Unicode

    Cheers,
    Fred


    There's no place like 127.0.0.1

    Tuesday, September 26, 2017 2:59 PM
  • This exports System.Object[] instead of the actual content of $exportLoc, $exportRooms.. etc..
    Tuesday, September 26, 2017 3:42 PM
  • Ah, so you've got lists in them? Sorry about that:

    $wrapper = [PSCustomObject]@{
    'locations' = $exportLoc -join ", "
    'rooms' = $exportRooms -join ", "
    'size' = $exportSize -join ", "
    'prices' = $exportPrice -join ", "
    } 
    
    $wrapper | Export-Csv data.csv -Encoding Unicode

    Cheers,
    Fred

    PS: Export-Csv has an -Append parameter, in case you want to add additional lines to an existing csv, so long as they have the same columns


    There's no place like 127.0.0.1

    Tuesday, September 26, 2017 3:52 PM
  • Sorry for the inconvenience
    The result of 

    $exportLoc -join ", "

    is

    , , , , , , , , , , , , , , , , , , ,


    Tuesday, September 26, 2017 4:03 PM
  • Well ... looks like you need to find a way to retrieve the information you actually need first, before exporting it.

    This means you only have empty strings in that variable.


    There's no place like 127.0.0.1

    Wednesday, September 27, 2017 7:29 AM
  • Here is the output of $exportLoc

    locataion
    ---------
    Beylikdüzü  Merkez
    Beylikdüzü  Kavaklı
    Beylikdüzü  Kavaklı
    Beylikdüzü  Kavaklı
    Esenyurt  Haramidere
    Beylikdüzü  Gürpınar
    Beylikdüzü  Kavaklı
    Beylikdüzü  Kavaklı
    Beylikdüzü  Kavaklı
    Beylikdüzü  Kavaklı
    Beylikdüzü  Kavaklı
    Beylikdüzü  Yakuplu
    Esenyurt
    Beylikdüzü  Kavaklı
    Beylikdüzü  Kavaklı
    Esenyurt
    Beylikdüzü  Kavaklı
    Beylikdüzü  Kavaklı
    Beylikdüzü  Kavaklı
    Beylikdüzü  Kavaklı
    Here is the output of $exportLoc -join ", "

    , , , , , , , , , , , , , , , , , , ,

    $exportLoc is not empty

    Wednesday, September 27, 2017 11:46 AM
  • Ah, I see. It is indeed not - quite - empty, but it's hidden in a property.

    Try this:

    $exportLoc.locataion -join ", "

    Cheers,
    Fred


    There's no place like 127.0.0.1

    Wednesday, September 27, 2017 12:11 PM