Simple Style Sheet Help

[Ron Cates]

Hi all,

I have been looking for a solution to add header labels to an excel file export. I found a post with a sample style sheet which will work fine if I can just figure out how to edit it so that it doesn't skip a row between each record. Can anyone tell me how to edit this thing?

<?xml version='1.0' encoding='UTF-8' ?>

xmlns:xsl='http://www.w3.org/1999/XSL/Transform'

xmlns:fm="http://www.filemaker.com/fmpxmlresult"

exclude-result-prefixes="fm">

Col1,Col2

Again I am just looking to remove the empty row between records. Any help is greatly appreciated.

Oh, and can the labels be bold?

Thanks

Edited April 7, 2010 by Guest

[TheTominator]

It doesn't skip a row for me when I test your code.

I can get blank lines between rows by exporting a FileMaker field that has line breaks in it.

I would check the data you are exporting. Line breaks are likely present in the data itself. Since you aren't quoting these CSV fields in the output, line breaks in the data will break the file format.

[TheTominator]

From what you are trying to achieve, it looks like you would be better off doing a FileMaker export to the Merge file format. That is identical to the CSV format except that it adds a first row containing the field names.

If you want to control the exact names present in the header column and the names you want are not the same as the FileMaker field names, you can create calculated fields that are named differently and are copies of the data in the fields you want to export.

You will want to name the Merge file to end with ".csv" so that Excel knows what to do with it. Excel gets confused if you use the default file extension ".mer".

[Ron Cates]

OK, so I guess I may have a few questions.

As for the row skipping, I am only exporting 6 fields in this export and I have checked them all for line breaks. Found none. You said "Since you aren't quoting these CSV fields in the output" Would that solve the problem and how do I quote the csv fields in the output?

Also, I have found that since the data in one of the fields contains a comma it is splitting that data into seperate cells. Any way to prevent this?

Can the labels be bold?

And to address TheTominator. I would preffer to create a template for my exports than having to create what could end up being a hundred fields or more to calculate column headers I will eventually need for various exports. But thanks.

[TheTominator]

Would that solve the problem and how do I quote the csv fields in the output?

Also, I have found that since the data in one of the fields contains a comma it is splitting that data into seperate cells. Any way to prevent this?

Can the labels be bold?

Yes, quoting the field data should solve both the line break and the handling of commas in the output.

The Comma Separated Values format you are generating requires the field data to be surrounded by quotes if it contains a comma.

ID,Name

125,Smith, John

Needs to be formatted as

ID,Name

125,"Smith, John"

so that the comma is not interpreted as a separator between the fields.

Microsoft Excel also allows you to put line breaks in the data itself as long as they are properly found between the quotes in the data field.

ID,Name,Address,

125,"Smith, John","ATTN: John

436 Main Street"

The CSV format doesn't have any built-in mechanism for making the items bold. It is a pure data format without any text markup features.

If you really wanted to provide the range of formatting that Excel provides, you would have to export to an Excel-native format such as the new Office XML format.

Here is a modification that adds quotes around each field value. Note that this will break if the data in the fields contains quotes.

Replace

with

"

"

[Ron Cates]

I did the replacement as you instructed and it did indeed solve the comma issue. However, it is still skipping rows. I have tested by exporting just one field at a time and have tried a few different fields. I can't find any line breaks in the data and yet it skips rows in between every time.

Here is the file after modifying as instructed.

<?xml version='1.0' encoding='UTF-8' ?>

xmlns:xsl='http://www.w3.org/1999/XSL/Transform'

xmlns:fm="http://www.filemaker.com/fmpxmlresult"

exclude-result-prefixes="fm">

Department,RPT Billing,Ticket Status,Artwork Status,Header Status,Print Status,Job Status

"

"

[Ron Cates]

Anyone have any ideas?

[TheTominator]

I can't find any line breaks in the data and yet it skips rows in between every time.

Here is the file after modifying as instructed.

Please zip a sample .csv output file that is demonstrating the row skipping. Post it as an attachment here so we can spot the issue.

I'm guessing that there is either one problem field that is escaping your notice, or there is a glitch in the way it is being imported into Excel (e.g. not dealing with text file line ending conventions).

[Ron Cates]

Here is one of the output files. I have applied the style sheet to several exports now and reguardless of what fields I am exporting I always get the extra rows.

Jobs_List-2011_Status_.zip

Edited April 12, 2010 by Guest

[TheTominator]

I don't think the file you posted came directly out of FileMaker using the latest stylesheet you posted.

Only the first field in the .csv file has quotes around it. The .csv file from the stylesheet puts quotes around every field including empty ones. (At least it does for me when I run it on under Mac OS X 10.5.8 with FMA 10v3.)

Perhaps you have provided something that came out of Excel.

I would like to see the file fresh from FileMaker that Excel is going to import.

[Ron Cates]

I am confused. That was the file that was exported directly from FileMaker and saved to my desktop. I have attached another from a different export but using the same style sheet.

Carter__TN-2011-_Basic_.zip

[TheTominator]

Ok, the new one is more like what I expected. There are quotes around each field.

The file does have a blank line between each row in the output for an as yet unknown reason.

Could you please post the stylesheet file that you are using. I think it may have some invisible characters in it that are hurting things. This may have come about if you downloaded it from a website and it was originally made for FileMaker 6 running on a Mac system.

[Ron Cates]

I just openned both examples in word and it looks like they are not the same. I'm not sure how but I will go back and check. I can see the quotes you were talking about. I also see that the first shows the extra line breaks and the second does not. However, when I open in excel both still have the extra rows.

[Ron Cates]

Here it is

Tickets_List_-_Basic.zip

[TheTominator]

I didn't see anything in the stylesheet that was definitely causing the problem, but I did see some spurious characters in the .csv file you provided so maybe it does have some sort of glitch we aren't seeing.

How about trying this file. I created it from the stylesheet you posted to the web. It doesn't have any extra leading whitespace such as the tabs that were in your file.

csv_export_dos_xsl.zip

[TheTominator]

The second .csv file you exported has line endings that consist of CR + CR + LF instead of the normal CR + LF for DOS.

Either CR + LF or just LF would work for importing into Excel. Why the extra CR is coming in is still a mystery.

[Ron Cates]

So, what can I do to fix it?

[TheTominator]

So, what can I do to fix it?

Does the stylesheet file I provided exhibit the same problem?

If we can narrow it down to why this glitch comes about and if there is a workaround, we can figure out how to fix it.

I'm thinking that it may be caused by editing the stylesheet in a program that doesn't deal with line endings in ASCII text files well. Word is a likely suspect.

If you move to using a text editor under Windows, I'd recommend something like the free Notepad++.

[Ron Cates]

Sorry, missed it (Doh!!)....

Ok, I downloaded and tried the file you provided and unfortunately the result is the same.

[TheTominator]

I was able to reproduce what you are experiencing by trying out the stylesheet with FM 10v3 under Windows XP. It looks like a bug in the Windows version. End of lines inside are not properly translated into the output under Windows.

I'm looking into a workaround.

Since XML + XSLT is very flexible with multiple ways of accomplishing the same thing, this should be solvable.

[Ron Cates]

Thanks for putting in so much effort. It is greatly appreciated :

[TheTominator]

Here is the solution.

This is essentially the same as you had before except that I put in a line

to generate the line break instead of relying on the two lines of

to put in its own line break choice which is buggy on Windows.

It is buggy in the manner that a line feed is converted to CR+CR+LF for no apparent reason whereas a CR is passed through as a CR. Excel will accept a CR as a line break for .csv files.

<?xml version='1.0' encoding='UTF-8' ?>

xmlns:xsl='http://www.w3.org/1999/XSL/Transform'

xmlns:fmp="http://www.filemaker.com/fmpxmlresult"

exclude-result-prefixes="fmp">

Department,RPT Billing,Ticket Status,Artwork Status,Header Status,Print Status,Job Status

""

The forum is mangling the XML text in unpredictable ways, so here is the file as an attachment.

csv_export_dos_cr.xsl.zip

Edited April 12, 2010 by Guest
attempted to get the post to display the XSLT correctly

[comment]

It sounds like you are experiencing the bug described here:

http://fmforums.com/forum/showpost.php?post/297468/

[TheTominator]

It sounds like you are experiencing the bug described here:

http://fmforums.com/forum/showpost.php?post/297468/

Yes, it is the bug described in the first post of that thread, but the bug isn't as beverly described in the last post. The bug is that any line feed will be translated into cr + cr + lf instead of cr+lf. It doesn't matter if the LF is meticulously placed or appears as a byproduct of automated processing.

[Ron Cates]

I used the new file you provided and of course it worked perfectly. I thank you once again for all the help. :