Solved

When combining several text files into one need to know how to only have the column headers in the combined file once

Posted on 2011-03-08
22
446 Views
Last Modified: 2012-05-11
I used the code from another posting to combine several text files into one, but need to know how to only have the column headers only combine once at the very top of the text file.

I have attached the coded that I used to create two separate script files in the "Send To" folder.

Thanks,
gdunn59
Create a text file and name it APPEND DIRS.VBS (or any file with a .vbs extension).  Inside, paste the following:

Const ForReading = 1
Output = InputBox("Enter Path, Filename and Extension of Appended Output."&chr(13)&"Press cancel or leave blank to search for directory") 
if output="" then 
 Set sa = CreateObject("Shell.Application")
 Set shFolder = sa.BrowseForFolder(0, "Select Folder to Save Output File", 0, "")
 if shFolder is nothing then wscript.quit
 Set shFolderItem = shFolder.Items.Item
 folderName = shFolderItem.Path
 Output = InputBox("Enter Filename and Extension of Appended Output") 
 if output="" then wscript.quit
 output = foldername & "\" & output
end if
Set c = CreateObject("Scripting.FileSystemObject")
Set d = c.CreateTextFile(output)
For Each strArgument in Wscript.Arguments
 set fld=c.getfolder(strArgument)
 for each fl in fld.files
  Set b = c.OpenTextFile(fl, ForReading)
  readerline=b.readall
  d.WriteLine readerline
 next
Next


Next create another text file and call it APPENDER.VBS (or any .vbs file), paste the following in it:

Const ForReading = 1
Output = InputBox("Enter Path, Filename and Extension of Appended Output."&chr(13)&"Press cancel or leave blank to search for directory") 
if output="" then 
 Set sa = CreateObject("Shell.Application")
 Set shFolder = sa.BrowseForFolder(0, "Select Folder to Save Output File", 0, "")
 if shFolder is nothing then wscript.quit
 Set shFolderItem = shFolder.Items.Item
 folderName = shFolderItem.Path
 Output = InputBox("Enter Filename and Extension of Appended Output") 
 if output="" then wscript.quit
 output = foldername & "\" & output
end if
Set c = CreateObject("Scripting.FileSystemObject")
Set d = c.CreateTextFile(output)
For Each strArgument in Wscript.Arguments
 if right(strArgument,4)=".xls" then 
  msgbox "Will Not Work With Excel Files.  Please Convert to Text First"
  wscript.quit
 end if
 Set b = c.OpenTextFile(strargument, ForReading)
 readerline=b.readall
 d.WriteLine readerline
Next

Open in new window

0
Comment
Question by:gdunn59
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 5
  • 2
  • +2
22 Comments
 
LVL 11

Expert Comment

by:donaldmaloney
ID: 35073674
For the first file I would read in all the data.
For the second and future files after line 42 set an indicator.
After line 49 test the indicator e'g = true and skip the write line, set the indicator false and continue
0
 
LVL 11

Expert Comment

by:donaldmaloney
ID: 35073690
Of course this will work only if tghe first line of every other file is a header line.
ELse test for Header information then skip the write on that line
0
 
LVL 1

Author Comment

by:gdunn59
ID: 35074553
Not sure how to include what your saying into the code.

Could you please provide this information?

Thanks,
gdunn59
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 11

Accepted Solution

by:
donaldmaloney earned 250 total points
ID: 35076980
See changes to First routine below:
OR is there some other test to know that this is a header line and not just data with NO header?



Private Sub Command0_Click()
On Error GoTo Err_Command0_Click
'Create a text file and name it APPEND DIRS.VBS (or any file with a .vbs extension).  Inside, paste the following:
Dim TestForHeader As Boolean   ' Sets up a test field as true/false
TestForHeader = True    ' Sets it as true
Const ForReading = 1
output = InputBox("Enter Path, Filename and Extension of Appended Output." & Chr(13) & "Press cancel or leave blank to search for directory")
If output = "" Then
 Set sa = CreateObject("Shell.Application")
 Set shFolder = sa.BrowseForFolder(0, "Select Folder to Save Output File", 0, "")
 If shFolder Is Nothing Then wscript.Quit
 Set shFolderItem = shFolder.Items.Item
 foldername = shFolderItem.Path
 output = InputBox("Enter Filename and Extension of Appended Output")
 If output = "" Then wscript.Quit
 output = foldername & "\" & output
End If
Set c = CreateObject("Scripting.FileSystemObject")
Set d = c.CreateTextFile(output)
For Each strargument In wscript.Arguments
 Set fld = c.getfolder(strargument)
 For Each fl In fld.files
  Set b = c.OpenTextFile(fl, ForReading)
  readerline = b.readall
  If Not TestForHeader Then     'Tests TEst Fiels so if TRUE it will skip the first line read
  d.WriteLine readerline
  Else
  TestForHeader = False  " So if it was true it is set to fals so all following inputs will be written
  End If
 Next
Next
0
 
LVL 1

Author Comment

by:gdunn59
ID: 35125753
donaldmaloney,

I tried the solution you posted, and I get the following error when executing:

     Line: 1
     Char: 19
     Expected end of statement


Thanks,
gdunn59
0
 
LVL 11

Assisted Solution

by:donaldmaloney
donaldmaloney earned 250 total points
ID: 35129511
I used your code and created a form/function however the lines
Private Sub Command0_Click()
On Error GoTo Err_Command0_Click
 were mine and should not be included.
So to put the right code in he right place

In your routine add these lines at the start:

Dim TestForHeader As Boolean   ' Sets up a test field as true/false
TestForHeader = True    ' Sets it as true

Then replace your line  50 with these 5 lines.

  If Not TestForHeader Then     'Tests TEst Fiels so if TRUE it will skip the first line read
  d.WriteLine readerline
  Else
  TestForHeader = False  " So if it was true it is set to fals so all following inputs will be written
  End If


This is for the function that appends text to your already created file.
SO each time you call the function it will start with a new input file  and this code will skip the first record.
If the first record is the header it will work.  If you have two header lines then other code would be needed.
If there is a explicit field in the header and it only refers to headers to be stripped then the test could be put in to skip all  records matching that field.
Or if there were more than 1 header record to skip you could start with an inbox function and skip that many records.
Don
0
 
LVL 1

Author Comment

by:gdunn59
ID: 35130280
I did not use the lines of code that had your subname, I just pasted starting with Dim TestForHeader As Boolean   ' Sets up a test field as true/false
0
 
LVL 11

Expert Comment

by:donaldmaloney
ID: 35132372
Hi did you just add/replace as in my last post?
0
 
LVL 1

Author Comment

by:gdunn59
ID: 35133259
Yes.  Here is what I have (see code).


Dim TestForHeader As Boolean   ' Sets up a test field as true/false
TestForHeader = True    ' Sets it as true
'Const ForReading = 1
output = InputBox("Enter Path, Filename and Extension of Appended Output." & Chr(13) & "Press cancel or leave blank to search for directory")
If output = "" Then
 Set sa = CreateObject("Shell.Application")
 Set shFolder = sa.BrowseForFolder(0, "Select Folder to Save Output File", 0, "")
 If shFolder Is Nothing Then wscript.Quit
 Set shFolderItem = shFolder.Items.Item
 foldername = shFolderItem.Path
 output = InputBox("Enter Filename and Extension of Appended Output")
 end if
 If output = "" Then wscript.Quit
 output = foldername & "\" & output
 end if
End If
Set c = CreateObject("Scripting.FileSystemObject")
Set d = c.CreateTextFile(output)
For Each strargument In wscript.Arguments
 Set fld = c.getfolder(strargument)
 For Each fl In fld.files
  Set b = c.OpenTextFile(fl, ForReading)
  readerline = b.readall
  If Not TestForHeader Then     'Tests TEst Fiels so if TRUE it will skip the first line read 
  d.WriteLine readerline
  Else
  TestForHeader = False  " So if it was true it is set to fals so all following inputs will be written
  End If
 Next
Next

Open in new window

0
 
LVL 65

Expert Comment

by:RobSampson
ID: 35178129
Try changing this section:
Set d = c.CreateTextFile(output)
For Each strArgument in Wscript.Arguments
	set fld=c.getfolder(strArgument)
	for each fl in fld.files
		Set b = c.OpenTextFile(fl, ForReading)
		readerline=b.readall
		d.WriteLine readerline
	Next
Next

Open in new window


to this, which determine whether there's already been some text written to the file, and then if so, skips the first line of each subsequent file.
Set d = c.OpenTextFile(output, 8, true)
For Each strArgument in Wscript.Arguments
	set fld=c.getfolder(strArgument)
	for each fl in fld.files
		Set b = c.OpenTextFile(fl, ForReading)
		If blnExists = True Then b.SkipLine
		readerline=b.readall
		d.WriteLine readerline
	Next
Next

Open in new window


Regards,

Rob.
0
 
LVL 11

Expert Comment

by:donaldmaloney
ID: 35184940
Rob,

I think you need a reset to blnExists  = false someplace else running that routine will skip every line or not depending how it is initialized.
Don
0
 
LVL 65

Expert Comment

by:RobSampson
ID: 35186596
Oh, you're right.  Change this (from your original code):
Set d = c.CreateTextFile(output)
For Each strArgument in Wscript.Arguments
	set fld=c.getfolder(strArgument)
	for each fl in fld.files
		Set b = c.OpenTextFile(fl, ForReading)
		readerline=b.readall
		d.WriteLine readerline
	Next
Next 

Open in new window


to this:
If c.FileExists(output) = True Then
   blnExists = True
Else
   blnExists = False
End If
Set d = c.OpenTextFile(output, 8, true)
For Each strArgument in Wscript.Arguments
	set fld=c.getfolder(strArgument)
	for each fl in fld.files
		Set b = c.OpenTextFile(fl, ForReading)
		If blnExists = True Then b.SkipLine
		readerline=b.readall
		d.WriteLine readerline
	Next
Next 

Open in new window


Regards,

Rob.
0
 
LVL 11

Expert Comment

by:donaldmaloney
ID: 35186894
gdunn59

OK I see that in My VB code I use
Dim TestForHeader As Boolean  
and the "A" in the line is the 19th character
The Variablet can be set up on the fly so just  comment out that line in my code.
But I am not sure why that would kill the code since all variables should be declared
and Rob does not set    blnExists          as  a declared  True/False
blnExists = True
ROb  any Idea why the declaration would kill the routine?

Don

0
 
LVL 45

Expert Comment

by:aikimark
ID: 35188579
@donaldmaloney

>>any Idea why the declaration would kill the routine?

This is a VBScript environment.  Although variables can (and should) be declared, all variables are of VARIANT data type.
0
 
LVL 11

Expert Comment

by:donaldmaloney
ID: 35190931
aikimark

OK Ty
0
 
LVL 11

Expert Comment

by:donaldmaloney
ID: 35691541
Dhaest:,

I believe my answer was correct.

Don
0
 
LVL 1

Author Comment

by:gdunn59
ID: 35749250
I never got it to work.  Thanks anyway.
0
 
LVL 45

Expert Comment

by:aikimark
ID: 35749352
@gdunn59

We thought you'd walked away from this question.  Welcome back.  We can stop  the automatic question closure process if you need an solution.

What did you try? (please post code)

What error messages did you receive?

What did the output file look like? (expected vs. actual)
0
 
LVL 53

Expert Comment

by:Dhaest
ID: 35759367
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When we want to run, execute or repeat a statement multiple times, a loop is necessary. This article covers the two types of loops in Python: the while loop and the for loop.
This is about my first experience with programming Arduino.
The viewer will be introduced to the technique of using vectors in C++. The video will cover how to define a vector, store values in the vector and retrieve data from the values stored in the vector.
In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question