Is it possible to treat consecutive delimiters as one using Import-Csv?

Question

I have some log files in txt format that contain double tabbed columns. I'm using the following PowerShell:

Import-Csv C:\path\to\file\PageLog.txt -Delimiter "`t" | Export-Csv C:\path\to\file\PageLog.csv -NoTypeInformation

So nothing crazy there, and it is working, but I end up with an empty column with an automatically generated H1 header. So is it possible for me to treat these two tabs as one like when importing through the "Data" tab in Excel? Or somehow omit the empty H1 column during Export-Csv?

Use `... | Select * -ExcludeProperty H1 |...`, see: https://stackoverflow.com/a/40085445/1701026 — iRon, Aug 10 '17 at 19:35

BenH · Accepted Answer · 2017-08-10T20:28:04.050

Unfortunately, for Import-CSV delimiters can only be one character long. This leaves you with two options. First is to use string parsing on the file to get down to one delimiter.

(Get-Content C:\path\to\file\PageLog.txt) -replace "`t`t","`t" | Out-File C:\path\to\file\PageLog.temp.txt
Import-Csv C:\path\to\file\PageLog.temp.txt -Delimiter "`t" | Export-Csv C:\path\to\file\PageLog.csv -NoTypeInformation

In this method you could skip the import/export and just replace with a comma.

(Get-Content C:\path\to\file\PageLog.txt) -replace "`t`t",',' | Out-File C:\path\to\file\PageLog.temp.txt

Otherwise you could import the csv with extra columns and filter. As iRon suggests, if you only have one extra column you can simply exclude it:

Import-Csv C:\path\to\file\PageLog.txt -Delimiter "`t" |
    Select-Object * -ExcludeProperty H1 |
    Export-Csv C:\path\to\file\PageLog.csv -NoTypeInformation

If you have multiples, Select-Object supports wildcards so you could use -ExcludeProperty H* with the assumption that none of your other columns started with H.

Otherwise you could get use a regex against the column names.

$CSV = Import-Csv C:\path\to\file\PageLog.txt -Delimiter "`t"
$GoodColumns = $CSV |
    Get-Member -MemberType NoteProperty |
    Where-Object {$_.name -notmatch '^H\d+$'} |
    Select-Object -ExpandProperty Name
$CSV | Select-Object $GoodColumns | Export-Csv C:\path\to\file\PageLog.csv -NoTypeInformation

Excluding the H1 column after Import was the quickest and easiest solution. At first I tried the Get-Content cmdlet, but it seems like -replace was not a valid parameter?? — Heath, Aug 10 '17 at 20:19
@Heath I forgot to wrap the Get-Content command, `-replace` isn't a parameter but the regex operator. Answer updated. — BenH, Aug 10 '17 at 20:29

score 2 · Answer 2 · answered Aug 10 '17 at 19:37

2

You should be able to run a search/replace function on the .csv (replace two consecutive tabs with one, no?)

I don't know if Import-Csv will allow -replace, but if not, you can convert the .csv file to a string (see Powershell Converting .csv format (Import-csv) to string), run -replace on it, then convert it back into a .csv.

answered Aug 10 '17 at 19:37

Chains

12,541
8
45
62

You're right and the search/replace was my first thought, but I have a bunch to do now and going forward so I didn't want so much manual modification of the log file. Thanks for the link btw it's very good. – Heath Aug 10 '17 at 20:23

Is it possible to treat consecutive delimiters as one using Import-Csv?

2 Answers2