Solved

"External table isn't in exptected format"

Posted on 2001-08-22
40
1,727 Views
Last Modified: 2008-03-17
Hello!

I'm trying to import what I believe are dBase files into MS Access 97.  I have files with the following extensions:

.mdx
.dbf
.dbt

Whenever I attempt to import the dbf file, MS Access 97 gives me the following error message:
"External table isn't in expected format"

I know I've imported other dbf files into MS Access 97 before, but this time I'm stumped!  Can anyone help?

Thanks,
PinnaclePr
0
Comment
Question by:PinnaclePr
  • 10
  • 9
  • 8
  • +5
40 Comments
 
LVL 12

Expert Comment

by:Paurths
ID: 6415655
Hi PinnaclePr,

is it possible to mail those files? (if possible, zip them)
ricky.thijs@pandora.be

cheers
Ricky
0
 
LVL 54

Expert Comment

by:nico5038
ID: 6415661
There are some .dbf formats access has trouble with.
I found out that Excel is accepting more as Access. S as long as the table size isn't over 64,000 rows, give it a try in excel.
BTW I suspect the .mdx to be an index file, no data and as I recall correct the .dbt is containing the table definition, but my memory does get sometimes rusty... ;-)

Nic;o)
0
 
LVL 12

Expert Comment

by:Paurths
ID: 6415664
btw, all i could find on the subject :  http://support.microsoft.com/support/kb/articles/Q135/3/74.asp
0
 
LVL 54

Expert Comment

by:nico5038
ID: 6415668
Still using only your left hand Ricky?

Nic;o)
0
 
LVL 12

Expert Comment

by:Paurths
ID: 6415682
lol, not really... hurts like helllllll (although it is getting better... --> almost in hall of shame)
0
 
LVL 54

Expert Comment

by:nico5038
ID: 6415703
Sorry for the intermezzo PinnaclePr, but do you know/have the application that created these files?

Nic;o)
0
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 6415941
of the three file type mentioned, the only one which has even a chance of be accessible (pardon the pun - oh am I bad?<LOL>) would the *.DBF - and that extension applies to a whole lot of different Database apps, each of which needs a different driver.  So knowing what App created the file is of extreme importance - unless you want to use a lot of Trial-And-Error.
0
 

Author Comment

by:PinnaclePr
ID: 6415949
Thanks everyone for your replies.  I have found some additional information about the source of the files.  They are from a program called Membership Plus.  I have pasted the following text below from the program's "Readme.txt" file:

[Begin Paste]
Membership Plus for Windows uses the Borland Database Engine (BDE) to connect to the database files for your organization.  The database files are in a standard dBase IV file format. This format supports input to other Windows programs such as dBase for Windows, Crystal Reports, Excel, etc.  
PLEASE NOTE:  Saving Membership Plus for Windows database files from other programs may modify the dBase IV format to the point that Membership Plus can not use the saved file.

The BDE is also used by other BDE-enabled applications such as Paradox for Windows or Quattro Pro for Windows.  These programs all refer to a setting called LEVEL, associated with a database driver such as dBase.  For Membership Plus 6.0 for Windows purposes, the LEVEL must be set to 7, which is the setting that the setup program attempts to establish.  If necessary, Membership Plus for Windows will detect it at the appropriate time if the LEVEL is not set to 7, and will request that you change the setting to 7.  The other BDE applications should work with that setting.
[End Paste]

So...I'm dealing with files in dBase IV file format created by the BDE.

Ricky, I'll email you a sample of some of the files...thanks for asking.

PinnaclePr
0
 
LVL 3

Expert Comment

by:SE081398
ID: 6416092
Just jumping in real quick to add a thought.   PinnaclePr after reading some of the posts I would suspect that if you were to try and open the file in excel it might work. As stated: "This format supports input to other Windows programs such as dBase for Windows, Crystal Reports, Excel, etc. ".   If it does then save the file and then import the new excel file into access.  

is this possible ?
0
 
LVL 54

Expert Comment

by:nico5038
ID: 6416418
Will only work upto 64,000 rows at max, as I stated above.
But indeed, PinnaclePr, did you give excel a try?

Nic;o)
0
 
LVL 7

Expert Comment

by:Nosterdamus
ID: 6416680
Hi PinnaclePr,

Did you try creatibg an ODBC connection to the file(s) and then accessing through this connection.

To create a new ODBC connection, Select "Control Panel->ODBC Data Sources (32Bit)". Click the "File DSN" tag and press the "Add..." button.
Select dbase driver from the list and follow the wizard's instructions till completion.

At the MS Access side, go to the Table Tag, and use the Link... option, to link to your newly created ODBC connection...


Hope this helps,

Nosterdamus
0
 
LVL 7

Expert Comment

by:Nosterdamus
ID: 6416690
BTW, at the MS Access side, you can also use the Import... option...
0
 

Author Comment

by:PinnaclePr
ID: 6418040
Regarding Excel; when attempting to open one of the .dbf files in MS Excel 97, I receive the following error message:
"This file is not in a recognizable format."

I can actually click OK through the error and it shows me the Excel Text Import Wizard.  However the data appears VERY garbled and, having tried all possible combinations of delimiter and import options, the data is basically unusable.

Nosterdamus,
I've attempted to create an ODBC connection as you suggested, but I'm receiving the following error message when attempting to import or link in MS Access 97:

"You cannot use ODBC to import from, export to, or link an external Microsoft Jet or ISAM database to your database."

I'm new to creating ODBC connections, so I may very well have done something wrong in creating the connection.  Here's what I've done:
1) Control Panel - select "ODBC Data Source (32bit)"
2) I selected the "File DSN" tab.
3) I clicked the Add button.
4) From the Create New Data Source dialog, I selected "Microsoft dBase Driver (*.dbf)" and then clicked the Next button.
5) I then typed "dBase" as my data source a name and then clicked the Next button.
6) I then clicked the Finish button.
7) From the ODBC dBase Setup dialog box, I selected "dBase IV" for the Version and kept the "Use Current Directory" checkbox checked.
8) I clicked on the OK button.
9) I clicked on the OK button to close the ODBC Data Source Administrator window.

From MS Access 97:
1) I selected "File | Get External Data | Import".
2) In the "Files of Type" pull-down, I selected "ODBC Databases".
3) From the "Select Data Source" dialog box, I double-clicked on "dBase.dsn" (which I created in the steps above).

I then get the following error as previously stated:
"You cannot use ODBC to import from, export to, or link an external Microsoft Jet or ISAM database to your database."

Please let me know if I'm doing something incorrectly.  THANKS!!!

PinnaclePr
0
 
LVL 12

Expert Comment

by:Paurths
ID: 6418266
hi, i sent the result to PinnaclePr.

ok here is what i did:
1) created a little application in Delphi that could read the file, put the retrieved data in records and wrote them to another file.

2) Import in Access: Problem.
The file held a memo field that had junk in it --> {Font\\Helvetica\10\Font{{

it appearantly had format-code in it in some, not all, records.
Further it has 'carriage-returns' that i could not detect in Delphi (not with my little knowledge of it...), thats the reason it is junk in excel also.
Thus creating extra records.


i wrote a procedure to get rid of those extra records, while looking for valid data in the extra records, and if found adding them to the previous valid record.


does it somehow add up Paul? (the mdb i mean?)

cheers
Ricky
0
 

Author Comment

by:PinnaclePr
ID: 6421937
Ricky,

THANK YOU for all of your efforts in importing the sample file I sent you into MS Access.  Unfortunately, the mdb file you generated and sent me only has a small percentage of the total records with data in the memo (Note) field.  I can go into the source program (Membership Plus) and browse through the records contained in the dbf files.  There are a large number of records in the source file that have text in the Note field (in the dbf file), but did not come over in your mdb file (the memo/note field contains only a single space character).  Your mdb file contains just over 600 records with actual data in the Note field.  There should actually be about 2600 records with text in the Notes field.

Apparently in cleaning up the records, valid text was removed.  Unfortunately, this won't work for me.  Also, I've got a total of about 40 dbf files that need to be imported AND this will be something I'll need to perform on a periodic basis.  So...my preference would be to find a way to import the dbf files into MS Access.  I have very easily imported dbf files from Membership Plus into MS Access in the past.  However this was with dbf's from a previous version of the product.  Now, with the current version, I'm unable to do so.

Still looking for a solution =*)  Increasing points to 400.

PinnaclePr
0
 

Author Comment

by:PinnaclePr
ID: 6421947
Whoops, can't increase to 400.  Apparently EE has a limit of 300 for a single question.

PinnaclePr
0
 
LVL 7

Expert Comment

by:Nosterdamus
ID: 6426045
Hi PinnaclePr,

This is what MS was to say about the Error message:


You cannot use ODBC to import from, export to, or link an external Microsoft Jet or ISAM database table to your database. (Error 3423)
You are attempting to link, import data from, or export data to either an external Microsoft Jet table or an external ISAM database table (for example, dBASE, FoxPro, Paradox, or Btrieve), but you have selected <SQL Database> as the data source. Select the appropriate data source for the data you are attempting to link.


I suggest that you create the link again, and make sure that you are specifying the correct format.


Create ODBC connection:
1. Lounch ODBC Data Source 32bit from Control Panel.
2. Click of "File DSN".
3. Click "Add...".
4. Select Microsoft dBase Driver (*dbf).
5. Click "Next".
6. Give your connection a name (you can use the old one that you created earlier).
7. Click "Next".
8. Click "Finish".
9. Select the appropriate dBase version (dBase III, dBase IV or dBase 5.0).
10. Uncheck the "User Current Directory" option.
11. Press the "Select Directory..." and browse to the relevant directory where your dBase file is located. Accept your selection.
12. Press the "Select Indexes..." and select the file to be used. Accept your selection.
13. Press "Options>>" and set any additional options, as needed.
14. Press OK and exit ODBC.

Now, lounch Access and try importing\linking again...

Does it work?

Nosterdamus
0
 
LVL 3

Expert Comment

by:SE081398
ID: 6429172
Nosterdamus, I got the same error msg when trying to create a second access 97 dsn.  I'm using and access front end that utilizes two diferent access back ends.  I was wanting to create a DSN that could be opened in the front end and point it to the second datasource.  in doing so I get the error msg pinniclepr is getting.  this leads me to believe that there's somthing else going on.  

I thought it might have something with the mdac but I've installed the latest version and still no fix.

 
0
 

Author Comment

by:PinnaclePr
ID: 6429621
Nosterdamus,

Thank you for further clarifying the process for me.  I'm fine down through step 11.  However, I'm not exactly sure what to do in Step 12.  In the "Select Indexes" dialog box, about 40 table names appear in the Tables listbox and what appears to be an index file (.mdx) for each of these tables in the Indexes listbox.  As I select a table from the Tables listbox, I notice that the desired mdx file gets highlighted in the Indexes listbox.  Is the something I should be doing in this screen?

If I go ahead and click OK at this point and continue on, I continue to get the dreaded "You cannot use ODBC to import from, export to, ...." error message.  

Unfortunately, the urgency of this issue is growing as every day passes.  Thanks for everyone's help so far!!

PinnaclePr
0
 
LVL 7

Expert Comment

by:Nosterdamus
ID: 6431648
Hi PinnaclePr,

If you'll post your e-mail address here, then I'll contact you for you to be able to send me a sample table with it's relevant index files for me to try to connect via ODBC.

I prefer to keep my e-mail address anonym...

Nosterdamus
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 7

Expert Comment

by:Nosterdamus
ID: 6432084
Hi Paurths,

I understand that you've got a sample of the file. Can you test the ODBC connection for PinnaclePr?

As PinnaclePr stated:
>>>Unfortunately, the urgency of this issue is growing as every day passes.

We might be able to save some time...

????


Nosterdamus
0
 
LVL 12

Expert Comment

by:Paurths
ID: 6432553
hi Nosterdamus,

same error as SE and PinnaclePr

send me a mail : ricky.thijs@pandora.be , i'll sent the files

cheers
Ricky
0
 
LVL 7

Expert Comment

by:Nosterdamus
ID: 6432930
Hi PinnaclePr,

Expert Paurths have sent me the sample file.

After running some checking on the file, I'm suspecting that the file is NOT a dBase file format (as for ANY dBase version I select in the ODBC, I get NO table name and no Index files...). I also get the "External table isn't in the expected format" when trying to import the file directly to access (no ODBC). The MS Access help describe the problem as if it is related to a comma delimited file formats ?!?!

ALSO, please read Arthur_Wood's comment again!!!

As you said in one of your prev posts:
>>>>I know I've imported other dbf files into MS Access 97 before, but this time I'm stumped!

I suggest that you re-check with the supplier(s) of this file again. It just might be possible that they give these files a "special treatment" for some reason, or that the application that they are using does NOT end up with a dBase file format. In this regard, please read your post again:
>>>>PLEASE NOTE:  Saving Membership Plus for Windows database files from other programs may modify the dBase IV format to the point that Membership Plus can not use the saved file.
It is just possible that this is what your supplier does...

If my suspicion is correct, then I'm afraid that you'll have no choice, but to go with Paurths suggestion, regarding dealing the file via some code, although you MUST know it's format...

Another approach you can try, is asking the supplier(s) of the file to provide different file formats (if possible) for checking, or asking him (them) to supply a specific file format which you can handle.


Paurths:
Thanx for your quick respond and file transferring...


Nosterdamus
0
 
LVL 12

Expert Comment

by:Paurths
ID: 6432997
btw, opening in Delphi showed that the file has a header in it.
0
 

Author Comment

by:PinnaclePr
ID: 6433755
Hey Guys!

THANKS for all of your posts!  I guess I should further explain my situation for clarification on why I need to get these files imported into MS Access (I should have detailed this up front - sorry).

I'm working for a small church management software company.  We have a church client who has hired us to convert their existing data from Membership Plus (http://www.parsonschurch.com/products/church/memplus.html) into our product (which is an MS Access 97 database application).  We have the Membership Plus program and have been able to export SOME of the data out of the program to CSV files (which we can easily import into MS Access).  However, the program does not provide full exporting capability to export ALL of the data - specifically the "Notes" field data which our client has used to record details about the individuals and families in their congregation.  Actually, this Notes Membership Plus dbf file is the file I emailed to Paurths (Ricky) (see previous posts).  

We have performed conversions from Membership Plus into our product (MS Access) in the past without ANY issues.  However, this was with an earlier version of the MP software.  Ultimately, I would like to be able to import the MP dbf files directly into MS Access 97.  However, we can get just about all of the data out of MP by exporting the data from the MP program.  However, we must have the Notes field data (there's quite a lot of data our client has entered into this field)...and, as I previously mentioned, the MP program's export capability for some strange reason does NOT include the Notes field.

I would be happy to email anyone else the Notes dbf file for further investigation - just email me at PinnaclePr@aol.com.  

Nosterdamus, as to your note about determining the supplier of the files - the files are coming directly from the Membership Plus program's data folder (which has about 40 dbf, mdx, and dbt files in it).

I've only got until the end of this week to complete the data transfer project - unless we request an extension (which may not look real good to the client).

THANKS again to everyone's continued help in resolving this mystery.

PinnaclePr (Paul)

0
 
LVL 54

Expert Comment

by:nico5038
ID: 6434359
I guess the field Ricky talks about:
>The file held a memo field that had junk in it --> {Font\\Helvetica\10\Font{{

Is the Notes field.
Obviously it's in .rtf format.
A "quick" trick could be to ask Ricky to copy the field to the clipboard and to paste it back "as plain text" in his Delphi program...

The "always working" solution would be to use copy/paste (e.g. supported by a macro-utility) of the key and Notes field to e.g. an excel or tabbed text file.

Another thought: Did you ask the manufacturer how to dump the field...

Nic;o)
0
 

Author Comment

by:PinnaclePr
ID: 6442769
Hello again!

I just got a reply from Membership Plus support via email:

"As of right now there is not a way to export the notes from Membership Plus to another program. I will put it into the programmers for a suggestion."

Which confirms my fear that I won't be able to simply use the MP program to export the necessary data.  

Nic;o) - yes, this is the notes field that Ricky referred to.

There are 3 files that seem to be associated with the MP Notes data:
- MbrNote.dbf (405kb)
- MbrNote.dbt (6202kb)
- MbrNote.inf (1kb)
- MbrNote.mdx (279kb)

The inf file is readable with notepad and I've pasted the contents below:

[dBase IV]
MDX1=MBRNOTE.MDX

[UIDX1 MBRID]
MDX1=MBRNOTE.MDX

I've also tried opening the dbt file in MS Word.  It's quite a mess, but I can definitely see that it's got RTF encoding (at least pieces of it anyway).

I've inquired further with Membership Plus technical support as to whether or not they could assist me with importing the MbrNote data into MS Access.  However, I don't have much faith they'll be any help.

Ricky, would you be open to attempting to "massage" the RTF data contained in the file in order to extract the raw text data.  Keep in mind that I need the member ID key value for each note field so that I can properly link it to the members other info (which I can export right from the MP program).  Please let me know.

PinnaclePr (Paul)

0
 
LVL 54

Expert Comment

by:nico5038
ID: 6442873
Hi Paul,

If Ricky can't, you can drop it zipped in my yahoo.com mailbox (nico5038 infront...) and I'll have a look.

Nic;o)
0
 
LVL 12

Expert Comment

by:Paurths
ID: 6442947
hi,

i sent Nico a copy of the file.
I'll also look into it, tomorrow, since its 1 AM here, and i need some sleep now.

cheers
Ricky
0
 
LVL 7

Expert Comment

by:Nosterdamus
ID: 6744356
Hi everyone,

Any progress????

Nosterdamus
0
 
LVL 12

Expert Comment

by:Paurths
ID: 6744434
i guess this one died silently.
i checked for the files, but i do not have them anymore...
0
 

Author Comment

by:PinnaclePr
ID: 6746295
Hello!

No...no progress at all...still same issue.  Basically we told the client we can't transfer the Notes field data from Membership Plus...didn't have any other choice.  This could mean a lot of re-entry for them.  I'd be happy to re-send anyone who's interested a sample dbf file or 2 to play with.  

BTW - we've had 3 or 4 Membership Plus data transfer projects since August (my initial posting date on this), and each time we've had to let the client know that the Notes data could not be transferred.  

Thanks,
PinnaclePr

0
 

Author Comment

by:PinnaclePr
ID: 6746298
Keep in mind that I'm looking for a long-term, re-usable solution here.

Thanks,
PinnaclePr
0
 
LVL 7

Expert Comment

by:Nosterdamus
ID: 6753071
Hi PinnaclePr,

If there any way you can put your hand on the file structure(s) of MP, then it might be possible to write some code to extract the information from the files and import it to access. Otherwize, I guess that the only choice you've got is to wait untill MP will develope some kind of export mechanism that can be used for that purpose.

Nosterdamus
0
 
LVL 1

Expert Comment

by:Moondancer
ID: 6913524
ADMINISTRATION WILL BE CONTACTING YOU SHORTLY.  Moderators Computer101 or Netminder will return to finalize these if still open in seven days.  Please post closing recommendations before that time.

Question(s) below appears to have been abandoned. Your options are:
 
1. Accept a Comment As Answer (use the button next to the Expert's name).
2. Close the question if the information was not useful to you, but may help others. You must tell the participants why you wish to do this, and allow for Expert response.  This choice will include a refund to you, and will move this question to our PAQ (Previously Asked Question) database.  If you found information outside this question thread, please add it.
3. Ask Community Support to help split points between participating experts, or just comment here with details and we'll respond with the process.
4. Delete the question (if it has no potential value for others).
   --> Post comments for expert of your intention to delete and why
   --> You cannot delete a question with comments, special handling by a Moderator is required.

For special handling needs, please post a zero point question in the link below and include the URL (question QID/link) that it regards with details.
http://www.experts-exchange.com/jsp/qList.jsp?ta=commspt
 
Please click the Help Desk link on the left for Member Guidelines, Member Agreement and the Question/Answer process for further information, if needed.  http://www.experts-exchange.com/jsp/cmtyHelpDesk.jsp

Click you Member Profile to view your question history and keep them all current with updates as the collaboration effort continues, to track all your open and locked questions at this site.  If you are an EE Pro user, use the Power Search option to find them.  Anytime you have questions which are LOCKED with a Proposed Answer but does not serve your needs, please reject it and add comments as to why.  In addition, when you do grade the question, if the grade is less than an A, please add a comment as to why.  This helps all involved, as well as future persons who may access this item in the future to seek help.

To view your open questions, please click the following link(s) and keep them all current with updates.
http://www.experts-exchange.com/questions/Q.20169130.html
http://www.experts-exchange.com/questions/Q.20172528.html




PLEASE DO NOT AWARD THE POINTS TO ME.  
 
------------>  EXPERTS:  Please leave any comments regarding your closing recommendations if this item remains inactive another seven (7) days.  Also, if you are interested in the cleanup effort, please click this link http://www.experts-exchange.com/jsp/qManageQuestion.jsp?ta=commspt&qid=20274643

Moderators will finalize this question if still open in 7 days, by either moving this to the PAQ (Previously Asked Questions) at zero points, deleting it or awarding expert(s) when recommendations are made, or an independent determination can be made.  Expert input is always appreciated to determine the fair outcome.
 
Thank you everyone.
 
Moondancer
Moderator @ Experts Exchange
0
 
LVL 7

Expert Comment

by:Nosterdamus
ID: 6950052
Hi Moondancer,

IMHO, if the status of the question remains as is, then a Q Points refund and a 0 (zero) points PAQ should be executed.

Thanks!

Nosterdamus
0
 

Author Comment

by:PinnaclePr
ID: 6950350
Moondancer,

This question was never resolved/answered.

PinnaclePr
0
 
LVL 1

Accepted Solution

by:
Moondancer earned 0 total points
ID: 6951133
Thank you everyone.

I have refunded 300 points to you and moved this to our PAQ ata zero points to close it.

Here's what you may want to consider.  If you still want to pursue this to get what you need, you could continue here with the participating experts and if a solution evolves, let us know and we can change the point values back.  Alternatively, if still needed, you may want to ask it again and include the link to this one as a reference to minimize additional keying redundancy or the like.

Moondancer - EE Moderator
0
 
LVL 1

Expert Comment

by:__Holly__
ID: 7526434
anyone got anymore feedback?

i have the same situation with a product called iBank.

i suspect that there is an easy answer-- that the batch that creates the DBF is termination abnormally and failing to write some sort of footer or something...

does anyone know the difference between dbase and foxpro? i think that these are made with foxpro, and i cant figure out why i was previously using the Dbase import and it was working perfectly-- until now..

206 660 9320
0
 
LVL 1

Expert Comment

by:__Holly__
ID: 7539832
btw-

it appears that the problem was due to the vendor changing from foxpro 6 to fp 7-- i believe that one of the indexes was mismatched or soemthing--- but i am _DEFINITELY_ going to attempts to do this via text (has anyone opened a dbf with notepad?)

the resolution for now?

i opened the dbf file in foxpro and exported to a different format (technically a xls)
0

Featured Post

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now