1 2 Previous Next 17 Replies Latest reply on Nov 24, 2015 11:22 AM by rouelf

    Excel or CSV import in FMGo

    TorstenBernhard

      Yesterday evening I created an ad-hoc solution for my wife who is managing a materials test lab.

      It took me some two hours to set it up and produce results from test data entered via CSV import. Sleek job done, that's where FileMaker excels in efficiency and ease of use.

      This morning I put it on my pad in FMGo. And found the following:

       

      CSV files received by email can be imported and stored in records and calculations work as well.

       

      One single but crucial script step does not work: The CSV file's data cannot be imported. This obiously breaks the entire workflow and makes it unusable on iOS.

      I guess it doesn't work on the new iPad Pro, too, since it's FMGo on iOS.

       

      Before writing a feature request I would like to read if any of you would find it useful to add the feature of importing data from Excel or CSV files in FMGo?

       

      Thanks in advance for feedback

      Torsten

        • 1. Re: Excel or CSV import in FMGo
          mikebeargie

          Torsten, you can use a webviewer to manually parse CSV files for import. It's a hackaround, but it works consistent enough for production systems.

           

          Would a native import be useful? yes of course. But it IS possible to import txt/csv to FMGo.

          • 2. Re: Excel or CSV import in FMGo
            rouelf

            Torsten, this has been an issue for a long time, go ahead with the feature request. Am sure it has been done before, but do it again.

             

            In the mean time there is a work around, though time consuming to write the scripts to parse the data into the appropriate fields in the table(s) of your solution. The following is a rough guide:

             

            Import CSV - FM Go

            You can import a CSV file to Filemaker Go using the Web Viewer.

            Place the CSV document in FileMaker Go so you can see it in Files on iPad/iPhone.

             

            example file: YourFile.csv

            In Layout, give the Web Viewer a Name such as WEBB  - (Inspector: Position Tab).

            In Web Viewer, Web Address (Object name: WEBB) write: Get( DocumentsPath ) & "YourFile.csv".

            Now you can see the CSV file like a spreadsheet in the Web Viewer.

             

            To import the information you have to write a script. Use GetLayoutObjectAttribute ( "WEBB" ; "Content" )

            to place the data in a variable or a field.

            when you use GetLayoutObjectAttribute on an iPad it grabs a lot of html with the data.

            You can study the data in the variable or field to figure how to scrape / parse the needed data. Below is

            a rough algorithm for how to find: the beginning and end of table, beginning and end of a record, beginning and end of field data.

             

            Hope that is useful.

             

            .

             

            <table>

            <tbody>

            <tr>

            <td>Date</td><td>Activity</td><td>Group</td><td>Start</td><td>End</td><td>Duration</td><td>Distance</td><td>Steps</td><td>Calories</td>

            </tr>

            .

            .

            .

             

            Loop

               First search for <table> for start position

            Loop End

             

            Loop

               New Records

               ....Every records starts with <tr> and ends with </tr>

               Loop

                  ....Every field starts with <td> and ends with </td>

                  Set Field

               Loop End

               Exit Loop If </table>

            Loop End

            • 3. Re: Excel or CSV import in FMGo
              rgordon

              I used to use a webviewer but found that Insert from URL a better option to but the text from the csv into a global field. The copy the data to a variable to parse the data. The script will run faster if you do the parsing with a variable versus a global field.

              • 4. Re: Excel or CSV import in FMGo
                beverly

                Do you use Insert From Url for CSV that is in your Documents (as FMGo sees it)?

                beverly

                • 5. Re: Excel or CSV import in FMGo
                  rouelf

                  Beverly,

                  Sadly, I have not yet used Insert From Url. Am still using a Web Viewer. What I do is, via script insert the CSV file to a container. Save the name of the csv file to a text field, Change the extension from .csv to .txt in the text field using Substitute. Then Export the Container with the csv file to the FM Go document path with a .txt extension (using the name in the text field). So that a new file is created (with .txt extension) in the FM Go document paths. Then point the Web Viewer to the file with the .txt extension in the document path. Then save using GetLayoutAttribute of the Web Viewer to a text field.  This text field now has data with most of the html stuff removed. The data is clean, in rows and comma separated, easy to parse.

                   

                  Of course one can change the extension of the csv file to .txt before transferring to the FM Go document path. and still use the Web Viewer and text field as above.

                   

                  Possible Issue:

                  In iOS I can see the Web Viewer has all the data. But the Text field screen display has data truncated to the iOS limitation of 64K characters. But the text field does appear to have all the data.

                  • 6. Re: Excel or CSV import in FMGo
                    beverly

                    roelf, I was asking Roy how he did this, but your explanation is great & I thank you!

                     

                    Roy, where are the .csv documents when you Insert From URL? and/or can they be in the Documents folder on iOS?

                    Beverly

                    • 7. Re: Excel or CSV import in FMGo
                      rgordon

                      Bev, the .csv is in the FileMaker Go documents folder. If you email the .csv file to the iPad or iPhone, you'll have an opportunity to tap on the attached csv and save it directly to  fmgo. You can also do this from Dropbox. I found when you do it this way instead of thru the webviewer, there is a lot less html garbage that you have to deal with. Also another problem that I had with the webviewer is if I had a string of numbers that looked close to being a phone number, the numbers would get converted to a phone number format.

                      • 8. Re: Excel or CSV import in FMGo
                        rouelf

                        rgordon,

                        Changing the extension from .csv to .txt also seems to clean up the html stuff when using the web viewer. The records are in lines without html (using the GetLayoutAttribute), and the fields are simply separated with commas. Yep, numbers that look like phone #’s, get treated like phone #’s.

                         

                        I use the web viewer because my files are always larger than the iOS 64K character limit for viewing the data.

                        • 9. Re: Excel or CSV import in FMGo
                          beverly

                          yes, I agree. Email or Dropbox. Once IN the Documents folder what 'url' do you use for "Insert From URL"?

                          Thanks!

                          • 10. Re: Excel or CSV import in FMGo
                            rgordon

                            here is an example

                             

                            Insert from URL[Select;No Dialog; g_data; "file"&get(documentspath)&"Data.csv]

                             

                            g_data is a global text field. I don't think .txt will work with the Insert URL.

                            • 11. Re: Excel or CSV import in FMGo
                              rgordon

                              Most of my users want to import data from Excel but Excel does not like saving data with a .txt extension. You can still have the users save the file as a csv and then have FileMaker insert the file into a global field and then export it back out with the txt extension.

                              • 12. Re: Excel or CSV import in FMGo
                                TorstenBernhard

                                Hi All

                                Thanks a lot for your help and advise. I managed to import the csv via web viewer as suggested by Mike and rouelf.

                                Beverly, rgordon, I also tried Insert From URL but this did not work for me until now. I guess its a path issue.

                                 

                                It does the heavy lifting (120 x 500 data matrix) even on my old iPhone 5 (around one minute to get the data imported and processed, 15 sec on iPad Air, 1 sec on Macbook), which is really cool.

                                And now I give it a nice mobile interface :-)


                                Cheers

                                Torsten

                                • 13. Re: Excel or CSV import in FMGo
                                  beverly

                                  Yes, Path IS important, that's why I wanted Roy to confirm his method!!

                                  • 14. Re: Excel or CSV import in FMGo
                                    TorstenBernhard

                                    Insert from URL[Select;No Dialog; g_data; "file:/"&get(documentspath)&"Data.csv] works fine in FMP but I can't make it work on iOS (iPad).

                                    What do I miss out?



                                    1 2 Previous Next