Our Preferred 5 Star Hosting Provider - Award Winning FileMaker Pro Hosting
Results 1 to 5 of 5
  1. #1
    Forum Member
    Join Date
    Jan 2017
    Posts
    20
    Location
    Kathmandu, Nepal
    FileMaker Pro Software
    FileMaker 14 Advanced
    OS
    Mac
    Skill Level
    Novice
    Last Post
    04-28-2017
    Local Date
    09-19-2017
    Local Time
    08:12 PM
    Last Active
    09-01-2017

    Script for importing a csv file whose column order keeps on changing

    I'm creating a script for importing a csv file. That file will get imported every week.

    I don't want to present a dialog box to user for mapping field since they might make mistake.

    However, there's a problem. The file's column order keeps on changing. So, I can't use the last import order.

    Is there anyway I can import records on the basis of column title? I know there's matching field name option but the problem is the column title of the csv file are like "Refunds ($)" and I can't name the filed using special characters like (, $ in filemaker.

    Any solution?

  2. #2
    Senior Moderator - FMT Preferred Developer Consultant - Netherlands, Europe, Asia, USA Maarten Witberg's Avatar
    Join Date
    Mar 2004
    Posts
    6,135
    Location
    netherlands
    FileMaker Pro Software
    version 11, 12, 13, 14, 15
    OS
    Windows & Mac
    Skill Level
    Developer
    Last Post
    06-29-2017
    Local Date
    09-20-2017
    Local Time
    04:12 AM
    Last Active
    07-16-2017
    So are the column titles always the same at least?
    Stick to what you don't know.

    http://www.maartenwitberg.nl

    Filemaker 15 Certified Developer

    -------

  3. #3
    Forum Member
    Join Date
    Jan 2017
    Posts
    20
    Location
    Kathmandu, Nepal
    FileMaker Pro Software
    FileMaker 14 Advanced
    OS
    Mac
    Skill Level
    Novice
    Last Post
    04-28-2017
    Local Date
    09-19-2017
    Local Time
    08:12 PM
    Last Active
    09-01-2017
    Quote Originally Posted by Maarten Witberg View Post
    So are the column titles always the same at least?
    Yes column titles are always same.

  4. #4
    Senior Moderator - FMT Preferred Developer Consultant - Netherlands, Europe, Asia, USA Maarten Witberg's Avatar
    Join Date
    Mar 2004
    Posts
    6,135
    Location
    netherlands
    FileMaker Pro Software
    version 11, 12, 13, 14, 15
    OS
    Windows & Mac
    Skill Level
    Developer
    Last Post
    06-29-2017
    Local Date
    09-20-2017
    Local Time
    04:12 AM
    Last Active
    07-16-2017
    OK then you migh try this approach.

    Paste the CSV as a whole in a (global) text field. Load it in a variable $rawCSV
    Take the first row of the CSV and store it as a (return delimited) variable $columnNames
    Take a list of your target field names or a fixed-order list of column names
    A possible way of matching the column names to the field names is by determining the position of the column name in the list of field names.


    Something like (not tested!!!)
    Code:
    #where the records need to end up
    go to layout [ Somelayout ( Sometable ) ]
    
    # load the whole CSV
    Set Variable [ $RawCSV ; Sometable::gCSVdrop ]
    
    # retrieve list of column names (line 1 of the CSV)
    Set Variable [ $columnNames ; substitute ( getvalue ( $rawCSV ; 1 ) ; "," ; ¶ ) // assuming it's really simply comma delimited, there are more CSV formats out there ]
    set variable [ $totalcolumns ; valuecount ( $columnNames ) ]
    
    #set a set of fieldnames
    set variable [ $fieldnames ; “fieldA¶fieldB¶…..” ]
    
    # retrieve the real data (all lines of the CSV except line 1)
    Set variable [ $CSVlines ; middlevalues ( $rawCSV ; 2 ; valuecount ( $rawCSV ) ]
    Set variable [ $totallines ; valuecount ( $CSVlines ) ]
    
    # loop 1: CSV lines
    Set variable [ $linecount ; 1 ]
    
    Loop
        Set variable [ $currentline ; substitute ( getvalue ( $CSVlines ; $linecount ; “,” ; ¶ ) // again assuming comma delimited]
        Set variable [ $count ; 1]
            # loop 2: Columns
            Loop    
                #ya
                new record / request [ ] 
    
                #get the current column name 
                Set variable [ $currentColumnName ; getvalue ( $columnnames ; $count ) ]
    
                 #determine the position number of the current column name
                # ValuePosition is a CF, see below
                Set variable [ $pos ; ValuePosition ( $fieldnames ; $currentColumnName )
    
                # match the column to the field by its position number
                Set Field by name [ field: Choose ( $pos ; "fieldA" ; "fieldB" ; ..... etc ) ; value: getvalue ( $currentline ; $count )  ]
    
                #move on to next column
                Set variable [ $count ; $count + 1 ]
    
                Exit loop if [ $count > $totalcolumns
            End loop
    
        #move on to next line
        Set variable [ $linecount ;  $linecount + 1 ]
        Exit loop if [ $linecount > $totallines 
    End loop
    ValuePosition is a custom function: https://filemakerhacks.com/2011/06/1...emaker-forgot/
    Last edited by Maarten Witberg; 02-07-2017 at 03:16 AM.
    Stick to what you don't know.

    http://www.maartenwitberg.nl

    Filemaker 15 Certified Developer

    -------

  5. #5
    Forum Member
    Join Date
    Jan 2017
    Posts
    20
    Location
    Kathmandu, Nepal
    FileMaker Pro Software
    FileMaker 14 Advanced
    OS
    Mac
    Skill Level
    Novice
    Last Post
    04-28-2017
    Local Date
    09-19-2017
    Local Time
    08:12 PM
    Last Active
    09-01-2017
    Thank you. Good suggestion.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Changing Sort Order on a Listing
    By Tgic in forum FileMaker Pro 10 Advanced
    Replies: 1
    Last Post: 08-12-2009, 05:55 PM
  2. Changing the order in a field
    By Terry215 in forum ScriptMaker and Scripting
    Replies: 1
    Last Post: 07-11-2009, 10:47 AM
  3. Script for changing Portal Sort Order with FMP 9
    By JohnP in forum FileMaker Pro 9
    Replies: 1
    Last Post: 02-24-2008, 09:56 AM
  4. Scripts Changing Sort Order? :confused:
    By cbielich in forum Find, Sort and Search
    Replies: 1
    Last Post: 03-23-2002, 10:13 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Welcome to the Original FileMaker Forum
With designated forums for almost every FileMaker topic...FileMaker Today is a FREE to join community where you can boost your FileMaker expertise, build better apps & solutions and interact with over 42,000+ FileMaker Developers from all over the world. If you're building FileMaker solutions, this is the place for you.
Follow FMT
We Are FMT
FMT is home of The World's leading FileMaker News Site, Worldwide Developer Directory, FileMaker Connect Community and so much more.