I have a txt file that I'd like to import, which contains answers to a test. The number of questions on the test varies year to year, but this year there are 110 (split in to two sections).

I'd like to import the file, so each answer is a field, and then score the answers against a 'correct' answer lookup table.

What's the best way of approaching this? I first thought of using vba to read the text file, split the text, and then use an SQL command to insert to to my table. This seems tedious (there are around 6500 respondents).

Anything else I could do instead?

Thanks,
Antro.

In exel, use the text-to-columns function with a fixed length delimiter. Then import the pre-split table into your db and use vba/sql to loop through each record and place the results into a properly normalized response table.

place the results into a properly normalized response table.
I'm chiming in because Craig was very subtle in his response. But I am highlighting part of his response because it is important:

"...place the results into a properly normalized response table."


What you have asked for (100 fields) is NOT a properly normalized response table.

Read up on normalization

http://support.microsoft.com/kb/283878

BEFORE continuing.

Thanks to you both. Hadn't thought of Excel, since the I wanted to keep the whole process as simple as possible for the end users, rather then use multiple office products. Hmm, got me thinking now though.

And as is usually the case, after posting the question, I thought of another way - import the text as one large string, then loop through taking the mid(answer,count,1) and comparing that with the correct answer. This elminates the original problem!

Thanks for the nudge towards normalisation too. I wasn't going to since all I do is a one -time score operation on the data, but I guess I should always normalise.

Thanks,
Antro.

...I guess I should always normalise.

Not necessarily, but in this case it will help you with your calculations on percentage correct, etc.

In down and dirty cases, normalization may be desired but time and the output needed may not require it. So, it isn't a hard and fast rule, but it is something to make sure to know whether you need it or not (and in the majority of cases you do need it).