LiveCode LessonsData GridLiveCode Data Grid Advanced OptionsHow do I create a Datagrid dynamically from tab delimited data

How do I create a Datagrid dynamically from tab delimited data

Most of the lessons show how to use and change previously created data grids, and assume you want to create them in the IDE. This lesson will explain how to create a table data grid by script.

Prepare your data

To make sure that the data grid can accept your tabular data, you should prepare it with these points in mind:

  • This example works with tab delimited data.
  • The first row of the data should include the names of the columns (this makes it easier to create columns later on)
  • each name should be unique
  • no empty names are allowed

One way to create tab delimited data is to choose "save as..." in Excel, and then select "text  file (tab delimited)". It is simple to load data into Livecode from such a file:

   answer file ""
   put url ("file:" & it) into tData

Create a data grid

Each data grid consists of a group with lots of scripts and objects. Luckily, you do not need to recreate all that from scratch. Instead you can simply copy a template data grid from the data grid library. Because this library is neccesary for all data grid functionality, it will be added to standalones by the IDE automatically. Please also see the lesson "What Do I Need to Do To Deploy a Standalone With A Data Grid?"  for an explanation when and how you need to add the library to standalones yourself.

The code to copy a datagrid:

    --copy the template group
   copy group "DataGrid" of group "Templates" of stack "revDataGridLibrary" to this card
   set the name of it to "my Datagrid"
   set the dgProp["style"] of group "my Datagrid" to "table"

Create the Row template

In theory, your data grid is already ready for use. But for the IDE to interact correctly with the new data grid, you also need to add a substack with the row template, just as the IDE would. On the second card of that stack, you will need to add a group called "Row Template", and you need to point your data grid to that group.

Here is how you could do that:

   --create row template
   put the name of this stack into tMainstack
   put "Data Grid Templates" && the seconds into tName
   create invisible stack tName
   set the mainStack of stack tName to tMainstack
   go stack tName
   create card
   create field "label"
   create graphic "Background"
   group field 1 and graphic 1
   set the name of last group to "Row Template"
   go stack tMainstack
   --point datagrid to template
   set the dgProp["Row Template"] of group "my Datagrid" to the long id of group "Row Template" of stack tName

 

Fill in your data

As a last step, you will want to insert the prepared data into the data grid. Again, this assumes that the first row of your data contains an unique description for each column:

   --add data
   put true into tFirstLineIsNames
   set the dgText[tFirstLineIsNames] of group "my DataGrid" to tData

Prepare the columns of the data grid

Because you are creating the datagrid from arbitrary data, you will also need to dynamically create all necessary columns. To do that you need to set the dgProp["columns"]. Note that it accepts the names only as a return delimited list.

If your data includes the column names in it's first row, you can simply replace tab with return:

   --create columns
   put line 1 of theData into tColumns
   replace tab with return in tColumns
   set the dgProp["columns"] of group "my DataGrid" to tColumns

Everything together

To test out the example scripts, simply copy the following 'mouseUp' handler into a button of an empty stack, and click on it with the 'browse' tool.

on mouseUp
   local tData, tMainstack, tName, tColumns, tFirstLineAreNames
   
   answer file "tab delimited" with type "any file" or type "tab file|tab|TEXT" or type "txt file|txt|TEXT"
   --in case the user cancels the file dialogue, the script does not proceed
   if it = "" then
      exit mouseUp
   end if
   put url ("file:" & it) into tData
     
   lock screen --when doing visual stuff, this helps to speed things up
   --make sure the group does not exist already
   if there is a group "my Datagrid" then
      delete group "my Datagrid"
   end if
   --copy the template group
   copy group "DataGrid" of group "Templates" of stack "revDataGridLibrary" to this card
   set the name of it to "my Datagrid"
   set the dgProp["style"] of group "my Datagrid" to "table"
   --position it nicely
   set the rectangle of group "my Datagrid" to 0,the bottom of me + 16,the width of this card, the height of this card
     
   --create row template
   put the name of this stack into tMainstack
   put "Data Grid Templates" && the seconds into tName
   create invisible stack tName
   set the mainStack of stack tName to tMainstack
   go stack tName
   create card
   create field "label"
   create graphic "Background"
   group field 1 and graphic 1
   set the name of last group to "Row Template"
   go stack tMainstack
   --point datagrid to template
   set the dgProp["Row Template"] of group "my Datagrid" to the long id of group "Row Template" of stack tName
     
   --create columns
   put line 1 of tData into tColumns
   --no empty column names are allowed, so I insert a space when that happens
   replace tab & tab with tab & " " & tab in tColumns
   replace tab with return in tColumns
   set the dgProp["columns"] of group "my DataGrid" to tColumns
     
   --add data
   put true into tFirstLineIsNames
   set the dgText[tFirstLineIsNames] of group "my DataGrid" to tData
end mouseUp

4 Comments

Charles

How do you copy a Data Grid table to the clipboard so it can be copied to a document?

Elanor Buchanan

Hi Charles, you can do this in code, something like

set the clipboardData["text"] to the dgText of group "DataGrid 1"

but as far as I can tell there is not a way to select the contents and use the keyboard shortcuts to copy the data to the clipboard. I suggesting adding this as an enhancement request at

http://quality.livecode.com/

Kind regards

Elanor

Charles

Can import a csv (Excel) file to DataGrid and then later after importing more csv files, export all of them as a csv file to Excel?

Elanor Buchanan

Hi Charles

There is not a way to do this directly but you could write some code to convert the CSV data to tab separated data and then set the dgText of the DataGrid. You could then go the other way to get it back.

The simplistic way to do this would be

replace comma with tab in tData

But you would have to look out for commas within items etc. There are a few CSV importing code snippets around that you might find useful.

Kind regards

Elanor

I hope that helps.

Kind regards

Elanor

Add your comment

E-Mail me when someone replies to this comment