Creating one file out of many, including the filename

K

ktm400

I have several hundred .csv files that Iam trying to combine in one file. I
can simply use the dos copy command to create a new file with the contents of
all these other files, but the problem with that is i cannot identify which
file is which in the new file.
Is there a way to do this with excel?
Thanks for any help
 
J

John Nurick

Excel offers no advantage. It's necessary to write code open each file
individually, read it a line at a time, add the file name, and then
write the modified line to the new file.

Here is a VBScript from my library that does the job for a single file
and could be modified (sorry, I'm late for the day job and can't do it
myself) to process multiple files (and to run in Access VBA).

=========================START OF VBScript
'Prepend FN.vbs: adds the filename to each line of the file
'VBScript
'Call from commandline as
' [cscript] Prepend.vbs InFile OutFile

Option Explicit

Dim fso 'As FileSystemObject
Dim fIn 'As TextStream
Dim fOut 'As TextStream
Dim strLine 'As String
Dim strFileName 'As String
Dim DELIM 'As String

DELIM = "," 'String to separate the Filename
'from the rest of the line. Change this
'as needed: e.g. to Chr(9) for Tab

Set fso = CreateObject("Scripting.FileSystemObject")
Set fIn = fso_OpenTextFile(WScript.Arguments(0))
Set fOut = fso.CreateTextFile(WScript.Arguments(1))
strFileName = WScript.Arguments(0)

Do Until fIn.AtEndOfStream
fOut.Write strFileName & DELIM & strLine
Loop

fIn.Close
fOut.Close
===================END OF VBScript

And here (this is why I prefer Perl for little utilities) is a Perl
script that does handle multiple files:

===================START OF CODE
#PrependFN.pl
#Usage:
# perl PrependFN.pl Infilespec Outfile
#
#Infilespec can be a wildcard filespec.

$outfile = pop @ARGV;
open OUTFILE, ">$outfile" or die "Couldn't open $outfile";

foreach $file (<$ARGV[0]>) {
open INFILE, $file or die "Couldn't open $file";
while(<INFILE>) {
print OUTFILE qq("$file",$_)
}
}
===================END OF CODE
 
K

ktm400

Thank you John

John Nurick said:
Excel offers no advantage. It's necessary to write code open each file
individually, read it a line at a time, add the file name, and then
write the modified line to the new file.

Here is a VBScript from my library that does the job for a single file
and could be modified (sorry, I'm late for the day job and can't do it
myself) to process multiple files (and to run in Access VBA).

=========================START OF VBScript
'Prepend FN.vbs: adds the filename to each line of the file
'VBScript
'Call from commandline as
' [cscript] Prepend.vbs InFile OutFile

Option Explicit

Dim fso 'As FileSystemObject
Dim fIn 'As TextStream
Dim fOut 'As TextStream
Dim strLine 'As String
Dim strFileName 'As String
Dim DELIM 'As String

DELIM = "," 'String to separate the Filename
'from the rest of the line. Change this
'as needed: e.g. to Chr(9) for Tab

Set fso = CreateObject("Scripting.FileSystemObject")
Set fIn = fso_OpenTextFile(WScript.Arguments(0))
Set fOut = fso.CreateTextFile(WScript.Arguments(1))
strFileName = WScript.Arguments(0)

Do Until fIn.AtEndOfStream
fOut.Write strFileName & DELIM & strLine
Loop

fIn.Close
fOut.Close
===================END OF VBScript

And here (this is why I prefer Perl for little utilities) is a Perl
script that does handle multiple files:

===================START OF CODE
#PrependFN.pl
#Usage:
# perl PrependFN.pl Infilespec Outfile
#
#Infilespec can be a wildcard filespec.

$outfile = pop @ARGV;
open OUTFILE, ">$outfile" or die "Couldn't open $outfile";

foreach $file (<$ARGV[0]>) {
open INFILE, $file or die "Couldn't open $file";
while(<INFILE>) {
print OUTFILE qq("$file",$_)
}
}
===================END OF CODE


I have several hundred .csv files that Iam trying to combine in one file. I
can simply use the dos copy command to create a new file with the contents of
all these other files, but the problem with that is i cannot identify which
file is which in the new file.
Is there a way to do this with excel?
Thanks for any help
Click to expand...
Click to expand...
 
K

ktm400

John - hoe exactly do I run this code in access? Sorry Iam new to a lot of
this, so please bear with me. Do I create a new macro?
Thanks
Gary

John Nurick said:
Excel offers no advantage. It's necessary to write code open each file
individually, read it a line at a time, add the file name, and then
write the modified line to the new file.

Here is a VBScript from my library that does the job for a single file
and could be modified (sorry, I'm late for the day job and can't do it
myself) to process multiple files (and to run in Access VBA).

=========================START OF VBScript
'Prepend FN.vbs: adds the filename to each line of the file
'VBScript
'Call from commandline as
' [cscript] Prepend.vbs InFile OutFile

Option Explicit

Dim fso 'As FileSystemObject
Dim fIn 'As TextStream
Dim fOut 'As TextStream
Dim strLine 'As String
Dim strFileName 'As String
Dim DELIM 'As String

DELIM = "," 'String to separate the Filename
'from the rest of the line. Change this
'as needed: e.g. to Chr(9) for Tab

Set fso = CreateObject("Scripting.FileSystemObject")
Set fIn = fso_OpenTextFile(WScript.Arguments(0))
Set fOut = fso.CreateTextFile(WScript.Arguments(1))
strFileName = WScript.Arguments(0)

Do Until fIn.AtEndOfStream
fOut.Write strFileName & DELIM & strLine
Loop

fIn.Close
fOut.Close
===================END OF VBScript

And here (this is why I prefer Perl for little utilities) is a Perl
script that does handle multiple files:

===================START OF CODE
#PrependFN.pl
#Usage:
# perl PrependFN.pl Infilespec Outfile
#
#Infilespec can be a wildcard filespec.

$outfile = pop @ARGV;
open OUTFILE, ">$outfile" or die "Couldn't open $outfile";

foreach $file (<$ARGV[0]>) {
open INFILE, $file or die "Couldn't open $file";
while(<INFILE>) {
print OUTFILE qq("$file",$_)
}
}
===================END OF CODE


I have several hundred .csv files that Iam trying to combine in one file. I
can simply use the dos copy command to create a new file with the contents of
all these other files, but the problem with that is i cannot identify which
file is which in the new file.
Is there a way to do this with excel?
Thanks for any help
Click to expand...
Click to expand...
 
J

John Nurick

Here's one (rather inefficient) way which will probably work provided
your csv files aren't too big.

1) Open Notepad, paste the revised PrependFN.vbs script below into it,
and save it as PrependFN.vbs in a convenient folder.

2) Create a new code module in your database and paste this procedure
into it:

Option Explicit
Public Sub ConcatenateCSVWithFilenames()
Dim strFolder As String
Dim strFileSpec As String
Dim strFileName As String

strFolder = "C:\Temp\Nathan\"
strFileSpec = "*.csv"

strFileName = Dir(strFolder & strFileSpec)
Do Until Len(strFileName) = 0
Shell "cscript ""C:\Bin\Fu\PrependFN.Vbs"" " _
& strFolder & strFileName _
& " ""C:\Temp\Outputfile.txt"" "
DoEvents
strFileName = Dir()
Loop
End Sub

3) In the procedure, replace
C:\Bin\Fu\Prepend.fn.vbs
with the actual location and name you used for the script, and
C:\Temp\Outputfile.txt
with the actual name and location you want for the output file.

4) Click somewhere in the procedure, display the Debug toolbar, and
start single-stepping through the code until it's working OK.

'=================================Updated PrependFN.VBS
'PrependFN.vbs: adds the filename to each line of the file
'VBScript
'Call from commandline as
' [cscript] Prepend.vbs InFile OutFile

Option Explicit

Dim fso 'As FileSystemObject
Dim fIn 'As TextStream
Dim fOut 'As TextStream
Dim strLine 'As String
Dim strFileName 'As String
Dim DELIM 'As String

DELIM = "," 'String to separate the Filename
'from the rest of the line. Change this
'as needed: e.g. to Chr(9) for Tab

Set fso = CreateObject("Scripting.FileSystemObject")
Set fIn = fso_OpenTextFile(WScript.Arguments(0))
Set fOut = fso_OpenTextFile(WScript.Arguments(1), 8, True)
strFileName = WScript.Arguments(0)

Do Until fIn.AtEndOfStream
strLine = fIn.ReadLine
fOut.WriteLine strFileName & DELIM & strLine
Loop

fIn.Close
fOut.Close
'=======================END OF CODE



John - hoe exactly do I run this code in access? Sorry Iam new to a lot of
this, so please bear with me. Do I create a new macro?
Thanks
Gary

John Nurick said:
Excel offers no advantage. It's necessary to write code open each file
individually, read it a line at a time, add the file name, and then
write the modified line to the new file.

Here is a VBScript from my library that does the job for a single file
and could be modified (sorry, I'm late for the day job and can't do it
myself) to process multiple files (and to run in Access VBA).

=========================START OF VBScript
'Prepend FN.vbs: adds the filename to each line of the file
'VBScript
'Call from commandline as
' [cscript] Prepend.vbs InFile OutFile

Option Explicit

Dim fso 'As FileSystemObject
Dim fIn 'As TextStream
Dim fOut 'As TextStream
Dim strLine 'As String
Dim strFileName 'As String
Dim DELIM 'As String

DELIM = "," 'String to separate the Filename
'from the rest of the line. Change this
'as needed: e.g. to Chr(9) for Tab

Set fso = CreateObject("Scripting.FileSystemObject")
Set fIn = fso_OpenTextFile(WScript.Arguments(0))
Set fOut = fso.CreateTextFile(WScript.Arguments(1))
strFileName = WScript.Arguments(0)

Do Until fIn.AtEndOfStream
fOut.Write strFileName & DELIM & strLine
Loop

fIn.Close
fOut.Close
===================END OF VBScript

And here (this is why I prefer Perl for little utilities) is a Perl
script that does handle multiple files:

===================START OF CODE
#PrependFN.pl
#Usage:
# perl PrependFN.pl Infilespec Outfile
#
#Infilespec can be a wildcard filespec.

$outfile = pop @ARGV;
open OUTFILE, ">$outfile" or die "Couldn't open $outfile";

foreach $file (<$ARGV[0]>) {
open INFILE, $file or die "Couldn't open $file";
while(<INFILE>) {
print OUTFILE qq("$file",$_)
}
}
===================END OF CODE


I have several hundred .csv files that Iam trying to combine in one file. I
can simply use the dos copy command to create a new file with the contents of
all these other files, but the problem with that is i cannot identify which
file is which in the new file.
Is there a way to do this with excel?
Thanks for any help
Click to expand...
Click to expand...
Click to expand...
 
K

ktm400

Thank you very much John

John Nurick said:
Here's one (rather inefficient) way which will probably work provided
your csv files aren't too big.

1) Open Notepad, paste the revised PrependFN.vbs script below into it,
and save it as PrependFN.vbs in a convenient folder.

2) Create a new code module in your database and paste this procedure
into it:

Option Explicit
Public Sub ConcatenateCSVWithFilenames()
Dim strFolder As String
Dim strFileSpec As String
Dim strFileName As String

strFolder = "C:\Temp\Nathan\"
strFileSpec = "*.csv"

strFileName = Dir(strFolder & strFileSpec)
Do Until Len(strFileName) = 0
Shell "cscript ""C:\Bin\Fu\PrependFN.Vbs"" " _
& strFolder & strFileName _
& " ""C:\Temp\Outputfile.txt"" "
DoEvents
strFileName = Dir()
Loop
End Sub

3) In the procedure, replace
C:\Bin\Fu\Prepend.fn.vbs
with the actual location and name you used for the script, and
C:\Temp\Outputfile.txt
with the actual name and location you want for the output file.

4) Click somewhere in the procedure, display the Debug toolbar, and
start single-stepping through the code until it's working OK.

'=================================Updated PrependFN.VBS
'PrependFN.vbs: adds the filename to each line of the file
'VBScript
'Call from commandline as
' [cscript] Prepend.vbs InFile OutFile

Option Explicit

Dim fso 'As FileSystemObject
Dim fIn 'As TextStream
Dim fOut 'As TextStream
Dim strLine 'As String
Dim strFileName 'As String
Dim DELIM 'As String

DELIM = "," 'String to separate the Filename
'from the rest of the line. Change this
'as needed: e.g. to Chr(9) for Tab

Set fso = CreateObject("Scripting.FileSystemObject")
Set fIn = fso_OpenTextFile(WScript.Arguments(0))
Set fOut = fso_OpenTextFile(WScript.Arguments(1), 8, True)
strFileName = WScript.Arguments(0)

Do Until fIn.AtEndOfStream
strLine = fIn.ReadLine
fOut.WriteLine strFileName & DELIM & strLine
Loop

fIn.Close
fOut.Close
'=======================END OF CODE



John - hoe exactly do I run this code in access? Sorry Iam new to a lot of
this, so please bear with me. Do I create a new macro?
Thanks
Gary

John Nurick said:
Excel offers no advantage. It's necessary to write code open each file
individually, read it a line at a time, add the file name, and then
write the modified line to the new file.

Here is a VBScript from my library that does the job for a single file
and could be modified (sorry, I'm late for the day job and can't do it
myself) to process multiple files (and to run in Access VBA).

=========================START OF VBScript
'Prepend FN.vbs: adds the filename to each line of the file
'VBScript
'Call from commandline as
' [cscript] Prepend.vbs InFile OutFile

Option Explicit

Dim fso 'As FileSystemObject
Dim fIn 'As TextStream
Dim fOut 'As TextStream
Dim strLine 'As String
Dim strFileName 'As String
Dim DELIM 'As String

DELIM = "," 'String to separate the Filename
'from the rest of the line. Change this
'as needed: e.g. to Chr(9) for Tab

Set fso = CreateObject("Scripting.FileSystemObject")
Set fIn = fso_OpenTextFile(WScript.Arguments(0))
Set fOut = fso.CreateTextFile(WScript.Arguments(1))
strFileName = WScript.Arguments(0)

Do Until fIn.AtEndOfStream
fOut.Write strFileName & DELIM & strLine
Loop

fIn.Close
fOut.Close
===================END OF VBScript

And here (this is why I prefer Perl for little utilities) is a Perl
script that does handle multiple files:

===================START OF CODE
#PrependFN.pl
#Usage:
# perl PrependFN.pl Infilespec Outfile
#
#Infilespec can be a wildcard filespec.

$outfile = pop @ARGV;
open OUTFILE, ">$outfile" or die "Couldn't open $outfile";

foreach $file (<$ARGV[0]>) {
open INFILE, $file or die "Couldn't open $file";
while(<INFILE>) {
print OUTFILE qq("$file",$_)
}
}
===================END OF CODE


On Tue, 20 Sep 2005 10:50:03 -0700, ktm400

I have several hundred .csv files that Iam trying to combine in one file. I
can simply use the dos copy command to create a new file with the contents of
all these other files, but the problem with that is i cannot identify which
file is which in the new file.
Is there a way to do this with excel?
Thanks for any help
Click to expand...
Click to expand...
Click to expand...
 
K

ktm400

John - I have done this and don't get an output file.
here is what I did.
create new module in my access database and pasted the following code to it:

Option Explicit
Public Sub ConcatenateCSVWithFilenames()
Dim strFolder As String
Dim strFileSpec As String
Dim strFileName As String

strFolder = "C:\boiler steam production"
strFileSpec = "*.csv"

strFileName = Dir(strFolder & strFileSpec)
Do Until Len(strFileName) = 0
Shell "cscript ""C:\downloads\PrependFN.Vbs"" " _
& strFolder & strFileName _
& " ""C:\Temp\Outputfile.txt"" "
DoEvents
strFileName = Dir()
Loop
End Sub


Created this file in c:\downloads: (called prependFN.vbs)

'PrependFN.vbs: adds the filename to each line of the file
'VBScript
'Call from commandline as
' [cscript] Prepend.vbs InFile OutFile

Option Explicit

Dim fso 'As FileSystemObject
Dim fIn 'As TextStream
Dim fOut 'As TextStream
Dim strLine 'As String
Dim strFileName 'As String
Dim DELIM 'As String

DELIM = "," 'String to separate the Filename
'from the rest of the line. Change this
'as needed: e.g. to Chr(9) for Tab

Set fso = CreateObject("Scripting.FileSystemObject")
Set fIn = fso_OpenTextFile(WScript.Arguments(0))
Set fOut = fso_OpenTextFile(WScript.Arguments(1), 8, True)
strFileName = WScript.Arguments(0)

Do Until fIn.AtEndOfStream
strLine = fIn.ReadLine
fOut.WriteLine strFileName & DELIM & strLine
Loop

fIn.Close
fOut.Close






John Nurick said:
Here's one (rather inefficient) way which will probably work provided
your csv files aren't too big.

1) Open Notepad, paste the revised PrependFN.vbs script below into it,
and save it as PrependFN.vbs in a convenient folder.

2) Create a new code module in your database and paste this procedure
into it:

Option Explicit
Public Sub ConcatenateCSVWithFilenames()
Dim strFolder As String
Dim strFileSpec As String
Dim strFileName As String

strFolder = "C:\Temp\Nathan\"
strFileSpec = "*.csv"

strFileName = Dir(strFolder & strFileSpec)
Do Until Len(strFileName) = 0
Shell "cscript ""C:\Bin\Fu\PrependFN.Vbs"" " _
& strFolder & strFileName _
& " ""C:\Temp\Outputfile.txt"" "
DoEvents
strFileName = Dir()
Loop
End Sub

3) In the procedure, replace
C:\Bin\Fu\Prepend.fn.vbs
with the actual location and name you used for the script, and
C:\Temp\Outputfile.txt
with the actual name and location you want for the output file.

4) Click somewhere in the procedure, display the Debug toolbar, and
start single-stepping through the code until it's working OK.

'=================================Updated PrependFN.VBS
'PrependFN.vbs: adds the filename to each line of the file
'VBScript
'Call from commandline as
' [cscript] Prepend.vbs InFile OutFile

Option Explicit

Dim fso 'As FileSystemObject
Dim fIn 'As TextStream
Dim fOut 'As TextStream
Dim strLine 'As String
Dim strFileName 'As String
Dim DELIM 'As String

DELIM = "," 'String to separate the Filename
'from the rest of the line. Change this
'as needed: e.g. to Chr(9) for Tab

Set fso = CreateObject("Scripting.FileSystemObject")
Set fIn = fso_OpenTextFile(WScript.Arguments(0))
Set fOut = fso_OpenTextFile(WScript.Arguments(1), 8, True)
strFileName = WScript.Arguments(0)

Do Until fIn.AtEndOfStream
strLine = fIn.ReadLine
fOut.WriteLine strFileName & DELIM & strLine
Loop

fIn.Close
fOut.Close
'=======================END OF CODE



John - hoe exactly do I run this code in access? Sorry Iam new to a lot of
this, so please bear with me. Do I create a new macro?
Thanks
Gary

John Nurick said:
Excel offers no advantage. It's necessary to write code open each file
individually, read it a line at a time, add the file name, and then
write the modified line to the new file.

Here is a VBScript from my library that does the job for a single file
and could be modified (sorry, I'm late for the day job and can't do it
myself) to process multiple files (and to run in Access VBA).

=========================START OF VBScript
'Prepend FN.vbs: adds the filename to each line of the file
'VBScript
'Call from commandline as
' [cscript] Prepend.vbs InFile OutFile

Option Explicit

Dim fso 'As FileSystemObject
Dim fIn 'As TextStream
Dim fOut 'As TextStream
Dim strLine 'As String
Dim strFileName 'As String
Dim DELIM 'As String

DELIM = "," 'String to separate the Filename
'from the rest of the line. Change this
'as needed: e.g. to Chr(9) for Tab

Set fso = CreateObject("Scripting.FileSystemObject")
Set fIn = fso_OpenTextFile(WScript.Arguments(0))
Set fOut = fso.CreateTextFile(WScript.Arguments(1))
strFileName = WScript.Arguments(0)

Do Until fIn.AtEndOfStream
fOut.Write strFileName & DELIM & strLine
Loop

fIn.Close
fOut.Close
===================END OF VBScript

And here (this is why I prefer Perl for little utilities) is a Perl
script that does handle multiple files:

===================START OF CODE
#PrependFN.pl
#Usage:
# perl PrependFN.pl Infilespec Outfile
#
#Infilespec can be a wildcard filespec.

$outfile = pop @ARGV;
open OUTFILE, ">$outfile" or die "Couldn't open $outfile";

foreach $file (<$ARGV[0]>) {
open INFILE, $file or die "Couldn't open $file";
while(<INFILE>) {
print OUTFILE qq("$file",$_)
}
}
===================END OF CODE


On Tue, 20 Sep 2005 10:50:03 -0700, ktm400

I have several hundred .csv files that Iam trying to combine in one file. I
can simply use the dos copy command to create a new file with the contents of
all these other files, but the problem with that is i cannot identify which
file is which in the new file.
Is there a way to do this with excel?
Thanks for any help
Click to expand...
Click to expand...
Click to expand...
 
J

John Nurick

So what does happen?

Is
C:\Temp\
a valid path on your computer? IOW, is there a folder called Temp on the
C: drive?

John - I have done this and don't get an output file.
here is what I did.
create new module in my access database and pasted the following code to it:

Option Explicit
Public Sub ConcatenateCSVWithFilenames()
Dim strFolder As String
Dim strFileSpec As String
Dim strFileName As String

strFolder = "C:\boiler steam production"
strFileSpec = "*.csv"

strFileName = Dir(strFolder & strFileSpec)
Do Until Len(strFileName) = 0
Shell "cscript ""C:\downloads\PrependFN.Vbs"" " _
& strFolder & strFileName _
& " ""C:\Temp\Outputfile.txt"" "
DoEvents
strFileName = Dir()
Loop
End Sub


Created this file in c:\downloads: (called prependFN.vbs)

'PrependFN.vbs: adds the filename to each line of the file
'VBScript
'Call from commandline as
' [cscript] Prepend.vbs InFile OutFile

Option Explicit

Dim fso 'As FileSystemObject
Dim fIn 'As TextStream
Dim fOut 'As TextStream
Dim strLine 'As String
Dim strFileName 'As String
Dim DELIM 'As String

DELIM = "," 'String to separate the Filename
'from the rest of the line. Change this
'as needed: e.g. to Chr(9) for Tab

Set fso = CreateObject("Scripting.FileSystemObject")
Set fIn = fso_OpenTextFile(WScript.Arguments(0))
Set fOut = fso_OpenTextFile(WScript.Arguments(1), 8, True)
strFileName = WScript.Arguments(0)

Do Until fIn.AtEndOfStream
strLine = fIn.ReadLine
fOut.WriteLine strFileName & DELIM & strLine
Loop

fIn.Close
fOut.Close






John Nurick said:
Here's one (rather inefficient) way which will probably work provided
your csv files aren't too big.

1) Open Notepad, paste the revised PrependFN.vbs script below into it,
and save it as PrependFN.vbs in a convenient folder.

2) Create a new code module in your database and paste this procedure
into it:

Option Explicit
Public Sub ConcatenateCSVWithFilenames()
Dim strFolder As String
Dim strFileSpec As String
Dim strFileName As String

strFolder = "C:\Temp\Nathan\"
strFileSpec = "*.csv"

strFileName = Dir(strFolder & strFileSpec)
Do Until Len(strFileName) = 0
Shell "cscript ""C:\Bin\Fu\PrependFN.Vbs"" " _
& strFolder & strFileName _
& " ""C:\Temp\Outputfile.txt"" "
DoEvents
strFileName = Dir()
Loop
End Sub

3) In the procedure, replace
C:\Bin\Fu\Prepend.fn.vbs
with the actual location and name you used for the script, and
C:\Temp\Outputfile.txt
with the actual name and location you want for the output file.

4) Click somewhere in the procedure, display the Debug toolbar, and
start single-stepping through the code until it's working OK.

'=================================Updated PrependFN.VBS
'PrependFN.vbs: adds the filename to each line of the file
'VBScript
'Call from commandline as
' [cscript] Prepend.vbs InFile OutFile

Option Explicit

Dim fso 'As FileSystemObject
Dim fIn 'As TextStream
Dim fOut 'As TextStream
Dim strLine 'As String
Dim strFileName 'As String
Dim DELIM 'As String

DELIM = "," 'String to separate the Filename
'from the rest of the line. Change this
'as needed: e.g. to Chr(9) for Tab

Set fso = CreateObject("Scripting.FileSystemObject")
Set fIn = fso_OpenTextFile(WScript.Arguments(0))
Set fOut = fso_OpenTextFile(WScript.Arguments(1), 8, True)
strFileName = WScript.Arguments(0)

Do Until fIn.AtEndOfStream
strLine = fIn.ReadLine
fOut.WriteLine strFileName & DELIM & strLine
Loop

fIn.Close
fOut.Close
'=======================END OF CODE



John - hoe exactly do I run this code in access? Sorry Iam new to a lot of
this, so please bear with me. Do I create a new macro?
Thanks
Gary

:

Excel offers no advantage. It's necessary to write code open each file
individually, read it a line at a time, add the file name, and then
write the modified line to the new file.

Here is a VBScript from my library that does the job for a single file
and could be modified (sorry, I'm late for the day job and can't do it
myself) to process multiple files (and to run in Access VBA).

=========================START OF VBScript
'Prepend FN.vbs: adds the filename to each line of the file
'VBScript
'Call from commandline as
' [cscript] Prepend.vbs InFile OutFile

Option Explicit

Dim fso 'As FileSystemObject
Dim fIn 'As TextStream
Dim fOut 'As TextStream
Dim strLine 'As String
Dim strFileName 'As String
Dim DELIM 'As String

DELIM = "," 'String to separate the Filename
'from the rest of the line. Change this
'as needed: e.g. to Chr(9) for Tab

Set fso = CreateObject("Scripting.FileSystemObject")
Set fIn = fso_OpenTextFile(WScript.Arguments(0))
Set fOut = fso.CreateTextFile(WScript.Arguments(1))
strFileName = WScript.Arguments(0)

Do Until fIn.AtEndOfStream
fOut.Write strFileName & DELIM & strLine
Loop

fIn.Close
fOut.Close
===================END OF VBScript

And here (this is why I prefer Perl for little utilities) is a Perl
script that does handle multiple files:

===================START OF CODE
#PrependFN.pl
#Usage:
# perl PrependFN.pl Infilespec Outfile
#
#Infilespec can be a wildcard filespec.

$outfile = pop @ARGV;
open OUTFILE, ">$outfile" or die "Couldn't open $outfile";

foreach $file (<$ARGV[0]>) {
open INFILE, $file or die "Couldn't open $file";
while(<INFILE>) {
print OUTFILE qq("$file",$_)
}
}
===================END OF CODE


On Tue, 20 Sep 2005 10:50:03 -0700, ktm400

I have several hundred .csv files that Iam trying to combine in one file. I
can simply use the dos copy command to create a new file with the contents of
all these other files, but the problem with that is i cannot identify which
file is which in the new file.
Is there a way to do this with excel?
Thanks for any help
Click to expand...
Click to expand...
Click to expand...
 
K

ktm400

Yes - c:\temp is a valid directory...
It appears to run without any problem, I do not see any error messages. When
I step into the module code, the line "Public Sub
ConcatenateCSVWithFilenames()" becomes highlighted in yellow..
then "strFolder = "C:\boiler steam production"
then "strFileSpec = "*.csv""
then "strFileName = Dir(strFolder & strFileSpec)"
then "Do Until Len(strFileName) = 0"
then "End Sub"

Iam assuming the yellow highlighted text is ok?



John Nurick said:
So what does happen?

Is
C:\Temp\
a valid path on your computer? IOW, is there a folder called Temp on the
C: drive?

John - I have done this and don't get an output file.
here is what I did.
create new module in my access database and pasted the following code to it:

Option Explicit
Public Sub ConcatenateCSVWithFilenames()
Dim strFolder As String
Dim strFileSpec As String
Dim strFileName As String

strFolder = "C:\boiler steam production"
strFileSpec = "*.csv"

strFileName = Dir(strFolder & strFileSpec)
Do Until Len(strFileName) = 0
Shell "cscript ""C:\downloads\PrependFN.Vbs"" " _
& strFolder & strFileName _
& " ""C:\Temp\Outputfile.txt"" "
DoEvents
strFileName = Dir()
Loop
End Sub


Created this file in c:\downloads: (called prependFN.vbs)

'PrependFN.vbs: adds the filename to each line of the file
'VBScript
'Call from commandline as
' [cscript] Prepend.vbs InFile OutFile

Option Explicit

Dim fso 'As FileSystemObject
Dim fIn 'As TextStream
Dim fOut 'As TextStream
Dim strLine 'As String
Dim strFileName 'As String
Dim DELIM 'As String

DELIM = "," 'String to separate the Filename
'from the rest of the line. Change this
'as needed: e.g. to Chr(9) for Tab

Set fso = CreateObject("Scripting.FileSystemObject")
Set fIn = fso_OpenTextFile(WScript.Arguments(0))
Set fOut = fso_OpenTextFile(WScript.Arguments(1), 8, True)
strFileName = WScript.Arguments(0)

Do Until fIn.AtEndOfStream
strLine = fIn.ReadLine
fOut.WriteLine strFileName & DELIM & strLine
Loop

fIn.Close
fOut.Close






John Nurick said:
Here's one (rather inefficient) way which will probably work provided
your csv files aren't too big.

1) Open Notepad, paste the revised PrependFN.vbs script below into it,
and save it as PrependFN.vbs in a convenient folder.

2) Create a new code module in your database and paste this procedure
into it:

Option Explicit
Public Sub ConcatenateCSVWithFilenames()
Dim strFolder As String
Dim strFileSpec As String
Dim strFileName As String

strFolder = "C:\Temp\Nathan\"
strFileSpec = "*.csv"

strFileName = Dir(strFolder & strFileSpec)
Do Until Len(strFileName) = 0
Shell "cscript ""C:\Bin\Fu\PrependFN.Vbs"" " _
& strFolder & strFileName _
& " ""C:\Temp\Outputfile.txt"" "
DoEvents
strFileName = Dir()
Loop
End Sub

3) In the procedure, replace
C:\Bin\Fu\Prepend.fn.vbs
with the actual location and name you used for the script, and
C:\Temp\Outputfile.txt
with the actual name and location you want for the output file.

4) Click somewhere in the procedure, display the Debug toolbar, and
start single-stepping through the code until it's working OK.

'=================================Updated PrependFN.VBS
'PrependFN.vbs: adds the filename to each line of the file
'VBScript
'Call from commandline as
' [cscript] Prepend.vbs InFile OutFile

Option Explicit

Dim fso 'As FileSystemObject
Dim fIn 'As TextStream
Dim fOut 'As TextStream
Dim strLine 'As String
Dim strFileName 'As String
Dim DELIM 'As String

DELIM = "," 'String to separate the Filename
'from the rest of the line. Change this
'as needed: e.g. to Chr(9) for Tab

Set fso = CreateObject("Scripting.FileSystemObject")
Set fIn = fso_OpenTextFile(WScript.Arguments(0))
Set fOut = fso_OpenTextFile(WScript.Arguments(1), 8, True)
strFileName = WScript.Arguments(0)

Do Until fIn.AtEndOfStream
strLine = fIn.ReadLine
fOut.WriteLine strFileName & DELIM & strLine
Loop

fIn.Close
fOut.Close
'=======================END OF CODE



On Wed, 21 Sep 2005 09:10:03 -0700, ktm400

John - hoe exactly do I run this code in access? Sorry Iam new to a lot of
this, so please bear with me. Do I create a new macro?
Thanks
Gary

:

Excel offers no advantage. It's necessary to write code open each file
individually, read it a line at a time, add the file name, and then
write the modified line to the new file.

Here is a VBScript from my library that does the job for a single file
and could be modified (sorry, I'm late for the day job and can't do it
myself) to process multiple files (and to run in Access VBA).

=========================START OF VBScript
'Prepend FN.vbs: adds the filename to each line of the file
'VBScript
'Call from commandline as
' [cscript] Prepend.vbs InFile OutFile

Option Explicit

Dim fso 'As FileSystemObject
Dim fIn 'As TextStream
Dim fOut 'As TextStream
Dim strLine 'As String
Dim strFileName 'As String
Dim DELIM 'As String

DELIM = "," 'String to separate the Filename
'from the rest of the line. Change this
'as needed: e.g. to Chr(9) for Tab

Set fso = CreateObject("Scripting.FileSystemObject")
Set fIn = fso_OpenTextFile(WScript.Arguments(0))
Set fOut = fso.CreateTextFile(WScript.Arguments(1))
strFileName = WScript.Arguments(0)

Do Until fIn.AtEndOfStream
fOut.Write strFileName & DELIM & strLine
Loop

fIn.Close
fOut.Close
===================END OF VBScript

And here (this is why I prefer Perl for little utilities) is a Perl
script that does handle multiple files:

===================START OF CODE
#PrependFN.pl
#Usage:
# perl PrependFN.pl Infilespec Outfile
#
#Infilespec can be a wildcard filespec.

$outfile = pop @ARGV;
open OUTFILE, ">$outfile" or die "Couldn't open $outfile";

foreach $file (<$ARGV[0]>) {
open INFILE, $file or die "Couldn't open $file";
while(<INFILE>) {
print OUTFILE qq("$file",$_)
}
}
===================END OF CODE


On Tue, 20 Sep 2005 10:50:03 -0700, ktm400

I have several hundred .csv files that Iam trying to combine in one file. I
can simply use the dos copy command to create a new file with the contents of
all these other files, but the problem with that is i cannot identify which
file is which in the new file.
Is there a way to do this with excel?
Thanks for any help
Click to expand...
Click to expand...
Click to expand...
 
J

John Nurick

You need to end strFolder with a \:

strFolder = "C:\boiler steam production\"

Without it, this
strFolder & strFileSpec
produces
C:\boiler steam production*.csv
which isn't what you want.

While you're stepping through the code, you can hover the mouse over the
various variables and tooltips will pop up to show their current values.

Yes - c:\temp is a valid directory...
It appears to run without any problem, I do not see any error messages. When
I step into the module code, the line "Public Sub
ConcatenateCSVWithFilenames()" becomes highlighted in yellow..
then "strFolder = "C:\boiler steam production"
then "strFileSpec = "*.csv""
then "strFileName = Dir(strFolder & strFileSpec)"
then "Do Until Len(strFileName) = 0"
then "End Sub"

Iam assuming the yellow highlighted text is ok?



John Nurick said:
So what does happen?

Is
C:\Temp\
a valid path on your computer? IOW, is there a folder called Temp on the
C: drive?

John - I have done this and don't get an output file.
here is what I did.
create new module in my access database and pasted the following code to it:

Option Explicit
Public Sub ConcatenateCSVWithFilenames()
Dim strFolder As String
Dim strFileSpec As String
Dim strFileName As String

strFolder = "C:\boiler steam production"
strFileSpec = "*.csv"

strFileName = Dir(strFolder & strFileSpec)
Do Until Len(strFileName) = 0
Shell "cscript ""C:\downloads\PrependFN.Vbs"" " _
& strFolder & strFileName _
& " ""C:\Temp\Outputfile.txt"" "
DoEvents
strFileName = Dir()
Loop
End Sub


Created this file in c:\downloads: (called prependFN.vbs)

'PrependFN.vbs: adds the filename to each line of the file
'VBScript
'Call from commandline as
' [cscript] Prepend.vbs InFile OutFile

Option Explicit

Dim fso 'As FileSystemObject
Dim fIn 'As TextStream
Dim fOut 'As TextStream
Dim strLine 'As String
Dim strFileName 'As String
Dim DELIM 'As String

DELIM = "," 'String to separate the Filename
'from the rest of the line. Change this
'as needed: e.g. to Chr(9) for Tab

Set fso = CreateObject("Scripting.FileSystemObject")
Set fIn = fso_OpenTextFile(WScript.Arguments(0))
Set fOut = fso_OpenTextFile(WScript.Arguments(1), 8, True)
strFileName = WScript.Arguments(0)

Do Until fIn.AtEndOfStream
strLine = fIn.ReadLine
fOut.WriteLine strFileName & DELIM & strLine
Loop

fIn.Close
fOut.Close






:

Here's one (rather inefficient) way which will probably work provided
your csv files aren't too big.

1) Open Notepad, paste the revised PrependFN.vbs script below into it,
and save it as PrependFN.vbs in a convenient folder.

2) Create a new code module in your database and paste this procedure
into it:

Option Explicit
Public Sub ConcatenateCSVWithFilenames()
Dim strFolder As String
Dim strFileSpec As String
Dim strFileName As String

strFolder = "C:\Temp\Nathan\"
strFileSpec = "*.csv"

strFileName = Dir(strFolder & strFileSpec)
Do Until Len(strFileName) = 0
Shell "cscript ""C:\Bin\Fu\PrependFN.Vbs"" " _
& strFolder & strFileName _
& " ""C:\Temp\Outputfile.txt"" "
DoEvents
strFileName = Dir()
Loop
End Sub

3) In the procedure, replace
C:\Bin\Fu\Prepend.fn.vbs
with the actual location and name you used for the script, and
C:\Temp\Outputfile.txt
with the actual name and location you want for the output file.

4) Click somewhere in the procedure, display the Debug toolbar, and
start single-stepping through the code until it's working OK.

'=================================Updated PrependFN.VBS
'PrependFN.vbs: adds the filename to each line of the file
'VBScript
'Call from commandline as
' [cscript] Prepend.vbs InFile OutFile

Option Explicit

Dim fso 'As FileSystemObject
Dim fIn 'As TextStream
Dim fOut 'As TextStream
Dim strLine 'As String
Dim strFileName 'As String
Dim DELIM 'As String

DELIM = "," 'String to separate the Filename
'from the rest of the line. Change this
'as needed: e.g. to Chr(9) for Tab

Set fso = CreateObject("Scripting.FileSystemObject")
Set fIn = fso_OpenTextFile(WScript.Arguments(0))
Set fOut = fso_OpenTextFile(WScript.Arguments(1), 8, True)
strFileName = WScript.Arguments(0)

Do Until fIn.AtEndOfStream
strLine = fIn.ReadLine
fOut.WriteLine strFileName & DELIM & strLine
Loop

fIn.Close
fOut.Close
'=======================END OF CODE



On Wed, 21 Sep 2005 09:10:03 -0700, ktm400

John - hoe exactly do I run this code in access? Sorry Iam new to a lot of
this, so please bear with me. Do I create a new macro?
Thanks
Gary

:

Excel offers no advantage. It's necessary to write code open each file
individually, read it a line at a time, add the file name, and then
write the modified line to the new file.

Here is a VBScript from my library that does the job for a single file
and could be modified (sorry, I'm late for the day job and can't do it
myself) to process multiple files (and to run in Access VBA).

=========================START OF VBScript
'Prepend FN.vbs: adds the filename to each line of the file
'VBScript
'Call from commandline as
' [cscript] Prepend.vbs InFile OutFile

Option Explicit

Dim fso 'As FileSystemObject
Dim fIn 'As TextStream
Dim fOut 'As TextStream
Dim strLine 'As String
Dim strFileName 'As String
Dim DELIM 'As String

DELIM = "," 'String to separate the Filename
'from the rest of the line. Change this
'as needed: e.g. to Chr(9) for Tab

Set fso = CreateObject("Scripting.FileSystemObject")
Set fIn = fso_OpenTextFile(WScript.Arguments(0))
Set fOut = fso.CreateTextFile(WScript.Arguments(1))
strFileName = WScript.Arguments(0)

Do Until fIn.AtEndOfStream
fOut.Write strFileName & DELIM & strLine
Loop

fIn.Close
fOut.Close
===================END OF VBScript

And here (this is why I prefer Perl for little utilities) is a Perl
script that does handle multiple files:

===================START OF CODE
#PrependFN.pl
#Usage:
# perl PrependFN.pl Infilespec Outfile
#
#Infilespec can be a wildcard filespec.

$outfile = pop @ARGV;
open OUTFILE, ">$outfile" or die "Couldn't open $outfile";

foreach $file (<$ARGV[0]>) {
open INFILE, $file or die "Couldn't open $file";
while(<INFILE>) {
print OUTFILE qq("$file",$_)
}
}
===================END OF CODE


On Tue, 20 Sep 2005 10:50:03 -0700, ktm400

I have several hundred .csv files that Iam trying to combine in one file. I
can simply use the dos copy command to create a new file with the contents of
all these other files, but the problem with that is i cannot identify which
file is which in the new file.
Is there a way to do this with excel?
Thanks for any help
Click to expand...
Click to expand...
Click to expand...
 
K

ktm400

OK John, did this.
Something is definately happening but I still get no file.
I get lots of little dos windows opening and closing on the windows toolbar
which I assume are the individual .csv files being opened and closed....but
still no outputfile.txt file

John Nurick said:
You need to end strFolder with a \:

strFolder = "C:\boiler steam production\"

Without it, this
strFolder & strFileSpec
produces
C:\boiler steam production*.csv
which isn't what you want.

While you're stepping through the code, you can hover the mouse over the
various variables and tooltips will pop up to show their current values.

Yes - c:\temp is a valid directory...
It appears to run without any problem, I do not see any error messages. When
I step into the module code, the line "Public Sub
ConcatenateCSVWithFilenames()" becomes highlighted in yellow..
then "strFolder = "C:\boiler steam production"
then "strFileSpec = "*.csv""
then "strFileName = Dir(strFolder & strFileSpec)"
then "Do Until Len(strFileName) = 0"
then "End Sub"

Iam assuming the yellow highlighted text is ok?



John Nurick said:
So what does happen?

Is
C:\Temp\
a valid path on your computer? IOW, is there a folder called Temp on the
C: drive?

On Thu, 22 Sep 2005 07:00:02 -0700, ktm400

John - I have done this and don't get an output file.
here is what I did.
create new module in my access database and pasted the following code to it:

Option Explicit
Public Sub ConcatenateCSVWithFilenames()
Dim strFolder As String
Dim strFileSpec As String
Dim strFileName As String

strFolder = "C:\boiler steam production"
strFileSpec = "*.csv"

strFileName = Dir(strFolder & strFileSpec)
Do Until Len(strFileName) = 0
Shell "cscript ""C:\downloads\PrependFN.Vbs"" " _
& strFolder & strFileName _
& " ""C:\Temp\Outputfile.txt"" "
DoEvents
strFileName = Dir()
Loop
End Sub


Created this file in c:\downloads: (called prependFN.vbs)

'PrependFN.vbs: adds the filename to each line of the file
'VBScript
'Call from commandline as
' [cscript] Prepend.vbs InFile OutFile

Option Explicit

Dim fso 'As FileSystemObject
Dim fIn 'As TextStream
Dim fOut 'As TextStream
Dim strLine 'As String
Dim strFileName 'As String
Dim DELIM 'As String

DELIM = "," 'String to separate the Filename
'from the rest of the line. Change this
'as needed: e.g. to Chr(9) for Tab

Set fso = CreateObject("Scripting.FileSystemObject")
Set fIn = fso_OpenTextFile(WScript.Arguments(0))
Set fOut = fso_OpenTextFile(WScript.Arguments(1), 8, True)
strFileName = WScript.Arguments(0)

Do Until fIn.AtEndOfStream
strLine = fIn.ReadLine
fOut.WriteLine strFileName & DELIM & strLine
Loop

fIn.Close
fOut.Close






:

Here's one (rather inefficient) way which will probably work provided
your csv files aren't too big.

1) Open Notepad, paste the revised PrependFN.vbs script below into it,
and save it as PrependFN.vbs in a convenient folder.

2) Create a new code module in your database and paste this procedure
into it:

Option Explicit
Public Sub ConcatenateCSVWithFilenames()
Dim strFolder As String
Dim strFileSpec As String
Dim strFileName As String

strFolder = "C:\Temp\Nathan\"
strFileSpec = "*.csv"

strFileName = Dir(strFolder & strFileSpec)
Do Until Len(strFileName) = 0
Shell "cscript ""C:\Bin\Fu\PrependFN.Vbs"" " _
& strFolder & strFileName _
& " ""C:\Temp\Outputfile.txt"" "
DoEvents
strFileName = Dir()
Loop
End Sub

3) In the procedure, replace
C:\Bin\Fu\Prepend.fn.vbs
with the actual location and name you used for the script, and
C:\Temp\Outputfile.txt
with the actual name and location you want for the output file.

4) Click somewhere in the procedure, display the Debug toolbar, and
start single-stepping through the code until it's working OK.

'=================================Updated PrependFN.VBS
'PrependFN.vbs: adds the filename to each line of the file
'VBScript
'Call from commandline as
' [cscript] Prepend.vbs InFile OutFile

Option Explicit

Dim fso 'As FileSystemObject
Dim fIn 'As TextStream
Dim fOut 'As TextStream
Dim strLine 'As String
Dim strFileName 'As String
Dim DELIM 'As String

DELIM = "," 'String to separate the Filename
'from the rest of the line. Change this
'as needed: e.g. to Chr(9) for Tab

Set fso = CreateObject("Scripting.FileSystemObject")
Set fIn = fso_OpenTextFile(WScript.Arguments(0))
Set fOut = fso_OpenTextFile(WScript.Arguments(1), 8, True)
strFileName = WScript.Arguments(0)

Do Until fIn.AtEndOfStream
strLine = fIn.ReadLine
fOut.WriteLine strFileName & DELIM & strLine
Loop

fIn.Close
fOut.Close
'=======================END OF CODE



On Wed, 21 Sep 2005 09:10:03 -0700, ktm400

John - hoe exactly do I run this code in access? Sorry Iam new to a lot of
this, so please bear with me. Do I create a new macro?
Thanks
Gary

:

Excel offers no advantage. It's necessary to write code open each file
individually, read it a line at a time, add the file name, and then
write the modified line to the new file.

Here is a VBScript from my library that does the job for a single file
and could be modified (sorry, I'm late for the day job and can't do it
myself) to process multiple files (and to run in Access VBA).

=========================START OF VBScript
'Prepend FN.vbs: adds the filename to each line of the file
'VBScript
'Call from commandline as
' [cscript] Prepend.vbs InFile OutFile

Option Explicit

Dim fso 'As FileSystemObject
Dim fIn 'As TextStream
Dim fOut 'As TextStream
Dim strLine 'As String
Dim strFileName 'As String
Dim DELIM 'As String

DELIM = "," 'String to separate the Filename
'from the rest of the line. Change this
'as needed: e.g. to Chr(9) for Tab

Set fso = CreateObject("Scripting.FileSystemObject")
Set fIn = fso_OpenTextFile(WScript.Arguments(0))
Set fOut = fso.CreateTextFile(WScript.Arguments(1))
strFileName = WScript.Arguments(0)

Do Until fIn.AtEndOfStream
fOut.Write strFileName & DELIM & strLine
Loop

fIn.Close
fOut.Close
===================END OF VBScript

And here (this is why I prefer Perl for little utilities) is a Perl
script that does handle multiple files:

===================START OF CODE
#PrependFN.pl
#Usage:
# perl PrependFN.pl Infilespec Outfile
#
#Infilespec can be a wildcard filespec.

$outfile = pop @ARGV;
open OUTFILE, ">$outfile" or die "Couldn't open $outfile";

foreach $file (<$ARGV[0]>) {
open INFILE, $file or die "Couldn't open $file";
while(<INFILE>) {
print OUTFILE qq("$file",$_)
}
}
===================END OF CODE


On Tue, 20 Sep 2005 10:50:03 -0700, ktm400

I have several hundred .csv files that Iam trying to combine in one file. I
can simply use the dos copy command to create a new file with the contents of
all these other files, but the problem with that is i cannot identify which
file is which in the new file.
Is there a way to do this with excel?
Thanks for any help
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Adding data to MS Access from csv file 0
Sub projects added as read only in Master file which are saved on sharepoint 1
Descenders clipped 0
Synchronization problems while moving folders to new email 1
Closing PDF file from Excel VBA (Adobe Reader, Adobe Acrobat, Browser) 0
Problems created by Recent updates to Outlook 365 0
Re-title multiple MS Project .mpp files at once 0
Trying to create custom labels from an csv file + Need items numerically counted out 0

Top