Avatar of James Murphy
James Murphy
Flag for Australia asked on

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

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?
Windows BatchScripting Languages

Avatar of undefined
Last Comment
gelonida

8/22/2022 - Mon
Bill Prew

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 Prew

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
johnsone

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.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Bill Prew

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


»bp
Zvonko

I would let Powershell do the conversation:

$inFile = 'input.txt' 
$outFile = 'Output.csv' 
Import-Csv -Path $inFile -Delimiter '|' | Export-Csv -Path $outFile -Delimiter ';' -NoType

Open in new window

Zvonko

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 

Open in new window

⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
James Murphy

ASKER
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

ASKER
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.
ASKER CERTIFIED SOLUTION
Bill Prew

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
James Murphy

ASKER
That is brilliant thank you!
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
Bill Prew

Welcome, glad that was useful.


»bp
tel2

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

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



⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.