Export to csv without the quotes

[jp450]

I am a newbe with FMpro. I have V 8.5 and I export my database to a CSV file. However the other program I use the data with does not like the quotes the FM puts around all the data.

Is there a way to export to a csv without the quotes around all the fields? This would make the transition between the two programs seamless.

Thanks

Jeff

[Vaughan]

You could, as a test, try opening the csv file in MS Word and removing all the quotes and then seeing if it works in the other program. I'd start there.

Unfortunately the quotes are part of csv specification. They are there in case any field values contain commas, since the extra commas would confuse things.

You could create an xml export without the quotes, but any commas in the fields will screw things up.

[jp450]

I am able to strip the quotes out with word by search and replace with nothing.

The other program I use works fine after removing the quotes. This all takes a few extra steps. This is a live interactive database that callers call into via the telephone to access the data. Currently the data is not as accurate as it could be due to the quotes and me not being able to clean them out. I would like to be able to export the data every time a new entry is made and thus updating the information available to the user when they call in.

[Vaughan]

Excellent for performing the test.

The answer you don't want to hear is that the other program should be able to work with csv files that have quotes. Can you contact the developer?

Others on the forum can advise on creating an XML export, but you're going to have to be really careful about cleaning the data and stripping out commas before exporting. Depending on what the data is, stripping out the commas might "damage" the data.

[Fenton]

The attached XSL file, when used in an XML Export, will strip the quotes. It does data only, no header, no field names. If you need those then you need to tell us. As Vaughan says, if you've got commas in the data, you're pretty much screwed if you strip out the quotes. Do they support tab-delimited? Likely not; they sound pretty clunky.

(P.S. It's for a FMPXMLRESULT grammar export.)

Plain_PC.xsl.zip

[jp450]

The attached XSL file, when used in an XML Export, will strip the quotes. It does data only, no header, no field names. If you need those then you need to tell us. As Vaughan says, if you've got commas in the data, you're pretty much screwed if you strip out the quotes. Do they support tab-delimited? Likely not; they sound pretty clunky.

(P.S. It's for a FMPXMLRESULT grammar export.)

I wish they supported the Tab sep values. I would not be having this issue. The data in the fields does not require any commas and should not have any. If for some reason a comma does show up I can fix that... All I need is the data, no column names. The other program looks at column 1, (permit number) and takes all the other data (Name, address, phone number) and logs it.

Like I said in my original post I am totaly new to FMP and as such am not familiar with XSL files. By using your attached file will this create an old fashioned CSV file?

[Fenton]

Yes, more or less (less in this case). It produces a comma-separated fields (columns), and return-separated records (rows) file. The term "CSV" can mean with or without quotes, depending on who's using it.

It is at first perhaps a little difficult to understand how an xsl (Extensible Stylesheet Language) file works. It is basically "transforming" the xml data coming out of FileMaker in its native FMXMLRESULT format. In other words, it comes out of FileMaker in one of two known formats (there's also DSOFMRESULT). But an xsl file can transform that into any logical pattern you want.

[jp450]

Thanks for the help Fenton!!! Got it all working they way I want it.

[Raybaudi]

Hi Fenton

an UP for your code ! :

BTW: If you revert back the generated file into an .fp7 file you'll have extra blank records... Why ?

[Fenton]

You could try it with just the #10 or just the #13. I don't do Windows much, but I've seen that sometimes it seems to add that extra return, like it's trying to "over" help you.

[Raybaudi]

You could try it with just the #10 or just the #13.

I had just tried what you say; both exports didn't show any particular chars into the NotePad but when I drag the file to FM, it creates a file with exact record alternate with blank one.

[comment]

Can you post an example?

[Raybaudi]

Here it is...

If you drag the prova.txt file to FM application, FileMaker will make a DB with alternate blank records.

Poleggi.zip

[comment]

You are somewhat confusing the issue by exporting a field with line breaks. These line breaks are exported as field content, and your style sheet is not supposed to alter them in any way. I am guessing that since you're exporting from Filemaker on Windows, in-field line breaks are passed on as CRLF by default.

However, your result file has THREE newline characters between each value: CR, CR, LF. I think you'll find the explanation here:

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

[Raybaudi]

Ok, let say me more...

I need to export a result of the List ( ) function ( export only one field ).

With the normal export as tab text, the result shows the ******* box between values.

Using the style sheet from Fenton, the result SEEMS good, but when I make a new FM file from that result, blank records appear.

So do you think that I have to use a different style sheet for this operation ?

However, your result file has THREE newline characters between each value: CR, CR, LF.

Which app do you use to display those chars ?

[comment]

I need to export a result of the List ( ) function ( export only one field ).

If you're exporting only one RECORD, you can do this directly from Filemaker, by using Export Field Contents… - however the result is ALWAYS encoded in UTF-16, which some applications do not handle well.

If you need the result of many records, then XML is the way to go - but this is a problem that has very little to do with the original topic of this thread.

I am curious what results do you get with the attached stylesheets (two options).

Which app do you use to display those chars ?

Any good text editor (e.g. TextWrangler) should be capable of showing invisible characters. I also use UnicodeChecker to tell me what those characters are.

NewTest.zip

[comment]

BTW, I was too concentrated on the XSLT angle before, but something was bugging me, and now I know what it was: why don't you simply GTRR and export the child records directly? Exporting only one field in tab-delimited format should give you exactly what you want.

[Raybaudi]

I am curious what results do you get with the attached stylesheets (two options).

raybaudi1.xsl works like the other, raybaudi2.xsl brings up an error dialog:

XML analisys error

Expected whitespace

Row: 1

Col: 68

[Raybaudi]

...I was too concentrated on the XSLT angle before...

Yes, that was a poor example to obtain the list to export.

Think to it as a lonely field with a list of things that must exported.

[comment]

Sorry about that, should have tested it myself. Try it now.

raybaudi3.xsl.zip

[Raybaudi]

Nope...

Even this generates a .txt file that can't be imported without the creation of blank records.

BTW: the offending char ( box ) that FileMaker exports ( normal .tab export ) is a VT ( vertical Tab ) instead of a CRLF ; I can see it with Scite, a win app.

[comment]

Let's try and pin-point the source of the problem. Could you please run the attached test. It will produce two files, "raw.xml" and transformed.txt". Please zip the resulting files BEFORE you open them in another application, and post them back.

Archive.zip

[Raybaudi]

This is the two generated files zipped result

transformed.zip

[comment]

I don't know. Clearly, this is a bug, and a more serious one than suggested in the thread I linked to earlier. Your raw XML is fine. Transforming it using the same stylesheet produces the expected result on 5 different processors, including Filemaker on Mac.

Try and see what results you get using the attached modification. It shouldn't make any difference, but it might be worth a shot, just in case.

I don't know what else to suggest, except report the bug and look for some workaround, like exporting from a repeating field (if that's an option).

TransformB.xsl.zip

[Raybaudi]

Nope.

This is the generated text file.

BTW: thank you very much to have serched to solve this problem

prova.zip

[comment]

I thought of one more thing that's worth a try. It would be also interesting to know if the problem affects all Windows systems or some only.

If this (TransformC.xsl) doesn't work, then try the workaround.

---

P.S. Please test using my file.

Workaround.zip

[LaRetta]

TransformC doesn't work for me but the workaround does - no blank alternate records.

[Raybaudi]

I thought of one more thing that's worth a try.

TrasformB and TrasformC

a1

a2

a3b1

b2

Workaround

a1

a2

a3

b1

b2

in other words: perfect ! :

[Raybaudi]

... but, with the original file ( that with a list of values into a field ) the result is always the same.

With Scite

melaCR

CRLF

peraCR

CRLF

susina

[Raybaudi]

Your Workaround on your file with Scite:

a1CR

a2CR

a3CR

b1CR

b2

[comment]

The workaround needs a different calculation in the Filemaker file.

[Raybaudi]

This isn't clear...

I need a different workaround

or

I can use that workaround but with a different calc on the list

?

[comment]

To use the "Workaround.xsl" stylesheet with your FMP file, you need to modify the calculation field in your FMP file. See my "Source.fp7" file - the export using the workaround is actually exporting the cListW field, instead of cList.

It's an ugly hack, but I don't know what else could work around this ugly bug.

[Raybaudi]

Ahhh...

I didn't think that you created another field ( cListW ), neither it can be easily seen into the script step.

So that solve the problem. Thank you very much.

[Joost Miltenburg]

Does anyone know if this problem is fixed in FM10 ?