Plan for mass import of Excel files

[pietlinden]

I have a large number of Excel files that contain Survey questions and
responses (I know the design is awful... I inherited this mess). Basic
structure is like this:

Demographic Questions | Survey Questions

the first row contains field names. So querying is not a huge problem.
I found this (finally!)

SELECT *
FROM [Excel 8.0;HDR=YES;IMEX=1;Database=C:\ClientInfo.xls;].[Sheet1$];

Then I can just loop through the files in a folder and process them all
in sequence.

Now for the fun part. I need to probably alias column names. Is
creating a table of (AccessField, XLColumnName) pairs the way to go?
then I could write the SQL on the fly and then just use
DBEngine(0)(0).Execute or use an ADO command...

Any thoughts?

I'm just thinking that doing a zillion of these manually is going to
get really old...

Thanks!
Pieter

[Tom van Stiphout]

On 22 Jan 2007 22:20:06 -0800, pi********@hotmail.com wrote:

Yes, an Alias table (ExcelFieldName, AccessQuestionID) is a good idea.
It can even accommodate the various spellings of a particular
question.
You did notice I did not say AccessFieldName, because I want to
normalize the design. Rather than a table with N AnswerX fields, I
want a table with QuestionID, Answer, and perhaps a few more fields
(a/o RespondentID).

-Tom.

>I have a large number of Excel files that contain Survey questions and
responses (I know the design is awful... I inherited this mess). Basic
structure is like this:

Demographic Questions | Survey Questions

the first row contains field names. So querying is not a huge problem.
I found this (finally!)

SELECT *
FROM [Excel 8.0;HDR=YES;IMEX=1;Database=C:\ClientInfo.xls;].[Sheet1$];

Then I can just loop through the files in a folder and process them all
in sequence.

Now for the fun part. I need to probably alias column names. Is
creating a table of (AccessField, XLColumnName) pairs the way to go?
then I could write the SQL on the fly and then just use
DBEngine(0)(0).Execute or use an ADO command...

Any thoughts?

I'm just thinking that doing a zillion of these manually is going to
get really old...

Thanks!
Pieter

[pietlinden]

On Jan 23, 7:57 am, Tom van Stiphout <[e-mail]:

On 22 Jan 2007 22:20:06 -0800, [e-mail] wrote:

Yes, an Alias table (ExcelFieldName, AccessQuestionID) is a good idea.
It can even accommodate the various spellings of a particular
question.
You did notice I did not say AccessFieldName, because I want to
normalize the design. Rather than a table with N AnswerX fields, I
want a table with QuestionID, Answer, and perhaps a few more fields
(a/o RespondentID).

-Tom.

Duly noted. Thanks Tom! Now I just have to wait for the DB to arrive.
The company just purchased the Access version of Research Tracker.
Once the database gets here, I can hopefully figure out the mappings.

I have to sort out weird mappings. Several columns in Excel, with only
one filled in (usually with an X) will have to collapse to one field in
my table. Easy enough to write an IIF clause to fix it, but are there
good ways of identifying fields like that? Use EXISTS and two SQLs?

Yeah... test it out... I'm on it. (Sorry, sort of thinking out loud.)

Pieter

[Tom van Stiphout]

On 23 Jan 2007 16:08:48 -0800, pi********@hotmail.com wrote:

Collapsing several Yes/No fields into a single field, I would resort
to binary: first re-value those columns from Null and 'X' to 0 and 1.
Then add them up:
(say we have 4 fields, YN1 ... YN4)
select YN1*8 + YN2*4+YN3*2+YN4*1 as CombinedValue from SomeTable
This would give you a unique value between 0 and 2^4-1, and you have
lost no information.

-Tom.

>

On Jan 23, 7:57 am, Tom van Stiphout <[e-mail]:

>On 22 Jan 2007 22:20:06 -0800, [e-mail] wrote:

Yes, an Alias table (ExcelFieldName, AccessQuestionID) is a good idea.
It can even accommodate the various spellings of a particular
question.
You did notice I did not say AccessFieldName, because I want to
normalize the design. Rather than a table with N AnswerX fields, I
want a table with QuestionID, Answer, and perhaps a few more fields
(a/o RespondentID).

-Tom.

Duly noted. Thanks Tom! Now I just have to wait for the DB to arrive.
The company just purchased the Access version of Research Tracker.
Once the database gets here, I can hopefully figure out the mappings.

I have to sort out weird mappings. Several columns in Excel, with only
one filled in (usually with an X) will have to collapse to one field in
my table. Easy enough to write an IIF clause to fix it, but are there
good ways of identifying fields like that? Use EXISTS and two SQLs?

Yeah... test it out... I'm on it. (Sorry, sort of thinking out loud.)

Pieter

[pietlinden]

On Jan 23, 9:35 pm, Tom van Stiphout <[e-mail]:

On 23 Jan 2007 16:08:48 -0800, [e-mail] wrote:

Collapsing several Yes/No fields into a single field, I would resort
to binary: first re-value those columns from Null and 'X' to 0 and 1.
Then add them up:
(say we have 4 fields, YN1 ... YN4)
select YN1*8 + YN2*4+YN3*2+YN4*1 as CombinedValue from SomeTable
This would give you a unique value between 0 and 2^4-1, and you have
lost no information.

-Tom.

The guy is going to re-enter all the data manually. I think he's
completely out of his mind. Sure, they're spreadsheets, but if you
know what you're doing, you can clean the mess up with VB... I guess he
doesn't really want to...

>

On Jan 23, 7:57 am, Tom van Stiphout <[e-mail]:

On 22 Jan 2007 22:20:06 -0800, [e-mail] wrote:

Yes, an Alias table (ExcelFieldName, AccessQuestionID) is a good idea.
It can even accommodate the various spellings of a particular
question.
You did notice I did not say AccessFieldName, because I want to
normalize the design. Rather than a table with N AnswerX fields, I
want a table with QuestionID, Answer, and perhaps a few more fields
(a/o RespondentID).

-Tom.

Duly noted. Thanks Tom! Now I just have to wait for the DB to arrive.
The company just purchased the Access version of Research Tracker.
Once the database gets here, I can hopefully figure out the mappings.

I have to sort out weird mappings. Several columns in Excel, with only
one filled in (usually with an X) will have to collapse to one field in
my table. Easy enough to write an IIF clause to fix it, but are there
good ways of identifying fields like that? Use EXISTS and two SQLs?

Yeah... test it out... I'm on it. (Sorry, sort of thinking out loud.)

Pieter

[Tom van Stiphout]

On 23 Jan 2007 19:42:03 -0800, pi********@hotmail.com wrote:

Sigh.
In Dutch we say: throwing pearls before the swines :-)

-Tom.

>

On Jan 23, 9:35 pm, Tom van Stiphout <[e-mail]:

>On 23 Jan 2007 16:08:48 -0800, [e-mail] wrote:

Collapsing several Yes/No fields into a single field, I would resort
to binary: first re-value those columns from Null and 'X' to 0 and 1.
Then add them up:
(say we have 4 fields, YN1 ... YN4)
select YN1*8 + YN2*4+YN3*2+YN4*1 as CombinedValue from SomeTable
This would give you a unique value between 0 and 2^4-1, and you have
lost no information.

-Tom.

The guy is going to re-enter all the data manually. I think he's
completely out of his mind. Sure, they're spreadsheets, but if you
know what you're doing, you can clean the mess up with VB... I guess he
doesn't really want to...

>>

>On Jan 23, 7:57 am, Tom van Stiphout <[e-mail]:
On 22 Jan 2007 22:20:06 -0800, [e-mail] wrote:

>Yes, an Alias table (ExcelFieldName, AccessQuestionID) is a good idea.
It can even accommodate the various spellings of a particular
question.
You did notice I did not say AccessFieldName, because I want to
normalize the design. Rather than a table with N AnswerX fields, I
want a table with QuestionID, Answer, and perhaps a few more fields
(a/o RespondentID).

>-Tom.

>Duly noted. Thanks Tom! Now I just have to wait for the DB to arrive.
The company just purchased the Access version of Research Tracker.
Once the database gets here, I can hopefully figure out the mappings.

>I have to sort out weird mappings. Several columns in Excel, with only
one filled in (usually with an X) will have to collapse to one field in
my table. Easy enough to write an IIF clause to fix it, but are there
good ways of identifying fields like that? Use EXISTS and two SQLs?

>Yeah... test it out... I'm on it. (Sorry, sort of thinking out loud.)

>Pieter

[pietlinden]

On Jan 23, 10:01 pm, Tom van Stiphout <[e-mail]:

On 23 Jan 2007 19:42:03 -0800, [e-mail] wrote:

Sigh.
In Dutch we say: throwing pearls before the swines :-)

-Tom.

Hey, I offered him what I'd written so far. Why he would choose to
manually reenter all the data is completely beyond me. Yes, the
cleanup would be ugly, but easier than reentering it all... but if he
feels that's a good use of his money, then so be it.