CODEWITHSUNDEEP
powershell
Tony
Tony

Reputation: 9571

powershell txt file delimited by " " to csv

I have a text file that is essentially two columns with n rows.

the columns are separated by a double space - powershells import-csv cmdlet only allows for a single character delimiter.

Whats my best way around this as my aim is to get my first column into a variable.

Upvotes: 2

Views: 1462

Answers (3)

Keith Miller
Keith Miller

Reputation: 802

If values don't contain spaces or those that do are quoted, why not just Import-Csv -Delimiter ' ' and ignore the second column:

PS C:\> @'
>> Header1  Header2
>> Data11  Data12
>> "Data 2 1"  "Data 2 2"
>> '@ | ConvertFrom-Csv -Delimiter ' '
WARNING: One or more headers were not specified. Default names
starting with "H" have been used in place of any missing headers.

Header1  H1 Header2
-------  -- -------
Data11      Data12
Data 2 1    Data 2 2

To get only thhe first column, you can surrond the expression in parentheses and use dot-notation:

PS C:\>>( @'
>> Header1  Header2
>> Data11  Data12
>> "Data 2 1"  "Data 2 2"
>> '@ | ConvertFrom-Csv -Delimiter ' ' ).Header1
WARNING: One or more headers were not specified. Default names
starting with "H" have been used in place of any missing headers.
Data11
Data 2 1

So, assuming you have a plain-text data file delimited by double-spaces, you would get your first column values into a variable like so:

$MyFile = 'c:\MyFile.txt'
$Column1Values = ( $MyFile | Import-Csv -Delimiter ' ' ).Header1

Upvotes: 0

mklement0
mklement0

Reputation: 437100

AdminOfThings' helpful answer provides good general pointers and generic solutions for parsing all columns.

Re:

to get my first column into a variable

# Create a sample file.
@'
foo  ...
one two  ...
more  ...
'@ > sample.txt

# Extract the first column.
$col1Values = (Get-Content sample.txt) -replace '  .*$'

Outputting $col1Values yields:

foo
one two
more

Note: If the first column's values are double-quoted (and don't contain embedded, escaped double quotes), append -replace '"' (or (...).Trim('"'))

Upvotes: 0

AdminOfThings
AdminOfThings

Reputation: 25001

A simple approach if your current delimiter is only present as a delimiter would be to pick a new delimiting character that is not present anywhere in the file. If we choose ;, you may do the following:

$content = (Get-Content file.csv) -replace '  ',';' | 
    ConvertFrom-Csv -Delimiter ';'
# outputs column1 values
$content.column1

If you cannot guarantee your delimiter won't be within the file, you will need to qualify the current text (usually with double quotes) to prevent the character from being wrongly accused of being a delimiter. This only needs to be done if your data is not qualified already.

$content = (Get-Content file.csv) -replace '(?=")|^|$|(?<=  )|(?=  )','"' -replace '  ',';' |
    ConvertFrom-Csv -Delimiter ';'

If your data is already qualified, then you will only want to replace the double space where it appears as a delimiter. But you will need to be mindful of qualifying characters appearing as data as well.

$content = (Get-Content file.csv) -replace '(?<!^|"  )"(?!$|  ")','""' -replace '(?<=")  (?=")',';' |
    ConvertFrom-Csv -Delimiter ';'

I am sure exceptions can be created for any of the approaches above. You will need to know your data and come up with verification checks to validate your data schema.

Upvotes: 1

Related Questions