We help IT Professionals succeed at work.

How to change pipe delimitered text file to a CSV file.

James Murphy
James Murphy asked
on
Hi,

Firstly thank you for taking a look at my problem.

What I need to do, is to somehow create a script, that will look at contents of a text file and change it from a layout of a pipe delimitered file to a csv file.

ie

1|2|3|4 would change to
1,2,3,4.


Would anyone have any ideas on how to accomplish this?
Comment
Watch Question

View Solution Only

Bill PrewTest your restores, not your backups...
SILVER EXPERT
Expert of the Year 2019
Top Expert 2016

Commented:
Do you need this for repetitious usage?  Meaning, the simple approach of opening in a text editor and doing a search and replace doesn't work for you?

In the existing file, are there any double quotes, or commas?

»bp
Bill PrewTest your restores, not your backups...
SILVER EXPERT
Expert of the Year 2019
Top Expert 2016

Commented:
Using Powershell it could be as simple as this, depending on your input file...

(Get-Content B:\EE\EE29174457\in.txt).replace('|', ',') | Set-Content B:\EE\EE29174457\out.txt

Open in new window

»bp
johnsoneSenior Oracle DBA
BRONZE EXPERT

Commented:
I think an important question is why?  Most anything that can read a CSV can use any other delimiter.

Typically, pipe delimited files are used because commas can appear in the data.  Commas are much more common than pipes.  By doing a simple replace, if the data contains any commas, you could be breaking your file.
Bill PrewTest your restores, not your backups...
SILVER EXPERT
Expert of the Year 2019
Top Expert 2016

Commented:
Yup, that's why I asked the questions I did...

»bp
ZvonkoSystems architect
BRONZE EXPERT
Top Expert 2006

Commented:

I would let Powershell do the conversation:

$inFile = 'input.txt' 
$outFile = 'Output.csv' 
Import-Csv -Path $inFile -Delimiter '|' | Export-Csv -Path $outFile -Delimiter ';' -NoType
ZvonkoSystems architect
BRONZE EXPERT
Top Expert 2006

Commented:

My example does not work for one-liner TXT.

You need header either in the TXT or in the script.


$inFile = 'input.txt' 
$outFile = 'Output.csv' 
$headers = "One", "Two", "Three", "Four" 
Import-Csv -Path $inFile -Delimiter '|' -Header $headers | Export-Csv -Path $outFile -Delimiter ';' -NoType
James Murphy

Author

Commented:
Hi,

Sorry for delayed response.

basically yes it is for an entire file.

say the file contents look like this:

1|20200302|5
2|234|45.70|Test Name|0000
2|234|45.70|Test Name|0000
2|234|45.70|Test Name|0000
2|234|45.70|Test Name|0000
9|4|182.80

I would like it to look like this instead:
1,20200302,5
2,234,45.70,Test Name,0000
2,234,45.70,Test Name,0000
2,234,45.70,Test Name,0000
2,234,45.70,Test Name,0000
9,4,182.80

The reason for this, the file is to be used in a Bulk Insert with MS SQL.

I can't get it to work when it is pipe delimetered format, but if I save it in excel as a CSV instead, and alter my bulk insert script it works perfectly.

I am trying to get it done by script to have it as one less step that the user needs to do.

One question, I haven't had anything to do with powershell as of yet, how do I go about running one of the above scripts? just paste it into a text file with a PS1 extension? that I then click on as though it was a bat file?
James Murphy

Author

Commented:
The real file is actually 79 columns long, but figured the above example would be sufficient.

It always has a header row, and a footer row.
Test your restores, not your backups...
SILVER EXPERT
Expert of the Year 2019
Top Expert 2016
Commented:
Since this powershell "script" is so small, you could even execute it right from a DOS command line as follows.  You only need the single quotes around the file paths if they have spaces in them, if not they aren't needed.

powershell.exe -ExecutionPolicy Bypass -Command "(Get-Content 'c:\temp\in.txt').replace('|', ',') | Set-Content 'c:\temp\out.txt'"

Open in new window

»bp
James Murphy

Author

Commented:
That is brilliant thank you!
Bill PrewTest your restores, not your backups...
SILVER EXPERT
Expert of the Year 2019
Top Expert 2016

Commented:
Welcome, glad that was useful.

»bp
tel2
BRONZE EXPERT

Commented:
As has been raised by Bill and johnsone before...
Is it possible that there could be any commas in the columns of data in the original file, James?
gelonida
BRONZE EXPERT

Commented:
CSV is a rather simple file format. However I would not underestimate the complexity of CSV.
There are dedicated libraries for reading / writing CSV which will take care of all boundary cases.
Whatever you implement by yourself will probably fail if you do not have very tight control over the contents of the file (like you know the contents will never contain quoting or delimiter characters or line breaks)

Python for example has a module name csv which will take care of this. Powershell might have something similar.
Hand crafted solutions will not work under all circumstances.

If interested in a python solution (If this is usable in your context) I could provide a small example