Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Batch Files
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Batch Files
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Batch Files
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Batch Files
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Batch Files
Thanks for the advice. I am nearly there. I have got the batch fil 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 o 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 -- nickysquawke ----------------------------------------------------------------------- nickysquawkes's Profile: http://www.excelforum.com/member.php...fo&userid=3063 View this thread: http://www.excelforum.com/showthread.php?threadid=50285 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Batch Files
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Batch Files
"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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I batch convert 97-2003 .xls files to 2007 .xlsx files | Excel Discussion (Misc queries) | |||
VB and Batch files | Excel Programming | |||
Batch conversion of XP files to 97 files | Excel Discussion (Misc queries) | |||
Batch Files | Excel Programming | |||
Batch files and VBA | Excel Programming |