Display Records from multiple CSV files

[chris5g]

I have csv files that I use for my database. They all have the same field names (FName, LName, Address) but are created 2 or more times a month. I end up with files named 12Jun03.txt, 17Jun03.txt, and 23Jun03.txt.

I can use each file name just like table name. Is there a way to write an SQL statement that will get the FName, LName, and Address from all 3 files (tables)? Is this what JOIN is used for? Or am I writing this to the wrong forum.

Thanks in advance!

 

[Genimuse]

I'm pretty certain that each of the text files will effectively be considered its own database and that the standard ODBC drivers aren't going to allow you to join data across them.

If you are unable to perform the join you could use the FSO to create a temporary file that consists of all three files appended together and then perform your query on it.

 

[Fubear]

If you can access them as tables then the code to merge them would be:

SELECT * FROM tbl1
UNION
SELECT * FROM tbl2
UNION
SELECT * FROM tbl3
...etc

The hard part would be if you wanted to automatically pickup the most recent 3 tables or so.

 

[chris5g]

Thanks guys! I was going to go for the temp file but was too lazy to write all the extra code. I thought there was a way to do it in SQL but I couldn't remember.

Anyway, the UNION method worked perfectly!


Here is how I did it ....


Function FileNames()

Dim strFiles, objFSO
Set objFSO = Server.CreateObject("Scripting.FileSystemObject&quot

'Get the folder object associated with the directory
Dim objFolder
Set objFolder = objFSO.GetFolder(sPath)

'Loop through the Files collection and get my files
Dim objFile
For Each objFile in objFolder.Files
'Check that file name is in nb??mon?? format
if LCase(Left(objFile.Name,2)) = "nb" AND LCase(Mid(objFile.Name,5,3)) = LCase(MonthName(Month(Now), True)) then
strFiles = strFiles & objFile.Name & ","
end if
Next
'Remove trailing comma
strFiles = Left(strFiles,(Len(strFiles)-1))

strFiles = Split(strFiles,",&quot
dim i, tmp

for i = 0 to UBound(strFiles)
tmp = tmp & "Select title,author,Collection,call from " & strFiles(i)
if i < UBound(strFiles) then
tmp = tmp & &quot; Union &quot;
end if
next

FileNames = tmp

'Clean up!
Set objFolder = Nothing
Set objFile = Nothing
Set objFSO = Nothing

End Function