1 Reply Latest reply on Apr 10, 2012 6:35 PM by disabled_JackRodgers

    Using Filemaker to Format CSV Order data for a Import into Business System

    LawrenceBibi

      Title

      Using Filemaker to Format CSV Order data for a Import into Business System

      Post

      I am trying to use filemaker to arrange incoming order data from a cvs file.  I need to change the cvs file from a one line per order spreed sheet to a two line per order spreadsheet.

      I need to have the header information one one line and the item information on the line underneath. The first column indicates what the record will be. The Header portion of the data starts with HDR in the first row and the line item information will start with ITM in the first column directy under the HDR info.

      The CSV file has all of the data on a single excel row.  I am bringing that data into filemaker and splitting it up into two tables. 1. Hear table and 2. line item data.  I am connecting them with a po#

       

      How can I export the data so I get a header row first then a item detail row?  Any suggestions would be great.

        • 1. Re: Using Filemaker to Format CSV Order data for a Import into Business System

          I would grind out and transfer of data into a new table.

          Let's call the table you want NewTable and TableA and TableB.

          Watch how sneaky this is....

          Create your NewTable and create a Link using PO# to TableA and TableB

          Start in TableA then repeat these steps in TableB.

          go to first record in TableA

          Loop

          Go To Layout TableA

          Set $_po to PO in TableA

          Go to layout NewTable

          Set Field PO to $_po, commit record

          Go To Layout TableA

          using the relational link:

          Set Field Newtable to field TableA

          ...do this for all of the fields

          commit record

          next record exit after last

          end loop

          To to TableA

          You have transfered all of the data to new records in NewTable. Repeat for Table B.

          Sort NewTable by PO and your field describing the types of field and there you are.  <-- the trick

          Export as excel

          Of course you could import the two tables into your new table if you can match the fields and produce the same result.