Export CSV with Headers

[jaboda]

I know this has been discussed elsewhere but I needed to Export some data and I wanted column headers. In Web Direct.

There seemed to be two options - either using an XSLT stylesheet and exporting as XML. Don't think this works in Web Direct, and looked like it would take some time to get my head around.

I also see there's a suggestion to export as a Merge file, and simply change the file extension. This looks good but won't support custom Column names.

So I developed my own simple export which works in FileMaker and Web Direct:

Quote

Set Variable [ $path; Value:"Rainfall Data.csv" ]
Set Variable [ $CurrentRecord; Value:Get ( RecordNumber ) ]
Go to Record/Request/Page [ First ]
Set Field [ Rainfall::Global; "\"Country\",\"Precipitation\",\"Year\"¶" ]
Loop
     Set Field [ Rainfall::Global; Rainfall::Global & "\"" & Rainfall::Country & "\",\"" & Rainfall::Precipitation & "\",\"" & Rainfall::Year & "\"¶" ]
     Go to Record/Request/Page [ Next; Exit after last ]
End Loop
Go to Record/Request/Page [ $CurrentRecord ] [ No dialog ]

Set Field [ Rainfall::Global Container; TextEncode ( Rainfall::Global ; "utf-8" ; 4 ) ]
Export Field Contents [ Rainfall::Global Container; “$path”; Create folders:No ]

 

It works well, but can cause complications if Unicode characters are included.

It seems Excel doesn't like UTF-8 without a Byte order mark (BOM) character, and requires the user to do a text import. LibreOffice fares better and prompts for an import defaulting correctly to UTF-8.

Any ideas on how to fix this would be appreciated. I'm thinking I'd need to replace the TextEncode with some PHP or a ScriptMaster function.

I have attached an example db.

 

Enjoy

CSV Headers Demo.zip

Edited July 13, 2020 by jaboda

[Ocean West]

be aware export field contents exports as UTF-16 

You may want to use baseElements plugin to export field contents or use the data file create open and write functions or even base elements to write out the file vs exporting contents.

since this is web direct you may need to export as PSOS and then import the file back to container where user can export.

 

[jaboda]

11 minutes ago, Ocean West said:

be aware export field contents exports as UTF-16 

You may want to use baseElements plugin to export field contents or use the data file create open and write functions or even base elements to write out the file vs exporting contents.

Does it export as UTF-16 even if the container is UTF-8?

Also not sure baseElelements will work with Web Direct, and sure the Data File script steps won't.

I could use PSOS but I still need to download it to the user, which would involve Export Field Contents.

Edited July 13, 2020 by jaboda

[comment]

9 minutes ago, jaboda said:

Does it export as UTF-16 even if the container is UTF-8?

Your script exports a UTF-8 encoded file.

 

21 minutes ago, jaboda said:

It works well, but can cause complications if Unicode characters are included.

Not sure what complications you are referring to. All characters are Unicode characters. 

 

[jaboda]

I tested it again and it does export UTF-8, not UTF-16.

 

The problem is that Excel requires a Byte order mark to open UTF-8 CSV files automatically. Otherwise it defaults to ASCII CSV. This is particularly annoying as UTF-8 is not supposed to require a BOM, unlike UTF-16.

The workaround is to open it in LibreOffice, which works correctly, or use the 'From Text/CSV' button on the Data ribbon in Excel.

 

I know this is an issue with Excel not FileMaker or the script, but as Excel is for many the de facto standard it'll cause a problem.

Edited July 13, 2020 by jaboda

[Ocean West]

are you opening on windows? or Mac my mac seems to opening exported file.

 

[Sky Willmott]

The issue is that that when the exported CSV opens in Excel, it messes up the special characters such as those used in "São Tomé and Principe"....

Aha - found a solution 🙂

Just insert the BOM as the first character of the global field content that you are exporting. It can be represented using Char ( 65279 ), so your calculation used to set the first line can be:

Char ( 65279 ) & "\"Country\",\"Precipitation\",\"Year\"¶"

I just tested in FMP and WebDirect and works great.. The exported CSV opens in Excel with all characters displaying correctly.

Revised sample file attached...

CSV Headers Demo_v2.fmp12

Edited July 13, 2020 by Sky Willmott

[jaboda]

10 minutes ago, Sky Willmott said:

Aha - found a solution 🙂

Just insert the BOM as the first character of the global field content that you are exporting. It can be represented using Char ( 65279 ), so your calculation used to set the first line can be:

Char ( 65279 ) & "\"Country\",\"Precipitation\",\"Year\"¶"

Thanks Sky - I did try that but didn't have the right Char code for the BOM.

It works perfectly now.

[Ocean West]

Awesome!

Here is a bit revised logic made. 

Using Insert Text for the $header

I also wrapping quote function and substitutes returns for commas make it a bit easier to read then escaping quotes.

[normanicus]

If you use the script step 'Write to Data File' you can  control just what is in the resulting file such text coding is the right type of linefeeds or whether data is enclosed in inverted commas.

It's a pity that the CSV standard isn't.

[jaboda]

Found a small bug with this export.

When exporting the fields the above examples either use the Quote function or add \" to add a double quote to the start and end of fields.

Unfortunately if the field contains double quotes this is not compliant with RFC-4180, which specifies that they should be escaped with a ". The above examples either leave them as is or use the FileMaker standard of \".

The fix is easy though - use the following custom function instead of the Quote function:

Quote

// CSVQuote ( text )
// Returns the text in quotes. Use " as escape character for quote instead of \

"\"" & Substitute ( text ; "\"" ; "\"\"" ) & "\""