0

I have this line of code that gets me all the AD users from my Active Directory server but when I look the CSV file it generates (open with Excel) then the file is not formatted properly.

Get-ADUser -Filter * -SearchBase "DC=mydomainname, dc=local" |
    Select-Object name, samaccountname |
    Export-Csv -path "\\mypcname\c$\ADusers.csv" -NoTypeInformation -Encoding UTF8

I expected the output to be like:

Column1 Column2
Jon Doe JDoe

And it is:

name,"samaccountname"
Guest,"Guest"

all in one column.

Edit:

Requested screenshot:

Excel screenshot

Ansgar Wiechers
  • 193,178
  • 25
  • 254
  • 328
Chris Bleck
  • 13
  • 6
  • Normally, we do not approve of screen shots, but this is an exception. Please load the file into Excel, and then take a screen shot of Excel with this file displayed and edit the screen shot into your question here. The text that you've posted is a proper CSV file, and if your Excel is working properly, you should see two columns, headed `name` and `samaccountname`, and the respective values, one user per line, below. – Jeff Zeitlin Aug 29 '19 at 10:56
  • i edited the question with screenshot as required @JeffZeitlin – Chris Bleck Aug 29 '19 at 11:16
  • 2
    Try doing the export without the `-Encoding UTF8`. Excel is not recognizing your file as a proper CSV, and that's the most likely reason. – Jeff Zeitlin Aug 29 '19 at 11:27
  • it didnt work unfortunately i get the very same csv – Chris Bleck Aug 29 '19 at 11:32
  • Does import-csv work on the file? I've seen Excel have problems with unicode format. What format does Notepad say it is if you save as? – js2010 Aug 29 '19 at 11:34
  • Highly relevant: https://stackoverflow.com/questions/6002256/is-it-possible-to-force-excel-recognize-utf-8-csv-files-automatically – Jeff Zeitlin Aug 29 '19 at 11:37
  • 1
    You might use another delimiter. Depending on your locale setting Excel might reserve the default ',' (comma) for something else. – Olaf Aug 29 '19 at 11:40
  • @JeffZeitlin i can do it manually from excel with the comma delimiter. I was hoping if the script can do it itself. – Chris Bleck Aug 29 '19 at 11:46
  • 1
    Your CSV and PowerShell code are fine; it's Excel that has the problem with the CSV. Did you check the "Highly Relevant" link I gave you? You may need to insert the BOM – Jeff Zeitlin Aug 29 '19 at 11:50
  • 2
    Possible duplicate of [How to correctly display .csv files within Excel 2013?](https://stackoverflow.com/questions/17953679/how-to-correctly-display-csv-files-within-excel-2013) – iRon Aug 29 '19 at 11:54
  • i thank everybody for the replies. export to csv is broken either way. I exported it to json instead – Chris Bleck Aug 30 '19 at 08:14

0 Answers0