CODEWITHSUNDEEP
powershellcsv
user16603959
user16603959

Reputation: 47

Using Out-File to write variables to an CSV file in proper format?

I am trying to write variables to a CSV file and I would like to write the variables for each loop in one row. My code below retrieves the information about a User from a Word document and then places that information into variables. For each user I would like to Out-file the user properties to a different row.

# Import active directory module for running AD cmdlets
Import-Module activedirectory


#Assign the document folder
$files = Get-ChildItem "C:\Users\username\Desktop\NewUserForms\"

forEach ($doc in $files) {

#Create word application object
$word = New-Object -ComObject Word.Application

#Assign document path
$doc = $doc.ToString()
$documentPath = "C:\Users\username\Desktop\NewUserForms\$doc" #Read-host => [Enter Template to use Ex:"C:\Users\username\Desktop\employeeform.docx"]

#open the document
$document = $word.Documents.Open($documentPath)

#list all tables in doc
$document.Tables | ft

#get info from a certain part of the table in the word doc
$pager = $document.Tables[1].Cell(4,2).range.text
$fname = $document.Tables[1].Cell(6,2).range.text
$lname = $document.Tables[1].Cell(8,2).range.text
$jobtitle = $document.Tables[1].Cell(15,2).range.text
$department = $document.Tables[1].Cell(16,2).range.text
$manager = $document.Tables[1].Cell(17,2).range.text
$pagernumber = $pager.Substring(17)


Write-Output $fname
Write-Output $lname

$information = $fname, $lname, $jobtitle, $department, $manager, $pagernumber
#Write-Output $information
$information | Out-File -Append -NoNewline C:\Users\username\Desktop\newusers.csv

#Close the document
$document.close()

#Close Word
$word.Quit()


                        }
pause

Currently Out-File will fill in the CSV file like this:

Test

Taco

Test Character

Testing Depot

TheBigMan

9999

I am having trouble figuring out how to place the User information all in one row.

Upvotes: 2

Views: 4684

Answers (1)

TheMadTechnician
TheMadTechnician

Reputation: 36342

The issue is that you're working with a CSV, but treating it like a text file. A CSV needs to be formatted a specific way, and if you're going to inject text into the CSV as if it were a text file (as you are doing) you will need to format your text accordingly. I'll show you what you're doing wrong and how to fix it, but please read past that to see a better way to accomplish your task.

What you're doing right now is creating an array of strings:

$information = $fname, $lname, $jobtitle, $department, $manager, $pagernumber

The simplest fix is to make that a single string with commas separating the values.

$information = $fname, $lname, $jobtitle, $department, $manager, $pagernumber -join ','

or

$information = "$fname,$lname,$jobtitle,$department,$manager,$pagernumber"

But, you're working with a CSV, the right way to do this would be to use Export-Csv. To do that you need to pass an object, not just a string or set of strings, to it, so let's convert what you're doing to objects:

#get info from a certain part of the table in the word doc
[PSCustomObject]@{
    pager = $document.Tables[1].Cell(4,2).range.text
    fname = $document.Tables[1].Cell(6,2).range.text
    lname = $document.Tables[1].Cell(8,2).range.text
    jobtitle = $document.Tables[1].Cell(15,2).range.text
    department = $document.Tables[1].Cell(16,2).range.text
    manager = $document.Tables[1].Cell(17,2).range.text
    pagernumber = $document.Tables[1].Cell(4,2).range.text.Substring(17)
}

Then you capture all that in a variable:

$Users = forEach ($doc in $files) {

And lastly you pipe it to your CSV

$Users | Export-Csv -NoTypeInfo -Append -Path C:\Users\username\Desktop\newusers.csv

Upvotes: 2

Related Questions