Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion GroupsFormsForms ProgrammingQueriesModules / DAO / VBAReports / PrintingMacrosDatabase DesignSecurityConversionImporting / LinkingSQL Server / ADPMultiuser / NetworkingReplicationSetup / ConfigurationDeveloper ToolkitsActiveX ControlsNew UsersGeneral 1General 2
Access DirectoryToolsTutorialsUser Groups
Related Topics
SQL ServerOther DB ProductsMS OfficeMore Topics ...
MS Access Forum / Importing / Linking / September 2005

Tip: Looking for answers? Try searching our database.

Creating one file out of many, including the filename

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
ktm400 - 20 Sep 2005 18:50 GMT
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
Reply to this Message
John Nurick - 21 Sep 2005 08:10 GMT
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

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
Reply to this Message
ktm400 - 21 Sep 2005 15:44 GMT
Thank you John

> 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
[quoted text clipped - 68 lines]
>
> Please respond in the newgroup and not by email.
Reply to this Message
ktm400 - 21 Sep 2005 17:10 GMT
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
[quoted text clipped - 68 lines]
>
> Please respond in the newgroup and not by email.
Reply to this Message
John Nurick - 21 Sep 2005 21:08 GMT
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?
[quoted text clipped - 73 lines]
>>
>> Please respond in the newgroup and not by email.

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
Reply to this Message
ktm400 - 21 Sep 2005 22:44 GMT
Thank you very much John

> Here's one (rather inefficient) way which will probably work provided
> your csv files aren't too big.
[quoted text clipped - 150 lines]
>
> Please respond in the newgroup and not by email.
Reply to this Message
ktm400 - 22 Sep 2005 15:00 GMT
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.
[quoted text clipped - 150 lines]
>
> Please respond in the newgroup and not by email.
Reply to this Message
John Nurick - 22 Sep 2005 18:42 GMT
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.
[quoted text clipped - 206 lines]
>>
>> Please respond in the newgroup and not by email.

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
Reply to this Message
ktm400 - 22 Sep 2005 18:59 GMT
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?

> So what does happen?
>
[quoted text clipped - 218 lines]
>
> Please respond in the newgroup and not by email.
Reply to this Message
John Nurick - 22 Sep 2005 21:54 GMT
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
[quoted text clipped - 230 lines]
>>
>> Please respond in the newgroup and not by email.

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
Reply to this Message
ktm400 - 26 Sep 2005 16:14 GMT
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

> You need to end strFolder with a \:
>
[quoted text clipped - 248 lines]
>
> Please respond in the newgroup and not by email.
Reply to this Message
John Nurick - 26 Sep 2005 19:34 GMT
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:

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

==========Start of code=========================================
Public Sub PrependFileNameAndConcatenate( _
  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========================================

>OK John, did this.
>Something is definately happening but I still get no file.
[quoted text clipped - 254 lines]
>>
>> Please respond in the newgroup and not by email.

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
Reply to this Message
ktm400 - 26 Sep 2005 22:09 GMT
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")

> Having got home early tonight I've had time to write a proper little VBA
> procedure, which was easier than troubleshooting the cobbled-together
[quoted text clipped - 267 lines]
> >> >> >> >>
> >> >> >> >> $outfile = pop @ARGV;
Reply to this Message
Douglas J. Steele - 26 Sep 2005 23:12 GMT
In John's code, he's declared the sub as

Public Sub PrependFileNameAndConcatenate( _
   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 PrependFileNameAndConcatenate("c:\boiler steam production\",
"c:\temp\outputfile.txt")

Signature

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

> Thanks for sticking with this John.
> Iam having a syntax problems with this part:
[quoted text clipped - 288 lines]
>> >> >> >> >>
>> >> >> >> >> $outfile = pop @ARGV;
Reply to this Message
ktm400 - 27 Sep 2005 14:39 GMT
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
PrependFileNameAndConcatenate. I then opened the immediate pane and pasted
this line into the window....
Call PrependFileNameAndConcatenate("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 PrependFileNameAndConcatenate is not available for selection.
I really appreciate the patience you have shown thus far John.
Thanks
Gary

> In John's code, he's declared the sub as
>
[quoted text clipped - 252 lines]
> >> >> >> >> >>
> >> >> >> >> >> Here is a VBScript from my library that does the job for a
Reply to this Message
John Nurick - 27 Sep 2005 21:47 GMT
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".

>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
[quoted text clipped - 265 lines]
>> >> >> >> >> >>
>> >> >> >> >> >> Here is a VBScript from my library that does the job for a

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
Reply to this Message
ktm400 - 27 Sep 2005 22:31 GMT
The module is now called Module1
Contents look like this:

Option Compare Database
Option Explicit

Public Sub PrependFilenameAndConcatenate( _
  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 PrependFileNameAndConcatenate("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?

> If the first two lines are
>     Option Explicit
[quoted text clipped - 252 lines]
> >> >> >> >> >> Set fOut = fso.OpenTextFile(WScript.Arguments(1), 8, True)
> >> >> >> >> >> strFileName = WScript.Arguments(0)
Reply to this Message
John Nurick - 28 Sep 2005 06:45 GMT
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.

>In the immediate window, I have this:
>Call PrependFileNameAndConcatenate("c:\boiler steam production\",
[quoted text clipped - 9 lines]
>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.
Reply to this Message
ktm400 - 28 Sep 2005 14:26 GMT
It worked! Thanks John

> 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.
[quoted text clipped - 17 lines]
>
> Please respond in the newgroup and not by email.
Reply to this Message
ktm400 - 28 Sep 2005 20:35 GMT
Ummmm....is there a way to concatenate by the date the files were created and
leave the file extension off?

> It worked! Thanks John
>
[quoted text clipped - 19 lines]
> >
> > Please respond in the newgroup and not by email.
Reply to this Message
John Nurick - 29 Sep 2005 06:57 GMT
>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 & ""","

>> It worked! Thanks John
>>
[quoted text clipped - 19 lines]
>> >
>> > Please respond in the newgroup and not by email.

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
Reply to this Message
ktm400 - 29 Sep 2005 19:46 GMT
Awesome! Thanks again John

> >Ummmm....is there a way to concatenate by the date the files were created
>
[quoted text clipped - 61 lines]
>
> Please respond in the newgroup and not by email.
Reply to this Message
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage
Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.