[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Exporting data from Filemaker to .tab and importing into excel?

Posted on 2004-11-10
14
Medium Priority
?
9,056 Views
Last Modified: 2012-08-14
I have exported whole database from file maker pro as .tab file and then I imported .tab file into MS excel to analyze. But I found some of the data fields are not complete, those fields are missing partial data. And it happened in perticular fields which are defined as big text boxes in filemaker pro e.g. Test Objective, Conclusions where I actually write atleast half of page. Can anybody tell me what the exact problem is?

0
Comment
Question by:skpd1978
  • 6
  • 3
  • 3
  • +2
14 Comments
 
LVL 19

Expert Comment

by:billmercer
ID: 12547332
When you import the tab file, it may be incorrectly determining what type of file it is. Make sure when importing into Excel that you specify it's a Delimited file, and specify the tab delimiter with no text qualifier. It's also possible you have some sort of unusual character in your FileMaker text field that is causing Excel a problem.

Try using Notepad to open the tab file you generated from FileMaker. Look and see if all of your text appears there. If it does, then the problem is definitely with the import into Excel.

Also, what version of FileMaker are you using?

0
 
LVL 28

Expert Comment

by:lesouef
ID: 12550173
I am afraid excel has a limit for the amount of char. in a field/cell..., probably 256char. Access used to have the same limit also, no idea if this is still true. Better check where the text is truncated, but I bet it will be a binary value.
0
 
LVL 19

Expert Comment

by:billmercer
ID: 12550559
Actually, the cell size limit is much bigger than that. I don't know the limit, but I can put thousands of characters in a single cell.
It should be able to hold a whole page of text with no problem. I suspect it's happening during import.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 28

Expert Comment

by:lesouef
ID: 12553453
Just checked, you're right, at least with tab files.
But I am sure I had this pb once in the past, and it was not due to a separator pb. But I can remember neither excel version nor the source of the text file... pretty useless information...
skpd, the more likely remains a tab code in a field. Is anything shifted after the truncated field?
0
 
LVL 19

Expert Comment

by:billmercer
ID: 12555503
I wonder if this could be caused by some other sort of special character from the FM7 data not being imported into Excel properly.
? Maybe some sort of unicode translation error or something.

skpd1978, can you generate a sample tab file that exhibits this problem, and upload it to a public place where we can download it and look at it?


0
 

Author Comment

by:skpd1978
ID: 12560442
Answer to billmercer's question:
what version of FileMaker are you using?
Filemaker Pro 5

Here I am shifting my database from FM Pro 5 to MS Access 2000
I tried to export all records from FM Pro5 in .tab, DIF and " ,  seperated" (Comma seperated) formats. My efforts in first two formats failed becuase those were giving me all truncated records. But with comma seperated I got the required results. It's giving me completed records. Then I converted that comma seperated file to Excel file and tried to import in MS Access. But some of those records are so large that I couldn't import that excel file to access and Access is giving me errors in importing excel file wizard. It's not generating new table in database.

Now that's the problem
Do you guys have any suggessions?
0
 
LVL 28

Expert Comment

by:lesouef
ID: 12561009
And if the , sep file works, it tends to confirm that you've got tabs inside the fm fields; and you probably have commas in the fields too, but it works because csv files from Fm have quotes around the field value while tab file have not.
I am not en expert for access, but since all fm text field can contain up to 64k characters, you have to size the access text fields the same, which is not the default value if I remember correctly; what is the text field size used by the wizard?
You'd better import manually?
0
 
LVL 19

Expert Comment

by:billmercer
ID: 12561055
Access does not allow text fields longer than 255 characters. If the text is longer than that, it must be put into a MEMO field instead of a TEXT field.
0
 

Author Comment

by:skpd1978
ID: 12561561
Access does not give me any chance to define data type when I import excel file using "Import external data" option. You can try it. It does not enable Data type field in "Get External Data" wizard. And when I click on Finish button at the end of procedure (with no fields defined and no key ID selected) it gives me error and says data not imported.

2nd matter is..... previous to this try I somehow managed to import excel file (converted from .tab sep file, & .tab file was exported from Fm Pro5)  to access (when I was not aware that some of the records are truncated). In this procedure I used same option of "Get External Data" wizard by choosing option of creating new table, this time access did not gave me error and created new table of Access database of my Excel file data. But this table is not useful to me because some records are truncated. Also when I try to modify data type of fields it does not allow me to save the changes, says "Data too large" even if I am changing from "Text" datatype to  "Memo" or from "NUmber" to "Text".

After all this,  now I was trying manually pasting truncated records in database it dose not allows me in that way either. It again says "Record is too large" and that perticular data type is defined as "Memo" automatically by Access when I imported the excel file. So I can neither modify this existing converted database by copy pasting nor can create new databse with excel file created from comma sep file. So right now I am stuck.

did anybody had this kind of experience?
0
 
LVL 19

Accepted Solution

by:
billmercer earned 1000 total points
ID: 12561633
Yes, and it's one of my biggest peeves with Access. The import sucks. That said, you're probably making it worse.

There's no reason to go from Filemaker to a Text file, then to an Excel file, and then to Access. That means your data is going through three separate translations, each one with its own idiosyncracies. Just import the tab file directly into Access, specifying the field type for each column. Don't accept the defaults, they are always wrong.

Or, the most direct way, create an ODBC connection to your FileMaker database and get the data directly from FM to Access without going through any intermediate files.
0
 
LVL 19

Expert Comment

by:billmercer
ID: 12561688
skpd1978, in future, you should really try to give as much information as possible when asking a question. If you had mentioned from the beginning that you were bringing the data into Access, you could have saved everyone quite a bit of time.
0
 

Author Comment

by:skpd1978
ID: 12565841
Billmercer & lesouef ,

Thanks for the replies. I apologies for any inconvinience. Actually I am new to database field. I uploaded this question when i realized that .tab file is not giving completed record, that time I thought if I can get this .tab file with completed records then my problem is solved, then I can just convert it to excel and import excel file in to Access. I also didn't know that I actually can import .tab file directly to Access.  

Now I will try with your suggessions. Give me some time to do this new procedure.

Thanks
skpd1978
0
 
LVL 10

Expert Comment

by:adriankohws
ID: 13373364
Why not trying foxpro or something else, it might help you eliminating future problems. Just an opinion.

Cheers.

Adrian Koh
0
 

Expert Comment

by:robdtaylor1
ID: 15005042
My comment below relates to all Microsoft Office Products I use.

Importing Data using Microsoft Products can lead to truncation issues. i.e. Access Export to CSV then open to Excel can cause strange problems when dealing with cell data sizes over 255 characters. Also Copy and Pasting from access to Excel with Field size over 255 chars leads to truncation.

The best method I have found to date (is for example: Using Excel to link to Access using the Get Data Wizard) and this imports the data correctly. I believe the cell size limit in Excel to be 32767 characters.

Good Luck

Rob Taylor

0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Pop up windows can be a useful feature of any Filemaker database.  Though best used sparingly, they can be employed in a multitude of different ways, for example;  as a splash screen at login, during scripted processes to control user input, as pick…
Conversion Steps for merging and consolidating separate Filemaker files The following is a step-by-step guide for the process of consolidating two or more FileMaker files (version 7 and later) into a single file with multiple tables. Sometimes th…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

873 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