How to add newline in existing CSV

Question

_{Wednesday, July 26, 2017 1:54 PM}

Hello

I have one csv where I want to add a row using PowerShell. In this CSV file, rows will be added parallel on same time by different user.

My CSV format is

'User ID','Region','Script','Run Date','Started Time','End Time','Status','Content Size'

I try to add row in CSV using following code is added in the CSV

$one="userid"
$two="APAC"
$three="CR"
$four="07/25/2017"
$five="12:24:20"
$six="12:23:19"
$seven="Failed"
$eight="None, Zero"
$result=$one+","+$two+","+$three+","+$four+","+$five+","+$six+","+$seven+","+$eight
Add-Content $csvfile -value $result -Encoding UTF8 -Force

 am getting data as

'User ID','Region','Script','Run Date','Started Time','End Time','Status','Content Size'userid1,APAC,CR,07/25/2017,12:24:29,12:30:02,Failed,None, Zero
userid,APAC,CR,07/25/2017,12:24:20,,Failed,None, Zero

I don't want to export-csv, as this is common csv and row will be added by multiple user same time using some script. If you see the last column it again splitting in two columns. PLease advise

All replies (12)

_{Wednesday, July 26, 2017 2:51 PM ✅Answered}

This should do it for you.

"`"$($env:USERNAME)`""

_{Wednesday, July 26, 2017 1:59 PM}

I want row should be add as

"userid","APAC","CR","07/25/2017","12:24:20","12:23:19","Failed","None, Zero"

_{Wednesday, July 26, 2017 2:13 PM}

Looks like you're on the right path. The problem you're having is that the quotations when outputting to something (console or csv) are removed, as they are viewed as denoting a string value.

What you want to do is wrap those double quotes with single quotes, like so:

$one='"userid"'
$two='"APAC"'
$three='"CR"'
$four='"07/25/2017"'
$five='"12:24:20"'
$six='"12:23:19"'
$seven='"Failed"'
$eight='"None, Zero"'
$result=$one+","+$two+","+$three+","+$four+","+$five+","+$six+","+$seven+","+$eight
Add-Content $csvfile -value $result -Encoding UTF8 -Force

It will keep the quotations in the CSV, which bypasses the comma separation for any commas enclosed by the quotations.

Output will look like this within the CSV:

"userid","APAC","CR","07/25/2017","12:24:20","12:23:19","Failed","None, Zero"

_{Wednesday, July 26, 2017 2:46 PM}

Thanks for inputs. I tried following, but it is not fetching value

$one='"$env:USERNAME"'

But it is returning as it is. Then I tried

$one=$env:USERNAME

It is returning username but without double quote.

What is the best way to handle this?

Avian

_{Wednesday, July 26, 2017 2:59 PM}

This seems to working. However one thing I noticed while insertion of row. Suppose there are 3 rows are added, If I run the script twiice, then total row should 5, but I noticed there are total 4 rows. It looks like last inserted row will be replacing with newrow and one more thing when I execute first time, it is starting from first row after header.

Am I missing anything?

_{Wednesday, July 26, 2017 3:03 PM}

I'm not seeing that when I run it. It posts a 1 for 1 line for run. Can you post the full script that you're running? 

_{Wednesday, July 26, 2017 3:05 PM}

Here is the code. CSV file only has one header row

$csvfile="C:\LogTemplate.csv"
$one="`"$($env:USERNAME)`""
$two='"APAC"'
$three='"CR"'
$four='"07/25/2017"'
$five='"12:24:20"'
$six='"12:23:19"'
$seven='"Failed"'
$ak="None, Zero"
$eight="`"$($ak)`""
$result="$one"+","+$two+","+$three+","+$four+","+$five+","+$six+","+$seven+","+$eight
Add-Content $csvfile  -value $result -Encoding Ascii -Force 

_{Wednesday, July 26, 2017 3:06 PM}

If multiple users are trying to write to the same file you will have issues..

The correct way to append to a Csv is this:

[pscustomobject]@{
    Col1  = $env:USERNAME
    Col2  = 'APAC'
    Col3  = 'CR'
    Col4  = '07/25/2017'
    Col5  = '12:24:20'
    Col6  = '12:23:19'
    Col7  = 'Failed'
    Col8  = 'None, Zero'
} | Export-Csv $csvfile -Append

Change 'COl1...Col8' to the names of your columns.  The export will provide the correct formatting.  All strings can be supplied as variables with no quoting.

No matter which method you use you will get intermittent errors if multiple users attempt to update a file at the same time.

\(ツ)_/

_{Wednesday, July 26, 2017 3:10 PM}

I don't know. I literally copied your script and ran it 16 times. I have 17 lines including the header.

_{Wednesday, July 26, 2017 3:13 PM}

Hi jrv

Thanks for sample code.

If multiple users are adding row, so what is the best way to add row in existing csv, please advise.

Avian

_{Wednesday, July 26, 2017 3:13 PM}

You are right, seems I have some confusion.

Thanks for your time.

_{Wednesday, July 26, 2017 3:37 PM}

Hi jrv

Thanks for sample code.

If multiple users are adding row, so what is the best way to add row in existing csv, please advise.

Avian

You would need to use a database file like MSAccess which supports locking or use file primitives that can open a file for shared write.  You can also use ACE drivers to update CSV files in shared mode.

\(ツ)_/