14 Replies Latest reply on Jun 28, 2010 11:17 AM by FentonJones

    Importing Tab Separated Text Files

    BMarot

      Title

      Importing Tab Separated Text Files

      Post

      I am attempting to populate my solution using a number of tab separated text files.

       

      As an example, let's say I have 4 fields (Last Name, First Name, City, Notes).  My tab separated text file will contain information like:

       

      Last Name      First Name      City      Notes Jones          Henry           Vancouver This is an example of a note Smith          John            Montreal  Another example of what a note may look like.

      The part that is giving me troubles is the notes field.  Some of the notes may be several lines long and may contain a number of line/paragraph breaks or other formatting.  Is there any way that I can retain line/paragraph break structure so that the information doesn't look like a jumbled mess when it's imported into the notes field? 

       

      I'm on a Mac running OSX 10.5.8 and I am using FileMaker Pro 10.

       

      Thanks,

       

      Brad

       

       

        • 1. Re: Importing Tab Separated Text Files
          philmodjunk
            

          You might be able to take out the carriage returns, import the records and then put them back.

           

          Example: replace each carriage return with §

           

          Import the data

          Use replace field contents' calculated result option and the following calculation to put back the returns:

           

          Substitute (table::notes ; "§" ; "¶")

           

          I was able to do the first step quite easily in word using its replace tool.

          • 2. Re: Importing Tab Separated Text Files
            BMarot
              

            I was thinking something similar, though I didn't know there was a "Substitute" function (I'm a beginner, at best).  Getting a unique character in at each return also seems quite reasonable.

             

            I tried creating a button to do what you suggested, both with the field specified and with no field specification.  If the field is specified then nothing seems to happen at all.  If there is no field specified then activating the button  causes everything in that field to be erased.

             

            Any other suggestions?  Possibly something that just needs to be tweaked?

            • 3. Re: Importing Tab Separated Text Files
              philmodjunk
                

              You can do the entire operation by making selections from the filemaker menus, once you've updated the text correctly. (Beware of destroying the line breaks in your tab file that you want to keep.)

               

              I don't see what a button has to do with anything here.

              • 4. Re: Importing Tab Separated Text Files
                comment_1
                   If you are able to replace the carriage returns in the source file with another character (without affecting those that separate the records), then replace them with the vertical tab character (ASCII 11). Then you can import the resulting file directly, with no post-processing required.
                • 5. Re: Importing Tab Separated Text Files
                  BMarot
                    

                  You can do the entire operation by making selections from the filemaker menus, once you've updated the text correctly. (Beware of destroying the line breaks in your tab file that you want to keep.)

                   

                  I don't see what a button has to do with anything here.


                  Ah, I see what you mean.  That seems to work.  I created a script with the "Replace Field Contents" script step in the hopes that I could use it in a script trigger.  I was testing functionality using a button.  I'm not sure why the button doesn't work the same way that the Replace Field Contents option in the menu works, but at least the menu is now an option.

                   

                  Thanks for your help.

                   


                  comment wrote:
                  If you are able to replace the carriage returns in the source file with another character (without affecting those that separate the records), then replace them with the vertical tab character (ASCII 11). Then you can import the resulting file directly, with no post-processing required.

                   

                   That sounds promising.  I'm not sure what you mean by vertical tab character.  How do I input a vertical tab character?


                  • 6. Re: Importing Tab Separated Text Files
                    comment_1
                      

                    If you are a using a decent text editor, you should be able to find:

                     

                    \r

                     

                    and replace it with:

                     

                    \x0B

                    • 7. Re: Importing Tab Separated Text Files
                      BMarot
                        

                      Hmm, strangely enough the button works fine as long as I allow dialog.  When I choose "perform without dialog" the contents of the field are erased.

                       

                      Edit:  In response to comment:

                       

                      I'm just using TextEdit to create the text files.  When I put \x0B into the file and then import into FileMaker nothing really happens.  It gets imported as plain text.  I'm sure this has more to do with me being completely clueless than anything else.

                      • 8. Re: Importing Tab Separated Text Files
                        lbellino

                        I have a similiar issue with importing txt files when it's tab delimited. I have multiple lines of text which is similiar. For example, the lines would look like below:

                         

                        StoreNumber StoreName City State Department Area
                        11356 BASSETT CENTER EL PASO TX Accessories Pad  2306' SF
                        11356 BASSETT CENTER EL PASO TX Boys Pad  2439' SF
                        11356 BASSETT CENTER EL PASO TX Combo Pad 1  3854' SF
                        11356 BASSETT CENTER EL PASO TX Combo Pad 2  564' SF
                        11356 BASSETT CENTER EL PASO TX Combo Pad 3  1501' SF
                        11356 BASSETT CENTER EL PASO TX Combo Pad 4  2347' SF
                        11356 BASSETT CENTER EL PASO TX Girls Pad  2179' SF

                         

                        When I import it, it reads each line as a page. Is there a way when I import it, that I only have to link up the store number, name, city, state only once instead of doing it on each page (each line of text)? As for the dept. areas, is there a way that all the different areas would show up on one page where I can link them all up at the same time? Should I be setting up my text file differently? Or setting up my database differently? Any help would be greatly appreciated. Thanks.




                        • 9. Re: Importing Tab Separated Text Files
                          comment_1

                          It's not a similar issue.

                           

                          You must import the file twice: once into a Stores table, Importing the StoreNumber and the rest of the store attributes, and once into an Areas table, importing the StoreNumber and the area attributes. In the Stores table, define the StoreNumber to validate as Unique, Validate always. Relate the two tables by matching StoreNumber.

                          • 10. Re: Importing Tab Separated Text Files
                            philmodjunk

                            What do you mean by "Page" ?

                             

                            One row should represent one record.

                             

                            If the columns of text are separated by tab characters, your import should enable you to map columns of text to specific fields in your database table.

                            • 11. Re: Importing Tab Separated Text Files
                              lbellino

                              I mis wrote...you're correct that they are records. Each line that I have in the one text file represents a record. So if I have 30 lines of text, each is a record. But in my case, each line says exactly the same thing, except for the different department areas and the square footage for them.

                              • 12. Re: Importing Tab Separated Text Files
                                FentonJones

                                When you type "\x0B" (no parenthesis, zero not "o") into the text in TextEdit, you just get those characters; as you say nothing happens. You need to get TextWrangler.

                                http://www.barebones.com/support/updates.html

                                One of the world's best text editors for free. One of the oldest Macintosh products (BBEdit)'s little brother.*

                                 

                                If you type "\x0B" into TextWrangler (TW), you also get nothing. But if you select that "\x0B", then use the command to set the Find criteria (Cmd-E, Cmd-opt-E is the "replace criteria), then you see "\\x0B" in the Find dialog. So, the text editor escapes it for you when set as the Find criteria. Now, put "\x0B" into the Replace box, and the text editor will replace the text "\x0B" with a real Vertical Tab whitespace character (which looks like a red upside down question mark if you turn on Show Invisibles).

                                 

                                This will import as is just as you want into FileMaker, as comment said. When developers say "text editor" we usually mean other than TextEdit (which I don't really know what it is; it's very good at what it does, but it isn't really text editing).

                                 

                                * I have BBEdit, which costs some money, but has many more features (to do with HTML, XML, etc.), as well as "Clippings". But TextWrangler shares an amazing amount of BBEdit's text feature set.

                                • 13. Re: Importing Tab Separated Text Files
                                  lbellino

                                  Thanks for the info. What type of program do I need to open Text Wrangler? I've already downloaded it from the website but can't open it..

                                  • 14. Re: Importing Tab Separated Text Files
                                    FentonJones

                                    Sorry, that answer was meant for the original poster (or whoever it was who said they were using the TextEdit application, hence were on a Mac). It is just a standard Mac application. So you'd just drag it off the DMG into your Applications folder (or within), then double-click it. It will not run on a PC. There are other text editors for PC.