help please : importing many .txt files in a folder to one table

  • Thread starter Thread starter mhmaid
  • Start date Start date
M

mhmaid

I have found this code in this group , with thanks to the the one who created
it .but dont know how can i use it

Function fImportAllFiles()

Dim strfile As String
Dim strPath As String

'File path '
strPath = "YOUR FILE PATH HERE"

'Change the default directory to the file path
'
ChDir strPath

'Find the firsttext file
'
strfile = Dir("*.txt")

'Loop through the string & import the files
'
Do While Len(strfile) > 0


DoCmd.TransferText acImportDelim, , "TableName", strPath & "\" &
strfile

'delete the file (consider moving it to an Archive folder instead.)
'Kill strPath & "/" & strfile

'Call Dir to get the next file
'
strfile = Dir
Loop

End Function


I Have changed it to the following
Function fImportAllFiles()

Dim strfile As String
Dim strPath As String

'File path '
strPath = "C:\txtfiles"

'Change the default directory to the file path
'
ChDir strPath

'Find the firsttext file
'
strfile = Dir("*.txt")

'Loop through the string & import the files
'
Do While Len(strfile) > 0


DoCmd.TransferText acImportDelim, , "Patients", strPath & "\" &
strfile

'delete the file (consider moving it to an Archive folder instead.)
'Kill strPath & "/" & strfile

'Call Dir to get the next file
'
strfile = Dir
Loop

End Function


my files are stored in c:\txtfiles
i want to import them to the access table called patients
need urgent help
all files are xls sheets in the type txt ( tab delimited)
tried but failed
I am not able to import the files manually as I am getting the following
errer msg:
the wizard was unable to import the file.please make sure it exist and in
the correct format
thanks
 
I have found this code in this group , with thanks to the the one who created
it .but dont know how can i use it

Function fImportAllFiles()

    Dim strfile As String
    Dim strPath As String

    'File path     '
    strPath = "YOUR FILE PATH HERE"

    'Change the default directory to the file path
    '
    ChDir strPath

    'Find the firsttext file
    '
    strfile = Dir("*.txt")

    'Loop through the string & import the files
    '
    Do While Len(strfile) > 0

        DoCmd.TransferText acImportDelim, , "TableName", strPath & "\" &
strfile

     'delete the file (consider moving it to an Archive folder instead.)
      'Kill strPath & "/" & strfile

      'Call Dir to get the next file
      '
      strfile = Dir
    Loop

End Function

I Have changed it to the following
Function fImportAllFiles()

    Dim strfile As String
    Dim strPath As String

    'File path     '
    strPath = "C:\txtfiles"

    'Change the default directory to the file path
    '
    ChDir strPath

    'Find the firsttext file
    '
    strfile = Dir("*.txt")

    'Loop through the string & import the files
    '
    Do While Len(strfile) > 0

        DoCmd.TransferText acImportDelim, , "Patients", strPath &"\" &
strfile

     'delete the file (consider moving it to an Archive folder instead.)
      'Kill strPath & "/" & strfile

      'Call Dir to get the next file
      '
      strfile = Dir
    Loop

End Function

my files are stored in c:\txtfiles
i want to import them to the access table called patients
need urgent help
all files are xls sheets in the type txt ( tab delimited)
tried but failed
I am not able to import the files manually as I am getting the following
errer msg:
the wizard was unable to import the file.please make sure it exist and in
the correct  format
thanks
Click to expand...

1. Create the Patients table with all the fields you want imported
from the text files.
2. Run the import once manually. Click on the Advanced button, and
create and save your Import Specification.
3. Copy the above code and paste it into a module (behind a button,
maybe).

Sounds like you're really new to Access - how specific do you need?...
basically, you should create the table first, then import into it.
 
mhmaid,

Are the files *.txt OR *.xls (Excel) files? Because this is not making
sense to me... "...all files are xls sheets in the type txt ( tab
delimited)..." I think you mean they are Excel files and then you would
need to change TransferText to DoCmd.TransferSpeadsheet acImport, ,
"Patients", strPath & "\" & > strfile
and "...strfile = Dir("*.txt")..." to strfile = Dir("*.xls")

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm
 
thanks for response

actullay , the files are created in excel , but saved as file type text(tab
delimited)

in case I want to import a file manullay , I can do it by just openning the
excel sheet file first, then saving the file as microsoft file workbook , and
in this case data will be imported as wanted. but takes time if I Have large
number of files in the save format.

that is why I am searching for a code to do the job
so the files are created in excel 2003 , but the files saved as type "text
(tab delimited)
 
Hi
I am calling the Function thru a button in one form.

I get run time error 31518
and on the same error msg it says "you cannot import the file
note that this msg i got if the strfile to the following :
strfile = Dir("*.xls")

but if i keep it as strfile = Dir("*.txt")
i didnt get any error msg , but its not importing any data

my files are just excel workbooks , initially exported from an MDIS system,
the person who made that program is no more working with us now ,so i am
facing dificullty in openning every single file , and resaving it after
changing the file from tab delimited to microsoft excel workbook

thanks
 
Hi Whipp,

the Extension is xls

saved as type :text (tab delimited)


both the Path and folder Name are Ok.
 
mhmaid,

An .xls is an Excel file, not a text file. You need to amke the changes I
recomended earlier....

Change the TransferText line to...

DoCmd.TransferSpeadsheet acImport, , "Patients", strPath & "\" & strfile

and change

strfile = Dir("*.txt") to ...

strfile = Dir("*.xls")


--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm
 
Don't be so quick to say that, Gina. Where I work, we have mainframe
production jobs that generate tab-delimited files that are FTPed to the
servers with an extension of xls so that they'll automatically open in Excel
when the users click on them. It's ugly, but it works.
 
Douglas,

I learn something new everyday! Thanks for pointing that out!

Question... If you're running an automatic import which would you use .xls
with TransferText? Hmmmm, if the answer is yes then the OP might be back!

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm
 
If it's a text file, you'd need to use TransferText, regardless of what the
file extension is. Unfortunately, I suspect that you'd run into the
"read-only" error you get when you use a file extension that's not
registered for use with TransferText.

http://support.microsoft.com/?id=245407
http://support.microsoft.com/?id=306144

Easiest approach would be to rename the file, do the import using
TransferText, then rename the file back once it's been imported. If that's
not possible, you might have to create a copy of the file (giving the copy a
valid extension), then delete the file.
 
You _sure_, then, that it was saved as "text (tab delimited)"?

What happens if you try opening the file using Notepad?
 
yes , i have tried this and its openning in note pad.
what could be the problem then with the code.
 
Function fImportAllFiles()

Dim strfile As String
Dim strPath As String

'File path '
strPath = "C:\txtfiles"

'Change the default directory to the file path
'
ChDir strPath

'Find the firsttext file
'
strfile = Dir("*.txt")

'Loop through the string & import the files
'
Do While Len(strfile) > 0


DoCmd.TransferText acImportDelim, , "Patients", strPath & "\" &
strfile

'delete the file (consider moving it to an Archive folder instead.)
'Kill strPath & "/" & strfile

'Call Dir to get the next file
'
strfile = Dir
Loop

End Function
 
Hmm. That should work.

Can you import a file manually (as opposed to through code)?
 
i have tried that before ,getting the following err msg
the wizard was unable to access the file c:\textfiles,make sure that file
exist and it is in the correct format
 
I'm confused. In your code sample, you set strPath equal to "C:\txtfiles",
yet here your error message is talking about "file c:\textfiles"

What are the exact steps you're following when you try to import it
manually?
 
Back
Top