Importing fault caused by comma

[jdenver]

I noticed if I use the File>Export Records command, the exported text is inclosed by quotes, so any commas in the text are imported ok when a .CSV is used.

But if I export in a script using a variable, then the exported text is not inclosed in quotes, so any commas in the text causes the full text not to be imported.

So how can I correct this as I need to import as a .CSV file.

Thanks in anticipation.

[Fitch]

In the same dialog box where you specify the export file path as a variable, it allows you to choose the file type. Be sure to choose Merge format if you want the quotes and field names.

[jdenver]

I dont use a dialog box. The line in the script is:

Set Variable ($cdata; Value:tb_customers::pk_customers & "," &tb_jobinfo::pk_job & "," & tb_jobinfo::date_finished & "," & tb_jobinfo::work_done & "," & tb_jobinfo::total

The $cdata is then used as the import in the same script.

When I call up a Show Custom Dialog for $cdata, to see the results, I see that because the 'work_done' field is a description of work carried out and can contain commas in the text I only get the first part up to the first comma.

When I tried to export the same fields to a .CSV manually and looked at the .CSV file, the different fields are incased in quotes (speech marks " ")

In other words the script will create a variable like this:

0230,4455,04/10/11,Services repaired, resealed and labelled,150

Whereas the manual export command to a .CSV file creates this:

"0230","4455","04/10/11","Services repaired, resealed and labelled","150"

When the script one in imported the work done just reads: Services repaired

Whereas importing the .CSV file, the work done reads: Services repaired, resealed and labelled.

Sorry for the confusion I just find it difficult to explain the fault. Hope this has made it clearer

Any ideas?

[comment]

Please explain this part:

The $cdata is then used as the import in the same script.

You cannot import a variable, so obviously something is missing from your description.

[jdenver]

The import line reads: Set Variable [$datain; Value:MWKS_ImportText( $data; $mapname)]

All this works, but as I said above the work_done text is truncated at the first comma in the paragraph it comes to during importing which doesn't happen if I manually import. And the only difference I can see is the Quotes"" missing from the individual fields that only appear if I use File - Import Records menu option.

[comment]

The import line reads: Set Variable [$datain; Value:MWKS_ImportText( $data; $mapname)]

Something is still missing from your description, because Set Variable [] is not a "import line" and MWKS_ImportText() is not a native Filemaker function. It looks like you are using a plugin to perform a pseudo-import, using a variable as the source. Which seems like a very elaborate detour, since you could simply load the data into individual variables, create a new record and set its fields to the variables.

Anyway, if you want the field values to be enclosed in quotes, you need to set the $cdata variable to something like:

"\"" & YourTable::YourField1 & "\",\"" & YourTable::YourField2 & "\",\"" & ... YourTable::YourFieldN & "\""

Note that this is not a perfect solution, because it doesn't address in-field quotes and other CSV limitations - see:

http://www.filemaker...25.html#1041694

[jdenver]

Yes your right, i am using a plugin as Im new to this and didn't know any other way of exporting the required data. I didn't want to export all the fields in a record, just a selection and couldn't get that to work. If there is a better way, can you give me a rough idea on where to start please.

[comment]

If there is a better way

A better way to do what? You didn't say what's the purpose of this export/import exercise.

[jdenver]

I need to select a selection of fields from the database and import them into MoneyWorks Express. Moneyworks supply the plugins like:

MWKS_ImportText( filename; importmap )

And I created the following script in FM:

Export Selected data to MoneyWorks

#Connect to MW Express

Set Variable [ $start; Value:MWKS_Connect( "E" ) ]

#Get data from FM

Set Variable [ $cdata; Value:tb_customers::pk_customers & "," & tb_customers::surname & "," & tb_customers::houseno_name & "," & tb_customers::line_address_1 & "," & tb_customers:: town & "," & tb_customers::county & "," & tb_customers::postcode ]

#Set up the Map Name

Set Variable [ $mapname; Value:"db_map" ]

#Import the data using the map name into MW

Set Variable [ $cdatain; Value:MWKS_ImportText( $cdata; $mapname ) ]

[Fitch]

Now we're getting somewhere. The question is really more about MoneyWorks than about FileMaker.

Checking out the MoneyWorks sample file, it appears that you can define an "import map" -- that's a MoneyWorks thing, not FileMaker -- as either tab-delimited or CSV. So when you construct your $cdata variable in FileMaker, you can either make it for CSV and build quotes around the fields as Michael described a couple of posts above, or you can make it for tab-delimited and separate the fields with tabs rather than commas.

HTH.

[jdenver]

Thanks guys for your help, Ive put extra quotes around the fields as set out 5 posts up, and it works well now. :laugh2: