Re: Importing All Files in a Directory

[ac]

Hi John, first, thanks for all the advice i can get from you.

I have created a table called "ABC" with fields: custID, Name, Contact

When executing the procedure below I got an error message: Field F1 not
defined in table "ABC".

Sub ImportAllFiles()
Dim strFileName As String
strFileName = Dir("f:\0voice\Current\New\Log\*.txt")
Do While strFileName <> ""
DoCmd.TransferText acImport, myimportspec, "CDR", strFileName
strFileName = Dir()
Loop
End Sub

What has gone wrong, it seems that the field name defined ealier using
wizard does not store in the spec. that's why it does not recognise
or convert the fields in the text file? I did a manual import using the spec
and it was ok.

Thanks
ac.

To re-phrash it:

could you tell me how i could create the import specs from access, i tried
searching for the saved template in the advanced tag during inport but can't
find in my PC. I need to use it as a template for all my files to be
imported.

These specifications are stored in the mdb file. You can use one in your
code by passing its name as an argument of DoCmd.TransferText, but as
far as I know you can't create or edit them under program control.

Read about schema.ini in the MSDN web but didn't say much. Do i need to
creat multiple schema.ini since i am importing CSV files with different name
as i read first line of the import spec (schema.ini) must be the file

names.

The best documentation for schema.ini I've found is at
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odbcjetsdk_98.asp

Schema.ini can have multiple sections each of which begins with the name
of the file. I believe that's intended to let you import files with
different structures by using different names for them; it's not much
use for importing files that have different names but the same
structure.

Perhaps the way round this would be to put the files to import in one
folder and schema.ini in another. Then have your code work through the
files as follows:

-Copy a file to the schema.ini folder, giving the copy the name that's
in schema.ini
-Import the copy
-Delete the copy
-Repeat with the next file.

ac. Thanks.

John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.

 

[ac]

1 correction:

Sub ImportAllFiles()
Dim strFileName As String
strFileName = Dir("f:\0voice\Current\New\Log\*.txt")
Do While strFileName <> ""
DoCmd.TransferText acImport, myimportspec, "ABC", strFileName '

******** Should be ABC instead of CDR

strFileName = Dir()
Loop
End Sub

ac.

Hi John, first, thanks for all the advice i can get from you.

I have created a table called "ABC" with fields: custID, Name, Contact

When executing the procedure below I got an error message: Field F1 not
defined in table "ABC".

Sub ImportAllFiles()
Dim strFileName As String
strFileName = Dir("f:\0voice\Current\New\Log\*.txt")
Do While strFileName <> ""
DoCmd.TransferText acImport, myimportspec, "CDR", strFileName
strFileName = Dir()
Loop
End Sub

What has gone wrong, it seems that the field name defined ealier using
wizard does not store in the spec. that's why it does not recognise
or convert the fields in the text file? I did a manual import using the spec
and it was ok.

Thanks
ac.

These specifications are stored in the mdb file. You can use one in your
code by passing its name as an argument of DoCmd.TransferText, but as
far as I know you can't create or edit them under program control.
names.

The best documentation for schema.ini I've found is at

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odbcjetsdk_98.asp

Schema.ini can have multiple sections each of which begins with the name
of the file. I believe that's intended to let you import files with
different structures by using different names for them; it's not much
use for importing files that have different names but the same
structure.

Perhaps the way round this would be to put the files to import in one
folder and schema.ini in another. Then have your code work through the
files as follows:

-Copy a file to the schema.ini folder, giving the copy the name that's
in schema.ini
-Import the copy
-Delete the copy
-Repeat with the next file.

read

the

MSDN web site about schema.ini but too brief to understand.
have you made it work for you with the schema.ini? how do you handle
multiple files with different names in schema.ini since you need to define
the
file name to be imported in the first line of the schema file.

Thanks for the advice.
ac.




Dim strFileName As String
strFileName = Dir("C:\MyFolder\*.txt")
Do While strFileName <> ""
DoCmd.TransferText acImport, SpecificationName, "TableName",
strFileName
strFileName = Dir()
Loop

--
Ken Snell
<MS ACCESS MVP>


I need to import all text files a directory into one
Access table.

I have created a specification file and a DoCmd for
TransferText. I can successfully import a single file,
but my loop routine is continuous. How do I establish a
routine that repeats until each file has been loaded.

John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.

 

[ac]

Thanks for the help.

I found the mistake was due to the spec. was not enclosed with "

cheers
ac

1 correction:

Sub ImportAllFiles()
Dim strFileName As String
strFileName = Dir("f:\0voice\Current\New\Log\*.txt")
Do While strFileName <> ""
DoCmd.TransferText acImport, myimportspec, "ABC", strFileName '

******** Should be ABC instead of CDR

strFileName = Dir()
Loop
End Sub

ac.

Hi John, first, thanks for all the advice i can get from you.

I have created a table called "ABC" with fields: custID, Name, Contact

When executing the procedure below I got an error message: Field F1 not
defined in table "ABC".

Sub ImportAllFiles()
Dim strFileName As String
strFileName = Dir("f:\0voice\Current\New\Log\*.txt")
Do While strFileName <> ""
DoCmd.TransferText acImport, myimportspec, "CDR", strFileName
strFileName = Dir()
Loop
End Sub

What has gone wrong, it seems that the field name defined ealier using
wizard does not store in the spec. that's why it does not recognise
or convert the fields in the text file? I did a manual import using the spec
and it was ok.

Thanks
ac.

but
can't different
name

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odbcjetsdk_98.asp

Schema.ini can have multiple sections each of which begins with the name
of the file. I believe that's intended to let you import files with
different structures by using different names for them; it's not much
use for importing files that have different names but the same
structure.

Perhaps the way round this would be to put the files to import in one
folder and schema.ini in another. Then have your code work through the
files as follows:

-Copy a file to the schema.ini folder, giving the copy the name that's
in schema.ini
-Import the copy
-Delete the copy
-Repeat with the next file.



ac. Thanks.

Hi all,
I am trying to do the same but not sure how to create the schema,

read

the
MSDN web site about schema.ini but too brief to understand.
have you made it work for you with the schema.ini? how do you handle
multiple files with different names in schema.ini since you need to define
the
file name to be imported in the first line of the schema file.

Thanks for the advice.
ac.




Dim strFileName As String
strFileName = Dir("C:\MyFolder\*.txt")
Do While strFileName <> ""
DoCmd.TransferText acImport, SpecificationName, "TableName",
strFileName
strFileName = Dir()
Loop

--
Ken Snell
<MS ACCESS MVP>


I need to import all text files a directory into one
Access table.

I have created a specification file and a DoCmd for
TransferText. I can successfully import a single file,
but my loop routine is continuous. How do I establish a
routine that repeats until each file has been loaded.






John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.