Dear all,

I am trying to export a custom header like :

"Destination, country Code, Vessel / Flight Name, ETS, ETA, Agent, Sum Insured £, TMK Rate, TMK Risk"

It works fine but i could not display the £.

Any ideas?

Attached is the script used. and the output.

It's difficult, if not impossible, to reproduce the problem using your description. It seems you have a custom function named ExportXSLT() - but we don't see what it does. 

I am also puzzled what is the actual format of your export. Your $path variable suggests it's .csv - but IMHO exporting a .csv file with a custom header should be much simpler.

 

Edited January 12, 20232 yr by comment

Thank you for your reply, will it help if I send you the custom function?

It was from an example I had picked from a video.

 

/* ExportXML ( headerValues, delimiter )

*/

Let ( delimiter =  Char ( 44)  ;  // setting the delimiter to a comma

"<?xml version=\"1.0\" encoding=\"utf-16le\"?>" & ¶ & 
"<xsl:stylesheet " & ¶ & 
"    version=\"1.0\" xmlns:xsl=\"http://www.w3.org/1999/XSL/Transform\"" & ¶ & 
"    xmlns:fm=\"http://www.filemaker.com/fmpxmlresult\"" & ¶ & 
"    exclude-result-prefixes=\"fm\">" & ¶ & 
"    <xsl:output method=\"text\" version=\"1.0\" encoding=\"utf-8\" />" & ¶ & 
"    <xsl:template match=\"/\">" & ¶ & 
"        <xsl:text>" & headerValues & "&#xD;</xsl:text>" & ¶ & 
"        <xsl:for-each select=\"fm:FMPXMLRESULT/fm:RESULTSET/fm:ROW\">" & ¶ & 
"            <xsl:for-each select=\"fm:COL\">" & ¶ & 
"                <xsl:value-of select=\"concat('&#x22;', fm:DATA/., '&#x22;')\" />" & ¶ & 
"                <xsl:if test=\"position() !=last()\"><xsl:text>" & delimiter & "</xsl:text></xsl:if>" & ¶ & 
"            </xsl:for-each>" & ¶ & 
"            <xsl:text>&#xD;</xsl:text>" & ¶ & 
"        </xsl:for-each>" & ¶ & 
"    </xsl:template>" & ¶ & 
"</xsl:stylesheet>"

)

 

I had to use GBP, the customer is okay with that.

Too much hassle for such a little benefit 

Thank you comment for your comments.

28 minutes ago, Lamine said:

will it help if I send you the custom function?

It's a step forward, but it would still take me a lot of work to pinpoint the problem (which seems to be an encoding issue).

I believe it would be much better to scrap your entire method - which I find unnecessarily convoluted - and do simply this:

1. Export your records as .csv file;
2. Open the exported file as data file;
3. Read the contents of data file into a variable;
4. Add your header and write the result back to the data file.

Here is a minimal demo file showing the method:

CustomHeader.fmp12

Following this two techniques, I have the same 2 issues.

Accents (spanish languaje), and with or without accents involved, all the data is populated in the first column.

You are asking "what did I do wrong" - but you're not telling us what exactly you did.

WRT all the data ending up  in the same column, it seems like your target application does not recognize the comma as field separator when importing. And the accents issue could be due to an encoding mismatch. But these are pure guesses. We need a way to reproduce the problem before we can provide an intelligent answer.

Hint: if you're exporting to a .csv file and opening it in Excel (or in another spreadsheet application), check the contents of the exported .csv file first. That will tell you whether the problem is with the export itself or unrelated to Filemaker. 

Also if your final target is Excel, going through .csv may not be the best strategy for you.

 

As precise as always comment. But in this case I didn't really asked anything, been precise. Just a joke.

All I've done is:
- downloaded your file, opened it, pressed the blue button = first result.

- added (número) to the variable $header, pressed the blue button = second result.

I don't think I propperly understand what you mean with "check the contents of the exported .csv first". If this can be done opening the .csv file with WordPad, this is what I get:

Name, Sum Insured £, número
"Alpha","11"
"Bravo","22"
"Charlie","33"

Finaly I open it with Excell 2016, and the results are attached.

Edited July 3, 20232 yr by Enigma20xx

8 minutes ago, Enigma20xx said:

what you mean with "check the contents of the exported .csv first".

I mean open the exported .csv file in a text editor (not a word processor) application. If you're on Windows, I guess Notepad would be a good choice.

FWIW, I did exactly what you said you did (downloaded my file, added "número" to the $header variable and ran the script). This is how my result looks in a text editor (BBEdit on macOS):

I am attaching the exported file so you can compare it to the one you get (again, do the comparison in a text editor). If they are not the same, then we have a big problem because all 3 stages of producing the file (export, read and write) are specified as UTF-8 - so there should be no issues due to wrong encoding (at least not during this step of the process).

As for opening in Excel: it seems that your version expects a different field separator (most likely a semi-colon) and quite possibly a different encoding too. Try following the instructions here when opening the file:
https://quizandsurveymaster.com/getting-excel-properly-show-accented-characters/ 

 

 

ExportedFile.csv.zip

It works fine opening the file with excel, following the steps found in the link you provided.

A few clicks more than explected, but does the job.

As always, your help is very much appreciated, comment.

8 minutes ago, Enigma20xx said:

A few clicks more than explected

Maybe you will find another method more suitable. This will export the records as XML, using a custom XSLT stylesheet to transform the result to Excel 2002/2003 XML format (a.k.a SpreadsheetML). Although this is not a "real" Excel file, it is a native Excel format and you should be able to open it in Excel with a double-click.

Export to Excel XML.zip

 

Ultimately, the best solution would be to produce an actual .xlsx file, like the one you get when exporting to Excel (only with a custom header row). I believe there are currently 3 ways to do this:

1. Use a plugin;
2. Use a Javascript library in web viewer;
3. Import your data to a temp table, add a header record and export the result.

(I haven't actually tried #1 or #2.) Yet another option is to export the data to Excel, then run some VBA code to add the header row in Excel (provided you have Excel installed on the exporting machine).