Cannot Import Text File

[jahistx]

Hi

I am attempting to import a pipe-delimited text file to MS Access 97 on an XP sp2 machine.

When running the import routine, error message 3433, "Invalid setting in excel key of the engines section of the windows registry" is returned.

Microsoft kb article 268264 (

http://support.microsoft.com/kb/268264)

described a fix for Access 2000, which I applied, but which has made no difference.

I also applied the registry change described at

http://www.smartcomputing.com/techs...fedf111aa084ed1acdf46336dfce3d4&ErrorID=21951

but this did not fix the problem either.

Can anyone help me edit the keys to the correct values to allow text file import?

Thanks in advance

 

[ad2]

How about bringing your text file into Excel, creating an Excel spreadsheet, import the spreadsheet as a new table into you database?

 

[jahistx]

Hi ad2

This is a solution I considered, however the .txt files are ftp'd from a server and imported to the MS Access database, and I would rather not have to put in the additional step involving Excel.

Could this be a problem with running Access 97 on XP?

 

[RoyVidar]

If nothing else helpes, I'd consider a programatic approach, here a short snippet using filesystemobject:

[tt]dim fs as object
dim txt as object
dim strIn as string
dim strArr() as string
dim strSql as string
dim strValue as string
dim lngCount as long
set fs=createobject("scripting.filesystemobject")
set txt=fs.opentextfile(<path and name>,1) ' 1 ForReading
do while not txt.atendofstream
strIn = txt.readline
strArr=split(strIn,"|")
for lngCount = 0 to ubound(strArr)
select case lngCount
case 0
strSql = "insert into <nameoftable> (field1" ' numeric field
strValue = " values (" & strArr(lngCount)
case 1
if (trim$(strArr(lngCount))<>"" then ' test for contents
strSql = strSql & ", field2" ' text field
strValue = strValue & ", '" & strArr(lngCount) & "'"
end if
...

case <N>
strSql = strSql & ", fieldN" ' text field
strValue = strValue & '", " & strArr(lngCount) & "'"
next lngCount
strSql = strSql & ")"
strValue = strValue & ")"
currentdb.execute strSql & strValue
loop
txt.close
set txt=nothing
set fs=nothing[/tt]

- air code, typed not tested, it's probably slower than using some of the transfer methods, but can give a bit more control

The split function is not available in 97 (became available in 2000+), but RickSpr's replacement functions should do faq705-4342 in the mean time

Roy-Vidar