Importing files into Access

[globalstrata]

Every day, I generate some files that are named as follow:

file.100
file.101
file.102
...

Every day the amount of files generated may be different.
The data inside of the document is fixed width. How do I write a macro that imports all those files' content to the appropiate table? The appropiate table will be named the same as the machine where the file was generated from. The first line of each file, contains the name of the machine where that name was generated from.
Gladys Rodriguez
GlobalStrata Solutions

http://www.globalstrata.com

 

[psbsmms]

I just worked on this for a co-worker, for the below code to work the files all have to be in the same folder and you will have to retrieve the filenames and open them as a recordset to get the name off the fuirst record then you can use the code below to loop through the folder. once the file is no longer needed you can use the NAME funtion to move it so you don't import it twice. You might consider having the file name the name you want the table to be, it would simplify the coding.

Private Sub Command0_Click()

Dim MyFileMakeTable, MyFileTransfer, MyFile, MyPath, MyName, MyTable, NameTable
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strSql As String

MyFile = Dir("C:\Documents and Settings\Smithp\My Documents\test_Import\*.txt&quot[Smith (Contractor), Peter MR] this needs to be changed to where your files are

Do Until MyFile = ""
MyFileTransfer = "C:\Documents and Settings\Smithp\My Documents\test_Import\" & MyFile
[Smith (Contractor), Peter MR] This create the table based on your file name, but you first must set up the base table from your files and change the column names to F1, F2, F3 .... and either name it file1 or change the strsql to your file name
NameTable = InStr(MyFile, ".&quot
MyTable = Left(MyFile, (NameTable - 1))
strSql = "select * into [" & MyTable & "] from file1"
DoCmd.SetWarnings False
DoCmd.RunSQL (strSql)
[Smith (Contractor), Peter MR] To set up your base table use the file import wizard from the database window and click on advanced and save the import specifications as EdBrown. make sure the specs are what you want.
DoCmd.TransferText acImportDelim, "EdBrown", MyTable, MyFileTransfer
DoCmd.SetWarnings True
MyFile = Dir
Loop

End Sub

 

[globalstrata]

We are unable to control the name of the file when they are generated.

I know that Access has problems importing files that have a weird extension. This is why I a looking for a way to rename these files.

I also was looking to use this code but access is giving me an error 3625 saying that it cannot find schema.ini which I have saved in the file that I have all the data at. This is the code that I am using:

Function MyImport()

Dim DF As Object
Dim i As Variant

Set DF = Application.FileSearch

With DF
.LookIn = "C:\DnLoad"
.FileName = "*.txt"
If .Execute() > 0 Then
MsgBox "Files Found:" & .FoundFiles.Count
For i = 1 To .FoundFiles.Count
MsgBox "File Found:" & .FoundFiles(i)
DoCmd.TransferText acImportFixed,"schema.ini", "MyLog", .FoundFiles(i)
Next i
Else
MsgBox "Files not found.", vbExclamation
End With
End Function


I know this code should work if I figure out the acImportFixed line. Then all I have to worry about is how to rename the files to be with extension txt so this function can work.
Gladys Rodriguez
GlobalStrata Solutions

http://www.globalstrata.com

 

[coco86]

Okay, here's some code. It may need to be tweeked buy hopefully it helps. (you'll need to set a reference to the Microsoft Scripting Runtime Library)

Dim fso As New FileSystemObject
Dim fsoFolder As Folder
Dim fsoFile As File
Dim fsoTextStream As TextStream
Dim NewFilename As String
Dim FilePath As String
Dim MachineName As String

Filepath = ????
Set fsoFolder = fso.GetFolder(FilePath)
For Each fsoFile In fsoFolder.Files
NewFilename = Left(fsoFile.Name, Len(fsoFile.Name) - 3) & "txt"
fsoFile.Name = NewFilename
Next

Set fsoTextStream = fspenTextFile(FilePath & NewFilename)

MachineName = fsoTextStream.Read

DoCmd.TransferText acImportFixed,[Specification Name Here],MachineName, FilePath & NewFilename

Rather than a schema file, create an import specification. Import one of the files manually, entering all the information. Then before clicking "finished" click the "advanced" tab and save the specification. Use the name you give it in the transfertext function

 

[psbsmms]

If you have everything but the extension figured out why not just use the name function to rename. If they all have the same extension you could loop throught the directory with the Dir(path/*.extension) and dir commands. I agree with creating the specification file as opposed to the schema it is much easier, it should also take care of you extesion problem without changing.