Using a general File Spec in VB to import many tables

  • Thread starter Thread starter Jose I. Avila
  • Start date Start date
J

Jose I. Avila

All,

Is there a way to import many different tables using a SINGLE import file
specification using VB in Access 2000?


Thanks,

Jose

**************************************
Private Sub CmdStart_Click()
Const ForReading = 1
Dim fs, f, fullpath
Dim FileName As String ' file name
Dim fname As String ' table name = file name without the .txt
Dim PathFile

' Define text file
Set fs = CreateObject("Scripting.FileSystemObject")
fullpath = "Z:\CM System Files\data\files.txt"
Set f = fs.OpenTextFile(fullpath, ForReading)

Do While Not f.AtEndOfStream
FileName = f.ReadLine
fname = StrReverse(Mid(StrReverse(FileName), 5))
PathFile = "z:\cm system files\data\" & FileName
DoCmd.TransferText acImportDelim, "General_Import_Spec", fname,
PathFile, True, ""

Loop
f.Close
Set f = Nothing
Set fs = Nothing
End Sub
**************************************
 
Jose said:
Is there a way to import many different tables using a SINGLE import file
specification using VB in Access 2000?
Click to expand...
**************************************
Private Sub CmdStart_Click()
Const ForReading = 1
Dim fs, f, fullpath
Dim FileName As String ' file name
Dim fname As String ' table name = file name without the .txt
Dim PathFile

' Define text file
Set fs = CreateObject("Scripting.FileSystemObject")
fullpath = "Z:\CM System Files\data\files.txt"
Set f = fs.OpenTextFile(fullpath, ForReading)

Do While Not f.AtEndOfStream
FileName = f.ReadLine
fname = StrReverse(Mid(StrReverse(FileName), 5))
PathFile = "z:\cm system files\data\" & FileName
DoCmd.TransferText acImportDelim, "General_Import_Spec", fname,
PathFile, True, ""

Loop
f.Close
Set f = Nothing
Set fs = Nothing
End Sub
Click to expand...

I don't understand the question, as long as you've set up
the import specification properly, what you have should work
(although I would would use File I/O instead of the file
system object).

If your question is how to create the import specification,
then you do that by manually going through the import
process manually and, after specifying the import options,
saving it with whatever name you want to use in the
TransferText method.
 
Not Exactly.

Your code does not seem to be written for generic files, so I am guessing
you want to extend this code.
Also guessing that your text files have different number of columns and data
types.
How do you plan to handle that?
What are the column names?

What you is achievable, just not with an import export spec.
You will also have to use text fields for all columns, and make them as wide
as the widest column

Use this code - not tested


Dim fs, f, fullpath, fol
Dim FileName As String ' file name
Dim fname As String ' table name = file name without the .txt
Dim PathFile
const Delimiter = vbtab
const ForReading = 1


' Define text file
Set fs = CreateObject("Scripting.FileSystemObject")
fullpath = "Z:\CM System Files\data\"
set Fol = fs.GetFolder(Fullpath)
set FC = Fol.Files
for each fi in FC ' files in Files collection
if right(fi.ShortName, 3) = "TXT" then
strFileName = FullPath & fi.ShortName
TableName = fi.Name
CurrFile = fs.openTextFile(strFileName, ForREading, False)
CurrLine = CurrFile.readline ' read the First Line
FieldNames = Split(CurrLine, delimiter)
'use the create tabledef method to create a new table and
' append the fields using column names from field names
'set field's datatype to varchar(255) or whatever you need
While not CurrFile.AtEndOfStream
CurrValues = split(CurrFile.ReadLine, Delimiter)
ssql = "Insert Into " & TableName & "Values ("
for i = 0 to ubound(CurrValues)
ssql = ssql & """ " & CurrValues(i) & ""","
Next
ssql = mid(ssql, 1, len(ssql) -1) & ")" 'remove the last comma
and close parens
db.execute (ssql)
Wend
End if
Next


f.Close
Set f = Nothing
Set fs = Nothing
End Sub
 
Jose,
You are welcome

I think your posting is incomplete.
As I had mentioned, my code is untested - it was a straight dump from my
mind.
I also did not create the tabledefs - you will have to do that. Ask me if
you are having trouble.

HS
 
Back
Top