tshrbhag

Hey guys,

I'm new to this forum and hope it helps me to solve my problem. I'm trying to import very special data to a database and have no idea how it could be done.

Here's what my table looks like

qno number
Question Text(255)
Option1 Text(255)
Option2 Text(255)
Option3 Text(255)
Option4 Text(255)
Option5 Text(255)
Option6 Text(255)
Option7 Text(255)
Option8 Text(255)
Answer Text(10)
Type Number {Comment: Type = 1 the question is single choice, type = 2 the question is multi choice}

I want to import questions and answers from a text or word file which may look like the following.

Q1. What is a computer?

A. Machine
B. Metal
C. Gas
D. Animal

Answer: A

Q2. What can a computer do?

A. Calculations
B. Prepare food
C. Wash your clothes
D. Print documents

Answer: A, D

and so on....

How can I write a code in VB to import every question in one record along with taking the correct answers for them?

I tried searching in google, but in vain. Would appreciate a link to some helpful resource or a sample code on how it could be done.

Thanks and hope someone helps me out here.

Terry

MKoslof

OK, so your table is already created correct? Now you just want to get data into it from a text file? If so, what is the structure of your text file, is it comma delimited, is it one piece of data per line, etc? Basically how is the data laid out on this text file and how does this relate to your current table structure. We need to know what the layout is in order to create the apppropriate routine to bring the data into the table.

tshrbhag

thanks for looking into this one. My text file will look exactly as the question answer structure given in my first post. I'm including it again.......

---------------Begining-----------------------

Q1. What is a computer?

A. Machine
B. Metal
C. Gas
D. Animal

Answer: A

Q2. What can a computer do?

A. Calculations
B. Prepare food
C. Wash your clothes
D. Print documents

Answer: A, D



---------------End---------------------------

When the code reads Q1. then create a record with 1 as the value in qno. Put the actual question in the field question. Put the available options in the fields option1, option2, option3 & option4. If more options are available use the fields upto option8.

Then read the answer. If it is more than two choices, then put the value 2 in the field type, else put the value 1 in field type. Then write the correct answer in the field answer. If the answer is A, the write 1 in answer. If it is D, then write 4 in the field answer. If the answer is A, C, then write the value 13 in the field answer. Similar if the answer is C, D then write 34.

Hope this makes it clear.

MKoslof

So what is the structure of you table? If you have already created it, what fields have you created.

So is it like this

qno: Primary Key, Int
question: string
option1: string
option2: string
option3: string, etc

So you have a table with 10 fields..the qno field, the question field, and 8 option fields? Will this be your structure? One table with 10 fields. You say there is a type field and a answer field as well? Basically, before doing anything you need to think about how you want to design this.

You might want two tables..one with only structural information such as: QuestionID, Question, and Options. This table has a unique QuestionID for each question and lists all potential options. Then you have an Answers table that links back to your QuestionID field..so you track what answers have been given for each unique question.

tshrbhag

Thanks for your suggestions MKoslof, but the system which will be using the mdb is already ready and I don't have time to make those table modifications at this time. But i'm coming a bit closer to this. I had to modify the text file to look like below. All data for one question on one single line. Next question on next line.


---------Begining------------
Q1. What is a computer? A. Machine B. Metal C. Gas D. Animal Answer: A
Q2. What can a computer do? A. Calculations B. Prepare food C. Wash your clothes D. Print documents Answer: A, D
----------End---------------

The code I used is below:

--------------------Code Start-----------------

Dim InputFile As String 'File name.
Dim FILE1 As Integer 'File pointer.

InputFile = "C:\test.txt"
FILE1 = FreeFile
Open InputFile For Input As #FILE1



Do While Not EOF(FILE1)
Line Input #FILE1, LineBuffer
Pos1 = InStr(LineBuffer, "A. ")
question = Mid$(LineBuffer, 1, Pos1 - 1) ' Assuming that there isn't a delimiter at the beginning of the record.
Pos2 = InStr((Pos1 + 1), LineBuffer, "B. ") 'find example of delimiter after instance noted in Pos1
option1 = Mid$(LineBuffer, (Pos1 + 1), (Pos2 - Pos1 - 1)) 'Take the area in between the delimiters.
Pos1 = InStr((Pos2 + 1), LineBuffer, "C. ") ' Get next delimiter position.
option2 = Mid$(LineBuffer, (Pos2 + 1), (Pos1 - Pos2 - 1))
Pos2 = InStr((Pos1 + 1), LineBuffer, "D. ") 'find example of delimiter after instance noted in Pos1
option3 = Mid$(LineBuffer, (Pos1 + 1), (Pos2 - Pos1 - 1)) 'Take the area in between the delimiters.
Pos1 = InStr((Pos2 + 1), LineBuffer, "Answer: ") ' Get next delimiter position.
option4 = Mid$(LineBuffer, (Pos2 + 1), (Pos1 - Pos2 - 1))

Me.Print question
Me.Print option1
Me.Print option2
Me.Print option3
Me.Print option4
Loop
---------------------Code End--------------------

Now all I want to know is how can I do this job without changing the original layout of my text file? Its gonna cost me a lot of time if I kept modifying the text files as there are hundreds of them.

Thanks in advance for your next reply.

Terry

MKoslof

OK, well if your table structure is set...thats fine. Basically you would have to read in each record and insert it into the database table. So say you will always have 10 lines per record (ex: the first ten lines of the text file are one record, the next are the second, etc..down until EOF). Basically what you would do is read the text file ten lines at a time and then split this at the new line (into an array). Now you have an array that represents your first record. Now, loop until UBound() of the array and add each member into the corresponding field in the table. Now, you could read in the entire text file at once..but then you would need to figure out the logic that splits at each unique records (every 10 lines, etc.)

tshrbhag

That seems to be a better idea. Infact, a simple logic for splitting the file into questions is locating Q1. then Q2. and so on. That way I know that i'm reading the complete question, whatever number of lines it may have.

BTW; could you tell me how I could read say 'n' number of lines from a text file into a string at one go? Then read the next 'n' lines into the same string replacing the first contents?

I'm not so good on writing codes as compared to writing logics in VB.

Thanks for your help again in advance.

Dennis DVR

I don't think you can read n lines from a textfile in one go using the open file method if you want to read a n records from a text file you should open it as a recordset not as a text file.

here's the link on how to open the text file using the ADODB.recordset

http://www.xtremevbtalk.com/showthre...ighlight=Excel

http://www.xtremevbtalk.com/showthre...ighlight=Excel

and if you want to open it as a text here's the link
http://www.xtremevbtalk.com/showthread.php?t=171908

MKoslof

You might want to use the text provider and ADO..basically do as Duane says and read in the text file as a recordset. However, this will be slower than using native file I/O methods. Basically, you would have to read in the file and set where to stop via the LOF() method. Such as read in to array1 LOF/<a specific variable>. It is do-able..but you are going to have to code it. And your settings would need to exact each time..meaning it is always 10 lines per record, etc.

Or you can hack your way around it, using a counter..and whenever the counter reaches a new multiple of 10, start a new array or string variable to hold the next record, etc.