I have a client whose site deals with fresh fruit & vegetables. They want to highlight those fruit & veg that are in season in each month.

I have already got a table of fruit & veg, and one with months. I now need to create a table linking veg to months.

Veg vegID (int identity) vegName

Month monthID monthName

Seems simple, no? A link table with two foreign keys, vegID and monthID

However, the real problems is this: first the client supplied the monthly availability in a Word doc (eg, January - asparagus, butternut, baby corn, February - apricots, granadillas, nectarines .. etc etc)

I requested the data in Excel.. and now I have been given the following format

January |February |March |April

Veg |Fruit |Veg |Fruit |Veg |Fruit |Veg |Fruit

asparagus |apricots |asparagus |apples |green beans |apples |green beans |apples butternut |granadillas |green beans |figs |beetroot |bananas |beetroot |bananas baby corn |nectarines |beetroot |nectarines |cabbage |avocados |broccoli |avocados

Does anyone have a suggestion as to how to transform this data (using Excel or SQL (or anything) into a usable format so that I can import into SQL and update the link table?

Preferably not by retyping the whole list!

Would they be adverse to using sheets for each month? That would make it a good bit easier to handle if you know that each sheet?http://support.microsoft.com/kb/321686

Granted most of what you'll find for this is vb-esque.

Also, if you import directly into the database using the import wizard in SQL server you can generally define the mapping. The linking table will be a bit harder, though. For that you would actually need to edit the import job and put your insert statements.

Otherwise it would require that the sheet is always the same, ie:

a b c d e f g h
January February March April
Fruit Veg Fruit Veg Fruit Veg Fruit Veg

Then you would have to look to row(0) to get month indexes, ie (pseudo code) 'before statement to loop

Dim januaryIdx as Integer, januaryFruitIdx as Integer, januaryVegIdx as Integer Dim februrayIdx as Integer, februaryFruitIdx as Integer, februaryVegIdx as Integer ....... if not rs.eof and not rs.bof if rs(0) == "January" then januaryIdx = 0 end if .....

end if rs.moveNext if rs(januaryIdx) == "Fruit" januaryFruitIdx = januaryIdx else if rs(januaryIdx) == "Veg" januaryVegIdx = januaryIdx end if

if rs(januaryIdx+1) == "Fruit" januaryFruitIdx = januaryIdx + 1 else if rs(januaryIdx) == "Veg" januaryVegIdx = januaryIdx + 1 end if 'continue going through and populate idx for each month rs.moveNext while not rs.eof 'generate SQL for january, feb, etc for this line rs.moveNext wend

as you can see it starts to get rather brute force (damn you vb and ado!). but if you have each one on a seperate sheet it gets easier.

Thanks.

In the end I imported the whole spread sheet, then wrote some SQL that selected column 1 into a temp table, then looped through the temp table, getting all veg and comparing to the veg table, inserting the vegID and the month ID into the link table.

Repeat x 24 and it is done.

In future I am going to send clients a basic questionnaire before they send in any content:

a) Have you ever used Excel, before you decided to use to supply content? a.i ) More than once?

b) What is your basic education level?

hahaha. i can't begin to think how many times i've had to write and re-write imports from a spread sheet even after i have: a) given them a template to work with only to receive the next file that is not in that format only to b) send the new excel back to client with template to have them fill it back out properly only to receive an entirely different excel spreadsheet in response. so i c) gave up and re-programmed the import only to bill them for it.