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

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

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

Select allOpen in new window

---

»bp

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.

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

---

»bp

I would let Powershell do the conversation:

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

Select allOpen in new window

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 

Select allOpen in new window

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?

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.

That is brilliant thank you!

Welcome, glad that was useful.

---

»bp

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?

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