By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,855 Members | 1,970 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,855 IT Pros & Developers. It's quick & easy.

No text file import choice in Access 97 import dialog box

P: n/a

I have been through every trick I know, or has been suggested.

I have a one to two million line fixed field database in text format.
I want to bring it into Access 97.

When I use the external data import, the list of files to import does
not include text files of any type, only other database formats.

On a 102,000 line text file, I was able to split it with Word 97,
import the split files into Excel 97 one at a time, and then save them
as dbf files. Access can read those. The bigger file would require
days of grief to do that with it.

I have been able to do this on other systems, but my copy of Office 97
Pro refuses to install all the file translation stuff for Access. It
goes through the motions, and reports success, but nothing has
changed. Two of the choices in the add/remove in setup will do
neither. Can't remove them, and can't install them.

I had a lot of trouble with Office 97 when I first installed it in a
different directory from a partial install of Office 2000. Later I
uninstalled 2000, but it seemed to have left a whole lot of stuff
behind, and reinstalling or repairing 97 didn't improve things.

So, I finally uninstalled 97, and then used the wonderful delete key
on every file or folder that looked like it might have anything to do
with Office of any kind. Granted, a bit of a judgment call, and
something else was sure to break, but I didn't want to have to
reinstall all four Gigs of stuff - no backup then, just original
disks - Then I used Registry Mechanic to clean out over 400 problems,
that I assume I created.

Finally, I did a reinstall of Win 98SE, and
the wonderful thing found all my other stuff. The only other thing
that seemed broken was Project 2000, which I own and had installed.
So, I uninstalled Project, blew away anything of Project or Office I
could find, ran Reg Mech again, and reinstalled Win 98SE again. Then I
installed Office 97, and everything but this text file thing works
great! I finally reinstalled Project 2000 as well, and it works fine.
Nothing else seemed broken.

I now have backups on a 250 Gig external drive on a USB port. But, if
I screw it us, and reinstall the same thing, I don't see I have gotten
anywhere. I suppose I should do a reformat of C, and reinstall
everything from scratch, but I really don't want to do that. It would
take me months to get it all reconfigured, and all the new tool bar
adjustments, and and and... I know you understand.

That's why I keep thinking that if I could find the specific files
that do the import function in Access, and delete them all, I could
then reinstall them from the disk, and have only the Office 97
versions. But I just don't understand all this well enough.

Thanks again. If you have other suggestions, I'd love to try them.

Bye,

Ray Clare


Nov 24 '06 #1
Share this Question
Share on Google+
1 Reply


P: n/a
You can build a function that reads your text file line by line and appends
the records to a table..
I don't remember the exact syntax, but the help will help. I'm going to
assume for simplicity's sake that each line is 15 characters long and you've
only got three fields, fld1 is a string, 2 a date, and 3 an integer.
dim sLine as string
dim sFld1 as string
dim dFld2 as date
dim iFld3 as integer
dim sSQL as string

Open <filenamefor input as #1

Do While Not EOF(1)

Line Input #1, sLine

sFld1 = Left(sLine, 15)
dFld2 = CDate(Mid(sLine, 16, 10))
iFld3 = CInt(Mid(sLine, 26, 10)

SQL = "INSERT INTO tablename (fld1, fld2, fld3) VALUES ( '" & sFld1 &
"',#" & dFld2 & "#, " & iFld3 & ")"
Currentdb.Execute SQL, dbfailonerror

Loop

Close #1

I think this works. It's not all that slow either. Since you've got a
large number of fields, it might be a good idea to write a function that
parses your variables, such as

Public Type MyFields
sFld1 as string
dFld2 as Date
iFld3 as integer
End Type

public Function ParseLine( byref mMyFields as MyFields, byef sLine as
string)

with mMyFields
sFld1 = Left(sLine, 15)
dFld2 = CDate(Mid(sLine, 16, 10))
iFld3 = CInt(Mid(sLine, 26, 10)
end with

end function

Matthew Wells
MW****@FirstByte.net
"Child of His" <Am******@hotmail.comwrote in message
news:Co******************************@comcast.com. ..
>
I have been through every trick I know, or has been suggested.

I have a one to two million line fixed field database in text format.
I want to bring it into Access 97.

When I use the external data import, the list of files to import does
not include text files of any type, only other database formats.

On a 102,000 line text file, I was able to split it with Word 97,
import the split files into Excel 97 one at a time, and then save them
as dbf files. Access can read those. The bigger file would require
days of grief to do that with it.

I have been able to do this on other systems, but my copy of Office 97
Pro refuses to install all the file translation stuff for Access. It
goes through the motions, and reports success, but nothing has
changed. Two of the choices in the add/remove in setup will do
neither. Can't remove them, and can't install them.

I had a lot of trouble with Office 97 when I first installed it in a
different directory from a partial install of Office 2000. Later I
uninstalled 2000, but it seemed to have left a whole lot of stuff
behind, and reinstalling or repairing 97 didn't improve things.

So, I finally uninstalled 97, and then used the wonderful delete key
on every file or folder that looked like it might have anything to do
with Office of any kind. Granted, a bit of a judgment call, and
something else was sure to break, but I didn't want to have to
reinstall all four Gigs of stuff - no backup then, just original
disks - Then I used Registry Mechanic to clean out over 400 problems,
that I assume I created.

Finally, I did a reinstall of Win 98SE, and
the wonderful thing found all my other stuff. The only other thing
that seemed broken was Project 2000, which I own and had installed.
So, I uninstalled Project, blew away anything of Project or Office I
could find, ran Reg Mech again, and reinstalled Win 98SE again. Then I
installed Office 97, and everything but this text file thing works
great! I finally reinstalled Project 2000 as well, and it works fine.
Nothing else seemed broken.

I now have backups on a 250 Gig external drive on a USB port. But, if
I screw it us, and reinstall the same thing, I don't see I have gotten
anywhere. I suppose I should do a reformat of C, and reinstall
everything from scratch, but I really don't want to do that. It would
take me months to get it all reconfigured, and all the new tool bar
adjustments, and and and... I know you understand.

That's why I keep thinking that if I could find the specific files
that do the import function in Access, and delete them all, I could
then reinstall them from the disk, and have only the Office 97
versions. But I just don't understand all this well enough.

Thanks again. If you have other suggestions, I'd love to try them.

Bye,

Ray Clare


Nov 24 '06 #2

This discussion thread is closed

Replies have been disabled for this discussion.