I am trying to create a batch process that will parse multiple multi-line text files (samples attached) and output their contents into a single pipe-delimited CSV file. I can't use commas as delimiters in the output because the baseline data may contain commas. Unfortunately, I am a novice when it comes to programming so any assistance would be greatly appreciated.
Here is some sample content from a couple of the attached files:
=====
1-12345-12345-98765-98765
COMPANY ONE
Options: Debugger, SQL Optimizer, PL/Sql Kb
=====
9-99999-88888-77777-66666
COMPANY ONE
=====
8-88888-77777-66666-55555
123456-7890
=====
0-11111-77777-33333-22222
COMPANY THREE
Options: none
=====
The first line is a serial number or product key. The second is the internal organization name which purchased the license. The third line may or may not exist, but is focused on a brief product description. The fourth line is either always blank or nonexistent. (The "=====" was used to designate different files and does not exist within the real data.)
Q: How can I take the content from each file and concatenate the data into a single line (per file) which is pipe-delimited, then append each additional file on subsequent lines?
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.
Paste the script below into a text file with a .vbs extension. Customize the value of the strFolder variable on line 5 with the location of the folder containing the files. Customize the value of the strOutput variable on line 7 with the location of the file to be created. Running the script will merge all text files in the folder into a pipe-delimited text file.
Well. We can read each file using a for command. This is my first run past on the issue. Change the source and dest lines:
@Echo off
setlocal enabledelayedexpansion
set source="c:\test\gp423\samp
set dest="C:\test\gp423\piped.
set line1=
set line2=
set line3=
for /f %%a in ('dir /a-d /b %source%') do (
set linenum=1
set line1=
set line2=
set line3=
for /f "delims=" %%g in (%%a) do (
set line!linenum!=%%g
set /a linenum=linenum+1
)
echo !line1!^|!line2!^|!line3!>%
)
and I hadn't refrshed to see Shift-3's suggestion there.
The last but one line lost an extra there too so at the moment it doesn;t append each line:
It needs to be
>>%dest% instead, i.e.
@Echo off
setlocal enabledelayedexpansion
set source="c:\test\gp423\samp
set dest="C:\test\gp423\piped.
del %dest% 2>NUL
for /f %%a in ('dir /a-d /b %source%') do (
set linenum=1
set line1=
set line2=
set line3=
for /f "delims=" %%g in (%%a) do (
set line!linenum!=%%g
set /a linenum=linenum+1
)
echo !line1!^|!line2!^|!line3!>>%
)
Shift-3, your solution works perfectly. I forgot one detail...
If I need to incorporate the filename (without the .key or .txt extension) as the first value passed into the output file, how do I do that?
Here is an example of how I would like each line to appear:
<filename>|<serialnbr>|<company
sample1|1-12345-12345-9876
sample2|9-99999-88888-7777
sample3|8-88888-77777-6666
sample4|0-11111-77777-3333
@Echo off
setlocal enabledelayedexpansion
set source="c:\test\gp423\samp
set dest="C:\test\gp423\piped.
del %dest% 2>NUL
for /f %%a in ('dir /a-d /b %source%') do (
set linenum=1
set line1=
set line2=
set line3=
for /f "delims=" %%g in (%%a) do (
set line!linenum!=%%g
set /a linenum=linenum+1
)
echo %%a^|!line1!^|!line2!^|!li
)
Try this, maybe due to space chars in your path? Try it as follows, removing the @echo off will show you more detail of what is going on too if you wish. I have added a few echos to show as it does it whcih can be removed after too if you want:
@Echo off
setlocal enabledelayedexpansion
set source="c:\test\gp 423\sample*.txt"
set dest="C:\test\gp 423\piped.txt"
del %dest% 2>NUL
for /f "delims=" %%A in ('dir /a-d /s /b %source%') do (
set linenum=1
set line1=
set line2=
set line3=
set shortname=%%~nxA
set filename=%%A
echo +++ !filename!
for /f "delims=" %%g in ('type "!filename!"') do (
set line!linenum!=%%g
set /a linenum=linenum+1
)
echo !shortname!^|!line1!^|!lin
echo --- !shortname!^|!line1!^|!lin
)
Steve
Here is a final version of the .vbs file that ended up working properly. I had to add a strFilename variable and include it in the final strResult variable assignment. All works properly now. Thanks to Shift-3 and dragon-it... Points awarded to both. :)
===== START CODE =====
Const ForReading = 1
Const ForWriting = 2
Const TriStateUseDefault = -2
strFolder = "C:\temp\convertfiles"
strDelimiter = "|"
strOutput = "C:\temp\output.txt"
On Error Resume Next
Set objFSO = CreateObject("Scripting.Fi
Set objFolder = objFSO.GetFolder(strFolder
For Each objFile in objFolder.Files
Set objStream = objFile.OpenAsTextStream(F
strFilename = objFile.Name
strText = objStream.ReadAll
objStream.Close
strResult = strResult & Replace(strFilename, vbCrLf, strDelimiter) & strDelimiter & Replace(strText, vbCrLf, strDelimiter) & vbCrLf
Next
Set objOutput = objFSO.OpenTextFile(strOut
objOutput.Write strResult
objOutput.Close
===== END CODE =====
Thanks for the points, sorry had not answered - had gone out to the gym for a bit.
If it should come in useful, to answer your questions:
set shortname=%%~nxA
sets the filename to (n)ame and e(x)tension so just:
set shortname=%%~nA
instead makes it name only.
but the blank lines get difficult as the for command ignores blank lines :-(
Steve
Sorry to come in late on this one (knowing the question is already closed) however, i couldn't resist the challenge... Here's a batch file version....
@echo off
setlocal enabledelayedexpansion
(for %%a in (*.txt) do (
set c=0
for /f "tokens=1* delims=]" %%b in ('type %%a ^| find /n /v "" ^| find "]"') do (
set /a c+=1
set l!c!=%%c
)
echo %%~na^|!l1!^|!l2!^|!l3!
))>results.txt
Sorry to come in late on this one (knowing the question is already closed) however, i couldn't resist the challenge... Here's a batch file version....
NOTE: Output is sent to the file RESULTS.TXT
@echo off
setlocal enabledelayedexpansion
(for %%a in (*.txt) do (
set c=0
for /f "tokens=1* delims=]" %%b in ('type %%a ^| find /n /v "" ^| find "]"') do (
set /a c+=1
set l!c!=%%c
)
echo %%~na^|!l1!^|!l2!^|!l3!
))>results.txt
Business Accounts
Answer for Membership
by: BrightApolloPosted on 2009-09-02 at 10:17:36ID: 25243231
So you want to replace carriage returns with pipes, output that line to a file, for each file passed in?