Jump to content

Recommended Posts

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 by jaboda
Link to post
Share on other sites

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.

 

Link to post
Share on other sites
Posted (edited)
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 by jaboda
Link to post
Share on other sites
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. 

 

Link to post
Share on other sites
Posted (edited)

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 by jaboda
Link to post
Share on other sites

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"....

15171954_Screenshot2020-07-13at20_38_23.thumb.png.73bd3905885475e5fe01d150bca45b36.png

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 by Sky Willmott
Link to post
Share on other sites
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.

Link to post
Share on other sites

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. ;)

Screen Shot 2020-07-13 at 1.03.08 PM.png

Link to post
Share on other sites

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.

Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Who Viewed the Topic

    2 members have viewed this topic:
    JW_NZ  dsto22 

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.