Powershell convert to CSV

[danhoffmann (dhoff)]

I have a txt file that I need to convert to a csv file.

The raw data looks like this:

Date Field1 Field2
11/01 12,432.98 901,459

There are a lot of blank spaces and no separator. I have tried various import-csv|export-csv combinations and just cannot get this right.

The closest I have gotten is this:

Import-Csv .\ASK_ConsumerStats.txt -Delimiter ' ' | Export-csv ask.csv -NoType

[rockn]

Do a replace on the file for " " to “,”

[martin9700]

Are they spaces or tabs? If you try this do you get a nice PowerShell list?

Import-Csv .\ASK_ConsumerStats.txt -Delimiter "`t"

[danhoffmann (dhoff)]

@martin9700

Are they spaces or tabs? If you try this do you get a nice PowerShell list?

Import-Csv .\ASK_ConsumerStats.txt -Delimiter "`t"

Yes I do

[jjoyner1985]

Are the gaps between each piece of data consistent? There’s a really neat regex way to do this, I’m sure, but since I’m still a regex newbie, the way I would normally handle this would be to open the document in notepad and tell it to replace each two space character pair with a single character that you want to use as your delimiter. It looks like your data already has commas in it. So, commas wouldn’t work. Then, tell notepad to replace each pair of delimiting characters with a single delimiting character until each gape between data pieces gets reduced to a single delimiter separation. Like so:

Data1 Data2 (5 space character separation)

Tell notepad to replace each pair of space characters with “&” changes it to:

Data1&& Data2 (two & and a space)

Tell notepad to replace each “&” and a single space character with just “&” changes it to:

Data1&&Data2

Tell notepad to change each “&&” to “&” to change to the final form of:

Data1&Data2

Then, use your special character as you delimiter.

Elegant? Not at all. Simple? Yes. Something to keep doing if you need to do this frequently? Not in the least bit.

[danhoffmann (dhoff)]

Date                                             Consume   Consumer G
---------------------------------------------------------------------
11/01                                           31,541.95   901,459
11/02                                           96,337.36   901,459
11/03                                          161,880.77   901,459
11/05                                          176,760.41   901,459
11/06                                          197,560.41   901,459

but still lots of spaces

[reedsmoller1451 (highwireact)]

Have you tried using a tab delimter? That would be something like this:

Import-Csv .\ASK_ConsumerStats.txt -Delimiter “`t” | Export-csv ask.csv -NoType

Please note that the second character in the “'t” string is not a single quote mark, it is a backtick.

[martin9700]

@reedsmoller1451

Have you tried using a tab delimter? That would be something like this:

Import-Csv .\ASK_ConsumerStats.txt -Delimiter “`t” | Export-csv ask.csv -NoType

Please note that the second character in the “'t” string is not a single quote mark, it is a backtick.

This is where I was going. Since it’s tab delimited we can import it as PowerShell objects, then export them back out.

[danhoffmann (dhoff)]

I got it to work…not via powershell, but it’s simple and it works for me.

FYI - its a crystal report, so I put all my output in a text field and hard coded the quotes and separators

“field1”,“field2”,“field3”

and before you ask, I am not able to export the crystal as csv due to other complications

Thanks for the super fast feedback from everyone!!

[Evan7191]

You can do something like this:

$Text = Get-Content Drive:\Path\File
$Text -ireplace '\s+',';' | Out-File Drive:\Path\File.csv
$CSV = Import-Csv Drive:\Path\File.csv -Delimiter ';'

I used a semicolon ( as a delimiter, because your data contains commas.