Page 2 of 3 FirstFirst 123 LastLast
Results 11 to 20 of 27
  1. #11
    Join Date
    Sep 2007
    Location
    Victoria, BC Canada
    Posts
    185
    Quote Originally Posted by DerekSmith View Post
    I would like to access an XL spreadsheet file. Each line holds a name, a date and a number of other text fields. I would like to load each name in turn and check its Soundex value (I know how to do the Soundex bit) against a target soundex string. If there is a match, I would then like to load in the rest of the line information into a record array for later display and printing.

    I have read Zarco Gajics tutorial on how to load and edit an XL spreadsheet into a DBGrid http://delphi.about.com/od/database/l/aa090903a.htm and am wondering if it is possible to bolt onto the back of this somehow, once the spreadsheet is loaded into the DBGrid, are the field values accessable for further processing and if so, either how do I do this, or where can I find information that can explain to me how? If it cannot be done, what route should I be exploring.

    Thanks
    DerekSmith
    Welcome Derek!

    Yes, everything you want is quite possible with Delphi. Actually, you want to ignore the grid and go with what's already at the back-end ... the underlying recordset of ADOQuery1.

    If you've followed the example from Zarco you have in hand a recordset in AdoQuery1. You started out by dropping it onto the form as a component and worked with it through the properties dialogue to get the records to display on the grid. You can also manipulate this recordset in code. Basically you need to create a method [function or procedure] that will look through all of the records in the recordset, find the ones you want to extract and store them for later processing when you do.
    Zarco has a topic on processing the recordset here:
    http://delphi.about.com/od/database/l/aa040301b.htm
    You'll want to replace the line "Do_Summary_Calculation;" with your mutex testing and extraction code.

    There is another way to skin this cat - a disconnected recordset. Suck the data out of the spreadsheet, disconnect, delete the rows that don't match the mutex and use the results in your report.

    A long row to hoe but taking the Beginners Guide to Delphi Database Programming will explain a lot to you.
    http://delphi.about.com/od/database/...secourse_2.htm

    HTH
    DaveK
    Last edited by TDelphiHobbyist; 02-28-2008 at 04:24 PM.

  2. #12
    Join Date
    Dec 2007
    Posts
    25
    OK $eymour, a bit more information.

    The purpose of the utility is to search the XL records for a name and a date.

    In my county there are ca 800 Parishes most of which have records of Births, Deaths, Marriages and Banns. These old records have been (are being) transcribed into XL spreadsheets. When a member of the Society wants to search the records for say a marriage of a Mr Roberts in 1846, the volunteer will open up the parish XL sheet and manually search through for Roberts and 1846. If they do not find a match, they will widen the search and look in all the local parishes records within a 10 mile radius (could be up to 30 parishes, each file of ca 10,000 records). This is a lot of manual work and they would like a utility that will allow them to select the home Parish, enter the name and the date and have that utility search the XL spreadsheets for them. I have already written the front end that allows the user to pick the home parish from all the county parishes and work out all the parishes within a 10 mile radius and list them for searching in order of ascending distance from the home parish. I have the part written to take in the name and create the Soundex search string and select either the target date or a date range and the type of search (i.e. birth, marriage etc.). To finish the utility, all I need to do now is to be able to access the data within the selected parish XL spread sheets and list any matches found.

    But I don't know how to get hold of the data from a spreadsheet to be able to work with it and I don't know where to look for information that explains how to do this.

    You said in your first reply, all I have to do was to read the sheet into a record and write this out to a .DAT file. Well, if you can explain to me how to read the XL data into a record, then I can work with that directly - I don't need to write it out to a .DAT file because I will already have it in my programme in a manner that can be searched.

    Can you explain to me how to read XL data into a record please.

    Many thanks for your help.
    Regards
    Derek
    Last edited by DerekSmith; 02-28-2008 at 05:06 PM.

  3. #13
    Join Date
    Dec 2007
    Posts
    5
    As $eymour said you can use excel for this.

    Because excel has the Visual Basic language underlying it you can use if statements in it. Of course if they used an if statement then it would have to be altered upon each use ie If you search for Mr. Robertson and then you want to switch to Mr. Smith then the if statment would be changed.

    The only problem with this i can see is you might not be able to have mutiple criteria ie Mr. Robertson 1984 Married but this i don't know.

    As people have suggested though a Delphi database application would be alot easier to use or alternativley an Access database program.

    Please correct me if i'm wrong i only discovered the stuff about excel and if statements recently

    Edit:

    Here's what looks to be a comprehensive guide on the matter of excel and if statements
    Last edited by JamesBlack; 02-28-2008 at 06:10 PM.

  4. #14
    Join Date
    Sep 2007
    Location
    Victoria, BC Canada
    Posts
    185
    Derek,

    Are you able to present the information [from the XL spreadsheets] in a grid as Zarco detailed?

    If so then providing you also have appropriate search fields [text boxes] on the form you could filter the records in the underlying recordset so that only the ones containing the fields values requested show up in the grid. Once you have the filtered values, using the existing reporting tools you could print these results off? This would seen simpler to me than the method you're trying to use based in the information you have provided.

    Again with Zarco ... [sorry no point me blathering on when someone else has said it already and much better than I could]
    http://delphi.about.com/od/database/l/aa051501a.htm

    Edit:
    With a bit of smoke and mirrors and the OnChange event of the TEdit controls you could automate the filtering to start culling out records as you type
    Last edited by TDelphiHobbyist; 02-28-2008 at 06:37 PM. Reason: Had an idea that'd be cool.

  5. #15
    Join Date
    Sep 2007
    Location
    Victoria, BC Canada
    Posts
    185
    James,
    I've used and seen a bit of VBA [Visual Basic for Applications] mostly in Access though. I started using Visual Basic many years ago because it's a much better way [IMHO] to manipulate Access - certainly provides a much better GUI. I think a Delphi front end would be far superior. Once Derek masters using it as a search engine expanding that to data entry isn't that much of a leap. I see using the XL spreadsheets as a data source as somewhat irrelevant ... it could be anything. Having said all that I know of people making a fairly tidy living with VBA.

    The IF function you've linked to is for formulas not VBA - those I've used quite a bit
    Last edited by TDelphiHobbyist; 02-28-2008 at 06:57 PM.

  6. #16
    Join Date
    Nov 2006
    Location
    UK
    Posts
    3,774
    The job can be done in delphi, as pointed out how to read from excel is covered in the about delphi link, once you read the data out of cells you can do pretty much anything you wish with it.

    If you're new to delphi it might seem a confusing journey though. We were trying to spare you that.
    Delphi !ROCKS!
    Got a question? Read this first!!!
    "You gotta help us, Doc. We've tried nothin' and we're all out of ideas"

  7. #17
    Join Date
    Dec 2007
    Posts
    25
    DaveK,

    Thank you for your specific help. The trick as you pointed out was to use the ADOQuery1 dataset and this works just fine. I used :-

    ADOQuery1.FindFirst ; // to set the pointer to the begining of the spreadsheet
    textinput:= ADOQuery1.FieldByName('Surname').AsString; // to pull in the name for further work
    and
    ADOQuery1.Next; // to step to the next record etc to EOF

    Many Thanks for the invaluable pointer in the right direction.

    Derek

  8. #18
    Join Date
    Dec 2007
    Posts
    25
    Hi DaveK,

    You grabbed my attention with your alternative method :-

    There is another way to skin this cat - a disconnected recordset. Suck the data out of the spreadsheet, disconnect, delete the rows that don't match the mutex and use the results in your report.
    Do you have any pointers as to where I could read up on this technique?

    TIA
    Derek

  9. #19
    Join Date
    Sep 2007
    Location
    Victoria, BC Canada
    Posts
    185
    Not a lot to read up on that I can find but I haven't really looked farther than google and included examples. The best example I've got off the cuff comes with Delphi:
    ??\Demos\DelphiWin32\VCLWin32\ADO\Briefcase
    [TurboPro has the demos under the program folder IIRC]

    Open the main form, double-click on the "Connected" check-box, which will open the code window to the code attached to that control. There you'll find an If statement [in the ConnectionIndClick procedure if you just want to look it up in the code window] - the "else" part of the if statement it is what you're looking for.

    BTW, the Briefcase demo uses ADO. You can see the TADODataset and TDatasource on the main form at the bottom left.

    Have a look see if it helps.

    Edit:
    With the form open, add [double-click] a TDBNavigator from the DataControls group on the ToolBox. Select the new TDBNavigator component, set it's align property to alBottom and it's Datasource property to EmpSource. Run the program, now you can do more than just edit records.

    If your getting "Can't find ..." errors;
    Open: C:\Program Files\Common Files\System\Ole DB\Data Links\DBDEMOS.udl in notepad,
    Change Borland to CodeGear
    Last edited by TDelphiHobbyist; 02-29-2008 at 02:01 PM.

  10. #20
    Join Date
    Sep 2007
    Location
    Victoria, BC Canada
    Posts
    185
    Derek, on the main page of this group there are "Sticky Threads" at the top. One of them is called Learning Delphi ... if you haven't had a chance to look at it, you should.

Page 2 of 3 FirstFirst 123 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •