P
Paul
I created a vba function to batch import text files. The text file is a csv
text file and it has number of columns to capture the voting result of an
election. Depending on the number of the candidates it can have number of
columns plus additional few columns to capture the answer of the survey
questions in the ballot paper and the data type for this survey questions is
always text either "Yes" or "No" for the answer. The format of the text file
(from left to right), the left most columns are for the voting result then
right after the voting result are the answer for the survey question(s). It
could have 1 to x questions. Each person can vote UP to MAXIMUM three
candidates. The following is an example of the text file
for example 7 candidates and 3 survey questions
1, , ,4, ,6, ,YES,No,Yes...................Valid
,2, ,4, , ,7,Yes,Yes,Yes....................Valid
, ,3,4, ,6, ,No,Yes,Yes.....................Valid
,2, , , , , ,Yes,No,No........................Valid
1,2, ,4,5, , ,No,Yes,No.....................Spoiled
I use the transfertext method to import the text file to an Access table
with the default field name created on each of the column on the text file
such as F1, F2, F3 and so on.
I need to create a function to determine if the vote is VALID or SPOILED and
to update each row of the record in the table with a new field called
"status". It could have x number of candidates so that the function need to
decide how many fields(columns) left to the very first survey answer column
which has the data type "text" and the voting result is in numeric with 1
for first candidate at column F1, 2 for second candidate at column F2 and so
on. Need to have a function to loop through the columns on each row od data
from left to right. If it is a numeric then make it equal to 1, the looping
stop when the data type is not numeric, then sum it up if the value is less
then or equal to 3 then the status is Valid else it is Spoiled. Thanks.
text file and it has number of columns to capture the voting result of an
election. Depending on the number of the candidates it can have number of
columns plus additional few columns to capture the answer of the survey
questions in the ballot paper and the data type for this survey questions is
always text either "Yes" or "No" for the answer. The format of the text file
(from left to right), the left most columns are for the voting result then
right after the voting result are the answer for the survey question(s). It
could have 1 to x questions. Each person can vote UP to MAXIMUM three
candidates. The following is an example of the text file
for example 7 candidates and 3 survey questions
1, , ,4, ,6, ,YES,No,Yes...................Valid
,2, ,4, , ,7,Yes,Yes,Yes....................Valid
, ,3,4, ,6, ,No,Yes,Yes.....................Valid
,2, , , , , ,Yes,No,No........................Valid
1,2, ,4,5, , ,No,Yes,No.....................Spoiled
I use the transfertext method to import the text file to an Access table
with the default field name created on each of the column on the text file
such as F1, F2, F3 and so on.
I need to create a function to determine if the vote is VALID or SPOILED and
to update each row of the record in the table with a new field called
"status". It could have x number of candidates so that the function need to
decide how many fields(columns) left to the very first survey answer column
which has the data type "text" and the voting result is in numeric with 1
for first candidate at column F1, 2 for second candidate at column F2 and so
on. Need to have a function to loop through the columns on each row od data
from left to right. If it is a numeric then make it equal to 1, the looping
stop when the data type is not numeric, then sum it up if the value is less
then or equal to 3 then the status is Valid else it is Spoiled. Thanks.