MParker Posted January 22, 2009 Share Posted January 22, 2009 ok, i have to figure out a way to export records from my database in a very specific format. this is the format it HAS to be in when it exports.... NOTE: quotes HAVE to export "FIELD1","","","","","","","","","","FIELD2","FIELD3","FIELD4",FIELD5,"FIELD6","FIELD7","","","FIELD8","FIELD9","","","FIELD10","","FIELD11","" each line in the csv that has to export will be a new record from the table. any help would be greatly appreciated. Link to comment Share on other sites More sharing options...
comment Posted January 23, 2009 Share Posted January 23, 2009 I am not sure I understand this fully. It seems like you need to insert several dummy fields in between your real fields? You coud probably do just that - or export as XML with a XSLT stylesheet doing it for you. Link to comment Share on other sites More sharing options...
jbernal Posted January 23, 2009 Share Posted January 23, 2009 The solution, which I've used before successfully, is to forget for a moment that you're exporting to .csv and, instead, create you own .csv formatting using a calculation field. You'll end up exporting only the calculation field, but the resulting file will contain exactly the data you want. Define a new calculation field (for example, "export") that returns text. Use this calculation: """ & FIELD1 & "","","","","","","","","","","" & FIELD2 & "","" & FIELD3 & "","" & FIELD4 & ""," & FIELD5 & ","" & FIELD6 & "","" & FIELD7 & "","","","" & FIELD8 & "","" & FIELD9 & "","","","" & FIELD10 & "","","" & FIELD11 & "",""" where FIELD5, unless you forgot the quotes, is intended to be a number of some type. The backslashes tell Filemaker to use literal quotes, thus preserving the double-quotes as field-delimiters, which is what you're looking for. By the way, most database programs will let you specify which columns you want to import into, solving the problem of having to manufacture dummy fields as you're doing here. For example, in MySQL the syntax is: load data local infile 'members.csv' into table members fields terminated by ',' enclosed by '"' lines terminated by 'r' (fm_record_id, id, name_first, name_last, full_name, name_company, contact_type, public_defender, work_phone, cell_phone, fax_number, emails, address_www, bar_number, member_status) Imagine that the members table contains any number of other columns, but I'm telling MySQL that I only want to import into the specified columns. Let me know if that helps. J Bernal Link to comment Share on other sites More sharing options...
MParker Posted January 23, 2009 Author Share Posted January 23, 2009 (edited) well, i've tried that...but it's not working. it's adding extra double-quotes. this is what happens when i do it that way.... """Testing Account"","""","""","""","""","""","""","""","""","""",""BC"",""123456789"",""987654321123"",20,""Invoice#101010"",""D"","""","""",""CCD"",""CoName"","""","""",""0000"","""",""0000"",""""" if i try to take out one of the quotes, it stops working all together. Edited January 23, 2009 by Guest Link to comment Share on other sites More sharing options...
comment Posted January 23, 2009 Share Posted January 23, 2009 I think the idea is to export the result as tab-separated text. Note that this is a hack, and can fail easily if your fields contain commas and/or quotes. Link to comment Share on other sites More sharing options...
MParker Posted January 23, 2009 Author Share Posted January 23, 2009 fields will never contain quotes....possible commas though. either way, i can't get it to work, so i'm going to have to export to excel and then create a macro to do it for me i guess. it just seems like there would have to be a way to do it in filemaker. Link to comment Share on other sites More sharing options...
comment Posted January 23, 2009 Share Posted January 23, 2009 it just seems like there would have to be a way to do it in filemaker. I already suggested two such ways. Link to comment Share on other sites More sharing options...
MParker Posted January 23, 2009 Author Share Posted January 23, 2009 well, the dummy fields idea couldn't work because of "field5" which can't have quotes around it on export. and xml....i have no clue how to make an xslt stylesheet...so, i think that option is out too. not to mention that the end product needs to be a txt file. Link to comment Share on other sites More sharing options...
comment Posted January 23, 2009 Share Posted January 23, 2009 i have no clue how to make an xslt stylesheet... There are some basic examples that come with the application, more on FMI's site, and numerous threads here - many of them with practical examples. And the result can certainly be a .txt file. But it does require some learning. Link to comment Share on other sites More sharing options...
Fenton Posted January 23, 2009 Share Posted January 23, 2009 Here is an example of CSV output, which gives you separator for each field (whether there's a value or not), and lets you specify the separator (quotes or not). It could be fancier, and use FileMaker's metadata to determine whether to put the quotes or not (number fields usually). But that's (much) more complex. Notice that it specifies the separator (whether there's FileMaker field data or not), and only uses FileMaker to specify the ROW and the value to put between the separators. It doesn't use FileMaker COL/DATA to specify whether to put a separator, just to put the value (if it exists). CSV_some_quotes.xsl.zip Link to comment Share on other sites More sharing options...
MParker Posted January 23, 2009 Author Share Posted January 23, 2009 that works perfectly! now, on to the next problem....how to i get it to export the next line, then the next....etc? as of right now, it's just exporting the first record. Link to comment Share on other sites More sharing options...
comment Posted January 23, 2009 Share Posted January 23, 2009 Hi Fenton, just curious: why did you prefer the "push" method on this one? Wouldn't it be easier to do: for each row, concat ( ... ) Link to comment Share on other sites More sharing options...
Recommended Posts
This topic is 5571 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