Import Fixed Width (.dat) file

[RJF]

I am trying to import a Fixed Width, text file called ERSCUST.dat into Access
(2000).

I am not able to import the .dat file, I get the error message “Cannot
Update. Database or object is read-only.†Once I change the extension to
..txt (ERSCUST.txt) it will work. The users do not want to manually change
the extension to import the file. Is there a way to Import a text file with
a .dat extension?

Many thanks,

 

[Jeanette Cunningham]

Hi,
there is a Name statement you can use to rename files.

Name C:\Temp\Test.dat As C:\Temp\Text.txt

There is also the FileCopy statement.

Jeanette Cunningham

 

[RJF]

Please forgive my ignorance. This is the code I'm using. Where would I
change the name of the .dat file that I'm trying to Import?

strFilter = ahtAddFilterItem(strFilter, _
"All Files (*.*)", _
"*.*")
strInputFileName = ahtCommonFileOpenSave( _
Filter:=strFilter, OpenFile:=True, _
DialogTitle:="Please select an input file...", _
Flags:=ahtOFN_HIDEREADONLY)

DoCmd.TransferText acImportFixed, "ERSCUST_Import_Spec", "ERSCUST",
strInputFileName

Thanks,

 

[Jeanette Cunningham]

Use strInputFileName as the original file name

Note the following is untested air code.
You will probably need to debug it.

create 2 variables
Dim strSource as String
Dim strRenamed as String

'remove the file extension from strInputFileName
strSource = Left$(strInputFileName, Len(strInputFileName) -3)
strRenamed = strSource & "txt"

DoCmd.TransferText acImportFixed, "ERSCUST_Import_Spec", "ERSCUST",
strRenamed

Jeanette Cunningham

 

[RJF]

Hi Jeanette,

I tried your code and it worked perfectly, kind of. The file name is
ERSCUST.Dat.

I used this path: G:\OSS\ADMIN\OSS_Tech\Rachel\Format_Files\DK and it worked.
I used this path: G:\OSS\ADMIN\OSS_Tech\Rachel\Format_Files\DK\Done and it
worked.

Then I tried this path: G:\OSS\OSSBill\TEST and I get the following error:

"The Microsoft Jet database engine could not find the object 'ERSCUST.txt'.
Make sure the object exists and that you spell its name and the path name
correctly."

This error occurs if I use any other path except for the first 2 above.

I'm doing everything the same except using a different path. The file is
the same, I just copied it from directory to directory. I then put Breaks in
the code to view it as it was running. It looked the same only with the
specified path I chose. But unless the path is the same as the first 2
above, the error occurs. It just doesn’t like any other paths.

Any idea of what might be causing the error?

Thank you so much for your help.

 

[RJF]

I figured out something else.

It's not actually renaming the .dat file to a .txt file. I had another .txt
file with the same name (ERSCUST.txt) and it was grabing the ERSCUST.txt file
even though I was selecting the ERSCUST.dat file. Once I took the
ERSCUST.txt file out of the directory, it's wouldn't import and I got the
error message I stated below.

So basically, the renaming code is not working for me.

Any ideas?

Thanks,

 

[RJF]

It's working now.

I don't know if this is the best way to do it, but this is the code I'm
using now:

strFilter = ahtAddFilterItem(strFilter, _
"All Files (*.*)", _
"*.*")
strInputFileName = ahtCommonFileOpenSave( _
Filter:=strFilter, OpenFile:=True, _
DialogTitle:="Please select an input file...", _
Flags:=ahtOFN_HIDEREADONLY)

'remove the file extension from strInputFileName
strSource = Left$(strInputFileName, Len(strInputFileName) - 3)
strRenamed = strSource & "txt"

SourceFile = strInputFileName ' Define source file name.
DestinationFile = strRenamed ' Define target file name.
FileCopy SourceFile, DestinationFile ' Copy source to target.

DoCmd.TransferText acImportFixed, "ERSCUST_Import_Spec",
"ERSCUST", DestinationFile

Thank you so much for your help, Jeanette.

 

[Jeanette Cunningham]

Well done.

Jeanette Cunningham

It's working now.

I don't know if this is the best way to do it, but this is the code I'm
using now:

strFilter = ahtAddFilterItem(strFilter, _
"All Files (*.*)", _
"*.*")
strInputFileName = ahtCommonFileOpenSave( _
Filter:=strFilter, OpenFile:=True, _
DialogTitle:="Please select an input file...", _
Flags:=ahtOFN_HIDEREADONLY)

'remove the file extension from strInputFileName
strSource = Left$(strInputFileName, Len(strInputFileName) - 3)
strRenamed = strSource & "txt"

SourceFile = strInputFileName ' Define source file name.
DestinationFile = strRenamed ' Define target file name.
FileCopy SourceFile, DestinationFile ' Copy source to
target.

DoCmd.TransferText acImportFixed, "ERSCUST_Import_Spec",
"ERSCUST", DestinationFile

Thank you so much for your help, Jeanette.