5 Replies Latest reply on Apr 13, 2013 2:39 PM by steve_ssh

    How to convert csv into html table with links?

    carlo.m

      I have CSV text.

       

      4 Columns:

      -ID

      -Start Date

      -End Date

      -Locations

       

      I want to convert that csv into an HTML table that takes advantage of the FMP://hostip/filename?script=ScriptName&Parameter=Parameter

       

      So I just really want to show columns 2,3 and 4 but need the information from column 1 as the parameter for the Script to run with FMP:// reference link

       

       

      I now I can create a script or a custom function to parse out line by line. But I think there is a more modular/efective way to approach this.

       

      Any ideas?

        • 1. Re: How to convert csv into html table with links?
          steve_ssh

          Hello Carlo,

           

          If, by chance, your CSV text is the result of an ExecuteSql function call, keep in mind that you can use the double-pipe || concatentation operator to tailor the Sql output to be very close to the HTML table that you are looking to create.

           

          In such a case, your work is almost done -- the only issue may be whether or not it will be necessary to process the output so that certain characters are properly escaped for HTML.

           

          Best,

           

          -steve

          • 2. Re: How to convert csv into html table with links?
            carlo.m

            Hi Steve,

             

            The csv is a result of ExecuteSQL. Would you mind elaborating on how using the || character instead of , will help?

             

            Thanks for the help!

            • 3. Re: How to convert csv into html table with links?
              steve_ssh

              Hi Carlo,

               

              Sure thing:

               

              Let's say, for instance, that right now, you are using ExecuteSql similar to this:

               

              Let([

               

                  varQuery = "SELECT ID, StartDate, EndDate, Locations FROM MyTable WHERE ID > 0";

              ];

               

                  ExecuteSQL( varQuery; ""; ""; "" )

              )

               

              In which case your output might look something like this:

               

                  1,2012-12-01,2012-12-31,Foo

                  2,2013-01-01,2013-01-12,Foo2

               

               

               

              The joy of the || operator is that it allows you to perform string concatenations in the SQL output.

               

              You can take advantage of this feature to do something similar to this:

               

               

              Let([

               

                  varQuery = "SELECT '<a href=\"fmp://etc-etc-etc-insert-proper-URL-here?' || ID || '\"><span>' || StartDate || '</span>' || EndDate || '</span>' || Locations || '</span></a>'  FROM MyTable WHERE ID > 0"

               

              ];

               

                  ExecuteSQL( varQuery2; ""; ""; "" )

              )

               

              Which would yield output something like this:

               

                  <a href="fmp://etc-etc-etc-insert-proper-URL-here?1"><span>12/1/2012</span>12/31/2012</span>Foo</span></a>

                  <a href="fmp://etc-etc-etc-insert-proper-URL-here?2"><span>1/1/2013</span>1/12/2013</span>Foo2</span></a>

               

               

              I'd suggest playing around with this example, and I believe that you will be able to easily produce the results that you are looking for.

               

               

              Two cautions:

               

                1) Pay close attention to the placement of both your single and double quote chars -- a small mistake will cause you an error which can be difficult to trace.

               

                2) If your data (I'm thinking of the locations field, in particular) contains characters that need to be HTML escaped (such as a < char), then you will need to improve upon this idea slightly to ensure that your data is properly escaped by the time it is in HTML format.  This could be perhaps accomplished by performing a carefully crafted Substitute on your SQL output, or, if you are willing to add one more field to your schema, you could add an HTML-escaped version of the location field which can be used especially for this query.

               

               

               

              Hope this helps, and good luck!

               

              -steve

              • 4. Re: How to convert csv into html table with links?
                carlo.m

                That'll do it!

                 

                Thanks!!!

                • 5. Re: How to convert csv into html table with links?
                  steve_ssh

                  Excellent!  Glad it worked out!

                   

                  -steve