Exporting to a CSV

[PC Infoman]

I am using php on my site to display prices for items, etc. It is pulling the data from a csv file that I create with Excel. However, all this data is also in my Filemaker Pro v7.0v1 database. I want to take Excel out of the loop and just create the csv files in FM.

When I export the data from FM into a CSV, I get a file that looks like this:

---------------------------------

"3223","Frog","0","9425","6500","5525","2275"

"9510","Animatronic Frog Puppet","0","0","0","0","0"

"3333A","Baby Green frog","0","5437.5","3750","3187.5","1312.5"

"3335","Don's Pacman Frog","1","1812.5","1250","1062.5","437.5"

"1841","Frog","0","137.75","95","80.75","33.25"

"1840","Frogs","5","616.25","425","361.25","148.75"

"3333","Baby Green Frog","1","3625","2500","2125","875"

"3335A","Large PacMan Frog","1","6525","4500","3825","1575"

"1652","Frog Flyer","1","3625","2500","2125","875"

"1652A","Frog Flyer","0","5437.5","3750","3187.5","1312.5"

"1843","Red Eyed Tree Frog","1","1087.5","750","637.5","262.5"

"1844","Poison Dart Frog","1","616.25","425","361.25","148.75"

---------------------------------

What I want is data that looks like this:

---------------------------------

item_no,quantity,Clearance,ISRental,NBRental,ISSale,NBSale

1652,Flying Frog,1,0.00,875.00,2500.00,2125.00,3625.00

1840,Frogs,5,234.81,148.75,425.00,361.25,616.25

1841,Frog,0,0.00,N/A,95.00,Call,137.75

1843,Red Eyed Tree Frog,1,0.00,262.50,750.00,637.50,1087.50

1844,Poison Dart Frog,1,0.00,148.75,425.00,361.25,616.25

3223,Animatronic Frog,0,0.00,N/A,6500.00,Call,Call

3333,Baby Green Frog,1,0.00,875.00,2500.00,2125.00,3625.00

3335,Pacman Frog,1,0.00,437.50,1250.00,1062.50,1812.50

1652A,Flying Frog,0,0.00,N/A,3750.00,Call,Call

3333A,Baby Green Frog-Animatronic,0,0.00,N/A,3750.00,Call,Call

3335A,Pacman Frog-Animatronic,1,0.00,1575.00,4500.00,3825.00,6525.00

---------------------------------

So, how do I get the FM csv file to not add "" to the file?

How do I add:

item_no,quantity,Clearance,ISRental,NBRental,ISSale,NBSale

to the top of the file? Other than this line, it does not matter to me what order the rest of the lines are in.

And how do I get the csv file to not put each field data in quotes?

Ideas?

Edited January 5, 2007 by Guest

[PC Infoman]

Is there ANY answer to this at all?

[mr_vodka]

Before anything I would upgrade to v7.03, v7.01 was unstable.

http://www.filemaker.com/support/uplist.html

[PC Infoman]

OK, I can get that done, but is that the reason I am having this problem?

[Marek P]

To get the field names on the first line, use "Merge" as export type (after clicking on "Export Records"). Note: this is still a comma separated output file. I always change the extension of the output file from ".mer" to ".csv".

I don't have an answer for how to keep quotes out of the output for each field value... Unless you want to use type "Tab-separated Text" which doesn't put quotes in, as far as I can tell.

Marek

Edited January 8, 2007 by Guest

[PC Infoman]

Before anything I would upgrade to v7.03, v7.01 was unstable.

http://www.filemaker.com/support/uplist.html

OK, the list refers to 7.0v4. However, at http://www.filemaker.com/support/downloads/index.html there is no such version to download. There is only 7.0v3

To get the field names on the first line, use "Merge" as export type (after clicking on "Export Records"). Note: this is still a comma separated output file. I always change the extension of the output file from ".mer" to ".csv".

Thank you. I will check into this.

Edited January 8, 2007 by Guest

[mr_vodka]

OK, the list refers to 7.0v4. However, at http://www.filemaker.com/support/downloads/index.html there is no such version to download. There is only 7.0v3

That documentation is for all versions. 7.04 is for FileMaker Server. 7.03 is the latest and you should update it. I believe I misquoted the actual fix in my earlier post, but none the less you need to update it to FM7.0v3.

Do that first and then lets drill down to the further details of your problem if it still persists.

[PC Infoman]

That documentation is for all versions. 7.04 is for FileMaker Server. 7.03 is the latest and you should update it. I believe I misquoted the actual fix in my earlier post, but none the less you need to update it to FM7.0v3.

You would think that they would seperate it out so there are no confusions. But whatever.

Do that first and then lets drill down to the further details of your problem if it still persists.

That is fine. I will do this. It just seems that you might offer suggestions in addition to "upgrade your software and then we can discuss it" in order to save some time.

[mr_vodka]

While I no longer have a copy of FM7 on my machine, maybe you could post a copy of the file to see if we can replicate the issue. I will try it in 8 & 8.5.

As for:

It just seems that you might offer suggestions in addition to "upgrade your software and then we can discuss it" in order to save some time

The first step to troublshooting is narrowing down your list of what could be wrong. Since you are on FM7.0v1 and as stated it is not that stable, the first step would be try the update.

[PC Infoman]

I am so sorry for my attitude. I fully understand troubleshooting procedures and I should have just done what you asked me too. That being said:

I have upgraded my Mac copy of the software to 7.0v3. However, I am unable to upgrade my Windows copy as it keeps giving me the following error:

The file "e:filesFileMaker Pro 7.msi" is not a valid installation package for FileMaker Pro 7. I put in the original cd (upgrade software) and pointed it to the file on the cd, yet this is what is says.

Ideas?

[Fenton]

To take control of your output you can use XML Export, with an XSL stylesheet. It requires you to specify what you want in between the fields. If you want a 1st line "header", you can either read the field names, from the Metadata section of the XML, or you can just type the darn things in manually (with the separator).

The following XSL will use the field names, with commas only between, same in the data. It uses the Unix style line ending of a single line feed. If you need Windows-specific line endings, add a return character ( ) before it ( )

It is hard to get the return entitities unescaped past the PHP parser rendering here on the forums.

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



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

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



	

	

		

			

				

				

					

						,

					

					

						

					

				 

							

		

		

		

			

			

			

					

					

						

							,

						

						

							

						

						

						

					

								

		

		

	

Edited January 8, 2007 by Guest

[Fenton]

Easy breezy method. Just type the darn header.

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



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

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



	

	

	item_no,quantity,Clearance,ISRental,NBRental,ISSale,NBSale


		

		

			

			

			

					

					

						

							,

						

						

							

						

						

						

					

								

		

		

	

Edited January 8, 2007 by Guest

[Fenton]

Strange. It translates the return entities first time you post. But if you edit the post and retype them, it leaves them alone. Let's try it: <-return. Yep.

Oh, I should have specified; that's an FMXMLRESULT grammar export.

Edited January 8, 2007 by Guest

[mr_vodka]

Hmm, you are getting the error message which has more information here, but it seems as if you already tried that.

Link to Error Message

[PC Infoman]

OK, I made a XLS file that contains the following

<?xml version='1.0' encoding='utf-8'?> exclude-result-prefixes="fmp" version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"> item_no,name,quantity,ISRental,NBRental,ISSale,NBSale ,

I then selected the seven fields to be exported. When I run the script, it gives me the following error:

XML Parsing Error

Invalid Document Structure



Line Number: 1

Column Number: 1

Ideas?

Edited January 8, 2007 by Guest

[comment]

Remove the enclosing

 and 

tags. They are not part of Fenton's code.

[Fenton]

Yes, sorry about those. I was trying to keep the PHP parser from translating my line ending entities in the xsl. I went back and removed the

 tags from my posted code. You must have grabbed it just before I finished with that.

[PC Infoman]

Ok, I took out the

 and 

statements. Now I get the following error:

XSLT warning: Fatal Error at (file, line 0, column 0):

An exception occurred! Type: XMLPlatformException,

Message: Could not open file: /< (,line -1, column -1)

This happens if I tell it to not use the xlm file but just the code I but in the box (the same cose as above but without the

 and 

lines). If I tell it to use the file instead, I get the same error as I previously mentioned. Edited January 8, 2007 by Guest

[Fenton]

Sounds like there might be a hidden character at the 1st position. Things like that happen when you copy/paste from web pages into files with xml code. You should be using a text editor such as BBEdit or its free sibling TextWrangler (both from http://www.barebones.com). Do not use TextEdit, unless you've switched its default format to plain text. Definitely don't use MS Word. They can add extra hidden crap which will break xml/xsl files. I'm attaching the plain file that I used.

Header_plain.xsl.zip

[comment]

It looks like you still have some characters there before the opening <?xml> declaration. Hard to tell with all this copy/paste. Try the attached instead.

infoman.zip

[PC Infoman]

It looks like you still have some characters there before the opening <?xml> declaration. Hard to tell with all this copy/paste. Try the attached instead.

Well, I used your xml file, but I removed the "Clearance" part.

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



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

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



	

	

	item_no,quantity,ISRental,NBRental,ISSale,NBSale


		

		

			

			

			

					

					

						

							

						

						

							



						

						

						

					

								

		

		

	

A couple of questions.



1.What is 

NBSale

Should it not be just "NBSale"?

2.The output file looks good, except some oof the fields have a $ next to the numerical value. Can I get rid of those?

3.Why is there a blank line between each output result?

Edited January 9, 2007 by Guest

[comment]

I'll let Fenton answer this - it's his code all the way. I just wanted to help by eliminating the transmission errors (and it turns out Fenton did even that just a few minutes before me).

[PC Infoman]

Thank you for your help. You have already been a great deal of help. I am not longer getting errors. It is now a formatting issue.

Edited January 9, 2007 by Guest

[PC Infoman]

Is there any additional answers to this?

[PC Infoman]

Anybody? Anybody?

[Fenton]

We don't know what you're asking. My file works. Comment's file works; other than that you need to reestablish the path to the xsl file; FileMaker set it to his hard drive -].

If yours is exporting with currency formatting then uncheck the format using current layout checkbox (lower left, export order dialog). Or format how you want and leave it checked (but don't put commas in the numbers; and they must be number fields for some of those).

[PC Infoman]

What I am needing is to find out how to not have the output results have quotes around each field value.

Currently the results look like this

"3223","Frog","0","2275","6500","5525","9425"

"9510","Animatronic Frog Puppet","0","0","Call","0","0"

"3333A","Baby Green frog","0","1312.5","3750","3187.5","5437.5"

"3335","Don's Pacman Frog","1","437.5","1250","1062.5","1812.5"

"1841","Frog","0","33.25","95","80.75","137.75"

"1840","Frogs","5","148.75","425","361.25","616.25"

"3333","Baby Green Frog","1","875","2500","2125","3625"

"3335A","Large PacMan Frog","1","1575","4500","3825","6525"

"1652","Frog Flyer","1","875","2500","2125","3625"

"1652A","Frog Flyer","0","1312.5","3750","3187.5","5437.5"

"1843","Red Eyed Tree Frog","1","262.5","750","637.5","1087.5"

"1844","Poison Dart Frog","1","148.75","425","361.25","616.25"

Edited January 19, 2007 by Guest

[Fenton]

That is exactly what the xml/xsl exports does. So we don't know what you're asking. Output from comment's file:

item_no,quantity,Clearance,ISRental,NBRental,ISSale,NBSale

0,9425.00,5525.00,3223,6500.00,2275.00,Frog

0 ,,,9510,,,Animatronic Frog Puppet

0,5437.50,3187.50,3333A,3750.00,1312.50,Baby Green frog

1,1812.50,1062.50,3335,1250.00,437.50,Don's Pacman Frog

[PC Infoman]

Ok, but I need the output to be in the format that I showed earlier, but without the quotes. How do we do this?

[LelandLong]

Just a quick question, to cut-in here, ....

Is this to be repeatable problem, or just occasionally? Will an occasionable human-controlled fix work, or is automation/script, etc required?

Reason I ask is that my Mac-formatted exported files are always having issues with Froogle, EBay, and my corporate web-site when coming directly from FileMaker. I have several ways of dealing with these issues every time and I have not automated the solution, but have found quick one-time solutions.

[jp450]

New user here... I am looking for a solution for this too. I want to export my data WITHOUT the quotation marks around all the data. Any suggestions?

[Fenton]

The xsl stylesheets included in the example will format .csv without the quotes. I'm using Unix line endings, ASCII 10, which is generally acceptible cross-platform, by web sites, etc.. You can prepend ASCII 13 for a Windows-specific line ending. I'm including both types in the xsl examples (but only using the Unix ones in the FileMaker script; you can adjust).

You can either include or not a header line with the field names; and those names can either come from the fields themselves, or be typed into the xsl manually.

CSV_Text_no_Quotes.zip