Import CSV or Excel Data into SketchUp

Hey guys, Im trying to import data from csv or excel files into sketchup and then assign that data to objects.
for example xyz coordinates and set an object at the imported positons…or angles for rotation?

I have recently started learning ruby and have been searching for plugins to replicate this

would appreciate any tips if anyone has any :sweat_smile:

thanks :grinning:

Hmm… I’m not sure if there’s anything out there doing exactly what you describe. There are certainly extensions that use CSV and similar to import data. But it’s a bit unclear to me exactly how “assign that data to objects” would be done.

Can you elaborate more in a high level of what you want to do?

Sounds like you have source data with position for object, then apply that to some “objects”. I assume these object will be groups or components. Would the CSV contain the name of the component to position?
Then there is the question, what do you do if a component have many instance? What is the instance is deeply nested in another group or component?

By the way, to read CSV file specifically, Ruby have support for that in the StdLib: https://ruby-doc.org/stdlib-2.2.0/libdoc/csv/rdoc/CSV.html
Should cover most scenarios of reading/writing.

There have been previous requests on how to do this in this very forum category (not that long ago.)

thanks for the reply tt

Ok I will try and elaborate as best as i can

so basically you get what I am trying to do

I have a source file with various data, such as x y z positions and and angles for rotation…amongst other information. for this particular task i would like sketchup to take the rotation angles and apply them to a range of components…in the source file that I have the components are named and each x y z location corresponds to a component

hope this was helpful

Tip 1: Use the magnifying glass icon at the top right to search a forum category (when in a category) for terms such as “CSV” or “Excel” (omit the quotes.)

Tip 2: You need to at least TRY to solve your challenge yourself. If you have problems with some code, then post the code you have tried, and ask specific questions about the errors you are encountering.

As said, this has been discussed recently here (as you’ll see if you search this category. See link above.)

Other than this …

thanks Dan

If you show us what you have so far, post a snippet here and we can guide you further.

ok so since writing this post I have tried my best to learn ruby, although there is still some things i do not understand i have begun to write a plugin to import my data

here is the code so far…

require 'sketchup.rb'
require 'extensions.rb'
require 'csv'

#Show ruby console, to see if there are any mistakes

SKETCHUP_CONSOLE.show

module Sirius
module LoudspekerImporter

#Menu items

UI.menu("Extensions").add_item("LS Importer") {
    UI.messagebox("Let's import some loudspeakers !")
    UI.openpanel("Where are your loudspeakers?!","C:Desktop/","CSV |*.csv| Excel| *.xls; *xlsx") 

}
   
end
end

so far this code allows the plugin to shown in the extensions menu and open .csv files and nothing more
what would be the next step? .read files? and store each line in multiple arrays? then assign variables to the arrays?

Can you edit your post, select the code and click the </> button in the post editor toolbar (or wrap it in triple backticks ``` on new lines)? Without that, the discourse forum applies typography which makes your code look more like a hard-to-read novel.

Here are some WIN32OLE examples that may help to read XLS files …
win32ole Samples.zip (14.4 KB)

Refer to the documentation for using the CSV library …
http://ruby-doc.org/stdlib-2.5.1/libdoc/csv/rdoc/CSV.html

Here is a better starting file …

# encoding: UTF-8

require 'sketchup.rb'
require 'extensions.rb'

# Show ruby console, to see if there are any mistakes
SKETCHUP_CONSOLE.show

module Sirius
  module LoudspekerImporter
  
    extend self

    # Module Variables
    @@loaded = false unless defined?(@@loaded)
    @@last_path ||= "C:/Desktop/"

    # Plugin Methods
   
    def import_control
      UI.messagebox("Let's import some loudspeakers !")
      filepath = UI.openpanel(
        "Import loudspeaker file...",
        @@last_path,
        "CSV (Comma Separated values)|*.csv|XLS (Excel Workbook)|*.xls;*.xlsx||"
      )
      if filepath.nil? # UI.openpanel returns nil if user cancels the dialog
        UI.messagebox("Import cancelled by user.")
        return false
      end
      case File.extname(filepath)
      when '.csv'
        @@last_path = File.dirname(filepath)
        get_csv_data(filepath)
      when '.xls','.xlsx'
        @@last_path = File.dirname(filepath)
        get_excel_data(filepath)
      else
        msg = "Unknown filetype! Import cancelled."
        puts "#{Module.nesting[0].name}: #{msg}"
        puts "  Filepath: \"#{filepath}\""
        UI.messagebox(msg)
      end
    end ###

    def get_csv_data(filepath)
      begin
        require 'csv' # load CSV library on demand
      rescue => e
        msg = "Error loading CSV Library! Import cancelled."
        puts "#{Module.nesting[0].name}: #{msg}"
        UI.messagebox(msg)
      else
        # No error. Load the CSV file and parse the data.
        puts "#{Module.nesting[0].name}: Loading loudspeaker data file ..."
        puts "  \"#{filepath}\""
        data = load_csv_data(filepath)
        parse_csv_data(data)
      end
    end ###

    def get_excel_data(filepath)
      begin
        require 'win32ole' # load WIN32OLE class on demand
      rescue => e
        msg = "Error loading WIN32OLE class! Import cancelled."
        puts "#{Module.nesting[0].name}: #{msg}"
        UI.messagebox(msg)
      else
        # No error. Load the XLS file and parse the data.
        puts "#{Module.nesting[0].name}: Loading loudspeaker data file ..."
        puts "  \"#{filepath}\""
        data = load_xls_data(filepath)
        parse_xls_data(data)
      end
    end ###

    def load_csv_data(filepath)
      data = []
      # File load code goes here
      return data
    end ###

    def load_xls_data(filepath)
      data = []
      # File load code goes here
      return data
    end ###

    def parse_csv_data(data)
      # Parse code goes here
    end ###

    def parse_xls_data(data)
      # Parse code goes here
    end ###


    # Define Menu items only ONCE
    unless @@loaded
      UI.menu("Extensions").add_item("LS Importer") { import_control() }      
      @@loaded = true
    end

  end
end

done, sorry about that

1 Like

Thanks Dan, i’m still in the learning process so this is a big help

As @DanRathbun explained, you need to break this down into several pieces such as he did with the different defined methods for loading and parsing tabular data.

Ruby has it’s own CSV module so you can start there, or use win32ole to control excel and read data from an .xls or .xlsx file.

I gather that you’re trying to automate modelling something like a soundstage for live music based on this data?

You will also need to break down the rest of your problem such as

  • where you are going to load your speaker components from?
  • how will the component names match to the tabular data?

After that it should be fairly easy to drop each component in the model with the given coordinates and rotation.

1 Like

I also just noticed this from your code (and parroted in my example.)

The user’s desktop folder is not at that location on MS Windows. It is here …

File.join( ENV['USERPROFILE'],"Desktop" )

It is likley "~/Desktop" on the Mac.


In some cases the folder names are localized in non-English Windows builds.

You should likely use the Windows Scripting Host’s WshSpecialFolders object via Ruby’s WIN32OLE class to get pathnames for the user’s desktop and documents folder. I do believe I’ve seen the “Documents” folder name localized for non-English Windows builds.

So up at the top of your module …

    IS_WIN =( Sketchup.platform == :platform_win rescue RUBY_PLATFORM !~ /darwin/i )
    if IS_WIN
      require 'win32ole'
      wsh_shell = WIN32OLE.new("WScript.Shell")
      USER_DESKTOP_PATH ||= wsh_shell.SpecialFolders("Desktop")
      USER_DOCUMENTS_PATH ||= wsh_shell.SpecialFolders("MyDocuments")
      wsh_shell = nil # let GC clean it up
    else # it's a Mac
      USER_DESKTOP_PATH ||= "~/Desktop"
      USER_DOCUMENTS_PATH ||= "~/Documents"
    end

    # Set the initial path history to User's Desktop:
    @@last_path ||= USER_DESKTOP_PATH
1 Like

yes this is more or less what I am trying to achieve

do you have any recommended reading material for this?

Hey, really appreciate your efforts in trying to help me through this Dan

After trying to experiments with the starting file you provided I keep getting this message

#<NoMethodError: undefined method `get_csv_data’

in sketchup when i try and test the plug in, here is the code so far…

    # encoding: UTF-8


    require 'sketchup.rb'
    require 'extensions.rb'

    #Show ruby console, to see if there are any mistakes

    SKETCHUP_CONSOLE.show

    module Sirius
        module LoudspeakerImporter
            extend self

            # Module variables
            @@loaded = false unless defined?(@@loaded)
            IS_WIN =( Sketchup.platform == :platform_win rescue RUBY_PLATFORM !~ /darwin/i )

        if IS_WIN

          require 'win32ole'

          wsh_shell = WIN32OLE.new("WScript.Shell")

          USER_DESKTOP_PATH ||= wsh_shell.SpecialFolders("Desktop")

          USER_DOCUMENTS_PATH ||= wsh_shell.SpecialFolders("MyDocuments")

          wsh_shell = nil # let GC clean it up

        else # it's a Mac

          USER_DESKTOP_PATH ||= "~/Desktop"

          USER_DOCUMENTS_PATH ||= "~/Documents"

        end

        

        # Set the initial path history to User's Desktop:

        @@last_path ||= USER_DESKTOP_PATH


                UI.menu("Extensions").add_item("Sirius Importer") {
                UI.messagebox("Let's import some loudspeakers !")

                filepath = UI.openpanel("Where are your loudspeakers?!", @@last_path ,"CSV |*.csv| Excel| *.xls; *xlsx") 
                if filepath.nil? #UI.openpanel returns nil if user cancels the dialog
                UI.messagebox("import cancelled :'(")
            return false
                end
            case File.extname(filepath)
            when '.csv'
                @@lastpath = File.dirname(filepath)
                get_csv_data(filepath)
            when '.xls' , '.xlsx'
                @@last_path = File.dirname(filepath)
                get_excel_data(filepath)
            else
                msg = "Unknown filetype..."
                puts "#{Module.nesting[0].name}: #{msg}"
                puts "Filepath: \"#{filepath}\""
                UI.messagebox(msg)
            end

            def get_csv_data(filepath)
                begin
                    require 'csv' #Load csv library
                rescue => e
                    msg = "Error loading CSV Library Import cancelled"
                    puts "#{Module.nesting[0].name} : #{msg}"
                    UI.messagebox(msg)
                else
                    # No error. Load the CSV file and parse data
                    puts "#{Module.nesting[0].name} : Loading loudspeaker data file..."
                    puts " \"#{filepath}\""
                    data = load_csv_data(filepath)
                    parse_csv_data(data)                
                end
            end

            def get_excel_data(filepath)
                begin
                    require 'win32ole' # load WIN32OLE on demand
                rescue => e
                    msg = "Error loading WIN32OLE, Import cancelled"
                    puts "#{Module.nesting[0].name}: #{msg}"
                    UI.messagebox(msg)
                else
                    #No error Load the XLS file and parse the data
                    puts "#{Module.nesting[0].name}: Loading loudspeaker data file..."
                    puts " \"#{filepath}\""
                    data = load_xls_data(filepath)
                    parse_xls_data(data)
                end
            end

            chosen



            def load_csv_data(filepath)
                require 'csv'
                
               data = []
                # File load code goes here 
                CSV.read(filepath, headers: true) do |row|
                    data << row
                end
                UI.messagebox ("#{data.length}, Loudpspeakers Imported..")
                
                return data
            end
            
            def load_xls_data(filepath)
                require 'win32ole'
                data = []
                # File load goes here
            end

            def parse_csv_data(data)
                require 'csv'

                load_csv_data.parse(filepath)
                # Parse code goes here  
            end
            
            def parse_xls_data(data)
                require 'win32ole'
                # Parse code goes here
                
            end

            }

            def self.colorByLayer
                model = Sketchup.active_model
                cbl = model.rendering_options["DisplayColorByLayer"]
                if cbl == false
                    model.rendering_options["DisplayColorByLayer"]= true
                else
                    model.rendering_options["DisplayColorByLayer"]= false
                end
            end



       
    end
    end

…where did I go wrong?

Try this. I’ve moved some code around to make the callback from the Menu item land in the LoudspeakerImporter namespace

# encoding: UTF-8
require 'sketchup.rb'
require 'extensions.rb'

#Show ruby console, to see if there are any mistakes
SKETCHUP_CONSOLE.show

module Sirius
  module LoudspeakerImporter
    extend self

    def menuCallback()
      UI.messagebox("Let's import some loudspeakers !")

      filepath = UI.openpanel("Where are your loudspeakers?!", @@last_path ,"CSV |*.csv| Excel| *.xls; *xlsx") 
      if filepath.nil? #UI.openpanel returns nil if user cancels the dialog
        UI.messagebox("import cancelled :'(")
        return false
      end
      
      case File.extname(filepath)
      when '.csv'
          @@lastpath = File.dirname(filepath)
          get_csv_data(filepath)
      when '.xls' , '.xlsx'
          @@last_path = File.dirname(filepath)
          get_excel_data(filepath)
      else
          msg = "Unknown filetype..."
          puts "#{Module.nesting[0].name}: #{msg}"
          puts "Filepath: \"#{filepath}\""
          UI.messagebox(msg)
      end
    end 
    
    def get_csv_data(filepath)
      begin
          require 'csv' #Load csv library
      rescue => e
          msg = "Error loading CSV Library Import cancelled"
          puts "#{Module.nesting[0].name} : #{msg}"
          UI.messagebox(msg)
      else
          # No error. Load the CSV file and parse data
          puts "#{Module.nesting[0].name} : Loading loudspeaker data file..."
          puts " \"#{filepath}\""
          data = load_csv_data(filepath)
          parse_csv_data(data)                
      end
    end

    def get_excel_data(filepath)
      begin
          require 'win32ole' # load WIN32OLE on demand
      rescue => e
          msg = "Error loading WIN32OLE, Import cancelled"
          puts "#{Module.nesting[0].name}: #{msg}"
          UI.messagebox(msg)
      else
          #No error Load the XLS file and parse the data
          puts "#{Module.nesting[0].name}: Loading loudspeaker data file..."
          puts " \"#{filepath}\""
          data = load_xls_data(filepath)
          parse_xls_data(data)
      end
    end

    def load_csv_data(filepath)
      require 'csv'
      data = []
      # File load code goes here 
      CSV.read(filepath, headers: true) do |row|
        data << row
      end
      UI.messagebox ("#{data.length}, Loudpspeakers Imported..")
      return data
    end
    
    def load_xls_data(filepath)
      require 'win32ole'
      data = []
      # File load goes here
    end

    def parse_csv_data(data)
      require 'csv'
      load_csv_data.parse(filepath)
      # Parse code goes here  
    end
    
    def parse_xls_data(data)
      require 'win32ole'
      # Parse code goes here
       
    end

    def self.colorByLayer
      model = Sketchup.active_model
      cbl = model.rendering_options["DisplayColorByLayer"]
      if cbl == false
          model.rendering_options["DisplayColorByLayer"]= true
      else
          model.rendering_options["DisplayColorByLayer"]= false
      end
    end
    
    # initialization code is often placed down here
    # Module variables
    @@loaded = false unless defined?(@@loaded)
    IS_WIN =( Sketchup.platform == :platform_win rescue RUBY_PLATFORM !~ /darwin/i )

    if IS_WIN
      require 'win32ole'
      wsh_shell = WIN32OLE.new("WScript.Shell")
      USER_DESKTOP_PATH ||= wsh_shell.SpecialFolders("Desktop")
      USER_DOCUMENTS_PATH ||= wsh_shell.SpecialFolders("MyDocuments")
      wsh_shell = nil # let GC clean it up

    else # it's a Mac
      USER_DESKTOP_PATH ||= "~/Desktop"
      USER_DOCUMENTS_PATH ||= "~/Documents"
    end

    # Set the initial path history to User's Desktop:
    @@last_path ||= USER_DESKTOP_PATH
    UI.menu("Extensions").add_item("Sirius Importer") {menuCallback()}
 
  end #module
end #module
  1. Ruby indents are 2 spaces not 4.

  2. I had given you the correct way to conditionally add one menu item at the end of all of the extension code, using the value of the module var @@loaded. You moved that code to an improper place without a conditional if keeping the menu item from creating multiple entries in the menu should you need to reload the file again during development.

  3. I had correctly put the control of the import command in a method rather than define it within a code block passed directly to the menu item constructor call. Doing it your way will mean you have to close SketchUp and restart it each time you make code changes and want them to be applied. This slows down development.
    Ruby is a dynamic language. This means that classes, modules and methods can be redefined at anytime. So if you tweak a method and want to apply the changes, then you reload the rb file you just edited and saved using Ruby’s global load() method instead of the global require() method.

  4. The require 'csv' statement in the parse_csv_data() method is not needed because it is loaded inside a rescue block in the get_csv_data() method, which should always be called first as it only calls the load_csv_data() and parse_csv_data() data methods IF there is no error in loading the CSV library.

  5. The require 'win32ole' statement in the parse_xls_data() method is not needed because it is loaded inside a rescue block in the get_xls_data() method, which should always be called first as it only calls the load_xls_data() and parse_xls_data() data methods IF there is no error in loading the WIN32OLE library.

  6. Calling the == method on a boolean object within a boolean evaluation is frivolous.
    ie … if cbl == false
    The reference cbl will be evaluated anyway, and as an expression of the if statement the result will be tested for truthiness / falsity anyway. So use …
    unless cbl … or … if !cbl

  7. On line 100 you have the reference identifier chosen just sttting there on a line by itself with no assignment or anything else. It was not previously defined and nothing is being done with it. I remove it.

  8. Your colorByLayer() method has several problems.
    a. It should not be a module method. (You do not need to define it with self.)
    b. Ruby methods are named with all lowercase words separated with underscores. CamelCase identifiers are reserved for Module and Class identifiers. Ie color_by_layer_toggle()
    c. It is not called from anywhere in the file, so I’ll omit this from the code for simplicity sake.

  9. In several places you inserted a space between a method identifier and it’s parameter list opening parenthesis. This is a big “no no” and generates a warning. In future Ruby releases this warning is going to change to a SyntaxError and such files will no longer load.

A lot of what I am seeing is you not knowing the basics of Ruby programming.
Really you need to make your life (and ours) easier by taking advantage of all the free tutorials and books on Ruby that are available.


Now the biggest problem that you yourself caused and why you get the NoMethodError is because when YOU did what I explain in (3) above, YOU also moved all the method definitions into a local block of code that is passed to the add_item() method when you created your menu item.
(Code blocks are a scope level.)

I will repost the correct way with a few extra statements of yours added. BUT this is the last time.
If you are going to mess up the code by moving things around then I’ll no longer waste my time.


@sWilliams, My original template above was written correctly. The OP messed it up. Yours has non-rubyish method names and incorrect placement of module variable definitions. Module variables MUST be defined before any use. They are NOT usually put at the bottom of code. They and constants are usually put at the top of the code.
I know you are trying to help, but giving the newb two competing templates and advice is counterproductive, IMO.


Here is the updated template …

Sirius_LoudspekerImporter_0.2.0.rb (3.8 KB)

Which looks like this …

# encoding: UTF-8

require 'sketchup.rb'
require 'extensions.rb'

# Show ruby console, to see if there are any mistakes
SKETCHUP_CONSOLE.show

module Sirius
  module LoudspekerImporter
  
    extend self

    # CONSTANTS
    
    VERSION = '0.2.0'

    IS_WIN =( Sketchup.platform == :platform_win rescue RUBY_PLATFORM !~ /darwin/i )

    if IS_WIN

      require 'win32ole'

      wsh_shell = WIN32OLE.new("WScript.Shell")

      USER_DESKTOP_PATH ||= wsh_shell.SpecialFolders("Desktop")

      USER_DOCUMENTS_PATH ||= wsh_shell.SpecialFolders("MyDocuments")

      wsh_shell = nil # let GC clean it up

    else # it's a Mac

      USER_DESKTOP_PATH ||= "~/Desktop"

      USER_DOCUMENTS_PATH ||= "~/Documents"

    end

    # MODULE VARIABLES
    @@loaded = false unless defined?(@@loaded)
    @@last_path ||= USER_DESKTOP_PATH

    # PLUGIN METHODS
   
    def import_control
      UI.messagebox("Let's import some loudspeakers !")
      filepath = UI.openpanel(
        "Import loudspeaker file...",
        @@last_path,
        "CSV (Comma Separated values)|*.csv|XLS (Excel Workbook)|*.xls;*.xlsx||"
      )
      if filepath.nil? # UI.openpaneel returns nil if user cances the dialog
        UI.messagebox("Import cancelled by user.")
        return false
      end
      case File.extname(filepath)
      when '.csv'
        @@last_path = File.dirname(filepath)
        get_csv_data(filepath)
      when '.xls','.xlsx'
        @@last_path = File.dirname(filepath)
        get_excel_data(filepath)
      else
        msg = "Unknown filetype! Import cancelled."
        puts "#{Module.nesting[0].name}: #{msg}"
        puts "  Filepath: \"#{filepath}\""
        UI.messagebox(msg)
      end
    end ###

    def get_csv_data(filepath)
      begin
        require 'csv' # load CSV library on demand
      rescue => e
        msg = "Error loading CSV Library! Import cancelled."
        puts "#{Module.nesting[0].name}: #{msg}"
        UI.messagebox(msg)
      else
        # No error. Load the CSV file and parse the data.
        puts "#{Module.nesting[0].name}: Loading loudspeaker data file ..."
        puts "  \"#{filepath}\""
        data = load_csv_data(filepath)
        parse_csv_data(data)
      end
    end ###

    def get_excel_data(filepath)
      begin
        require 'win32ole' # load WIN32OLE class on demand
      rescue => e
        msg = "Error loading WIN32OLE class! Import cancelled."
        puts "#{Module.nesting[0].name}: #{msg}"
        UI.messagebox(msg)
      else
        # No error. Load the XLS file and parse the data.
        puts "#{Module.nesting[0].name}: Loading loudspeaker data file ..."
        puts "  \"#{filepath}\""
        data = load_xls_data(filepath)
        parse_xls_data(data)
      end
    end ###

    def load_csv_data(filepath)
      data = []
      # File load code goes here
      CSV.read(filepath, headers: true) do |row|
        data << row
      end
    rescue => e
      puts e.inspect
      UI.messagebox("Error reading CSV file.\n#{e.message}")
    else
      UI.messagebox("#{data.length}, Loudpspeakers Read from File.")
    ensure
      return data # may be empty if a CSV error occurs
    end ###

    def load_xls_data(filepath)
      data = []
      # File load code goes here 
      UI.messagebox("#{data.length}, Loudpspeakers Read from File.")
      return data
    end ###

    def parse_csv_data(data)
      # Parse code goes here
      #UI.messagebox("#{data.length}, Loudpspeakers Imported..")
    end ###

    def parse_xls_data(data)
      # Parse code goes here
    end ###


    # Define Menu items only ONCE
    unless @@loaded
      UI.menu("Extensions").add_item("LS Importer") { import_control() }      
      @@loaded = true
    end

  end
end

:bulb:

1 Like