Results 1 to 13 of 13
  1. #1
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Atlanta
    Posts
    568
    Thanks
    7
    Thanked 1 Time in 1 Post

    Need Help With Complex Import

    I am a college professor and I am looking for some help in getting a large quantity of data from a testbank program called MicroTest into Access. I have checked with the company that sells MicroTest and the question database is in a proprietary format and they offer no help in exporting it to other programs.

    The only type of export they offer is to an ASCII file. I have some ideas on how to get from ASCII to Access but the there are complications I need help with.

    There are three types of questions in the database: true/false (TF), multiple choice (MC), and essay or free form (FF). True/false presents the fewest problems so let's look at it first.

    Each question exports to two lines, similar to the following:

    TF Sc None C 1 Q 1 A F
    Production and operations management concepts and techniques are limited to manufacturing organizations.

    The "" is a code that says a new question is starting.

    The "TF" is the question type and that will go into a field as-is.

    The "Sc None" says not the scramble any of the answers. That is meaningless in a true false question.

    The "C 1" says this question came from Chapter 1. The "1" needs to go into a numeric field.

    The "Q 1" says this is question one and will be dropped.

    The "A F" says the answer is False and the F needs to go into a field.

    The question then follows. It does not have returns in it UNLESS the question is more than one paragraph long, which is possible. This presents me with my first problem. When a question has more than one paragraph, Access treats those extra returns as field dividers even through I do not want it to.

    I can work around this by loading the file into Word first and doing a search and replace and replacing those extra returns with a character combination not found in the questions, say "@@" and then doing a reverse replace in Access only I cannot figure out how to use a return in the replace field of Access. However, if there is a better approach, I would love to hear it.

    I should note that many of the questions are longer than 255 characters long so I am having to import them into a memo field.

    My second major problem with true false questions is the way that MicroTest exports multiple problems. The header it adds before the question only has the "TF" and any changed information. If the next question is for the same chapter and has the same answer, it header might be just "TF".

    If all the information was going into each header, I know how to use a query to strip out the pieces of information and put them into their proper fields once I have everything imported. However, without all the information in each header, I am stuck.

    The next type of question is multiple choice:

    MC C 1 Sc 5 A E
    F.W. Taylor believed that:
    A. the worker should have more control over his job.
    B. the worker to be efficient should always be busy.
    C. the scientific method does not apply to labor.
    D. the Hawthorne studies were a serious threat to scientific management.
    E. scientific laws governed how much work a person could do each day.

    The problems with the header are identical only this time, the "Sc 5" is meaningful. If I can get help figuring out how to handle the TF headers, this addition is not a problem.

    These questions always have returns, one at the end of the question and one at the end of each potential answer. My preference is to get the question and all possible answers into the same memo field. The only choice I see is the search and replace described above but, as above, I cannot figure out how to make it work and would love a better way.

    The last question type is essay or free form:

    FF K a D b
    Describe the differences between manufacturing and service. Your answer should be complete and detailed.
    A
    Product
    Lower customer contact
    Lower variability of inputs
    Lower labor content
    Smoother and efficient
    More uniformity
    Easier to measure productivity
    Quality assurance is easier
    Service
    Higher customer contact
    Greater variability of inputs
    Higher labor content
    Slower and awkward
    Less uniformity
    Harder to measure productivity
    Quality assurance is harder

    Here, the answer is on a separate line with a "A" line before it. The answer may be longer than 255 characters so it will have to go into a memo field and it may have multiple returns in it.

    I can make my job a little easier by using MicroTest to export the TF, MC, and FF questions into separate files for individual importing. I already have the database structure put together to receive the questions. Any help or suggestions is greatly appreciated.

    Ronny
    Ronny Richardson

  2. #2
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Newbury, Berkshire, England
    Posts
    712
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Need Help With Complex Import

    Does your export facility offer a choice of field separators?


    Can you post some sample Data?

  3. #3
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Atlanta
    Posts
    568
    Thanks
    7
    Thanked 1 Time in 1 Post

    Re: Need Help With Complex Import

    No control what so ever. Some sample data follows.

    Ronny

    TF A T
    Most customers of services want products that are made correctly, customized to their needs, delivered on time, and priced competitively.
    TF A T
    Value-added services make the external customer's life easier.
    TF A F
    Increasing the time it takes to get new products into production and managing a diverse workforce are among the current issues facing OM executives.
    TF A T
    Total quality management, while used by many firms in the 1980's, became a truly pervasive business practice in the 1990's.
    MC Sc 5 A E
    F.W. Taylor believed that:
    A. the worker should have more control over his job.
    B. the worker to be efficient should always be busy.
    C. the scientific method does not apply to labor.
    D. the Hawthorne studies were a serious threat to scientific management.
    E. scientific laws governed how much work a person could do each day.
    MC Sc 4 A A
    In the 1950's and early 1960's, production management scholars:
    A. noticed the commonality of problems faced by all productive systems and emphasized the importance of viewing the production function as a system.
    B. made significant breakthroughs in robotics.
    C. introduced the field of OR to British management.
    D. clearly specified man's role in the productive system.
    E. none of the above answers are correct.
    MC Sc 5 A D
    The operations management transformation process in a hospital is primarily which of the following?
    A. physical
    B. location
    C. exchange
    D. physiological
    E. storage
    MC Sc 5 A E
    This individual developed the use of standardization in large scale mass production using a moving assembly line.
    A. Frederick Winslow Taylor
    B. Frank Gilbreth
    C. Adam Smith
    D. Charles Babbage
    E. Henry Ford
    MC Sc 5 A B
    This individual documented the economic benefits from work simplification in production through time and motion studies.
    A. Frederick Winslow Taylor
    B. Frank Gilbreth
    C. Adam Smith
    D. Charles Babbage
    E. Elton Mayo
    MC Sc 5 A A
    This individual was greatly responsible for the scientific management movement since the turn of the century.
    A. Frederick Winslow Taylor
    B. Frank Gilbreth
    C. Adam Smith
    D. Charles Babbage
    E. Elton Mayo
    MC Sc 5 A A
    This individual developed "bar-chart" techniques for activity scheduling.
    A. Henry Gantt
    B. Elton Mayo
    C. Adam Smith
    D. Walter Shewhart
    E. Frank Gilbreth
    Ronny Richardson

  4. #4
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Newbury, Berkshire, England
    Posts
    712
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Need Help With Complex Import

    Could you attach sample data in ASCII files in the exports you suggested, TF, MC, and FF?

  5. #5
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Newbury, Berkshire, England
    Posts
    712
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Need Help With Complex Import

    BTW, I suspect that the Data will be in some sort of regular data storage format like *.db or something. Have you tried opening it with the various import options offered by Excel?

  6. #6
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Atlanta
    Posts
    568
    Thanks
    7
    Thanked 1 Time in 1 Post

    Re: Need Help With Complex Import

    It is in their own special format spread across two different files. I tried opening it in Word and Excel and got nothing useful.
    Ronny Richardson

  7. #7
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Newbury, Berkshire, England
    Posts
    712
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Need Help With Complex Import

    Oh I see.

    Do you have any sample data in ASCII files in the exports, TF, MC, and FF?

  8. #8
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Atlanta
    Posts
    568
    Thanks
    7
    Thanked 1 Time in 1 Post

    Re: Need Help With Complex Import

    Below are a couple of each:

    TF A T
    Value-added services make the external customer's life easier.
    TF A F
    Increasing the time it takes to get new products into production and managing a diverse workforce are among the current issues facing OM executives.
    TF A T
    Total quality management, while used by many firms in the 1980's, became a truly pervasive business practice in the 1990's.
    MC Sc 5 A E
    F.W. Taylor believed that:
    A. the worker should have more control over his job.
    B. the worker to be efficient should always be busy.
    C. the scientific method does not apply to labor.
    D. the Hawthorne studies were a serious threat to scientific management.
    E. scientific laws governed how much work a person could do each day.
    MC Sc 4 A A
    In the 1950's and early 1960's, production management scholars:
    A. noticed the commonality of problems faced by all productive systems and emphasized the importance of viewing the production function as a system.
    B. made significant breakthroughs in robotics.
    C. introduced the field of OR to British management.
    D. clearly specified man's role in the productive system.
    E. none of the above answers are correct.
    FF K a D b
    Describe the differences between manufacturing and service. Your answer should be complete and detailed.
    A
    Product
    -------
    Lower customer contact
    Lower variability of inputs
    Lower labor content
    Smoother and efficient
    More uniformity
    Easier to measure productivity
    Quality assurance is easier

    Service
    -------
    Higher customer contact
    Greater variability of inputs
    Higher labor content
    Slower and awkwardLess uniformity
    Harder to measure productivity
    Quality assurance is harder
    Ronny Richardson

  9. #9
    Star Lounger
    Join Date
    Apr 2002
    Posts
    58
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Need Help With Complex Import

    In instances like this I usually write a separate Visual Basic routine/application which cycles through the initial ASCII file and builds an importable file. This would allow you to accommodate for the variances within the data. If you're familiar with Visual Basic and interested I can try to locate an old routine I've written to give you some ideas.

    Since some of your free-form questions/answers may make an individual record rather lengthy you should also research limitations on length of individual ASCII records Access will accept.

  10. #10
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Atlanta
    Posts
    568
    Thanks
    7
    Thanked 1 Time in 1 Post

    Re: Need Help With Complex Import

    In some experiments, I have not had any trouble importing longer ASCII records. My main problem was that Access limits text fields to 255 characters and many of the problems are longer than that. I solved that by using memo fields rather than text fields.

    Writing a VB program to process the ASCII files into a format acceptable to Access would be the best approach but I have never learned any VB. I have a few Word macros but I mostly record them and then make slight modifications to what Word records.

    I know some preprocessing is going to be required but I was hoping for a solution that did not require programming, perhaps a search and replace in Word.

    There are way, way too many questions to process by hand so it's clear that the process has to somehow be automated.

    Ronny
    Ronny Richardson

  11. #11
    Star Lounger
    Join Date
    Apr 2002
    Posts
    58
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Need Help With Complex Import

    If your deadline is not super tight email me a sample ASCII file of the data. This weekend I'll try to find one of my old VB routines and alter it to fit your purposes. It may not end up being the prettiest program, but it should get the job done.

  12. #12
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Atlanta
    Posts
    568
    Thanks
    7
    Thanked 1 Time in 1 Post

    Re: Need Help With Complex Import

    This is a great offer! My deadline is not tight and I have sent a sample via email.

    Thanks
    Ronny
    Ronny Richardson

  13. #13
    Star Lounger
    Join Date
    Apr 2002
    Posts
    58
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Some Questions/Comments

    Ronnie,

    I have the data sample and started the program. A few questions/comments:

    1) When changing type of question (i.e. from the TF to MC), is the chapter and/or other information left out if it is the same as the previous type question?

    2) I'm planning on collecting the data in the following fields and in the order listed. The finished file will be a delimited standard ASCII file. Please let me know NOW if you need this done differently. It isn't much of an issue to alter at this point, but could be down the line. NOTE: I'm NOT collecting the question # information as you indicated it was unneeded:

    Dim strTypeQuestion As String 'Style question (TF, MC or FF)
    Dim strScramble As String 'Scramble info
    Dim intChapter As Integer 'Chapter question is from
    Dim strAnswer 'Answer for question
    'Answers could be one character or
    'multiple paragraphs
    Dim strQuestion 'Actual question
    'Could be one multiple paragraphs

    3) I plan to use @@ as an indicator of where a paragraph return belongs in the questions/answers field. You can then run an update query in Access to change these the @@'s to paragraph returns. (I have a sample update query - remind me to send it.)

    Barring unforeseens, should have you a test program sometime Monday.

Posting Permissions

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