Re: Creating one file out of many, including the filename

Tech-Archive recommends: Fix windows errors by optimizing your registry

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
<ktm400@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:

>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(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
>> <ktm400@xxxxxxxxxxxxxxxxxxxxxxxxx> 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.
>>
>>

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.

.

Relevant Pages

  • Re: Filename variable
    ... strFileName is a string--it should be used to hold the name of the ... 'Dim FSO As Scripting.FileSystemObject ... Dim strFileName As String ... sFileName + 1), 1) ...
    (microsoft.public.excel.programming)
  • Re: Script: Remote shutdown of all domain computers
    ... When trying to run the script from a XP workstation, where i am logged in as ... Dim strBase, strFilter, strAttributes, strQuery, objRecordSet ... 2000 or above and allow shutdown, ... Public Function AllComputersAs String() ...
    (microsoft.public.windows.server.scripting)
  • Re: Execute macro for all documents in the folder
    ... It is simple enough to batch process a single folder. ... Dim strFileName As String ...
    (microsoft.public.word.docmanagement)
  • Search pattern
    ... Dim strfile As String ... Dim bAddressFound As Boolean ... Dim strCurrentChar As String ...
    (comp.databases.ms-access)
  • Re: Access97 - str vars not recognized but hardcoded string is... HELP
    ... > Function ng_Step2(pstrFileName As String) As Integer ... > Dim strTempFileName As String ... > Dim strFileName As String ... > Dim rxw As Integer ...
    (microsoft.public.access.macros)