Insert Row between each Record on Export Records

[josephmyates]

Trying to figure out a way to Export all the records from a FM database to an Excel file - that will Insert a row above each record - and insert only data from Field 2 (column B ) into Field 1 (column A) of the inserted row?

For Example:

INSERTED ROW - Field 1 (NAME) <- from Record#1

RECORD#1 - Field 1 (SKU), Field 2 (NAME), FIELD 3, Field 4, etc

INSERTED ROW - Field 1 (NAME) <- from Record#2

RECORD#2 - Field 1 (SKU), Field 2 (NAME), FIELD 3, Field 4, etc

Any help on how I would do this greatly appreciated!

[Ocean West]

instead of exporting as excel build your desired structure as desired in a variable or field and save it out as csv.

or create a virtual list and export the virtual list. 

[comment]

It's not possible to modify the exported file when exporting in the Excel (.xlsx) file format (except possibly with a plugin).

However, it is certainly possible if you can accept another format - such as .csv or the Excel 2003 XML format - for the exported file. In such case I would suggest exporting as XML and using a custom XSLT stylesheet to transform the export to the desired format. Another option is to build the exported file within Filemaker first (as suggested above) - but this requires adding some resources to your file and the result can only be .csv.

 

[bcooney]

You can export from a virtual list table as .xlsx

[comment]

5 minutes ago, bcooney said:

You can export from a virtual list table as .xlsx

True, but still lot of work - probably the most labor-and-resources-intensive option of all mentioned.

 

[Ocean West]

I am sort of doing this type of thing right now. That generates this tab delineated object in a variable. That will be eventually imported or added to another file. 

If I wanted I could add a header row to match the target table so I can import matching names.

FF7E21FF-A897-45DE-BD5B-936E7651A37857[1620] JBLFK - JBL Flywear Kit VRX-AF	1	10
FF7E21FF-A897-45DE-BD5B-936E7651A37857[1184] DC - 100' Power Cable	1	10
C08ABC73-0240-48BA-8FDC-7578E599559657[1184] DC - 100' Power Cable	1	-3
FF7E21FF-A897-45DE-BD5B-936E7651A37857[1036] PNDG - 17' GRAY PIPE & DRAPE  W/ uprights & cross bars	1	12
C08ABC73-0240-48BA-8FDC-7578E599559657[1036] PNDG - 17' GRAY PIPE & DRAPE  W/ uprights & cross bars	1	-3.6
B6A724AB-ABE3-4EF8-BF02-418C8C0723FA57[1426] PCM - Professional Camera Operator	1	10
DB30861F-11EE-4505-B92E-25397B659AEA57[1424] T20 - 20' Box Truck	2	5.00
5373AA12-B3B6-4BCA-8DFB-5BF1405F4ADD57Credit Card Fee	1	1.82

Below is my script (still being developed)

Commit Records/Requests
	[ No dialog ]
Set Variable [ $$items; Value:"" ]

#Equipment
Go to Object [ Object Name: "portal_equipment" ]
Go to Portal Row
[	 First ]
Loop
	Exit Loop If [ Get(ActivePortalRowNumber) = Count( show_equipment::uid ) ] 
	Set Variable [ $items; Value:List ( $items ;
		qboItem ( "equipment" ) & Code(9) & "[" & show_equipment::id_equipment & "] " & show_equipment::product_code & " - " & show_equipment::Product & Char(9) & show_equipment::ExtendedQty & Char (9) & show_equipment::Price &
		If ( show_equipment::DiscountRate ; "¶" & qboItem ( "discount" ) & Code(9) & "[" & show_equipment::id_equipment & "] " & show_equipment::product_code & " - " & show_equipment::Product & Char(9) & "1" & Char (9) & -show_equipment:: Discount )
		)]
	Go to Portal Row
		[ Next; Exit after last ]
End Loop

#Labor
Go to Object [ Object Name: "portal_labor" ] 
Go to Portal Row
	[ First ]
Loop
	Exit Loop If [ Get(ActivePortalRowNumber) = Count( show_labor::uid ) ] 
	Set Variable [ $items; Value:List ( $items ; qboItem ( "labor" ) & Code(9) & "[" & show_labor::id_equipment & "] " & show_labor::product_code & " - " & show_labor:: Product & Char(9) & show_labor::ExtendedQty & Char (9) & show_labor::Price
	)]
Go to Portal Row
	[ Next; Exit after last ]
End Loop

#Trucking
Go to Object [ Object Name: "portal_trucking" ]
Go to Portal Row
	[ First ]
Loop
	Exit Loop If [ Get(ActivePortalRowNumber) = Count( show_trucking::uid ) ]
	Set Variable [ $items; Value:List ( $items ;
	qboItem ( "freight" ) & Code(9) & "[" & show_trucking::id_equipment & "] " & show_trucking::product_code & " - " & show_trucking::Product & Char(9) & show_trucking::ExtendedQty & Char (9) & show_trucking::Price
	)]
Go to Portal Row
	[ Next; Exit after last ]
End Loop

#Fee
Set Variable [ $items; Value:List ( $items ;
	If ( Shows::isCreditCard ; qboItem ( "fee" ) & Code(9) & "Credit Card Fee" & Char(9) & "1" & Char (9) & Shows::
	CreditCardAmount) )]
Commit Records/Requests
	[ No dialog ]
Set Variable [ $$items; Value:$items ]

 

[josephmyates]

19 hours ago, Ocean West said:

instead of exporting as excel build your desired structure as desired in a variable or field and save it out as csv.

or create a virtual list and export the virtual list. 

I actually do want to export this as a CSV - so want to know how I would set this up as a "variable or field" and save out as a csv.

Started looking into creating a virtual list - but seems a little complex for what i want to do

[comment]

4 minutes ago, josephmyates said:

I actually do want to export this as a CSV

Are you still in version 16?

[Ocean West]

An example: 

records.fmp12

[comment]

48 minutes ago, Ocean West said:

An example: 

Careful with that. It will fail if a field contains a double-quote character.

 

Edited March 9, 2022 by comment

[Ocean West]

Oh I agree, you have to really know your data set when adhoc creating specific file format - as it doesn't pass thru any export interpreters. Especially when writing files knowing proper line endings etc. 

[josephmyates]

2 hours ago, Ocean West said:

An example: 

records.fmp12 288 kB · 1 download

Thanks for the example file Ocean West - the listOf is exactly how I want to have the exported CSV file appear - 6 rows

But when I did an Export Records (CSV) and selected the listOf field to export - it has only 3 rows in the CSV file, not 6

Also tried exporting the row field during export - and again only 3 rows were exported to CSV file

Also all fields are exported into Column A

What am I missing?

[Ocean West]

Right click field export field contents. 

[josephmyates]

1 hour ago, Ocean West said:

Right click field export field contents. 

Not sure where I right click export field contents? 

[comment]

5 hours ago, Ocean West said:

export field contents

That's not something I would readily recommend, because the result will be UTF-16 encoded. At least not without testing first that the target application can read such file.

But I get the impression that my advice is not welcome here, so...

[Ocean West]

I completely agree with @comment that these shortcuts may seem to work but more often than not they will fail at scale and it becomes technical debt. Requiring more work to fix or cause random errors that may not be easily identified. 

Here is a method that will use the virtual list technique and exporting the csv

records.fmp12

 

[josephmyates]

Thanks Ocean West! That exports the CSV exactly as I need - now just have to wrap my head around creating these calculation fields and script in my databases

[comment]

5 hours ago, Ocean West said:

I completely agree with @comment

Uhm... you are agreeing with something I did not say.

What I did say - and will repeat here for the sake of anyone who might be reading this in the future, looking for a solution to a similar problem - is that there are two simple solutions:

1. Export as XML/XSLT
Required resources:
- 1 script;
- 0 fields;
- 1 external text file.
Additional advantage: fields can be exported using the "Apply current layout's data formatting…" option, which can save a lot of work when exporting currency and/or date fields, for example.

2. Build the CSV file inside Filemaker 
Required resources:
- 1 script;
- 1 global container field (can be in any table); in version 18 or higher this field is no longer required, since the result can be written directly to a data file;
- (optional) 1 custom function to escape in-field quotes.

IMHO either one of these methods is much preferable to virtual table. But then I am not here to sell anything, and since OP chose to ignore me I will leave it at that.
 

Edited March 10, 2022 by comment

[josephmyates]

Comment, Thanks for your solutions - as I am looking for a simple solution - as I am not experienced at all with creating virtual tables

For solution #1 - Export as XML/XSLT - after the export I can then open the XML file and save as CSV file format correct?

Not sure exactly how to write the script needed to create this Export?

Record 1 Row 1 - FIELD 2

Record 1 Row 2 - FIELD 1, FIELD 2, FIELD 3, FIELD 4, FIELD 5, FIELD 6, FIELD 8, FIELD 9

Repeat above with next records

[comment]

6 hours ago, josephmyates said:

For solution #1 - Export as XML/XSLT - after the export I can then open the XML file and save as CSV file format correct?

No. The export process creates the expected CSV file directly. No subsequent action is required.

I am attaching a minimalistic demo showing how this works. By "minimalistic" I mean there is no quoting of cells and no escaping of in-field quotes - IOW, the exported fields cannot contain commas, carriage returns or double-quotes.

demo.zip

[josephmyates]

Comment, thanks for the demo!

This is exactly what I'm looking for...

Used your "export2rows.xsl" stylesheet to export records from my database - and it created a csv file just as I needed it formatted

Can't thank you enough!!

 

[Ocean West]

If your in a hosted and this function needs to run from multiple users you may need to consider managing the style sheet.

Either exporting from a container field or if memory serves the style sheet can be hosted on web server and referenced when importing. 

[comment]

On 3/12/2022 at 1:23 AM, Ocean West said:

if memory serves the style sheet can be hosted on web server

Correct: the stylesheet can be specified either by a file path or by a URL.