9 Replies Latest reply on May 30, 2012 4:57 PM by strngr12

    Creating XML from Tab Delimited Document

    strngr12

      Hello!

       

      I am trying to turn a document with this format:

       

      ReelID:115
      AltReelID:B115_C003_0522Q5
      CamReelID:B115
      Camera:B
      Camera Model:EPIC-X
      Camera Model ID:5

       

      Into something that looks like this:

       

      <attribute="ReelID>115</attribute>

      <attribute="AltReelID">B115_C003_0522Q5</attribute>

      <attribute="CamReelID">B115</attribute>

      <attribute="Camera">B</attribute>

      <attribute="Camera_Model">EPIC-X</attribute>

      <attribute="Camera_Model_ID">5</attribute>

       

      There are two things going on here. One is that I need the formatting. The other is that the attribute key names can't have spaces in them. Can anyone help? It would be much appreciated!

       

      Thanks!

       

      Dan

        • 1. Re: Creating XML from Tab Delimited Document
          comment

          Not sure how your question applies to Filemaker specifically. True, you could import the tab-delimited file and export the data as XML, but there are probably other applications that would be more efficient at this task.

           

           

          strngr12 wrote:

           

          One is that I need the formatting. 

           

          What do you mean by "formatting"?

           

           

          strngr12 wrote:

           

          The other is that the attribute key names can't have spaces in them.

           

          Aren't the attributes constant? If not, you could simply replace spaces with underscores, as shown in your example. 

          • 2. Re: Creating XML from Tab Delimited Document
            strngr12

            Hello,

             

            Thanks for the response.  The text is actually in a field in a Filemaker Document.  The process itself is part of a much bigger process of creating an xml based on field names and values in the document as a whole.  The rest I have figured out as they are single fields with single values, but this one field, as you can see, is just an import of a whole tab delimited output file from another application.  They're just importing the entire document into one field.

             

            Since I don't have access to the file this was imported from, and since I'd like to turn the entire xml output into a push button process I'd like to contain it into a script operating on that field.  That way, you can just push a button in the FMP doc and export an xml that contains all the other fields plus this one parsed.

             

            I misspelled that, but what I meant is that I need to reformat the tab delimited text file into xml key/value pairs.  I can just run a substitute routine on the spaces, but I thought it important to mention because it needs to be in any code that someone may help with.

             

            Thanks,

             

            Dan

            • 3. Re: Creating XML from Tab Delimited Document
              comment

              strngr12 wrote:

               

              The text is actually in a field in a Filemaker Document. 

               

              That's not a good starting point. The text obviously contains structured data, and the best way to store/process the data is to match its structure to Filemaker's structure. Perhaps you should start the process by creating records in a related table (6 records with 2 fields, using your example).

               

               

              The other thing is that your XML output example is not valid XML: an attribute cannot stand alone - it must describe an element, e.g.

               

              <YourElement ReelID="115" AltReelID="B115_C003_0522Q5" CamReelID="B115" Camera="B" Camera_Model="EPIC-X" Camera_Model_ID="5"/>

               

               

              strngr12 wrote:

               

              The process itself is part of a much bigger process of creating an xml based on field names and values in the document as a whole. 

               

              If you mean you want to put together the entire XML document as text in a Filemaker field, I'd recommend against it.

              • 4. Re: Creating XML from Tab Delimited Document
                strngr12

                Hello,

                 

                Thanks for helping.  I have no control over the document I am staring with so I can't change how the metadata is stored in it.  I just need to be able to export the metadata in each record as a specially formatted xml file for use in another application.  I'm not trying to store the xml file in FMP, I'm trying to take records in the FMP document and export an xml file with info from each record that corosponds to a file in another application - namely a quicktime file.

                 

                Basically my script so far goes to a record, grabs the name of the item to be updated, itereates through the fields and writes to a text file a key/value pair taken from each field.  When it reaches the last field of a record it moves on to the next record and does the same.  The output looks like this:

                 

                       <xml>

                               <item_list>

                                       <item name="Scene1.mov">

                                            <attributes>

                                               <attribute key="attribute1">value1</attribute>

                                               <attribute key="attributeX">valueX</attribute>

                                            </attributes>

                                       </item>

                                       <item name="SceneX.mov">

                                            <attributes>

                                              <attribute key="attribute1">value1</attribute>

                                              <attribute key="attributeX">valueX</attribute>

                                            </attributes>

                               </item_list>

                       </xml>

                 

                Where sceneX is any number of items with each record in the document being a different item. Attributes are any number of attributes with the keys taken from the field name and the value taken from the value of the field.

                 

                As mentioned, the vast majority of the fields are easy because it's just:

                 

                field name = attribute key name

                field value = attribute value.

                 

                But each item also has this one text field that is an imported tab-delimited text file that I would also like to turn into that format and include in the attributes list.  That's tougher to do and I'm not sure how to go about it.

                 

                I can just take the file, open it in FMP and then run the same kind of routine as above, but then that would be a two step, two document process.  I'd rather, if possible, keep this all self contained so when someone ran the process they could just hit one button and get that output.  Once this is done I need to pass this along to someone else to run every day so I need to keep the workflow as tidy as possible.

                 

                Thanks,

                 

                Dan

                • 5. Re: Creating XML from Tab Delimited Document
                  comment

                  It's difficult to see the entire picture from your description. Judging from your output example, you should have a Scenes (or Items?) parent table, and a child table of Attributes. IIUC, the attributes are actually bundled in a field of the parent table. As I said, that's not a good starting point. Why can't you import the attributes as records into the child table?

                  • 6. Re: Creating XML from Tab Delimited Document
                    PalmDBS

                    Although I agree with Michael that this isn't proper XML, here is a script that delivers what you proposed (file also attached)...

                     

                    Screen Shot 2012-05-30 at 3.16.37 PM.png

                    • 7. Re: Creating XML from Tab Delimited Document
                      strngr12

                      Sorry, I'll try to be more clear.

                       

                      So, the way it works is that there's a production that has a "codebook."  The primary use of that codebook is that every shot they do has a unique record - say, Scene 28, Take 2, A Camera.  In that record are individual fields - one for scene, take and camera, for example, but also a lot of other stuff like "lens" and "Timecode" and "Sound Roll."

                       

                      It's their codebook put together for their purposes and formatted the way they want it so I have no control over that.

                       

                      Seperate to that there is another, third-party service in which they store Quicktime files of all their shots.  Those files are named using the format "scene_take_camera.mov" so, for the example above, "28_2_A.mov" would be the name of the quicktime for that shot.

                       

                      Now, that other service can add metadata to the files using a specific xml format so that if you highlight a QT and hit an "info" button all this metadata from the codebook will pop up so you can see what lens it was shot with or what sound roll the sound came from, for example.  I am trying to write a script for their codebook that they can use to create an "Update" button that will create an xml to add all the metadata to this other service.

                       

                      Now, I can use simple FMP calcualtions to match the filename from the scene, take and camera fields for the <item name="28_2_A.mov> line of the xml to match the file.  Once I have that for a specific record I can just iterate through each field and create the <attribute key="sound_roll">A005</attribute> part, for example, to add all the metadata.  That is all tested and works fine for all the fields except that one.

                       

                      So I can get this far:

                       

                      <xml>

                          <items>

                              <item name="28_1_A.mov">

                                  <properties>

                                      <description>Everybody's gone surfing</description>

                                  </properties>

                                  <attributes>

                                      <attribute key="episode_number">Show 101</attribute>

                                      <attribute key="lens_focal_length">35mm </attribute>

                                      <attribute key="lto_primary">1071L5</attribute>

                                      <attribute key="lto_secondary">1072L5</attribute>

                                      <attribute key="is_deleted">No</attribute>

                                      <attribute key="shot_name">28_01-A</attribute>

                                      <attribute key="filepath_r3d">"/Volumes/RAID6B/Jobs//B115_C003_0522Q5_001.R3D"</attribute>

                                      <attribute key="filepath_h264">"/Volumes/RAID6B/Jobs//028_01-A.mov"</attribute>

                                  </attributes>

                              </item>

                          </items>

                      </xml>

                       

                      But I'd like to also get that whole imported document in that one field into that <attribut="key">value</attribute> list.  So, to sum up, I can't change the way the data is imported or represented in the FMP document nor can I change the format of the xml I need to output.  I'm at a point now where my script can output all of their text fields into an xml document that works as far as it goes, but there is a lot of metadata in that imported docuemnt that I would also like to get into the xml.

                      • 8. Re: Creating XML from Tab Delimited Document
                        comment

                        I am afraid I am still confused. It's their codebook, in their format - but it's you that gets their data, isn't it? If their data is structured (i.e. there is a field for Scene, a field for Take, a field for Camera, etc. - and every shot is an individual record), at what point does it get all packed together into a single field?

                         

                        My point is that there are two ways you can go about it: one follows the path of least resistance, which is to take advantage of (a) Filemaker being a database (i.e. something that handles data structured as records and fields), (b) XML being also a structured data format, and (c) Filemaker's XML exporting capability.

                         

                        The other way is to look at the whole thing as an exercise in text manipulation. This is certainly possible, but it requires a lot of effort and IMHO is not worth-while.

                        • 9. Re: Creating XML from Tab Delimited Document
                          strngr12

                          Thanks so much!  I think I can make this work!