Problem:
Google Task import into TickTick or GTasks only imports GTasks that are active and if Completed, then only 1 year old.
If you want to import very old Completed tasks into TickTick, there is no way to do this with the TickTick nor Google GTasks.
Solution:
Pre Req’s:
Get the Google Tasks Export Format:
Get the TickTick Import File Format:
See https://help.ticktick.com/forum/topic/348273/import-list-items-from-excel
Also, run an export to see the EXACT format, there is heade and footer stuff
Run a export and save as a SampleTickTickExport.csv
See Figure 2 to compare TickTick and Google tasks csv format.
1. Export ALL tasks from Google Tasks to .csv including tasks up to 5 years old. using https://tasks-backup.appspot.com/ (check all the check boxes for hidden/deleted)
tasks_import_export_[email]_2018-05-24.csv
2. import that .csv into a SQL table, then transform the data to what TickTick expects.
Import GoogleTasks.csv into SQL table. : [tasks_import_export_f]
Use SQL (attached file TaskImportExport.sql) to clean up data. Step 1 in sql file
Use SQL to create a new table “Staging” [tasks_import_export_d] Step 2 in sql file
3. Export staging data (see #2 below in SQL) to a csv file using Results Save to file, save the csv name Staging_GTasks.csv
4. Because TickTick wants unix style LF, you must supply a LF at end of each line , NOT CRLF
Open in Notepad++, replace CRLF with LF for (very easy in Notepad++)
5. Open Staging_GTasks.csv in textpad & open SampleTickTickExport.csv
paste the contents from Staging_GTasks.csv into SampleTickTickExport.csv in between header and footer.
6. import into Tick Tick using back utility (very nice!)
7. Done!
8. Someday soon automate this! perhaps 2-3days of code we could make a web app to create the TickTick file, or use this manual proc,
here is API that may be a start:
FAQ:
Code:
9. I may just change my mind buz:
https://blog.hubspot.com/marketing/best-to-do-list-apps-tools
Why pay when I get possibly all I need in Wunderlist?
Figure 1:
Figure 2:
Figure 3 SQL:
use [myNGC_STG_Custom_PCard]
go
-- -1) Clean Up
--select * FROM [dbo].[tasks_import_export_f] where len([due])= 0 and len([completed]) =0
--update [dbo].[tasks_import_export_f] set [due] = '2018-04-29' where len([due])= 0 and len([completed]) =0
--select * FROM [dbo].[tasks_import_export_f] where len([due])= 0 and len([completed]) =0
--update [dbo].[tasks_import_export_f] set [due] = '2018-04-29' where len([due])= 0 and len([completed]) =0
----update [dbo].[tasks_import_export_f] set [due] = [due] + 'T04:00:00+0000' where len([due])> 0
--update [dbo].[tasks_import_export_f] set [completed] = left([completed], 10) where len([completed])> 0
--update [dbo].[tasks_import_export_f] set [completed] = [completed] + 'T04:00:00+0000' where len([completed])> 0
--2) export to csv, save to file, replace CRLF with LF (Notpad++)
set nocount on
SELECT '"' +[tasklist_name]+ '"' "List Name"
,'"' +[title]+ '"' "Title"
,'"' +replace(dbo.[udfTrimInvalidChars](ltrim(rtrim([notes]))), '\n' , CHAR(13))+ '"' "Content"
--,replace(dbo.[udfTrimInvalidChars](ltrim(rtrim([notes]))), '\n' , CHAR(10) ) "Content2"
,'"' +'N' + '"' "Is Checklist"
,'"' +[due]+ '"' "Start Date"
,'"' +[due]+ '"' "Due Date"
,'"' +''+ '"' "Reminder"
,'"' +''+ '"' "Repeat"
,'"' + '0'+ '"' "Priority"
,'"' +case when len([completed]) > 0 Then '2' Else '0' End + '"' "Status"
,'"' +case when len(due) =0 then [completed] Else [due] End + '"' "Created Time"
,'"' +[completed]+ '"' "Completed Time"
,'"' +'-1099511627776' + '"' "Order"
,'"' +'America/New_York'+ '"' "Timezone"
,'"' +'' + '"' "Is All Day"
FROM [dbo].[tasks_import_export_f]
CREATE FUNCTION [dbo].[udfTrimInvalidChars](@String VARCHAR(MAX))
RETURNS VARCHAR(MAX)
BEGIN
Declare @Result varchar(max)
Set @Result = ltrim(rtrim(Isnull(@String, '')))
Set @Result = replace(replace(replace(replace(replace(replace(replace(@Result, '''', ''), '"', ''), ' ', ' '), char(160), ''), CHAR (13) + CHAR (10) , ''), CHAR (10), ''), CHAR (9), '')
Set @Result = case when len(@Result) = 0 then null else ltrim(rtrim(@Result)) End
return dbo.[udfTrim](@Result)
END
CREATE FUNCTION [dbo].[udfTrim] (
@str VARCHAR(MAX)) RETURNS VARCHAR(MAX)
AS
BEGIN
DECLARE @trimchars VARCHAR(10)
SET @trimchars = CHAR(9)+CHAR(10)+CHAR(13)+CHAR(32)
IF @str LIKE '[' + @trimchars + ']%' SET @str = SUBSTRING(@str, PATINDEX('%[^' + @trimchars + ']%', @str), 8000)
RETURN ltrim(rtrim(@str))
END
--Refs:
--https://www.cnet.com/how-to/how-to-import-and-export-in-google-tasks/
--https://productforums.google.com/forum/#!topic/calendar/pSmEFZPJG50
--https://www.technorms.com/24082/export-gmail-tasks-google-tasks-backup
--https://tasks-backup.appspot.com/progress--https://help.ticktick.com/forum/topic/348273/import-list-items-from-excel
--https://www.ticktick.com/#settings/backup
