Creating a TXT from a CSV and it's removing the commas, which are needed

Question

I have created a fairly simple loop that pulls data from active directory. This works just fine, but the issue i'm running into is the required export. According to the vendor, the output needs to be a txt file without the quotes, as a TXT file, and tab as delimeter since its going to be imported into SQL. Pretty simple to remove quotes from a csv, however i'm finding that users with commas in their titles are breaking everything when using out-file. Here is my rough script.

$Users = Get-Aduser -searchbase 'OU=Users,DC=Company,DC=com'

$Array = [System.Collections.ArrayList]@()

ForEach ($User in $Users){
      $match = New-Object PSObject
           $match | Add-Member NoteProperty "User Name" $User.sAMAccountName
           $match | Add-Member NoteProperty "Password" "Password1"
           $match | Add-Member NoteProperty "First Name" $User.givenName
           $match | Add-Member NoteProperty "Last Name" $User.surname
           $match | Add-Member NoteProperty "E-mail Address" $User.mail
           $match | Add-Member NoteProperty "Business Title" $User.Title
           $match | Add-Member NoteProperty "Department/Function" $User.Department
           $match | Add-Member NoteProperty "Country" $User.c
           $Array += $match
           }

#Export LMS file removing comma as delimiter and making tab the delimiter
$Array | Export-csv -Delimiter "`t" -path c:\temp\Array.csv -Encoding UTF8 -NoTypeInformation

#Import the csv file, convert to CSV removing all "" and export final file at tab delimited txt file
$Array2 = Import-csv C:\temp\Array.csv | ConvertTo-Csv -NoTypeInformation | % { $_ -replace '"', '' } | Select -Skip 1 | Out-File C:\temp\Import.txt

I change the delimeter from comma to tab with "-Delimeter "`t" and export with encoding being UTF8 because of international characters used for our users in other Countries. The csv exported looks just fine. What breaks is when I import the csv back into powershell and attempt to just remove the quotes. When I out-file any user with a title, such as "Director, NA Infrastructure" everything will be removed after the comma. How can I convert this and only remove quotes but keep everything else?

I appreciate the support.

Edit1--

Here is how the output should be:

User Name   Password    First Name  Last Name   E-mail Address Business Title   Department/Function Country
01234567 Password1 John Doe [email protected] Director, NA Infrastructure IT US

When using export-csv or out-fil the , there removes everything after, so its just

01234567 Password1 John Doe [email protected] Director                       

Edit 2 --- I changed things a bit and started to try to just directly to a txt using export-csv. then remove the quotes. This might be working. Checking with Vendor

#Export file removing comma as delimiter and making tab the delimiter
$Array | Export-csv -Delimiter "`t" -path c:\temp\Array.txt -Encoding UTF8 -NoTypeInformation

#Import the csv file, convert to CSV removing all "" and export final file at tab delimited txt file
$LArray2 = (Get-Content C:\Temp\Array.txt -Encoding UTF8) | ForEach-Object {$_ -replace '"',''} | Out-File C:\temp\Array_Final.txt

I tried to think about opening with notepad++, then doing the work and saving, but couldn't see how to do more than just open the file with notepad++ and use automation.

Answer 1

If you run the script in PowerShell 7.0+, you can take advantage of the newly introduced parameter for Export-Csv -UseQuotes Never (More info here), combined with the -Delimiter "`t" parameter. This will give you tab delimited output with no quotes.

$Array | Export-csv -Delimiter "`t" -UseQuotes Never -Path c:\temp\Import.txt -Encoding UTF8 -NoTypeInformation