I saw the tutorial on connecting to MYSql, Access so forth. How do I connect to an Excel spreadsheet? I want to be able to make a nice database that takes the stduent's information and picture. Staff will be able to look up a student by any criteria and see picture of that student. Is this possible? What connection string do I use. I am totally new to this. I built databases with Microsoft Access and FileMaker Pro. I am excited about this program but want to make sure I can do what I want. Please advise.
I think you can setup an ODBC connection to an Excel spreadsheet, in which case using a DSN means no connection string is required. Very much doubt you can get access to pictures thoughs. Why do you want to use Excel?
If you want to build the program in REBOL, you could just export your existing data from the spreadsheet as a CSV file, and then parse it with something like: Code: ; Read and parse the CSV formatted file: filename: %filename.csv data: copy [] lines: read/lines filename foreach line lines [ append/only data parse/all line "," ] ; Add headers from sections of the spreadsheet to each line item: info: copy "" foreach line data [ either find "Header" line/1 [ info: line/1 ][ append line info ] ] ; Remove the unwanted descriptive header lines: remove-each line data [find "Header" line/1/1] remove-each line data [ (line/3 = "TITLE") or (line/3 = "DESCRIPTION") ] Here's a quick hack of my "card file" example to demonstrate how to save student records with a displayed image: Code: REBOL [title: "Student Photos"] write/append %data.txt "" database: load %data.txt view center-face gui: layout [ text "Load an existing record:" name-list: text-list blue 300x80 data sort (extract database 5) [ if value = none [return] marker: index? find database value n/text: pick database marker a/text: pick database (marker + 1) p/text: pick database (marker + 2) o/text: pick database (marker + 3) p/image: load pick database (marker + 4) p/text: "" show gui ] text "Name:" n: field 300 text "Address:" a: field 300 text "Phone:" p: field 300 text "Notes:" o: area 300x60 text "Image:" i: btn 300 [ i/text: request-file p/image: load to-file i/text show gui ] at 340x20 p: image 300x300 across btn "Save" [ if n/text = "" [alert "You must enter a name." return] if find (extract database 5) n/text [ either true = request "Overwrite existing record?" [ remove/part (find database n/text) 5 ] [ return ] ] save %data.txt repend database [ n/text a/text p/text o/text (read/binary to-file i/text) ] name-list/data: sort (extract copy database 5) show name-list ] btn "Delete" [ if true = request rejoin ["Delete " n/text "?"] [ remove/part (find database n/text) 5 save %data.txt database do-face clear-button 1 name-list/data: sort (extract copy database 5) show name-list ] ] clear-button: btn "New" [ n/text: copy "" a/text: copy "" p/text: copy "" o/text: copy "" p/image: none show gui ] ]
Here's an article I just published http://www.onhealthtech.com/content.php?7-Importing-patients-into-Medisoft-part-2 on importing a CSV file, but I turned it into a pipe delimited file first because the simple parse of "," can fail if there are commas inside the cells of the spreadsheet.
BTW, you can eliminate a variable this way Code: lines: read/lines filename foreach line lines [ append/only data parse/all line "," ] Code: foreach line read/lines filename [ append/only data parse/all line "," ]
Wow, did not expect such quick and good answers. The reason I am using Excel is because our school database downloads in Excel format. I have built a program that we can look up our student by ID or by name and see their picture. This comes in real handy because we have over 2500 students and sometimes we need to identify one. I built it in FileMaker Pro 10. It is a nice program but of course it takes so much resource because I have to build stand alone programs. The district will not buy Server licenses. Thank you for everyone's help. I am going to give this a try. I am very new at this. I tried to go by Barnes and Nobles to get the Rebol for Dummies but they do not carry it. QUESTION: NOTCHENT Right now I have all my photos in a folder. That is how I call it into FileMaker Pro 10. It resides in a folder on the C:\ drive. Can I do that the same way for this program?
Yes, that's even easier, because you don't have to save the data to the REBOL data file. Just save the file name, and load it each time you update the image display. Here's an updated example: Code: REBOL [title: "Student Photos"] write/append %photos.txt "" database: load %photos.txt view center-face gui: layout [ text "Load an existing record:" name-list: text-list blue 300x80 data sort (extract database 5) [ if value = none [return] marker: index? find database value n/text: pick database marker a/text: pick database (marker + 1) p/text: pick database (marker + 2) o/text: pick database (marker + 3) i/text: pick database (marker + 4) photo/image: load to-file i/text photo/text: "" show gui ] text "Name:" n: field 300 text "Address:" a: field 300 text "Phone:" p: field 300 text "Notes:" o: area 300x60 text "Image:" i: btn 300 [ i/text: request-file photo/image: load to-file i/text show gui ] at 340x20 photo: image 300x300 across btn "Save" [ if n/text = "" [alert "You must enter a name." return] if find (extract database 5) n/text [ either true = request "Overwrite existing record?" [ remove/part (find database n/text) 5 ] [ return ] ] save %photos.txt repend database [ n/text a/text p/text o/text i/text ] name-list/data: sort (extract copy database 5) show name-list ] btn "Delete" [ if true = request rejoin ["Delete " n/text "?"] [ remove/part (find database n/text) 5 save %photos.txt database do-face clear-button 1 name-list/data: sort (extract copy database 5) show name-list ] ] clear-button: btn "New" [ n/text: copy "" a/text: copy "" p/text: copy "" o/text: copy "" i/text: copy "" photo/image: none show gui ] ]
By the way, a line by line explanation of the card file example can be found at http://musiclessonz.com/rebol.html#section-8.3 Also the file requester can be made to suit your needs better, for example: Code: request-file/title/file/filter "Select a Photo:" "" %/C/folder/ ["*.jpg" "*.png"]
Adding Photos Ok, I noticed when I ran the file that I had to click on the bar to look for photo. Is there a way to write the program that brings the photo's in automatically by linking it with the student's ID number? Also would I be able to make a search field that someone can search for the student by name or student ID? Thank you for all the help.
Yes - that functionality is already built in. Just make one of the fields an ID number. You can add a many fields as you'd like. The button is just there for the user to select the image when creating a new record. If you already have the file names defined for each student, in your other database, just transfer that information over as part of each individual record (one field can hold the file name). To make a search field you could start with something as simple as: Code: t: text-list f: field [ results: copy [] foreach item (extract database 5) [ if find item f/text [ append results item t/data: results show t ] ] ]
Invalid Words The following code gives me an "Invalid word" error: Syntax Error: Invalid word -- STUDENTID,LASTNAME,FIRSTNAME,DOB,GRAD E ** Near: (line 1) STUDENTID,LASTNAME,FIRSTNAME,DOB,GRADE I saved the Excel file into a CSV (MS_DOS) file. Do I need to change the headers? What is wrong. Thank you. Code: REBOL [title: "StudentListRebol"] write/append %StudentList.csv "" database: load %StudentList.csv ; Read and parse the CSV formatted file: filename: %StudentList.csv data: copy [] lines: read/lines StudentList foreach line lines [ append/only data parse/all line "," ] ; Add headers from sections of the spreadsheet to each line item: info: copy "" foreach line data [ either find "Header" line/1 [ info: line/1 ][ append line info ] ] ; Remove the unwanted descriptive header lines: remove-each line data [find "Header" line/1/1] remove-each line data [ (line/3 = "TITLE") or (line/3 = "DESCRIPTION") ]
This looks wrong write/append %StudentList.csv "" ; you can just initialize the file to an empty file without the append database: load %StudentList.csv ; not sure what this is for ... it now loads an empty file ; Read and parse the CSV formatted file: filename: %StudentList.csv ; you set the filename to the name of the empty file data: copy [] lines: read/lines StudentList ; StudentList is not defined .. maybe you mean %StudentList, or you meant filename ... but so far it's reading an empty file foreach line lines [ append/only data parse/all line "," ]
Invalid Words The %studentlist.csv is my list of students in excel that I saved as a csv file. So I am not sure what you mean about an empty file. That file has information in it. So apparently I am setting it up wrong. I want the Excel file information to poplulate the studentphoto.r that was made by notchent. I was just testing this code to see if it gave me the information that I wanted. I got confused when it basically told me that my headers were invalid words.
Ok, try this - remove this line Code: write/append %StudentList.csv "" and change this line Code: lines: read/lines StudentList to Code: lines: read/lines filename
Still getting the same error. Here is the code that I changed. Thank you. Code: REBOL [title: "StudentListRebol"] database: load %StudentList.csv ; Read and parse the CSV formatted file: filename: %StudentList.csv data: copy [] lines: read/lines filename foreach line lines [ append/only data parse/all line "," ] ; Add headers from sections of the spreadsheet to each line item: info: copy "" foreach line data [ either find "Header" line/1 [ info: line/1 ][ append line info ] ] ; Remove the unwanted descriptive header lines: remove-each line data [find "Header" line/1/1] remove-each line data [ (line/3 = "TITLE") or (line/3 = "DESCRIPTION") ]
Noteleks, to avoid a lot of wasted time, can you post a few example lines of your exported CSV file (with dummy data)?
It will not let me upload the example file that I have. When I go to get it. It does not show the csv file. The upload box only defaults to all files, but it will not see the file. Any ideas?
Open the .csv file with notepad or any other text editor, and copy/paste a few lines directly into a reply here. 4 or 5 records should be plenty
STUDENTID,LASTNAME,FIRSTNAME,DOB,GRADE 111111,Doe,Steven D,6/16/1992,12 111111,Doe,Jonathan Daniel,12/16/1991,12 111111,Smith,Karen J,12/3/1991,12 111111,Jones,Michael J,6/4/1992,12 111111,Taylor,Ryan C,1/10/1992,12 111111,Adam,Kaitlan C,4/30/1992,12 111111,Washington,Gabryela,3/31/1992,12 111111,Travolta,Juan D,1/24/1992,12 111111,Cruise,Amber E,5/8/1992,12
