importing filename and content of file

[susannne]

hi all,

im searching for a solution to import several hundred files into an access tbl.

it are .txt files, with a specific name, which should be together with the content be imported.

bruno.txt
[
234
456
345]

and i would like to see the table like:

name |costs
-----------------
bruno 234
bruno 456
bruno 345

i allready found a way to only im port the content, but to import the filename also, which is very important doesnt work.


DoCmd.TransferText acImportDelim, , "tblTmp", Me!Path + "bruno.txt"

that will together with a

DB.Execute "INSERT INTO tblCosts(costs) " & _
"select F1 from tblTmp"

do it.

can anyone help ?

Thx

 

[ShiftyJaffa]

Hi Susanne

Try this assuming you have a text box on your form called txtFileName and its contents are 'bruno.txt' :-

DoCmd.TransferText acImportDelim, , "tblTmp", Me!Path + txtFileName

DB.Execute "INSERT INTO tblCosts (name,costs) " & _
"select '" & left(txtFileName,len(txtFileName)-4) & "', F1 from tblTmp"

This should be fine aslong as your filename has 4 surplus characters (ie .TXT) on the end to get rid of. Also, I'm not sure if you'll have problems with 'name' as a field, you might consider changing it in the table to txtName or if not using paranthesis [name] etc.

Hope that helps

Paul

 

[WayneRyan]

Susanne,

I think that you need VBA code to do this:

Dim dbs As DAO.Database
Dim rst As DAO.RecordSet

Dim strFile As String
Dim strFileName As String
Dim strPath as String
Dim strBuffer As String

Dim blnMore As Boolean

strPath = "C:\SomeDir\"

Set dbs = CurrentDb
Set rst = dbs.OpenRecordSet("YourTable")

strFile = Dir(strPath & "*.txt")
While strFile <> ""
   ' Open a text file
   Open strPath & strFile For Input As #1
   ' Read the name
   Line Input #1, strBuffer
   ' Fix the Name
   strFileName = Mid(strBuffer, 1, Len(strBuffer) - 4)
   ' Skip the "["
   Line Input #1, strBuffer
   ' Get All Numbers 
   Line Input #1, strBuffer
   blnMore = True
   ' Read in all numbers until one has a "]"
   While blnMore
      If Instr(1, strBuffer, "]") > 0 Then
         strBuffer = Mid(strBuffer, 1, Len(strBuffer) - 1)
         blnMore = False
      End If
      ' Put into your table
      rst.AddNew
      rst!FileName = strFileName
      rst!TheNumber = strBuffer
      rst.Update
      If blnMore Then Line Input #1, strBuffer
      Wend
   ' Process Next File
   Close #1
   strFile = Dir()
   Wend

Wayne