12 Replies Latest reply on Jun 20, 2011 4:38 PM by sanfranman59

    Controlling column width in dbf export

    sanfranman59

      Title

      Controlling column width in dbf export

      Post

      When exporting a simple 3 column FileMaker Pro table to a .dbf file, I keep getting the following message:

      "Some data will be truncated when exporting because one or more fields or records is too long for the export format."

      After exporting from FMP, I read the .dbf into a SAS data set and when I check the column width there, sure enough, it's 254 characters. However, there is no data value that's wider than 8 characters in that field.

      What determines the width of the field when exporting to a .dbf file? And is there some way to control that or to compress the width of the field that is exported so that it's only as wide as the widest value in the field? I have no idea why FileMaker thinks the field needs to be 254 characters.

      I'm using FMP 11 on a Windows XP system.

        • 1. Re: Controlling column width in dbf export
          philmodjunk

          I would think 255 characters is the maxium size of your DBF text fields.

          You can probably ignore this message if you are certain that all data is less than this maximum length.

          If you want, you can try setting a maximum number of characters validation rule for 255 characters on each field and see if you still get this warning message. (I think you're getting a generic warning that you MIGHT lose data if any text field has more than 255 characters in it.)

          • 2. Re: Controlling column width in dbf export
            aammondd

            could be the field name length is an issue

            From the help file

            only ten characters per field name (spaces are converted to underscores, letters are converted to all uppercase)

            another limitation

            • 3. Re: Controlling column width in dbf export
              sanfranman59

              Since I can't figure out how to respond to individual responses, I guess I'll just post this as an "answer" to my original post ...

              @PhilModJunk ... I'm sure you're correct that the maximum size of a .dbf text field is 254 characters. But what I don't understand is why FileMaker is creating a field of that width when the widest entry in the data file is only 8 characters. I don't mind ignoring the message. But I would much prefer that FileMaker was a little smarter in creating the .dbf file. The FileMaker table has 3 fields. The field I'm having trouble with is set up as a text field and I do restrict it to 8 characters in width. After I export the data to a .dbf file and import it into a SAS data set, the width of the column is 254. But when I use SAS to check the length of the widest entry, it's 8. Another field in the same FileMaker table that is set up as a text field and is restricted to 10 characters in width doesn't have the same type of problem. Again, I have no idea why the FileMaker export utility is creating the .dbf file with one of the fields maxed out in width to 254 characters.

              @aammondd ... the field names are truncating, but that's not a problem for me. It's the width of the field itself, not the width of the field name. No individual record in this FMP table is anywhere near 4000 bytes. There are two text fields with data of a maximum of 8 and 10 characters in width and one date field.

              • 4. Re: Controlling column width in dbf export
                aammondd

                The error message may be because of the fieldname. Is the fieldname that is truncating the same one you are having issues with?

                Also how are you restricting the length of the filemaker fields?

                Is the field set to 254 the last field in the file?

                 

                 

                • 5. Re: Controlling column width in dbf export
                  philmodjunk

                  This may be a limitation of FileMaker Exports to DBF format. It may be possible to set a validation to 8 characters--but with "validate always" enabled. I don't honestly know if that will make any difference here or not, ubt you might experiment and see if it makes any difference here or not.

                  • 6. Re: Controlling column width in dbf export
                    aammondd

                    I was thinking because the name is truncating it may be unable to establish the "length" restriction.

                    An odditiy for sure. I wonder when the last time someone seriously looked at the dbf export code.

                     

                    • 7. Re: Controlling column width in dbf export
                      sanfranman59

                      @aammondd ... Thanks for your replies.

                      Re "The error message may be because of the fieldname. Is the fieldname that is truncating the same one you are having issues with?" ... All 3 of the field names are truncating. But I really don't think this has to do with the length of the field name. I didn't have this message start popping up until today. This is a master data file to which I append records from another FMP data file that are added by end users. I go out to a remote site and pick up the end user data file about once a month and append the records to the master file. The field names have not changed over time and until today, I never got the truncation message.

                      Re "Also how are you restricting the length of the filemaker fields?" ... I restrict what can be entered in the fields using the Manage Database Options Field Validation tab. I have the following requirements: Not empty, Unique value and Validation by calculation. The Validation by calculation criterion is "If(Length(Hospital Req Number) = 8;1;0)", with which my intent is to restrict the contents of the field to 8 characters. However, I do allow the end user to override the validation and we do sometimes wind up with more than 8 characters in the field. But I'm pretty confident that I've never seen anything as wide as 254 characters.

                      Even if there was such an entry at one time, I clean up any entry over 8 characters before appending the records to my master file. could it be that once a field expands to accommodate a wide entry it doesn't contract again when that entry is either deleted or fixed?

                      Re "Is the field set to 254 the last field in the file?" ... The field that comes out in the .dbf export as 254 in width is not the last field in the FMP file. It's the first.

                      • 8. Re: Controlling column width in dbf export
                        aammondd

                        Are your restricting the 10 characters the same way?

                        There is a checkbox on the Validation tab "Maximum number of characters." Is this checked and set for either field?

                        You could try that in your export

                        • 9. Re: Controlling column width in dbf export
                          philmodjunk

                          The validation Rule I have been referring to is not by calculation as that will not affect field size on export, but the specific "Maximum Number of Characters" setting--which may or may not affect the field width of the exported table. It can affect field width with certain uses in ODBC according to posts that I have read so it's worth a try here to see what you get.

                          • 10. Re: Controlling column width in dbf export
                            sanfranman59

                            Thanks again to both of you for your prompt responses today.

                            I think I tried using the "Maximum Number of Characters" criterion some time ago and for some reason didn't like what it was doing, although I can't recall now what I didn't like about it.

                            In any case, I just now tried it with the 8-character field in question and it had no impact on the field width in the .dbf file created by the FMP export utility ... i.e. it's still 254 characters in width even though the widest value (of nearly 175,000) is only 8.

                            I have a hypothesis for what might have happened. One of the users entered a value in the field that's supposed to be restricted to 8 characters and accidentally held down some key while they were distracted. They then saved the record, overriding the validation. Then they noticed what they'd done and either deleted the record and re-entered it or edited the record, deleting the extraneous characters. In the background, perhaps FMP changed the width of the field to accommodate the extraneous characters, but then didn't reset the width when the wide entry was removed or fixed.

                            • 11. Re: Controlling column width in dbf export
                              philmodjunk

                              Did you ever manage to get data exported with text fields of width less than 254?

                              Your hyponthesis seems unlikely, but could be easily tested if you created a small test file from scratch and tried a DBF export with it to see what results you get.

                              • 12. Re: Controlling column width in dbf export
                                sanfranman59

                                Re "Did you ever manage to get data exported with text fields of width less than 254?" ... Yes ... in fact, today is the first day I've ever received the truncation error message and I've been working with about a 5 or 6 of these types files (one each for a number of study sites) for about 10 years now. That said, I've noticed many times before that fields in .dbf files exported from FMP have unexpectedly wide field widths ... just not so wide as to trigger that error message (apparently).

                                At some point, I will probably try doing some experimenting as you suggest.