Batch Files

[nickysquawkes]

I have several a directory of text files that contain the same data but for different weeks.

E.G.

JANHOURS.txt
FEBHOURS.txt
MARHOURS.txt
APRHOURS.txt
TOTALHOURS.txt

I want to combine all the files ending with hours into totalhours.txt so it can be loaded into a pivot table. However I need this to be automatic. I know it can be done using a batch file, but I can't remeber the batch code to do this. Can anyone help?

Thanks

[Martin Fishlock]

Do you want to append all the files to gether, if so can there are no other
files in the directory, then open a dos shell window and change directory
(cd) to the folder and try:

copy *.txt totalhours.txt

that should do it.

Otherwise you can write a vba script to copy the files.


--
HTHs Martin


"nickysquawkes" wrote:

>
> I have several a directory of text files that contain the same data but
> for different weeks.
>
> E.G.
>
> JANHOURS.txt
> FEBHOURS.txt
> MARHOURS.txt
> APRHOURS.txt
> TOTALHOURS.txt
>
> I want to combine all the files ending with hours into totalhours.txt
> so it can be loaded into a pivot table. However I need this to be
> automatic. I know it can be done using a batch file, but I can't
> remeber the batch code to do this. Can anyone help?
>
> Thanks
>
>
> --
> nickysquawkes
> ------------------------------------------------------------------------
> nickysquawkes's Profile: http://www.excelforum.com/member.php...o&userid=30630
> View this thread: http://www.excelforum.com/showthread...hreadid=502858
>
>

[Dave Peterson]

I think I'd use:
copy *.txt totalhours.ALL
instead of:
copy *.txt totalhours.txt

I think that DOS will yell when it tries to copy Totalhours.txt into
totalhours.txt (since it has the same extension).

But after the copy, .all can be renamed to .txt if required.


Martin Fishlock wrote:
>
> Do you want to append all the files to gether, if so can there are no other
> files in the directory, then open a dos shell window and change directory
> (cd) to the folder and try:
>
> copy *.txt totalhours.txt
>
> that should do it.
>
> Otherwise you can write a vba script to copy the files.
>
> --
> HTHs Martin
>
> "nickysquawkes" wrote:
>
> >
> > I have several a directory of text files that contain the same data but
> > for different weeks.
> >
> > E.G.
> >
> > JANHOURS.txt
> > FEBHOURS.txt
> > MARHOURS.txt
> > APRHOURS.txt
> > TOTALHOURS.txt
> >
> > I want to combine all the files ending with hours into totalhours.txt
> > so it can be loaded into a pivot table. However I need this to be
> > automatic. I know it can be done using a batch file, but I can't
> > remeber the batch code to do this. Can anyone help?
> >
> > Thanks
> >
> >
> > --
> > nickysquawkes
> > ------------------------------------------------------------------------
> > nickysquawkes's Profile: http://www.excelforum.com/member.php...o&userid=30630
> > View this thread: http://www.excelforum.com/showthread...hreadid=502858
> >
> >

--

Dave Peterson

[Dave Peterson]

Ron de Bruin does it with .csv files, but using .txt seems like it wouldn't be
much of a change:

http://www.rondebruin.nl/csv.htm



nickysquawkes wrote:
>
> I have several a directory of text files that contain the same data but
> for different weeks.
>
> E.G.
>
> JANHOURS.txt
> FEBHOURS.txt
> MARHOURS.txt
> APRHOURS.txt
> TOTALHOURS.txt
>
> I want to combine all the files ending with hours into totalhours.txt
> so it can be loaded into a pivot table. However I need this to be
> automatic. I know it can be done using a batch file, but I can't
> remeber the batch code to do this. Can anyone help?
>
> Thanks
>
> --
> nickysquawkes
> ------------------------------------------------------------------------
> nickysquawkes's Profile: http://www.excelforum.com/member.php...o&userid=30630
> View this thread: http://www.excelforum.com/showthread...hreadid=502858

--

Dave Peterson

[nickysquawkes]

Thanks for the advice. I am nearly there. I have got the batch file setup doing the following.

copy header.txt allhours
copy *hours.csv allhours.txt

Which produces the following result:

Month,Age,NameFeb,48,Mike
Feb,57,Sarah
Feb,19,BurtJan,16,Ted
Jan,96,Dave
Jan,91,ElgarMar,56,Kate
Mar,39,Vciky
Mar,67,Susan

I need it to produce the example below. It won't start each file on the new line.

Month,Age,Name
Feb,48,Mike
Feb,57,Sarah
Feb,19,Burt
Jan,16,Ted
Jan,96,Dave
Jan,91,Elgar
Mar,56,Kate
Mar,39,Vciky
Mar,67,Susan

Any more suggestions, please?

[Dave Peterson]

It looks like that your text files are coming from UNIX (or MACs). The use a
different end of line marker than DOS/Windows text files.

Lines in DOS text files usually end with a carriage return followed by Line
feed.

In Unix land, they end with only a linefeed.

So either you don't have any end of line marker or you have the wrong end of
line marker (difficult to tell in your message).

You can open the file in a text editor that can show hex characters (I use
UltraEdit (http://www.ultraedit.com), but there are thousands of free editors
out there) to verify what you have.

If your files have the Unix end of line markers, there are also thousands of
converters available.

If your files don't have any end of line markers, I'm not sure if there are any
programs available to add them--but you could open the file and hit enter at the
end of the (each???) line.

But it sure looks like you have a combination of file types out there.

=======
Or maybe you could just let excel try to do the work (untested):

Option Explicit
Sub testme01()

Dim myNames() As String
Dim fCtr As Long
Dim myFile As String
Dim myPath As String
Dim DestCell As Range
Dim wkbk As Workbook

'change to point at the folder to check
myPath = "c:\my documents\excel"
If Right(myPath, 1) <> "\" Then
myPath = myPath & "\"
End If

myFile = ""
On Error Resume Next
myFile = Dir(myPath & "*.csv")
On Error GoTo 0
If myFile = "" Then
MsgBox "no files found"
Exit Sub
End If

'get the list of files
fCtr = 0
Do While myFile <> ""
'hours.csv
If LCase(myFile) Like "*hours.csv" Then
fCtr = fCtr + 1
ReDim Preserve myNames(1 To fCtr)
myNames(fCtr) = myFile
End If
myFile = Dir()
Loop

If fCtr > 0 Then
Set DestCell = Workbooks.Add(1).Worksheets(1).Range("a2")
DestCell.Parent.Range("a1").Resize(1, 3).Value _
= Array("Month", "Age", "Name")
For fCtr = LBound(myNames) To UBound(myNames)
Set wkbk = Workbooks.Open(Filename:=myPath & myNames(fCtr))
ActiveSheet.UsedRange.Copy _
Destination:=DestCell
With DestCell.Parent
Set DestCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
End With
wkbk.Close savechanges:=False
Next fCtr
End If

End Sub




nickysquawkes wrote:
>
> Thanks for the advice. I am nearly there. I have got the batch file
> setup doing the following.
>
> copy header.txt allhours
> copy *hours.csv allhours.txt
>
> Which produces the following result:
>
> Month,Age,NameFeb,48,Mike
> Feb,57,Sarah
> Feb,19,BurtJan,16,Ted
> Jan,96,Dave
> Jan,91,ElgarMar,56,Kate
> Mar,39,Vciky
> Mar,67,Susan
--

Dave Peterson

[Robin Clay]

"Dave Peterson" wrote:

> It looks like that your text files are coming from UNIX (or MACs). The use a
> different end of line marker than DOS/Windows text files.
>
> Lines in DOS text files usually end with a carriage return followed by Line
> feed.
>
> In Unix land, they end with only a linefeed.
>
> So either you don't have any end of line marker or you have the wrong end of
> line marker (difficult to tell in your message).


Perhaps:

Create (in the appropriate folder) a file containing just a CRLF, i.e. in DOS

C:\ > Copy Con CRLF.txt

[Ctrl Z]

Then another:

C:\ > Copy Con CopyAll.BAT

type %a.txt >>totalhours.txt
type CRLF.txt >>totalhours.txt

[Ctrl Z]

Then (still in DOS or a Shelled BAT file) use e.g.
for %%a in (Janfile Febfile Marfile Aprfile....) do copyall %%a

....or similar...

--
Regards

Robin