Help Importing Multiple Text Files into Different Tables

[OJP]

Hello,
I have spent several hours adapting code I found to import multiple txt files
into different tables. Despite all my efforts, I keep getting the following
error code eventhough the file is there.

Run Time error 3011
The Microsoft Jet Database Engine could not find the object ".
Make sure the object exists and that you spell it's name and path correctly

The problem resides on the line:

DoCmd.TransferText acImportDelim, strTable, strPathFile, ynFieldName

I know everything else works fine as I have stepped through it.

Any help will be appreciated.

Here is the code:

Public Sub Import()

On Error GoTo Err_F

Dim strPathFile As String
Dim strFile As String
Dim strPath As String
Dim strSpec As String
Dim strRight7 As String

Dim strTable As String
Dim ynFieldName As Boolean

ynFieldName = False
strPath = "H:\GFI_TVM_PEM_Translation\TestEnv\TVM\TXT\"
strSpec = "TVMImportSpecs" ' This is my saved import specification
strFile = Dir(strPath & "*.txt")

Do While Len(strFile) > 0

strRight7 = Right(strFile, 7) ' The last 3 digits determine which
table gets the data

Select Case strRight7
Case "101.txt"
strTable = "TVM101"
GoTo Import_Data
Case "102.txt"
strTable = "TVM102"
GoTo Import_Data
Case "103.txt"
strTable = "TVM103"
GoTo Import_Data
Case "104.txt"
strTable = "TVM104"
GoTo Import_Data
Case "105.txt"
strTable = "TVM105"
GoTo Import_Data
Case "106.txt"
strTable = "TVM106"
GoTo Import_Data
Case "107.txt"
strTable = "TVM107"
GoTo Import_Data
Case Else
Exit Sub
End Select
'strFile = Dir()
'Loop

Import_Data:

strPathFile = strPath & strFile
'(((((((
' The next line of code is the problem
')))))))

DoCmd.TransferText acImportDelim, strTable, strPathFile, ynFieldName

Name strPath & strFile As "H:\GFI_TVM_PEM_Translation\TestEnv\TVM\
Archive\TXT\" & strFile 'Move the files to the archive folder
strFile = Dir()
Loop

Exit_F:
Exit Sub

Err_F:
MsgBox Err.Number & " " & Err.Description
Resume Exit_F

End Sub

 

[orange via AccessMonster.com]

Hello,
I have spent several hours adapting code I found to import multiple txt files
into different tables. Despite all my efforts, I keep getting the following
error code eventhough the file is there.

Run Time error 3011
The Microsoft Jet Database Engine could not find the object ".
Make sure the object exists and that you spell it's name and path correctly

The problem resides on the line:

DoCmd.TransferText acImportDelim, strTable, strPathFile, ynFieldName

I know everything else works fine as I have stepped through it.

Any help will be appreciated.

Here is the code:

Public Sub Import()

On Error GoTo Err_F

Dim strPathFile As String
Dim strFile As String
Dim strPath As String
Dim strSpec As String
Dim strRight7 As String

Dim strTable As String
Dim ynFieldName As Boolean

ynFieldName = False
strPath = "H:\GFI_TVM_PEM_Translation\TestEnv\TVM\TXT\"
strSpec = "TVMImportSpecs" ' This is my saved import specification
strFile = Dir(strPath & "*.txt")

Do While Len(strFile) > 0

strRight7 = Right(strFile, 7) ' The last 3 digits determine which
table gets the data

Select Case strRight7
Case "101.txt"
strTable = "TVM101"
GoTo Import_Data
Case "102.txt"
strTable = "TVM102"
GoTo Import_Data
Case "103.txt"
strTable = "TVM103"
GoTo Import_Data
Case "104.txt"
strTable = "TVM104"
GoTo Import_Data
Case "105.txt"
strTable = "TVM105"
GoTo Import_Data
Case "106.txt"
strTable = "TVM106"
GoTo Import_Data
Case "107.txt"
strTable = "TVM107"
GoTo Import_Data
Case Else
Exit Sub
End Select
'strFile = Dir()
'Loop

Import_Data:

strPathFile = strPath & strFile
'(((((((
' The next line of code is the problem
')))))))

DoCmd.TransferText acImportDelim, strTable, strPathFile, ynFieldName

Name strPath & strFile As "H:\GFI_TVM_PEM_Translation\TestEnv\TVM\
Archive\TXT\" & strFile 'Move the files to the archive folder
strFile = Dir()
Loop

Exit_F:
Exit Sub

Err_F:
MsgBox Err.Number & " " & Err.Description
Resume Exit_F

End Sub

DoCmd.TransferText acImportDelim, strTable, strPathFile, ynFieldName

From Access Help
docmd.TransferText(TransferType, SpecificationName, TableName, FileName,
HasFieldNames, HTMLTableName, CodePage)

Looks like you're missing the SpecificationName

 

[ojp via AccessMonster.com]

Hi Orange,
Thank you for your reply.

I went ahead and added the spec as noted (I removed it at some point while
testing the resto of the code and forgot to place it back in). The line in
question now reads:

DoCmd.TransferText acImportDelim, strSpec, strTable, strPathFile, ynFieldName

I stepped through the code with no change. I still got error 3011.

Any other ideas please?

Thank you!

Hello,
I have spent several hours adapting code I found to import multiple txt files

[quoted text clipped - 88 lines]

End Sub

DoCmd.TransferText acImportDelim, strTable, strPathFile, ynFieldName

From Access Help
docmd.TransferText(TransferType, SpecificationName, TableName, FileName,
HasFieldNames, HTMLTableName, CodePage)

Looks like you're missing the SpecificationName

 

[Ken Snell]

Just guessing, but I assume that the tables that get the imported data do
not have ".txt" in their names? This line of code is returning the last 7
characters of the full file name:

strRight7 = Right(strFile, 7) ' The last 3 digits determine which
table gets the data

For example, if the file is named "TV109ABC123.txt", your above code returns
"123.txt" as the strRight7 value.

I assume that you want the file name to be parsed without the ".txt"
portion. If yes, change the above line of code to this:

strRight7 = Right(Left(strFile, Len(strFile) - 4), 7) ' The last 3
digits determine which
table gets the data

 

[ojp via AccessMonster.com]

Hi Ken,

Thanks for your reply. The issue is not the way the code determines the
correct table to be used. On my code, I am reading the last 7 characters as
you noted. Then, on the Select statement I use those 7 characters to assign
the value to strTable and use the correct table. I am sure your code will
exclude the ".txt" portion of the file and will also work, but unfortunately
that will not resolve the problem with error code 3011.

I am just about to start testing with how the data is contained in the .txt
files. Maybe I initialized a field wrong? Maybe data is missing? who knows...
I will continue cranking it and post any new developments.

Any other ideas are welcome!

Just guessing, but I assume that the tables that get the imported data do
not have ".txt" in their names? This line of code is returning the last 7
characters of the full file name:

strRight7 = Right(strFile, 7) ' The last 3 digits determine which
table gets the data

For example, if the file is named "TV109ABC123.txt", your above code returns
"123.txt" as the strRight7 value.

I assume that you want the file name to be parsed without the ".txt"
portion. If yes, change the above line of code to this:

strRight7 = Right(Left(strFile, Len(strFile) - 4), 7) ' The last 3
digits determine which
table gets the data

Hello,
I have spent several hours adapting code I found to import multiple txt

[quoted text clipped - 92 lines]

End Sub

 

[Ken Snell]

Sorry, I overlooked your Select Case block.

Have you identified if the "object not found" is the table, or the file?
While stepping through the code, when you get to the
DoCmd.TransferSpreadsheet line, hover the cursor over the strPathFile
variable to see if it's the value you expect; do the same for strTable.

--

Ken Snell
http://www.accessmvp.com/KDSnell/

Hi Ken,

Thanks for your reply. The issue is not the way the code determines the
correct table to be used. On my code, I am reading the last 7 characters
as
you noted. Then, on the Select statement I use those 7 characters to
assign
the value to strTable and use the correct table. I am sure your code will
exclude the ".txt" portion of the file and will also work, but
unfortunately
that will not resolve the problem with error code 3011.

I am just about to start testing with how the data is contained in the
.txt
files. Maybe I initialized a field wrong? Maybe data is missing? who
knows...
I will continue cranking it and post any new developments.

Any other ideas are welcome!

Just guessing, but I assume that the tables that get the imported data do
not have ".txt" in their names? This line of code is returning the last 7
characters of the full file name:

strRight7 = Right(strFile, 7) ' The last 3 digits determine
which
table gets the data

For example, if the file is named "TV109ABC123.txt", your above code
returns
"123.txt" as the strRight7 value.

I assume that you want the file name to be parsed without the ".txt"
portion. If yes, change the above line of code to this:

strRight7 = Right(Left(strFile, Len(strFile) - 4), 7) ' The last
3
digits determine which
table gets the data

Hello,
I have spent several hours adapting code I found to import multiple txt

[quoted text clipped - 92 lines]

End Sub

 

[ojp via AccessMonster.com]

I found the problem!!!!!!!!!

The issue has to do with the name structure of the .txt file. The 3rd party
process that generates the files assembles fine names as XXXXXXXXXX.XXX.XXX.
txt

The TransferText command was not happy with the additional periods in the
file name. I renamed my test files to contain just one "." and the code ran
beautifully!

Thank you for looking at my code and for all your help.

Until the next one!

Sorry, I overlooked your Select Case block.

Have you identified if the "object not found" is the table, or the file?
While stepping through the code, when you get to the
DoCmd.TransferSpreadsheet line, hover the cursor over the strPathFile
variable to see if it's the value you expect; do the same for strTable.

Hi Ken,

[quoted text clipped - 41 lines]

 

[Ken Snell]

Thanks for posting the solution so that others can benefit in the future.