CSV to Data Grid / date time sorting

[kbirand]

Hi Everyone,

I am new to Livecode. I have a simple csv file which consists of 6 columns

user | action | folder | ip | date | time
john | copy | /users/cccc/ | 192.143.23.45 | 29.01.2009 | 17:43

I have no problem reading it out to a data grid table and sorting them.
The only problem I am facing is sorting the date column. I am using the date time sort order for the date column but it does not understand so no sorting is done.
All sorting features are working fine.

I assume data grid doesnt actually understand the data in the date cells. So can not do the actual sorting.
I have faced a similar problem with Xcode but I solved by definin each cell data as date(myDate).
I think merging the date and time column can solve it. if so how is that possible.
Or any other suggestions.

I am using the following code:


on mouseUp
 
local tFileName, tFileContents

put URL ("xxx.yyy.zzz/log.txt") into tFileContents

put "User,Action,FileFolder,Ip,Date,Time" & return before line 1 of tFileContents

put line 1 of tFileContents into tColumnTitles
replace comma with return in tColumnTitles
set the dgProp[ "columns" ] of group "MobileNumbersDataGrid" to tColumnTitles

replace comma with tab in tFileContents

set the dgText[true] of group "MobileNumbersDataGrid" to tFileContents
put the DGData of grp "MobileNumbersDataGrid" into tArray
set the DGData of grp "MobileNumbersDataGrid" to tArray



end mouseUp

[gpb01]

Hi,
AFAIK, if you want to use the standard DataGrid sort capability (little arrow on the header row), you have to store the date/time in a single field and in a format that can be easly sorted (e.g. SQL DateTime format : YYYY-MM-DD HH:MM:SS or using convert to convert date and time in seconds) and then use the "column behavior" to convert the internal format to the format that you want to show to the user.

Remember that there is NOT a "date" type in LiveCode ... all is "alphanumeric" and if you try to sort your dates as is (e.g. GG.MM.YYYY) you have just the ... alphabetical order

Personally, I store the date in the DataGrid array using the seconds and I convert them using the "column behavior" ...

See "How Do I Override the Default Behavior For Rendering Data to a Cell ?", page 124 of the "LiveCode DataGrid" pdf.

Guglielmo

[kbirand]

Ok How can I merge my column 5 (date) and column 6 (time) and convert them to seconds before placing them into a data grid.

Thanks.
Koray Birand

[gpb01]

How can I merge my column 5 (date) and column 6 (time) and convert them to seconds before placing them into a data grid.
...

Hi Koray,
I suppose ... is a question ... also if no question mark

The answer is ... use the "convert" statement that you find in the dictionary !
Assuming you use the GG/MM/DD format in your system ...

Code: Select all

convert your_date && your_time from system date and long time to seconds

Guglielmo

[kbirand]

Dear Guglielmo,

Have you seen my code... Because I could not figure out where to put your line of code.

Thanks,
Koray Birand

[gpb01]

... dependig which solution you prefer :

a) You create a NEW column (that can be also invisible to the users) where you put the seconds and you sort the DG on this column

b) You change the lables and the last two items of each row of your data without adding a new colum, replacing the item date and item time with only one item containing the seconds.

In both case, obviously, you have to put the "convert" BEFORE setting the dgText of your DG.

Guglielmo

[kbirand]

I feel like an idiot. I am not used to live code scripting so struggling a a lot.
I could not succeed with andy of the methods. Can you paste your code to my code so that I can see how it works..

Thanks
Koray Birand

[gpb01]

... ok, the easiest solution :

Code: Select all

on mouseUp
local tFileName, tFileContents, tLine, tDateTime
--
put URL ("xxx.yyy.zzz/log.txt") into tFileContents
--
put "User,Action,FileFolder,Ip,Date,Time,Seconds" & return before line 1 of tFileContents
--
put line 1 of tFileContents into tColumnTitles
replace comma with return in tColumnTitles
set the dgProp[ "columns" ] of group "MobileNumbersDataGrid" to tColumnTitles
--
-- calculate and add the new column item (Seconds) for each line
--
repeat with nLine = 2 to (the number of lines in tFileContents)
  put the line nLine of tFileContents into tLine
  put (the fifth item of tLine) && (the sixth item of tLine) into tDateTime
  convert tDateTime from system date and long time to seconds
  put comma & tDateTime after line nLine of tFileContents
end repeat
--
replace comma with tab in tFileContents
set the dgText[true] of group "MobileNumbersDataGrid" to tFileContents
put the DGData of grp "MobileNumbersDataGrid" into tArray
set the DGData of grp "MobileNumbersDataGrid" to tArray
--
end mouseUp

... not tried so check the sintax but .. broad outlines should work

Guglielmo

[kbirand]

It works perfectly but unfortunately it takes 13 seconds because of the repeat (The csv file has 11.000 lines)
Any other fast suggestions ?

[gpb01]

... mmm ... probably can be optimized to reduce the time, but the best solution is to add the "Seconds" column ... at the source of data

Guglielmo

[kbirand]

I can't add another column because the data is generated by an other source. So it is dynamic.
And also what I actually need is to be able to click on the triangle on the header and to be able to sort.
Seconds is nice for just sorting initially but if I hide the column then i can not do any sorting.

When you import an array to a table in Xcode applescript studio, if the cell has a date data the table is
able to sort it. Does not live code has this feature. It should since it has a date time sorting ability.

So why can a data grid understand dd.mm.yyyy as a date and sort it ?

Anybody ?

Koray Birand

[gpb01]

... If you study carefully the "LiveCode DataGrid" (http://lessons.runrev.com/s/lessons/m/datagrid/pdf) and the "DataGrid Lessons" (http://lessons.runrev.com/m/datagrid) you can find first how to sort column programmatically (so you can handle sort also by invisible columns) and second how to work with the "Column behavior" to handle special content columns.

Guglielmo

[gpb01]

Hi Koray,
I found this lesson which can be very useful for you ... "How Do I Customize Column Sorting?" here : http://lessons.runrev.com/s/lessons/m/d ... mn-sorting

Guglielmo

[phaworth]

It works perfectly but unfortunately it takes 13 seconds because of the repeat (The csv file has 11.000 lines)
Any other fast suggestions ?

Lots of good advice regarding how to do this in datagrid terms but one thing to note about your script. In general, repeat loops of the form "repeat with x=1 to the number of lines of <whatever>" are very inefficient, especially when you have 11,000 lines to process. It's much quicker to use a "repeat for each line x in the lines of <whatever>". Sometimes you still need the line number, in which case just use a variable initialized to zero before the loop and add 1 to it at the top of the loop.

Pete