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

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?

skpd1978Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

billmercerCommented:
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
lesouefCommented:
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
billmercerCommented:
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
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

lesouefCommented:
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
billmercerCommented:
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
skpd1978Author Commented:
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
lesouefCommented:
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
billmercerCommented:
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
skpd1978Author Commented:
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
billmercerCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
billmercerCommented:
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
skpd1978Author Commented:
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
adriankohwsCommented:
Why not trying foxpro or something else, it might help you eliminating future problems. Just an opinion.

Cheers.

Adrian Koh
0
robdtaylor1Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
FileMaker Pro

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.