need to export specific data...

[MParker]

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.

[comment]

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.

[jbernal]

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

[MParker]

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

[comment]

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.

[MParker]

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.

[comment]

it just seems like there would have to be a way to do it in filemaker.

I already suggested two such ways.

[MParker]

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.

[comment]

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.

[Fenton]

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

[MParker]

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.

[comment]

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 ( ... )