Import from dbf truncates data

[tjubb]

I tried to import data from a Paradox table into FMP8. I got an error about not being able to import and that the format had to be a dbase file. So I exported all the records from Paradox to a dbf file. I was then able to import successfully into my Filemaker file. Problem is that on one particular field (which is large free flowing text/memo field) data appears truncated. The confusing thing I see is that it doesn't seem to truncate in any pattern. Some records appear to have been simply cut off after a set number of characters, other records seem to have first letters truncated off random words or a combination of the two. I have opened the dbf file that I imported from and I see no signs of truncation. So this it is definitely FMPro causing the truncation.

Make sense?
Any help would be greatly appreciated.
Thanks,
Tom

 

[JeanW]

Try to make a Tab-Separated Text format from your Paradox file and see if the result is the same.

A *.tab file has less chances to change the data.

 

[TStriker]

Good suggestion Jean. I've had a lot of luck with .CSV files too--maybe give that a try if the tab separated gives you trouble.

-Striker

 

[JeanW]

Data files are delimited in some way: one character marks where each field ends, another character where each records ends.

Common file formats usually uses paragraphs/carriage returns for the record delimiter and commas or tabs for the field delimiters (hence tab-delimited and comma-delimited file formats).

When in doubt about something I open the data in f.i. Word to check for strange characters....

 

[tjubb]

I've tried the Tab-Separated format and although I don't see any truncation in the middle of field data, I do get tons of blank fields where there should be data. I've tried just about every kind of export file with no luck so I'm kinda stuck now and not sure how to get this conversion working properly. Any other ideas?

 

[TStriker]

What version for Filemaker are you running? Have you tried the XML format?

-Striker

 

[tjubb]

I am using FM Pro 8.0v1.

I don't see any export options to XML in Paradox unless it is somewhere else other than the export dialog box.

 

[TStriker]

Okay. I don't have any real suggestions--I just thought you might be running an ancient version. BTW, there is an 8.0v2 updater out. You should consider upgrading but read the release notes first because it does introduce some new bugs.

Have you tried using a completely different PC? I'm grasping-at-straws here.

-Striker

 

[tjubb]

I too am grasping straws at this point and just might have to get the few that will be using the database to clean up the entries manually.

I have tried on two different pc's with the same results. I'll give the v2 updater a try but not expecting any miracles at this point.

Thanks to everyone for at least putting some thought to this.

Thanks,
Tom

 

[JeanW]

And if you make an intermediate FileMaker file, just to import the data, clean it out there, and import the cleaned data into your live application ?

Is there a sort of 'pattern' at record level from Paradox.
Being not familiar with Paradox, does it use an SQL file for data ?

 

[tjubb]

Paradox uses a .db file. The data does not look truncated when I view the export file, it only truncates once it's imported into Filemaker.

 

[JeanW]

I would really check the data in f.i. Word to see if there are 'hidden' signs/formats somewhere.
Remove all the 'double' returns.
So, make sure that between each data string is just one and only one returnsign.
Save the file as Unicode 8 and import that fiel in FileMaker.

It's just guessing, but I think Paradox places 2 returns at some places. FileMaker takes the second return as a blank record....

My 2 centavos

 

[tjubb]

Jean, what do you mean by "f.i. Word"?

 

[tjubb]

Here is the Paradox export box. Could someone clarify what the best options to use are. I was separating fields with tabs even though this image doesn't show that.

 

[TStriker]

Howdy tjubb,

Have you tried:

Fields Separated By: Semi-Colons
Fields Delimited By: Quotes

Other that that, I THINK you're in the right neighborhood. But I have not used Paradox.

-Striker

 

[DeMann]

I would use fields separated by | and perhaps fields delimited by # (or some other character that you don't use).

What may be happening is that you may have quotes in your data. When FileMaker is importing this, it may be looking at that quote as the end of the the field, and not knowing what else to do, making some of the fields blank.

_______________
[cN]

 

[tjubb]

I've checked the Paradox database and used delimiters that are not already used in the data fields. The import into FM is still not quite right. How does Filemaker know what you are using for the delimiters?

I've got about 20 fields in my database and when I import into FM it seems everything just gets crammed into the first 4 fields.