9

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.

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.

Therefore, I would like to remove all NULLs from the .CSV file prior to trying to pipe it into the format-table cmdlet.

My basic code is below:

$CSV=import-csv "c:\temp\tablename.csv"
$CSV | format-table -autosize | out-string -width 4096 >"C:\TEMP\tablename.txt"

I've tried doing something like:

$CSV | -replace($null,"") | format-table -autosize | out-string -width 4096 > "C:\TEMP\tablename.txt"

but I'm still getting the NULLs.

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.

CSV file as shown in a hex editor:

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

Notice at EE0h the first character is NULL, 0x00.

Andriy M
  • 76,112
  • 17
  • 94
  • 154
user500741
  • 833
  • 5
  • 14
  • 25
  • The *NULL value* in your title in conjunction with the `sql-server` tag was very misleading, so I modified the title slightly to remove the ambiguity. I'm not sure if it doesn't sound clumsy or awkward now, so please feel free to edit it again as you see fit. – Andriy M Mar 26 '12 at 05:43

6 Answers6

18

After a bit of playing around, I finally figured out that this syntax worked:

(Get-Content "C:\temp\tablename.csv") -replace "`0", "" | Set-Content "C:\temp\tablename.csv"
Nacht
  • 3,342
  • 4
  • 26
  • 41
user500741
  • 833
  • 5
  • 14
  • 25
  • 1
    If I could heart a post, I would. I was using a PowerShell script to modify an XML Android manifest file for versioning purposes, and this "HEX 0x00" error was super frustrating. Thanks! – srbrills Oct 05 '17 at 17:15
11

All of the submitted answers are work-arounds and do not address the core issue, which is that powershell uses the utf-16 encoding by default (this is why you're getting NULL i.e. 0x00 between all characters). The solution is to tell powershell to use utf-8:

$stuff | Out-File $out_path -Encoding UTF8

Also see this thread

Community
  • 1
  • 1
sam-6174
  • 3,104
  • 1
  • 33
  • 34
  • I agree to @user2426679. This answer should be accepted. I was using string.Replace and then saving with Out-File. The issue was with encoding and specifying UTF8 worked like a charm. – Mayur Dhingra Feb 17 '17 at 08:52
  • Thanks ! I had a similar problem with an npm parser, Even though the auto-generated xml was set to at the time of creation, running the above fixed the non-white characters in my hex view. And certainly a better way than replacing individual characters. – Vinay May 16 '18 at 20:18
5
-replace "`0", " "

This worked for me in a text file just fine.

nixda
  • 2,654
  • 12
  • 49
  • 82
psstarkey
  • 51
  • 1
  • 1
4

Update - Now that I see what you mean by NULL (hex 0x00) I can give you another approach.

You can just filter out these byte by reading the file as binary like this:

Get-Content "c:\temp\tablename.csv" -Encoding Byte | ? {$_ -ne 0x00} | Set-Content "c:\temp\tablename2.csv" -Encoding Byte
Andy Arismendi
  • 50,577
  • 16
  • 107
  • 124
  • Hi - thanks for the prompt reply. What you said makes sense; however, I tried both of your methods and the resulting output still contains the 0x00 NULL. Here is a fragment of the output. Notice on line EE0h, the first character is 00. ` 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` – user500741 Mar 26 '12 at 00:37
  • --Sorry, I tried editing the original question and can't seem to get the formatting to stay in blocking mode. – user500741 Mar 26 '12 at 00:42
  • @user500741 Don't forget, it's just text so whatever the literal text you want to remove will be what you will use as the first argument of the `-replace` operator. I changed my examples a bit so you can more easily see this. – Andy Arismendi Mar 26 '12 at 01:47
  • Thanks for the response -- the absolute value when looking at it in unprintable. Trying it was "00" doesn't work either. The hex-dump that I provided shows it as 0x00 and the ASCII side simply shows it as an unprintable character using a ".". – user500741 Mar 26 '12 at 02:37
3

Use '\xnn' to match characters by their hex representation:

(get-content c:\temp\tablename.csv) -replace '\x00','' | set-content c:\temp\tablename.csv
mjolinor
  • 66,130
  • 7
  • 114
  • 135
0

Going off Andy's reply and your response, it looks like the 'null' value you want to get rid of is actually "00" in text.

So you'd want to do this instead:

(Get-Content "C:\temp\tablename.csv") -replace " 00 ", " " | Set-Content "C:\temp\tablename.csv"

This will convert:

00000EE0h: 00 2C 32

into:

00000EE0h: 2C 32

Huon
  • 300
  • 3
  • 10
  • 1
    No, that was a hex dump of the file to show what each byte looks like. NotePad++, for example, shows it a a "NULL". If I look at it in hex view, it shows as a 0x00 byte or "." since it's non-printable. – user500741 Mar 26 '12 at 02:33