PHP to Excel to Access - Merged cell problem

[Mousewrites]

Hello,

I'm trying to make an acess database from the search results from the BPAL website (perfume). Each listing has the perfume group, name, discription, scent family (floral, woody, spicy, ect), and a link to buy two sizes of bottels OR lists it as discontuned.

I copied the results of a search on the site into excel. Here's a portion of the excel spredsheet:

Book2
	A	B	C	D	E	F
1	AREA	NAME	DESCRIPTION	SCENTFAMILIES
2	ArsDraconis	Dragon'sBlood	DESCRIPTION	Fruity	buy5ml	buy10ml
3				Green/Leafy/Herbal
4	ArsDraconis	Dragon'sBlood	DESCRIPTION	Resin/Incense/Smoky	buy5ml	buy10ml
5	ArsDraconis	Dragon'sEye	DESCRIPTION	Floral	buy5ml	buy10ml
6				Resin/Incense/Smoky
7	ArsDraconis	Dragon'sHeart	DESCRIPTION	Floral	buy5ml	buy10ml
8				Fruity
9				Resin/Incense/Smoky
10	ArsDraconis	Dragon'sMilk	DESCRIPTION	Fruity	buy5ml	buy10ml
11				Oriental/Gourmand/Spicy
12				Resin/Incense/Smoky
13	ArsDraconis	Dragon'sTears	DESCRIPTION	Green/Leafy/Herbal	buy5ml	buy10ml
14				Ozone/Marine/Fresh
15				Resin/Incense/Smoky
16	AstrologicalOils	Aquarius	DESCRIPTION		Discontinued
17	AstrologicalOils	Aries	March21-April19	Oriental/Gourmand/Spicy	Discontinued
18			DESCRIPTION
19	AstrologicalOils	Cancer	June21-July22	Fruity	Discontinued
20			DESCRIPTION
Sheet1

Then I imported the spreadsheet into Access, letting it make the fields.

See my problem? Where there was more than one 'scent family,' access only took the first one. The others became their own entry, blank except for the scent family field.

So for Dragon's Blood, (ID 1) it lists the scent family as Fruity. Then there is a blank record (ID 2) that has it's scent family listed as Green / Leafy / Herbal.

I added check boxes (yes/no) for each of the possible scent families to the table, but it would take a very long time to go through by hand and switch them over.

Does anybody have any ideas how I can either write a query to do this, or a different way to import the information?

Link to the Access file: http://home.earthlink.net/~thehivemind/BPAL.mdb[/code]

 

[SydneyGeek]

Looks like you have a one-to-many problem here, which Excel doesn't handle very well. You'll need to do the following:
1. Build a unique list of Area + Name (best to use Advanced Filter for this)
2. Give each item in this list an ID number
3. Using lookups, build separate tables containing ID, Description; ID, Scent Families; ID, Stock Sizes
Import these tables into Access. Let Access add a separate primary key for the first table -- that way, any others that you add manually will automatically get ID's. Once imported, relate the tables via the ID value and build queries to check that the assembled version makes sense.

If you need help on this, post back

Denis

 

[Mousewrites]

Hmmm, that makes sense, I think.

I'm not quite sure what you mean by using advanced filter to get the area and name fields out. Couldn't I just select them? (or am I missing something really fundamentally important here? It's been known to happen...)

So I break those off into their own excel spreadsheet, and then give them IDs? In excel, or should I let access give them IDs?

My question there is then do I have to hand assign the IDs to the description, scent family, and stock sizes?

Sorry to be less than competent; I'm still learning access, and databases in general.

Thanks for your help!

 

[SydneyGeek]

I guess there are a couple of ways to proceed. One will do the job properly, but take quite a bit of description. Then there's the quick and dirty method, which I will describe now. In essence, this involves filling all the blanks in the Excel table before importing, and then using Access' Table Analyzer to split the data up for you. The Analyzer gives you less control but gets you going quickly.
First, fill the blanks in the table.
This is tedious without code, so here's a macro.

Sub FillTheBlanks()

    Range("A1").CurrentRegion.Select
    Selection.SpecialCells(xlCellTypeBlanks).Select
    Selection.FormulaR1C1 = "=R[-1]C"
    Range("A1").CurrentRegion.Select
    Selection = Selection.Value
End Sub

In your Excel file, press Alt + F11 to enter the macro editing environment. Insert | Module, then copy and paste the code above into this module.
Go back to Excel, press F8 to bring up the dialog, make sure FillTheBlanks is selected, and press ENTER. Your table should no longer have any blank cells; the blanks will be filled with the "parent" value above then in the column.
Now, import into Access as before
Do this in a blank database so you can start from scratch. Name the imported table Table1, but DON'T create a primary key
Now run the Table Analyzer
Tools | Analyze | Table will launch a wizard. Follow the wizard, renaming new tables and fields as prompted. Once it finishes running you'll have a database set up with relationships. You should be able to use this for searching etc.

If you don't like the result of the wizard, post back and I can take you through creating your own database from the imported table.

Denis