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
442 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
  • 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
 

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
 
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
 

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
 

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
 

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
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
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
 

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
control image tags in a string ? 12 65
index Out OF Range Exception error 4 31
Automate switch config backup 3 19
Birthdays 3 0
This is an explanation of a simple data model to help parse a JSON feed
Although it can be difficult to imagine, someday your child will have a career of his or her own. He or she will likely start a family, buy a home and start having their own children. So, while being a kid is still extremely important, it’s also …
The viewer will learn how to pass data into a function in C++. This is one step further in using functions. Instead of only printing text onto the console, the function will be able to perform calculations with argumentents given by the user.
The viewer will learn how to use the return statement in functions in C++. The video will also teach the user how to pass data to a function and have the function return data back for further processing.

758 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now