11 Replies Latest reply on Aug 13, 2012 12:34 PM by usbc

    Script import of a .csv file that uses single quotes?

    Oliver_Reid

      I have a .csv file that uses single quotes (') , whereas it seems FMP uses double quotes (")

       

      By first importing into Excel, I can do this one time, but I then for additional imports I will have field called, say,

       

      Number1

       

      but the import script step will see the header field name in the csv file as

       

      'Number1'

       

       

      There are hundreds of fields and I want/need to use "matching names" and I need the recrurring import from these files to be a one click step for the user

       

      Note the data itself is all numeric and is not enclosed in quotes. Its the field names in songle quotes that cauaing a probelm.

       

       

      Any ideas?

        • 1. Re: Script import of a .csv file that uses single quotes?

          Oliver,

           

          Why use Excel at all? Simply set up the .csv import in FileMaker.

           

          Winfried

          • 2. Re: Script import of a .csv file that uses single quotes?
            Oliver_Reid

            Wnfired

             

            Yes but the file uses a single ' as a text qualifier. FM expects "

             

            I got round the problem for now by miving through Escel ro field names with the ' around them, and importing new data without using matching names.

             

            But as teh applciation devlops and more fields are addedd, I would really like to use matching names to script imort orders.

             

            As far as I can tell that is impossible with a csv file that uses single quotes. This will be a recurring import: I can't ask the client to fix the csv content every time ( hourly, actually)

            • 3. Re: Script import of a .csv file that uses single quotes?
              Oliver_Reid

              Unless of course there is an XSLT sheet out there that converts single quote csv to an FMP-friendly XML format?

              • 4. Re: Script import of a .csv file that uses single quotes?
                comment

                Oliver_Reid wrote:

                 

                Unless of course there is an XSLT sheet out there that converts single quote csv to an FMP-friendly XML format?

                 

                No, that's not possible. XSLT requires an XML file as the input.

                • 5. Re: Script import of a .csv file that uses single quotes?
                  comment

                  Oliver_Reid wrote:

                   

                  I would really like to use matching names to script imort orders.

                   

                  As far as I can tell that is impossible with a csv file that uses single quotes.

                   

                  Well, you could rename your target table's field names to include the quotes...

                   

                  I'd suggest looking into a plugin (or AppleScript, if you're on OS X) to pre-process the source file.

                  • 6. Re: Script import of a .csv file that uses single quotes?
                    beverly

                    Yes, this is an excellent suggestion by Michael. You can actually create a "temp table" (if you wish) by importing the first time and let FM create the field names (with the single quote). From then on, additional imports can use the "matching names" option.

                     

                    You could then "map" the single-qouted field names to non-quoted field names, if you need them.

                     

                    I often use a temporary import table to bring in Excel (or CSV) that needs to be "cleaned up" for better use with FM.

                    Since this is multiple imports (Oliver said hourly), don't fight with it.

                     

                    Another option is a File-type plug-in that would revise the first row to remove the single quotes before import into FM.

                     

                    Beverly

                     

                    On 12 Aug 2012, at 7:54 AM, Michael Horak replied in whole or in part:

                    • 7. Re: Script import of a .csv file that uses single quotes?
                      usbc

                      Along the same theme as Bev & Michael, you can import the file into a single text field and do a substitute to another text field.

                      Something like - Substitute ( Imported Text ; Char (  34 );"'"  ) . Then a export / import routine.

                      • 8. Re: Script import of a .csv file that uses single quotes?
                        Oliver_Reid

                        Tried that . When you export it then puts " around the whole block "

                        • 9. Re: Script import of a .csv file that uses single quotes?
                          TomHays

                          If you can, fixing the problem at the source is the best option. Change whatever is generating the "CSV" files to use double quotes instead of single quotes. Using single quotes to surround columns in CSV files is very uncommon.

                           

                          Otherwise fixing the files in an automated way before importing them at all is the second best option.

                           

                          As others have mentioned, a FileMaker plug-in that allows you to modify files is a good way to let FileMaker perform the fixing just prior to import.

                           

                          I can show you how to do this easily in a cross-platform way in the Lua scripting language using the free LuaFy plug-in.

                           

                          After installing the LuaFy plug-in, customize and add the following lines to your import Script to prepare the CSV file for importing.

                           

                          Set Variable [$fullPath_OldCSVFile; Value: ...]

                          Set Variable [$fullPath_NewCSVFile; Value: ...]

                          Set Variable [$luaScript; Value: ...]

                          Set Variable [$luaResult; Value: LuaFy_RunScript($luaScript; $fullPath_OldCSVFile; $fullPath_NewCSVFile)]

                          Set Variable [$luaErrResult; Value: LuaFy_LastError]

                          If [LuaFy_LastError = 0]

                             # Do your import from $fullPath_NewCSVFile

                          else

                            # Report the error situation (Lua's error is found in $luaErrResult)

                          End If

                           

                           

                          The values of $fullPath_OldCSVFile and $fullPath_NewCSVFile need to be the full paths of the the old CSV file you want fixed and the new file containing the fixed CSV data.

                          On Macintosh this might be something like:

                             /Volumes/Macintosh HD/Users/thays/Documents/data/oldfile.csv

                          On Windows this might be something like:

                             C:\Documents and Settings\Tom Hays\My Documents\data\oldfile.csv

                          or

                             C:/Documents and Settings/Tom Hays/My Documents/data/oldfile.csv

                          The latter form may be more useful since it closer to FileMaker's

                             filewin:/C:/Documents and Settings/Tom Hays/My Documents/data/oldfile.csv

                          which you would use in FileMaker's "Specify File" dialog on import.

                           

                          If you want to avoid having to delete the new CSV file after importing, you can use FileMaker's temporary directory, i.e. Get(TemporaryPath). Files in this directory will automatically be deleted when you exit the FileMaker session.

                           

                          The value for $luaScript can be the following short script. Note that there are both single and double quotes on the gsub() line. This substitution replaces the single quotes with the double quotes that FileMaker likes. (I assume that you don't have any double quotes in the value of the field names on the first line.) This substitution is only done on the first line. The rest of the lines are copied as-is. If you prefer to delete the single quotes around the fields, use line = line:gsub("'(.-)'", "%1") instead.

                           

                          local theOldFile, theNewFile = ...

                          local outFile, theError = io.open(theNewFile,'w')

                          if outFile==nil then error(theError) end

                          local isFirstLine = true

                          for line in io.lines(theOldFile) do

                             if isFirstLine then

                                line = line:gsub("'(.-)'", "\"%1\"")

                             end

                             outFile:write(line.. '\n')

                             isFirstLine = false

                          end

                          outFile:close()

                           

                          You can use this directly in the LuaFy_RunScript() calculation instead of assigning it to a script variable, but it is easier to debug escaping the double quotes if you can observe the value of the variable in the FileMaker Pro Advanced Data Viewer window.

                           

                          If you don't want to figure out how to escape the double quotes (and the slash while you are at it), you can create a global variable and paste the script into it. Use the global in place of the script variable.

                           

                          Some explanation of the script above.

                          All parameters after the first in LuaFy_RunScript() are passed to the Lua in the special variable "...". The first line in the Lua script assigns the two values you provided to two Lua variables.

                          io.lines() takes care of opening, reading each line, and closing the file when done.

                          The gsub() line looks for a pattern of single quote plus zero or more characters followed by a single quote. The "-" in the pattern says to match the fewest number of characters that fit the pattern. If we used .* instead of .-, it would match the longest sequence and match all of the first row at once.

                           

                          -Tom

                          • 10. Re: Script import of a .csv file that uses single quotes?
                            beverly

                            Oliver, if you pull all into one field (many records) and fix with the substitute, you must export AS .TXT (.tab) - tab delimited. You do NOT want to put double quotes around the values. Remember that you have something like this:

                            ================

                            'field1','field2','field3'\r

                            1,2,3\r

                            4,5,6\r

                            7,8,9

                            ====

                             

                            If you IMPORT as tab and push into one field each "row", you can subsitute on the first row. Then EXPORT as tab-delimited. Then you can RE-IMPORT as .csv (because it will be corrected):

                            ==========

                            field1,field2,field3\r

                            1,2,3\r

                            4,5,6\r

                            ...

                            ====

                            • 11. Re: Script import of a .csv file that uses single quotes?
                              usbc

                              Bev is correct. Also, you can correct it in that single field and export it as a Merge file AND give it the .csv suffix via your export script.

                              Then the import using field names will work as if you got a clean .csv file. I just did a quicky test. Either way works.

                              Chuck