IT Professional

Albert Widjaja

<div>
To prevent issues with the delimiter you could use a ASCII control character between the fields in the $line<br />
Hexadecimal 1F (or decimal 31) could be used for this as this is the ASCII Unit Separator.<br />
<br />

<pre><code class="code-1 nolinks" id="code-20-43065965-1"><span>$datatable = New-Object System.Data.DataTable</span>
<span>[void]$datatable.Columns.Add(&quot;Column1&quot;)</span>
<span>[void]$datatable.Columns.Add(&quot;Column2&quot;)</span>
<span>[void]$datatable.Columns.Add(&quot;Column3&quot;)</span>
<span>$line = &quot;test,;|1&quot; + $([char]0x1F) + &quot;test,;|2&quot; + $([char]0x1F) + &quot;test,;|3&quot;</span>
<span>$Delimiter = $([char]0x1F)</span>
<span>$null = $datatable.Rows.Add($line.Split($Delimiter))</span>
</code></pre>
</div>


<div>
<blockquote>
If a value within double quotes contains for example a comma, the data is split out incorrectly. I'm just wondering the best way to handle delimiters in values please?
</blockquote>
<br />
it depends on how the source data looks like and how it was being read to $line<br />
<br />
the source data must have a <b>proper format</b> before you can differentiate whether it's a data or delimiter, or else if we are saying the line data is in a <b>fixed length content</b>, etc, else it will be difficult for the task.
</div>


<div>
oBdA - Thank you so much for the solution, it worked brilliantly. :)<br />
<br />
zalazar -Thank you for your solution to use ASCII 31 as the field separator<br />
<br />
Ryan Chong - Thanks for your input to consider the format of the file, it's a really useful to think about this too.
</div>


<div>
You're welcome and thanks too.
</div>


<div>
<div class="content wysiwyg-content">
Hi,<br />
<br />
Currently I'm using the following to insert a row of data into a datatable:<br />
<br />
$Delimiter = ','<br />
$null = $datatable.Rows.Add($line.<wbr />Split($Del<wbr />imiter))<br />
<br />
The delimiter can change from a comma, semi-colon, pipe etc<br />
<br />
If a value within double quotes contains for example a comma, the data is split out incorrectly. I'm just wondering the best way to handle delimiters in values please?<br />
<br />
Thanks,<br />
Helen
</div>
</div>


Hi,

Currently I'm using the following to insert a row of data into a datatable:

$Delimiter = ','
$null = $datatable.Rows.Add($line.Split($Delimiter))

The delimiter can change from a comma, semi-colon, pipe etc

If a value within double quotes contains for example a comma, the data is split out incorrectly. I'm just wondering the best way to handle delimiters in values please?

Thanks,
Helen

PowerShell - csv with delimiters inside double quotes

Windows PowerShell is a task automation and configuration management framework from Microsoft, consisting of a command-line shell and associated scripting language built on the .NET Framework. PowerShell provides full access to the Component Object Model (COM) and Windows Management Instrumentation (WMI), enabling administrators to perform administrative tasks on both local and remote Windows systems as well as WS-Management and Common Information Model (CIM) enabling management of remote Linux systems and network devices.