Jump to content
Sign in to follow this  
MParker

need to export specific data...

Recommended Posts

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.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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 by Guest

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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

I already suggested two such ways.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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 account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

  • Who Viewed the Topic

    1 member has viewed this topic:
    Tattwam 
×

Important Information

By using this site, you agree to our Terms of Use.