MS Access Database with External Data - Creating one file out of many, including the filename

Author

Creating one file out of many, including the filename

ktm400

2005-09-20, 1:25 pm

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

John Nurick

2005-09-21, 3:24 am

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(WScri
pt.Arguments(1))
strFileName = WScript.Arguments(0)

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

fIn.Close
fOut.Close
===================E
ND OF VBScript

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

===================S
TART 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",$_)
}
}
===================E
ND OF CODE

On Tue, 20 Sep 2005 10:50:03 -0700, ktm400
<ktm400@discussions.microsoft.com> wrote:

>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

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.

ktm400

2005-09-21, 9:49 am

Thank you John

"John Nurick" wrote:

> 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(WScri
pt.Arguments(1))
> strFileName = WScript.Arguments(0)
>
> Do Until fIn.AtEndOfStream
> fOut.Write strFileName & DELIM & strLine
> Loop
>
> fIn.Close
> fOut.Close
> ===================E
ND OF VBScript
>
> And here (this is why I prefer Perl for little utilities) is a Perl
> script that does handle multiple files:
>
> ===================S
TART 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",$_)
> }
> }
> ===================E
ND OF CODE
>
>
> On Tue, 20 Sep 2005 10:50:03 -0700, ktm400
> <ktm400@discussions.microsoft.com> wrote:
>
>
> --
> John Nurick [Microsoft Access MVP]
>
> Please respond in the newgroup and not by email.
>
>

ktm400

2005-09-21, 11:25 am

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" wrote:

> 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(WScri
pt.Arguments(1))
> strFileName = WScript.Arguments(0)
>
> Do Until fIn.AtEndOfStream
> fOut.Write strFileName & DELIM & strLine
> Loop
>
> fIn.Close
> fOut.Close
> ===================E
ND OF VBScript
>
> And here (this is why I prefer Perl for little utilities) is a Perl
> script that does handle multiple files:
>
> ===================S
TART 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",$_)
> }
> }
> ===================E
ND OF CODE
>
>
> On Tue, 20 Sep 2005 10:50:03 -0700, ktm400
> <ktm400@discussions.microsoft.com> wrote:
>
>
> --
> John Nurick [Microsoft Access MVP]
>
> Please respond in the newgroup and not by email.
>
>

John Nurick

2005-09-21, 8:25 pm

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 ConcatenateCSVWithFi
lenames()
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.

'===================
==============Update
d 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
<ktm400@discussions.microsoft.com> wrote:
[color=darkred]
>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" wrote:
>

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.

ktm400

2005-09-21, 8:25 pm

Thank you very much John

"John Nurick" wrote:

> 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 ConcatenateCSVWithFi
lenames()
> 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.
>
> '===================
==============Update
d 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
> <ktm400@discussions.microsoft.com> wrote:
>
>
> --
> John Nurick [Microsoft Access MVP]
>
> Please respond in the newgroup and not by email.
>
>

ktm400

2005-09-22, 9:24 am

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 ConcatenateCSVWithFi
lenames()
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\Prepend
FN.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" wrote:

> 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 ConcatenateCSVWithFi
lenames()
> 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.
>
> '===================
==============Update
d 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
> <ktm400@discussions.microsoft.com> wrote:
>
>
> --
> John Nurick [Microsoft Access MVP]
>
> Please respond in the newgroup and not by email.
>
>

John Nurick

2005-09-22, 1:24 pm

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
<ktm400@discussions.microsoft.com> wrote:
[color=darkred]
>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 ConcatenateCSVWithFi
lenames()
> 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\Prepend
FN.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" wrote:
>

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.

ktm400

2005-09-22, 1:24 pm

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
ConcatenateCSVWithFi
lenames()" 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" wrote:

> 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
> <ktm400@discussions.microsoft.com> wrote:
>
>
> --
> John Nurick [Microsoft Access MVP]
>
> Please respond in the newgroup and not by email.
>
>

John Nurick

2005-09-22, 8:25 pm

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.

On Thu, 22 Sep 2005 10:59:03 -0700, ktm400
<ktm400@discussions.microsoft.com> wrote:
[color=darkred]
>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
> ConcatenateCSVWithFi
lenames()" 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" wrote:
>

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.

ktm400

2005-09-26, 11:24 am

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" wrote:

> 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.
>
> On Thu, 22 Sep 2005 10:59:03 -0700, ktm400
> <ktm400@discussions.microsoft.com> wrote:
>
>
> --
> John Nurick [Microsoft Access MVP]
>
> Please respond in the newgroup and not by email.
>
>

John Nurick

2005-09-26, 8:25 pm

Having got home early tonight I've had time to write a proper little VBA
procedure, which was easier than troubleshooting the cobbled-together
stuff we had before. Paste this into a module in your database, and then
you can run it from the Immediate pane with something like this:

PrependFileNameAndCo
ncatenate "C:\boiler steam production",
"C:\temp\combined.csv", "*.csv"

==========Start of code================
====================
=====
Public Sub PrependFileNameAndCo
ncatenate( _
ByVal TheFolder As String, _
OutputFile As String, _
Optional FileSpec As String = "*.csv")

'Reads all files in TheFolder that match FileSpec.
'Concatenates them to OutputFile, prepending the filename
'to each line.

Dim lngIn As Long
Dim lngOut As Long
Dim strFN As String
Dim strLine As String

lngOut = FreeFile()
Open OutputFile For Output As #lngOut

'Make sure folder name ends with \
If Right(TheFolder, 1) <> "\" Then
TheFolder = TheFolder & "\"
End If

strFN = Dir(TheFolder & FileSpec)
Do While Len(strFN) > 0 'loop through all files
lngIn = FreeFile()
Debug.Print "Processing " & strFN
Open TheFolder & strFN For Input As #lngIn
'enclose file name in quotes
strFN = """" & strFN & ""","
Do Until EOF(lngIn) 'loop through lines in files
Line Input #lngIn, strLine
Print #lngOut, strFN & strLine
Loop
Close #lngIn
strFN = Dir() 'get next filename
Loop
Close #lngOut
End Sub
=============End of code================
====================
====

On Mon, 26 Sep 2005 08:14:02 -0700, ktm400
<ktm400@discussions.microsoft.com> wrote:
[color=darkred]
>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" wrote:
>

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.

ktm400

2005-09-26, 8:25 pm

Thanks for sticking with this John.
Iam having a syntax problems with this part:
ByVal c:\boiler steam production\ As String, _
c:\temp\outputfile.txt As String, _
Optional FileSpec As String = "*.csv")

"John Nurick" wrote:
[color=darkred]
>
> Having got home early tonight I've had time to write a proper little VBA
> procedure, which was easier than troubleshooting the cobbled-together
> stuff we had before. Paste this into a module in your database, and then
> you can run it from the Immediate pane with something like this:
>
> PrependFileNameAndCo
ncatenate "C:\boiler steam production",
> "C:\temp\combined.csv", "*.csv"
>
> ==========Start of code================
====================
=====
> Public Sub PrependFileNameAndCo
ncatenate( _
> ByVal TheFolder As String, _
> OutputFile As String, _
> Optional FileSpec As String = "*.csv")
>
> 'Reads all files in TheFolder that match FileSpec.
> 'Concatenates them to OutputFile, prepending the filename
> 'to each line.
>
> Dim lngIn As Long
> Dim lngOut As Long
> Dim strFN As String
> Dim strLine As String
>
> lngOut = FreeFile()
> Open OutputFile For Output As #lngOut
>
> 'Make sure folder name ends with \
> If Right(TheFolder, 1) <> "\" Then
> TheFolder = TheFolder & "\"
> End If
>
> strFN = Dir(TheFolder & FileSpec)
> Do While Len(strFN) > 0 'loop through all files
> lngIn = FreeFile()
> Debug.Print "Processing " & strFN
> Open TheFolder & strFN For Input As #lngIn
> 'enclose file name in quotes
> strFN = """" & strFN & ""","
> Do Until EOF(lngIn) 'loop through lines in files
> Line Input #lngIn, strLine
> Print #lngOut, strFN & strLine
> Loop
> Close #lngIn
> strFN = Dir() 'get next filename
> Loop
> Close #lngOut
> End Sub
> =============End of code================
====================
====
>
>
> On Mon, 26 Sep 2005 08:14:02 -0700, ktm400
> <ktm400@discussions.microsoft.com> wrote:
>

Douglas J. Steele

2005-09-26, 8:25 pm

In John's code, he's declared the sub as

Public Sub PrependFileNameAndCo
ncatenate( _
ByVal TheFolder As String, _
OutputFile As String, _
Optional FileSpec As String = "*.csv")

You do not change those, replacing the variable names with your values.
Instead, you call his routine, passing it the values:

Call PrependFileNameAndCo
ncatenate("c:\boiler steam production\",
"c:\temp\outputfile.txt")

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

"ktm400" <ktm400@discussions.microsoft.com> wrote in message
news:13419ADF-AC21-4F96-8142- 573DF3548B45@microso
ft.com...[color=darkred]
> Thanks for sticking with this John.
> Iam having a syntax problems with this part:
> ByVal c:\boiler steam production\ As String, _
> c:\temp\outputfile.txt As String, _
> Optional FileSpec As String = "*.csv")
>
> "John Nurick" wrote:
>

ktm400

2005-09-27, 9:24 am

OK - I have copied Johns code and pasted into a new module deleting the first
2 lines that access puts in there.......I saved this module as
PrependFileNameAndCo
ncatenate. I then opened the immediate pane and pasted
this line into the window....
Call PrependFileNameAndCo
ncatenate("c:\boiler steam production\",
"c:\temp\outputfile.txt")
When I try to run it, it pops a window up asking which module to run.....and
there are none to select.
For some reason PrependFileNameAndCo
ncatenate is not available for selection.
I really appreciate the patience you have shown thus far John.
Thanks
Gary

"Douglas J. Steele" wrote:
[color=darkred]
> In John's code, he's declared the sub as
>
> Public Sub PrependFileNameAndCo
ncatenate( _
> ByVal TheFolder As String, _
> OutputFile As String, _
> Optional FileSpec As String = "*.csv")
>
> You do not change those, replacing the variable names with your values.
> Instead, you call his routine, passing it the values:
>
> Call PrependFileNameAndCo
ncatenate("c:\boiler steam production\",
> "c:\temp\outputfile.txt")
>
>
> --
> Doug Steele, Microsoft Access MVP
> http://I.Am/DougSteele
> (no e-mails, please!)
>
>
>
> "ktm400" <ktm400@discussions.microsoft.com> wrote in message
> news:13419ADF-AC21-4F96-8142- 573DF3548B45@microso
ft.com...

John Nurick

2005-09-27, 8:25 pm

If the first two lines are
Option Explicit
Option Database
put them back.

The other thing is that you can call a module just about anything
*except* the same name as a function. Rename the module to solve this
problem; many people start module names with a prefix such as "vb" or
"bas".

On Tue, 27 Sep 2005 06:39:04 -0700, ktm400
<ktm400@discussions.microsoft.com> wrote:
[color=darkred]
>OK - I have copied Johns code and pasted into a new module deleting the first
>2 lines that access puts in there.......I saved this module as
> PrependFileNameAndCo
ncatenate. I then opened the immediate pane and pasted
>this line into the window....
>Call PrependFileNameAndCo
ncatenate("c:\boiler steam production\",
>"c:\temp\outputfile.txt")
>When I try to run it, it pops a window up asking which module to run.....and
>there are none to select.
>For some reason PrependFileNameAndCo
ncatenate is not available for selection.
>I really appreciate the patience you have shown thus far John.
>Thanks
>Gary
>
>
>"Douglas J. Steele" wrote:
>

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.

ktm400

2005-09-27, 8:25 pm

The module is now called Module1
Contents look like this:

Option Compare Database
Option Explicit

Public Sub PrependFilenameAndCo
ncatenate( _
ByVal TheFolder As String, _
OutputFile As String, _
Optional FileSpec As String = "*.csv")

'Reads all files in TheFolder that match FileSpec.
'Concatenates them to OutputFile, prepending the filename
'to each line.

Dim lngIn As Long
Dim lngOut As Long
Dim strFN As String
Dim strLine As String

lngOut = FreeFile()
Open OutputFile For Output As #lngOut

'Make sure folder name ends with \
If Right(TheFolder, 1) <> "\" Then
TheFolder = TheFolder & "\"
End If

strFN = Dir(TheFolder & FileSpec)
Do While Len(strFN) > 0 'loop through all files
lngIn = FreeFile()
Debug.Print "Processing " & strFN
Open TheFolder & strFN For Input As #lngIn
'enclose file name in quotes
strFN = """" & strFN & ""","
Do Until EOF(lngIn) 'loop through lines in files
Line Input #lngIn, strLine
Print #lngOut, strFN & strLine
Loop
Close #lngIn
strFN = Dir() 'get next filename
Loop
Close #lngOut
End Sub

In the immediate window, I have this:
Call PrependFileNameAndCo
ncatenate("c:\boiler steam production\",
"c:\temp\outputfile.txt")

When I go to run, a window asks for the module to run. Module1 is not shown.
If I type in Module1 and hit enter, the following statement is appended to my
module1 code:
Sub module1()

End Sub

Should Module1 be showing up when I go to run? Why do I need the immediate
window?

"John Nurick" wrote:
[color=darkred]
> If the first two lines are
> Option Explicit
> Option Database
> put them back.
>
> The other thing is that you can call a module just about anything
> *except* the same name as a function. Rename the module to solve this
> problem; many people start module names with a prefix such as "vb" or
> "bas".
>
> On Tue, 27 Sep 2005 06:39:04 -0700, ktm400
> <ktm400@discussions.microsoft.com> wrote:
>

John Nurick

2005-09-28, 3:25 am

All you have to do is type the command into the Immediate pane and hit
Enter. You don't have to use the Run menu or select a module.

On Tue, 27 Sep 2005 14:31:11 -0700, ktm400
<ktm400@discussions.microsoft.com> wrote:

>In the immediate window, I have this:
>Call PrependFileNameAndCo
ncatenate("c:\boiler steam production\",
>"c:\temp\outputfile.txt")
>
>When I go to run, a window asks for the module to run. Module1 is not shown.
>If I type in Module1 and hit enter, the following statement is appended to my
>module1 code:
>Sub module1()
>
>End Sub
>
>Should Module1 be showing up when I go to run? Why do I need the immediate
>window?
>

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.

ktm400

2005-09-28, 9:24 am

It worked! Thanks John

"John Nurick" wrote:
> All you have to do is type the command into the Immediate pane and hit
> Enter. You don't have to use the Run menu or select a module.
>
> On Tue, 27 Sep 2005 14:31:11 -0700, ktm400
> <ktm400@discussions.microsoft.com> wrote:
>
>
> --
> John Nurick [Microsoft Access MVP]
>
> Please respond in the newgroup and not by email.
>
>

ktm400

2005-09-28, 8:26 pm

Ummmm....is there a way to concatenate by the date the files were created and
leave the file extension off?

"ktm400" wrote:
[color=darkred]
> It worked! Thanks John
>
> "John Nurick" wrote:

John Nurick

2005-09-29, 3:25 am

On Wed, 28 Sep 2005 12:35:07 -0700, ktm400
<ktm400@discussions.microsoft.com> wrote:

>Ummmm....is there a way to concatenate by the date the files were created

Do you mean only concatenate files created on a certain date (if so,
just move all other files out of the folder); concatenate files in date
order; include the date as well as the file name in each record; or
something else?

For that matter, do you mean the date the file was created, or the date
the file was last modified? You can get the latter by using something
like this

Dim strTimeStamp As String
...
strTimeStamp = Format(FileDateTime(
TheFolder & strFN), "yyyy-mm-dd")

The "strTimeStamp = "line must be inserted in the code after the
existing line
Do While Len(strFN) > 0 'loop through all files
but before you open the file with
Open TheFolder & strFN For Input As #lngIn

Having got the date, add it to the
Print #lngOut
line, making sure to include a separating comma between it and the next
field.

> and
>leave the file extension off?

Do it just before you enclose the filename in quotes:

'chop off the file extension (which is in FileSpec)
strFN = Left(strFN, Len(strFN) - Len(FileSpec))
'enclose file name in quotes
strFN = """" & strFN & ""","

[color=darkred]
>
>"ktm400" wrote:
>

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.

ktm400

2005-09-29, 8:25 pm

Awesome! Thanks again John

"John Nurick" wrote:

> On Wed, 28 Sep 2005 12:35:07 -0700, ktm400
> <ktm400@discussions.microsoft.com> wrote:
>
>
> Do you mean only concatenate files created on a certain date (if so,
> just move all other files out of the folder); concatenate files in date
> order; include the date as well as the file name in each record; or
> something else?
>
> For that matter, do you mean the date the file was created, or the date
> the file was last modified? You can get the latter by using something
> like this
>
> Dim strTimeStamp As String
> ...
> strTimeStamp = Format(FileDateTime(
TheFolder & strFN), "yyyy-mm-dd")
>
> The "strTimeStamp = "line must be inserted in the code after the
> existing line
> Do While Len(strFN) > 0 'loop through all files
> but before you open the file with
> Open TheFolder & strFN For Input As #lngIn
>
> Having got the date, add it to the
> Print #lngOut
> line, making sure to include a separating comma between it and the next
> field.
>
>
>
> Do it just before you enclose the filename in quotes:
>
> 'chop off the file extension (which is in FileSpec)
> strFN = Left(strFN, Len(strFN) - Len(FileSpec))
> 'enclose file name in quotes
> strFN = """" & strFN & ""","
>
>
>
>
> --
> John Nurick [Microsoft Access MVP]
>
> Please respond in the newgroup and not by email.
>
>

Sponsored Links