Page 1 of 1
Converting field data to a database
Posted: Mon Aug 29, 2016 2:53 am
by montymay
I have a stack of thousands of cards containing twelve fields. Too many cards! I now want to convert the data into a database by (1) creating a delimited text file and then (2) loading it into a SQLite database, but some LC fields are multi-lined, e.g., field "cites". (See script below.) Correct me if I'm wrong, but I think that the following script can mostly get step 1 done, but I don't understand how to preserve the structure of the fields with multiple lines. If I use the following script, the line corresponding to each card is not a single line: each line of fld "cites" is in its own line.
Is there is a way to keep the lines of muti-lined fields within the same line as the other items?
Code: Select all
on mouseup
repeat with x=1 to the number of cards of stack "ops"
put fld "optype" of card x of wd "ops" into tOpType
put fld "date" of card x of wd "ops" into tDate
put fld "pcfnum" of card x of wd "ops" into tPCFnum
put fld "agency" of card x of wd "ops" into tAgency
put fld "question" of card x of wd "ops" into tQuestion
put fld "opinion" of card x of wd "ops" into tOpinion
put fld "facts" of card x of wd "ops" into tFacts
put fld "rationale" of card x of wd "ops" into tRationale
put fld "comment" of card x of wd "ops" into tComment
put fld "cites" of card x of wd "ops" into tCites
put fld "keys" of card x of wd "ops" into tKeys
put fld "solicitor" of card x of wd "ops" into tSol
put tOptype&Tab&tdate&Tab&tpcfnum&Tab&tagency&Tab&tquestion&Tab&topinion&Tab&tfacts&Tab&trationale&Tab&tcomment&Tab&tCites&Tab&tKeys&Tab&tSol into line x of URL "file:c:/Users/myPath/data.txt"
end repeat
end mouseup
I have other questions about this process, but I will save them for a separate post. Thanks for any pointers or tips.
Monty
Re: Converting field data to a database
Posted: Mon Aug 29, 2016 2:16 pm
by Mikey
The easiest way to do this, IMHO, is not to create a CSV file, first (which is what you are proposing), but instead to just iterate through the cards. If you are intent on creating a csv file, then you can use tab for your field delimiters, but instead of using CR for your records, use something else, perhaps ascii 2, for instance (or you can quickly write a script that will find the lowest ascii value that is not in any of your fields, and use that as your line delimiter).
As I said, I would suggest just iterating through the cards. When you do that, you could use a parameterized query. If you do that, then when you pass the value of each field, the CR's will automatically be preserved.
INSERT INTO myTable(a,b,c,d) VALUES(:1,:2,:3,:4)
Then the array you generate will be roughly of the form
myArray[1][field "1"]
myArray[2][field "2"]
Re: Converting field data to a database
Posted: Mon Aug 29, 2016 6:29 pm
by Klaus
Hi Monty,
what Mikey said!
hwere some hints to save some typing by setting the defaultstack and speeding up saving of your data:
Code: Select all
on mouseup
set the defaultstack to "ops"
repeat with x=1 to the number of cards of stack "ops"
put fld "optype" of card x into tOpType
put fld "date" of card x into tDate
put fld "pcfnum" of card x into tPCFnum
...
## First collect ALL data in a variable:
put tOptype&Tab&tdate&Tab&tpcfnum&Tab&tagency&Tab&tquestion&Tab&topinion&Tab&tfacts&Tab&trationale&Tab&tcomment&Tab&tCites&Tab&tKeys&Tab&tSol & CR after tOutputData
end repeat
## Remove trailing CR from variable:
delete char -1 of tOutputData
## And finally write data to disk "en bloc", MUCH faster than writing line by line:
put tOutputData URL "file:c:/Users/myPath/data.txt"
end mouseup
Best
Klaus
Re: Converting field data to a database
Posted: Mon Aug 29, 2016 6:59 pm
by Mikey
One other thing: If you are intent on generating a csv file, you could make it into a "real" csv file, which surrounds each column with an apostrophe, and separates them with a comma:
'a','b','c'
'xfirstline
xSecondLine','y','z1firstline
z1secondline
z1thirdline'
And if whatever package you are using to parse the resulting file knows "real" csv (e.g. libreOffice), it will recognize the multi-line lines as CR's embedded in a column.
Re: Converting field data to a database
Posted: Wed Aug 31, 2016 4:53 am
by montymay
Thank you Mikey and Klaus,
Reading your responses, I'm beginning to see how to correctly create a CSV file, but also beginning to see how "reiterating through the cards" is better. Taking as my template the script from the LC lesson on sqlite, I wrote the following script.
Code: Select all
on mouseup
set the defaultstack to "ops"
put specialFolderPath("documents") & "/myPath/allopsdata.sqlite" into tDatabasePath
put revOpenDatabase("sqlite", tDatabasePath, , , , ) into tDatabaseID
put "CREATE TABLE allopsdata (type char(50), date char(50), . . . . etc. ))" into tSQL --There are 12 fields
revExecuteSQL tDatabaseID, tSQL
repeat with x=7 to the number of cds of wd "ops"
put fld "optype" of card x into tOpType
put fld "date" of card x into tDate
. . . etc.
put "INSERT INTO allopsdata(type, date, . . . etc.) VALUES ('tOpType','tDate', . . . );" into tSQL
end repeat
end mouseup
But I don't understand the last lines in Mikey's response about the array that will be generated, so I am thinking the above script won't work. I don't see how this script generates an array. Could you confirm it will work, correct it, and give further guidance? Thanks so much.
Monty
Re: Converting field data to a database
Posted: Wed Aug 31, 2016 1:31 pm
by Mikey
Code: Select all
lock screen
repeat with i = 1 to the number of cards
go to card i of this stack
repeat with j = 1 to the number of fields on this card
put field j into myArray[j]
end repeat -- with j = 1 to the number of fields on this card
## DO THE INSERT HERE
end repeat -- with i = 1 to the number of cards
That will give you an array. The indexes will be 1..n. The rest is left as an exercise for the reader.
Re: Converting field data to a database
Posted: Sun Sep 04, 2016 8:13 am
by montymay
Hello Mikey,
Thank you for your last post in response to my request for help. From it and other readings I realize I am in way over my head and definitely must study SQL in depth, but for now it would be much appreciated if you could give one last explanation of your script, in particular, the meaning of these three lines:
INSERT INTO myTable(a,b,c,d) VALUES(:1,:2,:3,:4)
Then the array you generate will be roughly of the form
myArray[1][field "1"]
myArray[2][field "2"]
This script is my attempt to follow your suggestion, but it is way off the mark:
Code: Select all
on databaseInsertopdata
set the defaultstack to "ops"
put 1 into z
repeat with i=7 to 9
go card i of this stack
repeat with j=1 to 13
put field j into MyArray[j]
answer myarray[j]
end repeat
answer z
## What goes into parentheses after VALUES?
put "INSERT INTO opsDetails (optype, date, . . . etc.) VALUES(MyArray[j], MyArray[j], . . . etc.);" after tSQL
end repeat
put getDatabaseID() into tDatabaseID
revExecuteSQL tDatabaseID, tSQL --Does another variable go at the end of this line?
end databaseInsertopdata
I just need an a short explanation of the meaning and purpose of "(:1,:2:,:3, etc.)". I read something about "placeholders" in SQL code. Are these placeholders? Also, the notation "myArray[1][field "1"]" suggests that a multi-dimensional array is used. Yes? One last tip and I will trouble you no more!
Monty
Re: Converting field data to a database
Posted: Sun Sep 04, 2016 1:43 pm
by Mikey
Monty,
Yes, those are placeholders in the SQL, and the array code is wrong. Yes, it was a two-dimensional array, but you would not build a parameter array that is two-dimensional in this case. If you are INSERTing the data during your visit to each card, then you would only use a one-dimensional array. The keys of that array correspond to the placeholders, and you would do something like
Code: Select all
lock screen
repeat with x = 1 to the number of cards in this stack
go to card x of this stack
repeat with i = 1 to the number of fields on this card
put field i into myArray[i]
end repeat #with i = 1 to the number of fields on this card
### INSERT GOES HERE
end repeat # with x = 1 to the number of cards in this stack
Your SQL would still look something like
INSERT INTO myTable (column1,column2,column3) VALUES(:1,:2,:3)
Again, sorry about the bad advice on the array.
Re: Converting field data to a database
Posted: Wed Sep 07, 2016 1:12 pm
by MaxV
You have to send data in binary mode to the database, this way you can send anything and don't care about what it contents.
Livecode support binary mode to databases, see
http://livecode.wikia.com/wiki/SQLite chapter
working with binaries.
For example I'd use this code, please note the
*b prefixes for binary transfers at the end of the code:
########CODE#######
repeat with x=1 to the number of cards of stack "ops"
put fld "optype" of card x of wd "ops" into tOpType
put fld "date" of card x of wd "ops" into tDate
put fld "pcfnum" of card x of wd "ops" into tPCFnum
revExecuteSQL myID, "insert into mytable (optype,date,pcfnum) values(:1,:2,:3)", "*btOptType", "*btDate","*bPCFnum"
end repeat
#####END OF CODE#####
Re: Converting field data to a database
Posted: Wed Sep 07, 2016 1:36 pm
by Mikey
Did I miss something, Max? I didn't see any binary columns in his description.
Re: Converting field data to a database
Posted: Wed Sep 07, 2016 5:22 pm
by MaxV
You don't need them. Passing data in binary mode just send the data as is and the database store it.
You have a text like this:
"hello
my friend"
but you can't use a standard query, cause the return or other messy chars.
If you use binary mode, no problem.
Re: Converting field data to a database
Posted: Sun Sep 11, 2016 12:30 pm
by AxWald
Hi,
MaxV wrote:If you use binary mode, no problem.
Thx! There's still fancy new things to learn, fascinating!
Have fun!