Databases
StackOverflow2013
Postsdbo
POHow to remove NULL char (0x00) from object within PowerShell
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHow to remove NULL char (0x00) from object within PowerShell
    text
    copied!<p>I have a .CSV file that I created using SQL Server's BCP command-line BULK-COPY utility to dump a bunch of database tables.</p> <p>Since I want to import these .CSV file's using Powershell and convert them to a nice report using the format-table cmdlet, I'm having issues with columns lining up, etc,. because some columns contain NULLs from SQL Server. I don't have the option to convert the NULL from SQL Server first; due to the way I'm exporting the table to CSV.</p> <p>Therefore, I would like to remove all NULLs from the .CSV file prior to trying to pipe it into the format-table cmdlet.</p> <p>My basic code is below:</p> <pre><code>$CSV=import-csv "c:\temp\tablename.csv" $CSV | format-table -autosize | out-string -width 4096 &gt;"C:\TEMP\tablename.txt" </code></pre> <p>I've tried doing something like:</p> <pre><code>$CSV | -replace($null,"") | format-table -autosize | out-string -width 4096 &gt; "C:\TEMP\tablename.txt" </code></pre> <p>but I'm still getting the NULLs. </p> <p>Does anyone know how to remove the NULLs from my CSV so I can display a nice tabular report. I want to get these .TXT reports imported into SVN but the NULLs are going to cause me problems, plus it skews the reports.</p> <p>CSV file as shown in a hex editor:</p> <pre><code>00000EA0h: 31 38 39 2C 31 31 39 2C 37 35 29 2C 77 68 69 74 189,119,75),whit 00000EB0h: 65 2C 77 68 69 74 65 2C 2C 2C 2C 2C 2C 2C 2C 2C e,white,,,,,,,,, 00000EC0h: 2C 2C 2C 2C 2C 2C 2C 2C 2C 2C 2C 2C 2C 2C 2C 2C ,,,,,,,,,,,,,,,, 00000ED0h: 2C 2C 0D 0A 61 63 62 34 33 5F 30 31 2C 4F 4E 2C ,,..acb43_01,ON, 00000EE0h: 00 2C 32 37 2C 39 39 2C 2F 61 63 62 34 33 5F 30 .,27,99,/acb43_0 00000EF0h: 31 2F 34 33 62 61 6C 61 6E 63 65 73 2E 67 69 66 1/43balances.gif </code></pre> <p>Notice at EE0h the first character is NULL, 0x00.</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload