Newbies jp450 Posted December 14, 2007 Newbies Share Posted December 14, 2007 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 Link to comment Share on other sites More sharing options...
Vaughan Posted December 14, 2007 Share Posted December 14, 2007 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. Link to comment Share on other sites More sharing options...
Newbies jp450 Posted December 14, 2007 Author Newbies Share Posted December 14, 2007 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. Link to comment Share on other sites More sharing options...
Vaughan Posted December 14, 2007 Share Posted December 14, 2007 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. Link to comment Share on other sites More sharing options...
Fenton Posted December 14, 2007 Share Posted December 14, 2007 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 Link to comment Share on other sites More sharing options...
Newbies jp450 Posted December 20, 2007 Author Newbies Share Posted December 20, 2007 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? Link to comment Share on other sites More sharing options...
Fenton Posted December 21, 2007 Share Posted December 21, 2007 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. Link to comment Share on other sites More sharing options...
Newbies jp450 Posted December 21, 2007 Author Newbies Share Posted December 21, 2007 Thanks for the help Fenton!!! Got it all working they way I want it. Link to comment Share on other sites More sharing options...
Raybaudi Posted October 11, 2008 Share Posted October 11, 2008 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 ? Link to comment Share on other sites More sharing options...
Fenton Posted October 11, 2008 Share Posted October 11, 2008 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. Link to comment Share on other sites More sharing options...
Raybaudi Posted October 11, 2008 Share Posted October 11, 2008 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. Link to comment Share on other sites More sharing options...
comment Posted October 11, 2008 Share Posted October 11, 2008 Can you post an example? Link to comment Share on other sites More sharing options...
Raybaudi Posted October 11, 2008 Share Posted October 11, 2008 Here it is... If you drag the prova.txt file to FM application, FileMaker will make a DB with alternate blank records. Poleggi.zip Link to comment Share on other sites More sharing options...
comment Posted October 12, 2008 Share Posted October 12, 2008 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/ Link to comment Share on other sites More sharing options...
Raybaudi Posted October 12, 2008 Share Posted October 12, 2008 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 ? Link to comment Share on other sites More sharing options...
comment Posted October 12, 2008 Share Posted October 12, 2008 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 Link to comment Share on other sites More sharing options...
comment Posted October 12, 2008 Share Posted October 12, 2008 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. Link to comment Share on other sites More sharing options...
Raybaudi Posted October 12, 2008 Share Posted October 12, 2008 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 Link to comment Share on other sites More sharing options...
Raybaudi Posted October 12, 2008 Share Posted October 12, 2008 ...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. Link to comment Share on other sites More sharing options...
comment Posted October 12, 2008 Share Posted October 12, 2008 Sorry about that, should have tested it myself. Try it now. raybaudi3.xsl.zip Link to comment Share on other sites More sharing options...
Raybaudi Posted October 12, 2008 Share Posted October 12, 2008 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. Link to comment Share on other sites More sharing options...
comment Posted October 12, 2008 Share Posted October 12, 2008 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 Link to comment Share on other sites More sharing options...
Raybaudi Posted October 12, 2008 Share Posted October 12, 2008 This is the two generated files zipped result transformed.zip Link to comment Share on other sites More sharing options...
comment Posted October 13, 2008 Share Posted October 13, 2008 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 Link to comment Share on other sites More sharing options...
Raybaudi Posted October 13, 2008 Share Posted October 13, 2008 Nope. This is the generated text file. BTW: thank you very much to have serched to solve this problem prova.zip Link to comment Share on other sites More sharing options...
comment Posted October 13, 2008 Share Posted October 13, 2008 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 Link to comment Share on other sites More sharing options...
LaRetta Posted October 13, 2008 Share Posted October 13, 2008 TransformC doesn't work for me but the workaround does - no blank alternate records. Link to comment Share on other sites More sharing options...
Raybaudi Posted October 13, 2008 Share Posted October 13, 2008 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 ! : Link to comment Share on other sites More sharing options...
Raybaudi Posted October 13, 2008 Share Posted October 13, 2008 ... 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 Link to comment Share on other sites More sharing options...
Raybaudi Posted October 13, 2008 Share Posted October 13, 2008 Your Workaround on your file with Scite: a1CR a2CR a3CR b1CR b2 Link to comment Share on other sites More sharing options...
comment Posted October 13, 2008 Share Posted October 13, 2008 The workaround needs a different calculation in the Filemaker file. Link to comment Share on other sites More sharing options...
Raybaudi Posted October 13, 2008 Share Posted October 13, 2008 This isn't clear... I need a different workaround or I can use that workaround but with a different calc on the list ? Link to comment Share on other sites More sharing options...
comment Posted October 13, 2008 Share Posted October 13, 2008 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. Link to comment Share on other sites More sharing options...
Raybaudi Posted October 13, 2008 Share Posted October 13, 2008 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. Link to comment Share on other sites More sharing options...
Joost Miltenburg Posted January 9, 2009 Share Posted January 9, 2009 Does anyone know if this problem is fixed in FM10 ? Link to comment Share on other sites More sharing options...
Recommended Posts
This topic is 3564 days old. Please don't post here. Open a new topic instead.
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now