Converting field data to a database

[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

[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"]

[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

[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.

[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

[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.

[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

[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.

[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#####

[Mikey]

Did I miss something, Max? I didn't see any binary columns in his description.

[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.

[AxWald]

Hi,

If you use binary mode, no problem.

Thx! There's still fancy new things to learn, fascinating!

Have fun!