How do I rename the headers on a text file?
When working with data imported from external files you need to be able to import, modify and export the data. In this lesson we will look at importing a csv file and renaming the headers. Text processing of this kind is an area of particular strength for LiveCode.
Create the interface
First lets make the interface for importing and displaying the csv file. Open a new stack and add a field, called "text" and a button called "import".
Script the import button
We need to allow the user to choose a file to import. We use the answer file command to display a dialog allowing the user to choose a file. We can use answer file with type to ensure that only CSV files are displayed.
We check the result function to make sure the user has not cancelled the dialog. If the dialog has not been cancelled the filename of the file they have chosen is put into the it variable. We will put the value into the variable tFileName so we can use it later.
on mouseUp local tFileName answer file "Please choose a file to import" with type "Comma Separated Values|csv|CSV" if the result is not "cancel" then put it into tFileName end if end mouseUp
Display the data
The previous step gave us the path to the file we want to display, but not the actual data. To get the contents of the file we can use the URL keyword. We add a line to the script of our button to get the contents of the file and put them in a variable, we then add another line to put the file contents into the field.
on mouseUp local tFileName, tFileContents answer file "Please choose a file to import" with type "Comma Separated Values|csv|CSV" if the result is not "cancel" then put it into tFileName ## Get the contents of the file put URL ("file:" & tFileName) into tFileContents ## Display the file contents in the field put tFileContents into field "text" end if end mouseUp
Rename the headers
At this stage we want to rename the headers of our file, perhaps to remove spaces from the headers. There are a number of ways to do this in LiveCode, the simplest is to edit the field manually, but if you were using a non-editable field to display the data you could use the replace command. This replaces one piece of text with another piece of text.
In this case we might want to replace the column header "policy ID" with "ID" we can do this by executing the following line in the message box
replace "policy ID" with "ID" in field "text"
As you can see the column header has been changed in the field.
You have to be careful when using replace because it will replace all instances of the given text with the new text. We only want to replace the column header so if we want to be a little more careful we can modify our LiveCode to only look at the first line of the field.
replace "policy ID" with "ID" in line 1 of field "text"
This means if the text "policy ID" appears anywhere else in the field it won't be replaced.
Using a datagrid
A field is simple but not the clearest way of displaying our data, so lets import the csv file and display it in a data grid instead. Data grids provide a simple and flexible means of displaying data, for more information have a look at the data grid lessons at
Firstly lets remove our field and add a data grid instead, we will name it "text" again.
Set up the data grid columns
Choosing the file works the same way as before but now we need to do a little extra work to display the data, not much though.
Firstly we need to set up the column titles in the datagrid, we know these are in the first line of our csv file. To set up the column titles of a data grid in LiveCode we need to set the columns property of the datagrid, this is a line delimited list so we take the first line of our file, replace the commas with return and set the column property of the data grid
put line 1 of tFileContents into tColumnTitles replace comma with return in tColumnTitles set the dgProp[ "columns" ] of group "text" to tColumnTitles
Display the data in the data grid
To display our data in the data grid we set the dgText property of the data grid. The dgText is tab separated so we need to replace our commas with tabs. We will also specify that the first line of the data contains the column headers, all the data will then be assigned to the appropriate column depending on the first line. We pass the value true for the parameter pFirstLineContainsHeaders of dgText.
replace comma with tab in tFileContents set the dgText[true] of group "text" to tFileContents
Rename a header in the data grid
Renaming a column in a data grid is very simple, just a single line of LiveCode. Again you can execute this line in the message box, or you could add a button.
set dgColumnName ["statecode"] of group "text" to "State"
Good Stuff . . .