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

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
 
P

Piet Linden

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.
 
G

Gina Whipp

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
 
M

mhmaid

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)
 
M

mhmaid

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
 
M

mhmaid

Hi Whipp,

the Extension is xls

saved as type :text (tab delimited)


both the Path and folder Name are Ok.
 
G

Gina Whipp

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
 
D

Douglas J. Steele

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.
 
G

Gina Whipp

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
 
D

Douglas J. Steele

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.
 
M

mhmaid

if i change to this i get run time error 3274,external file is not in the
expected format
 
D

Douglas J. Steele

You _sure_, then, that it was saved as "text (tab delimited)"?

What happens if you try opening the file using Notepad?
 
M

mhmaid

yes , i have tried this and its openning in note pad.
what could be the problem then with the code.
 
M

mhmaid

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
 
D

Douglas J. Steele

Hmm. That should work.

Can you import a file manually (as opposed to through code)?
 
M

mhmaid

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
 
D

Douglas J. Steele

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?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Import File Information 0
Importing txt file 7
how to import multiple Excel files into multiple Access tables? 9
Add file name to table when importing several txt files using an- 2
Importing Excel spreadsheets from a folder 5
Problem with access acImportDelim importing csv with headers 1
Select Folder and Import from all Excel files in Folder 4
Import mutiple name text files into Access 2003 1

Top