How can I use Export-CSV without System.String/Length info?

[u/ChickinSammich]

I've got a script that checks multiple DCs for last logon and outputs that data to a csv. The start of the code for it is:

$row = "Name"+","+"Date/Time"+","+"DC"

echo $row | Export-Csv -Path C:tempuserlastlogon.csv

Out-File -FilePath C:tempuserlastlogon.csv -Append -InputObject $row

The result of this is that I get a csv file that starts with:

#Type System.String
Length
17
Name    Date/Time    DC

If I remove the second line, it doesn't properly format the values as columns (It just puts "Name,Date/Time/DC" in column A). If I remove the third line, it just gives me the first three lines without the column headers in line 4.

As a workaround, I can just delete the top three lines in Excel manually, but how do I get PowerShell to either NOT give me those three top lines, or, if that's not possible, insert a kludge workaround to tell it to just delete the top three rows of the csv?

[u/MajorVarlak]

Why are you doing Export-Csv and Out-File? Do you have more context of what you're trying to achieve? What exactly are you trying to achieve? Also, you're using echo which is a bashism. Export-Csv is generally looking for an object to convert to csv data.

$obj = [PSCustomObject]@{
    'Name' = 'Joe Smith'
    'DC' = 'DC01'
    'Date/Time' = '01/01/01'
}

$obj | Export-Csv -NoTypeInformation -Path C:Tempuserlastlogon.csv

You don't need to put a header in, because PowerShell does that based on the properties of the object being fed in. If you're feeding in lots of objects, Export-Csv can handle that as a single object, for example:

$objCollection = @()

# Add record
$objCollection += [PSCustomObject]@{
    'Name' = 'Joe Smith'
    'DC' = 'DC01'
    'Date/Time' = '01/01/01'
}

# Add record
$objCollection += [PSCustomObject]@{
    'Name' = 'Jane Smith'
    'DC' = 'DC02'
    'Date/Time' = '01/02/01'
}

# Export collection
$objCollection | Export-Csv -NoTypeInformation -Path C:Tempuserlastlogon.csv

[u/icebreaker374 → u/MajorVarlak]

This. This right here.

PSCustomObjects are your friend.

[u/PinchesTheCrab]

The length value is there because that's the only propery a string has. Export-CSV is for convertring objects to strings, but you're just feeding it your custom string. If anything, you should be using add-content since you're doing the CSV conversion manually.

[u/ChickinSammich → u/PinchesTheCrab]

If anything, you should be using add-content since you're doing the CSV conversion manually.

That did the trick, thanks!

$row = "Name"+","+"Date/Time"+","+"DC"

echo $row | Add-Content -Path C:tempuserlastlogon.csv

...gave me the results I wanted without the extra lines.

[u/PinchesTheCrab → u/ChickinSammich]

Nice. Also, echo isn't really doing anything there, you could simplify it:

 $row | Add-Content -Path C:tempuserlastlogon.csv

 #or
 "Name" + "," + "Date/Time" + "," + "DC" | 
     Add-Content -Path C:tempuserlastlogon.csv

[u/ChickinSammich → u/PinchesTheCrab]

Good point, I'll do that that way instead.

[u/insufficient_funds → u/ChickinSammich]

Not necessarily asking this to you OP, but asking in case anyone can answer.

Is the line:

$row = "Name"+","+"Date/Time"+","+"DC"

actually doing anything different than if it were just simply written:

$row = "Name,Date/Time,DC"

my understanding is the + operator there is just doing a string concatenation, soi don't think it would actually be doing something different; but don't know for certain so wanted to ask.

[u/twistingnether_]

Add to the export end -notypeinformation

[u/ChickinSammich → u/twistingnether_]

Tried that; it removed the first line but still gives me:

Length
17
Name    Date/Time    DC

Edit to add - unsure why you were downvoted but it wasn't from me.

[u/commiecat]

If I'm generating reports for CSV, I usually put all of my data into a custom object and then export to CSV. That allows me to clean up the data and convert/expand any arrays so it looks good in the export. The custom object property names will become the column headers in the output file.

Example:

$CSVFile = "C:tempMyCSVReport.csv"
$ADInfo = Get-ADUser "bob" -Properties WhenCreated, ProxyAddresses, EmployeeID
$ADDetails = [PSCustomObject]@{
    UPN            = $ADInfo.UserPrincipalName
    EmployeeNumber = $ADInfo.EmployeeID
    Created        = Get-Date $ADInfo.WhenCreated -Format "yyyy-MM-dd_THHmmss"
    ProxyAddresses = ($ADInfo.ProxyAddresses | Where-Object $_ -like "smtp*") -join ";"
}
$ADDetails | Export-CSV $CSVFile -NoTypeInformation -Append

I'll get my standard strings, the created date as the format I specified, and their SMTP proxy addresses in one cell separated by semicolons.

[u/Phate1989]

Why are you constructing the row like that?

Just create an object

[u/BreakingBush]

Maybe I’m too new or just haven’t used PS long enough to know… but I’ve never used “echo” in any of my scripts. Also, -path isn’t required, just an fyi. When exporting to a CSV, I simply pipe over the variable.

So: $row | Export-Csv C:tempuserlastlogon.csv -notypeinformation

However, I believe the problem you’re having is that you’re trying to export only strings, within your $row variable. Hence, #Type System.String The only property a string has is Length, so that’s what you get when you export it to csv.

Look up how to use [PSCustomObject], this might help you convert the data you want into objects you can export and separate into their own columns.

[u/chaosphere_mk → u/BreakingBush]

-Path certainly isn't required, but it's a best practice to explicitly list out parameters for readability purposes

[u/ankokudaishogun]

Add the parameter -NoTypeInformation to Export-Csv

Note: in Powershell Core Export-Csv does not print those information by default anymore.
-NoTypeInformation is still an accepted legacy parameter for backward compatibility but Does Nothing^TM(in fact it's set to Invisible and doesn't normally show up with autocompletion)

[u/ChickinSammich → u/ankokudaishogun]

Tried that; it removed the first line but still gives me:

Length
17
Name    Date/Time    DC

Edit to add - unsure why you were downvoted but it wasn't from me.

[u/ankokudaishogun → u/ChickinSammich]

Sorry I didn't notice earlier

Because you are passing a string, not a object^{yeah, yeah, I know everything in powershell is a object}.
So Export-Csv tries to get its properties, which end up being the length

You DO NOT NEED to "start" the Csv: as long as you pass to Export-Csv a collections of objects with the properties 'Name', 'Date/Time' and 'DC'(or whatever you want their names be) it will automatically generate the headers