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
