jdenver Posted October 4, 2011 Posted October 4, 2011 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 Posted October 4, 2011 Posted October 4, 2011 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 Posted October 4, 2011 Author Posted October 4, 2011 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 Posted October 4, 2011 Posted October 4, 2011 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 Posted October 5, 2011 Author Posted October 5, 2011 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 Posted October 5, 2011 Posted October 5, 2011 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 Posted October 5, 2011 Author Posted October 5, 2011 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 Posted October 5, 2011 Posted October 5, 2011 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 Posted October 6, 2011 Author Posted October 6, 2011 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 Posted October 6, 2011 Posted October 6, 2011 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 Posted October 6, 2011 Author Posted October 6, 2011 Thanks guys for your help, Ive put extra quotes around the fields as set out 5 posts up, and it works well now. :laugh2:
Recommended Posts
This topic is 4816 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