I have a utility to load data into an application. It requires that the data be comma delimited. One vendor supplying the data says they can only provide it in a pipe delimited file. I need a simple script I can add to a batch file to change:
John|Doe|123 Main Street, Suite 200|New York|NY
into
"John","Doe","123 Main Street, Suite 200","New York","NY"
I need the data surrounded in quotes because of the poitential for imbedded commas in the addresses.
I'm not particular about the method used, but I am not a programmer. I need something I can plug into a script so it will say:
convert filea.txt filea.cvs
utility filea.csv
Thanks.
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.
Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
The above text should work, with one exception -- many people use bar-delimited text because the vertical bar rarely appears in "real" data. However, it IS possible for the quotes to appear in the data.
You can add this as line 15.5 in the above script to address this:
strLine = Replace(strLine, Chr(34), Chr(34) & Chr(34))
This should double all existing sets of double-quotes before you add the extra ones...
Here's a batch file version.
Copy and paste the code into Notepad and save as CONVERT.BAT. Fire up a DOS box and enter the following command:
CONVERT filea.txt filea.csv
Please report back if there are problems.
@echo off
setlocal enabledelayedexpansion
for /f "tokens=*" %%a in ('type "%~1"') do (
set line=%%a
echo "!line:|=","!">>"%~2"
)
Business Accounts
Answer for Membership
by: Shift-3Posted on 2009-08-25 at 14:27:54ID: 25182453
Paste the script below into a text file with a .vbs extension. Running it with the input file as the first argument and the output file as the second argument (e.g.script.vbs "file a.txt" "file a.csv") will create a comma-delimited text file.
Select allOpen in new window