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?
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?
Using Powershell it could be as simple as this, depending on your input file...
»bp
(Get-Content B:\EE\EE29174457\in.txt).replace('|', ',') | Set-Content B:\EE\EE29174457\out.txt»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.
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
»bp
I would let Powershell do the conversation:
$inFile = 'input.txt'
$outFile = 'Output.csv'
Import-Csv -Path $inFile -Delimiter '|' | Export-Csv -Path $outFile -Delimiter ';' -NoTypeMy 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 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?
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?
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.
It always has a header row, and a footer row.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
That is brilliant thank you!
Welcome, glad that was useful.
»bp
»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?
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
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
In the existing file, are there any double quotes, or commas?
»bp