Excel Export

[spongebob]

Hi Forum!

I want to export to Excel, but, trouble is, I have about 100 cells horizontally to fill per line.

It would be a real pain to haveto create 100 fields and then populate them for each record, so Im looking for a simpler way.

How about, if I can "build" a string in just one field that contains all the values and then only export that one field? Like so:

Exportfield = "test1" & Char ( 9 ) & "test12" & "test13" & "test14" & "¶" & "test15"

What I need to know for this is:

1. How would i specify a TAB (Char (9) didnt work) so that it delimits the field and Excel hops to the next field.

2. How do I specify "End of a record" ie the return (no, "¶" didnt do the trick)

3. Maybe how to specify a newline within a cell

4. Is it possible to color the cells somehow (Maybe export somekind of command in the cell that excel might comprehend?)

Thanks for any comments and suggestions!

Spongebob

[comment]

If you don't have 100 fields, then what do you use to populate the 100 cells?

[spongebob]

Haa Haaaaa! Behold a trick question!!

Answer: I have a LOOP that currently displays results for a table only holding 6 fields. But I want the Loop to deliver results for 100 fields that I want exported into in Excel (not displayed).

Ha haaa! True answer. Bet you werent ready for that eh?

Thanks lots!

Luv

Spongebob

[comment]

It's not a trick question - and I am not sure I understand the answer. Do you mean you repeat the same 6 fields over and over, or what?

[spongebob]

No let me explain if I wasnt clear.

I have a loop that calculates 6 results. I place those into 6 fields in one record. I can export that then.

Now Im extending the loop to generate well over 200 results for one record. At the end the Loop will generate many such records. And i wanto export all that so i have it in Excel, preferably without the need to define 200 fields.

Thats all...thats why I prefer one field per record which I can "fill up using string commands" from within the loop, and then export the 40 or so records which each have one field (containing text and tabs etc). Then when i open that in Excel, i will have, hey presto, 200 columns.

But for that to work I must know how I can put a TAB that Excel will recognize into a field.

Is that better?

[comment]

Short answer: it's not possible. You can't fool the system. Filemaker knows that tabs (or commas) inside fields need to be protected when exporting.

Possible workarounds:

1. Compile the entire export (200 columns x n rows) into a global field, then export the field contents (as text file);

2. Export as XML, and use a custom XSL stylesheet to transform the output to tab/comma delimited text or Excel 2002/3 XML format;

3. Do the transformation in Excel.

[spongebob]

Ok if I choose 1, how would I put a or into the global field?

Any documentation on how best to do this in XML? Never used XML before.

[comment]

how would I put a or into the global field?

Set Field [ global ; "Value 1" & Char (9) & "Value 2" & Char (9) & ... & "Value 200" & ¶ ]

Any documentation on how best to do this in XML? Never used XML before.

You can find stylesheet examples in both this and the XML section. Basically you need to export a field with all the values and use the stylesheet to delimit them using a tab within the record, and a return between records.

[spongebob]

ok

Set Field [ global ; "Value 1" & Char (9) & "Value 2" & Char (9) & ... & "Value 200" & ¶ ]

does not work for me.

Not in a Global or a Normal field, not exported as tab delimited file, comma delimited file or Excel file. How exactly woud you export that?

In all these variations, FM sticks all the data into one field.

Thanks

[Fenton]

This XSL stylesheet will put a Tab between each field, and a carriage return (which you could change, to Unix #10, or to Windows #13#10).

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

exclude-result-prefixes="fmp" version="1.0"

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

One Two Three

Edited June 29, 2010 by Guest

[comment]

How exactly woud you export that?

Export Field Contents...

[TheTominator]

Unless I am misunderstanding something, if your field data does not have quotes, or line breaks, it sounds like you want to do the following

Create a field "Exportfield".

Exportfield =

""" & "test1" & """ & "," &

""" & "test2" & """ & "," &

""" & "test3" & """ & "," &

...

""" & "test199" & """ & "," &

""" & "test200" & """

If your data doesn't have commas, you may be able to do without enclosing the data in quotes and just use

Exportfield =

"test1" & "," &

"test2" & "," &

"test3" & "," &

...

"test199" & "," &

"test200"

If desired, create an extra record at the beginning of the found set to contain header names instead of test values.

Once you have this field defined for each record,

export just the one field: Exportfield.

Export as a tab-delimited file with the name "whatever.csv".

Since you are exporting only one field, there are no tabs and each record will be on a separate line.

Since you will be importing this text file as comma-separated, Excel will break it up into columns properly due to the commas you placed in the calculated text.

[spongebob]

Sorry it took so long to reply.

"Since you will be importing this text file as comma-separated, Excel will break it up into columns properly due to the commas you placed in the calculated text."

Thats the crux of the matter. I need to be able to just doubleclick the excel spreadsheet and it opens and everything is there. I cant ask my client to do an Import into excel all the time.

So the XML Solution above looks tastier...if only I knew where to look or how to exactly apply that very cool tip above? Never done XML exports...could you give me a short step-by-step? I am IT very saavy, I also do CSS Sheets but I completely dont understand how to do an XML export properly as I have never done that before..and the manual, well the manual is not very hot on this topic.

[spongebob]

"Export Field Contents..."

okay thanks that worked! As a matter of interest...how big is a single global text field allowed to be in number of characters?

[spongebob]

By the way, if you could give me the XML Tipps Id be really happy but one more question.

With that method, could one actually change the background colors of some cells in Excel through the export?

uuuu oooo I hear you say, now hes asking for it..I guess I am.. : Any chance of something cool like that? You know, actually make Excel do what I want, and not what Bill wants? :aapple:

[Fenton]

Well, there's not a whole lot to the basic XML Export. There's not a whole lot in the Help about it though.

8. In the Specify XML and XSL Options dialog box, do the following:

• Specify the XML grammar to use by choosing either the FMPDSORESULT format or the FMPXMLRESULT format.

• To apply an XSLT style sheet to the XML during export, select Use XSL style sheet and specify the source of the XSLT style sheet. The XSLT source may be a file or the result of an HTTP request.

You'd want to use the FMPXMLRESULT grammar in this case, as the XSL stylesheet expects it. When you choose XML as the type of Export, then a 2nd dialog comes up; that's where you choose the XSL file.

P.S. The XSL I posted is generic, in the sense that it will do all the fields you choose, without having to hard-code which into the XSL. But the "header" line with the names is hard-coded. So you'd have to type all the field names in, or parse them out of a Merge export (using a text editor).

OR use an XSL file which can read them from the fields that are exported. I should have posted that instead

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



exclude-result-prefixes="fmp" version="1.0"

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



	

		

			

					

					

			

		

		

		

			

					

					

			

			

		

	

P.P.S. The forum PHP is adding the "php" to the xml declaration; it' supposed to be just "xml". Likely does not matter.

Edited July 16, 2010 by Guest

[comment]

how big is a single global text field allowed to be in number of characters?

Up to 1 billion (limited by available memory).

I need to be able to just doubleclick the excel spreadsheet and it opens and everything is there. I cant ask my client to do an Import into excel all the time.

I am not entirely sure what you mean by that. All the suggestions made in this thread would result in a text file - either tab or comma separated. Such file can be opened by Excel or imported into an existing spreadsheet.

The other option (with I mentioned briefly earlier) is to use XML/XSLT to export in Excel 2002/3 XML format. This is a native Excel format, though not identical to .xls.

[spongebob]

Great Ill try all that and be back in a month or so. Thanks for all these tipps. XML Export, here I come.