Deconstruction/Reconstruction Of A CSV File

[TheDeal56]

I have an Excel Sheet (Imported CSV) that has columns similar to this:

Company Name..........Employee..........Employee 2..........Employee 3
Jones Electric............Kyle Brown........Chris Ford............Matt Daniels

I need it to look like this:
Company Name..........Employee
Jones Electric............Kyle Brown
Jones Electric............Chris Ford
Jones Electric............Matt Daniels

The amount of names that a company has associated with it could vary anywhere from 0 to 30. I'm thinking that I need to import my sheet into access, then have access read each column and make it into its own table. Once that is done, I need access to read from each of the Employee tables and match their unique ID up with the corresponding ID in the Company Name table. After that process is finished, I would need access to make a new table that looks like the one I listed above. I would like for this entire process to be as automated as can be. Is this possible? Please let me know where I should start my research on solving this problem. Thanks.

 

[GolferGuy]

1. Import into Access
2. Create query to read columns 1 and 2 (CompanyName, Employee) and append this into to your table.
3. Create query to read columns 1 and 3 and append this into your table. Then do columns 1 and 4, then 1 and 5, etc. Be sure to not append a record if the Employee X field has no data in it.

 

[gemma-the-husky]

load the table as is.

the easy way (less code, and its only 1 time)

you will then need a query for each column of names

for column 1 make a query summarising group by name, totals query. then append these names to a names table, with an autonumber index, and a unique index on the name

repeat this for columns 2,3,4 etc

you will find you add fewer and fewer names for each column ,as some will already be there


now you have a lookup index for each name.
you should also separate the company names (no problem), but they should have a numeric reference, rather than using the text (more efficient, and easier to change the names if you need to)

so design a newtable normalised as you required with companyref, and employeeref as numbers

now for each column of your table, you can do an append query, matching the epmloyee names, but loading the lookup VALUE into the final table


you end up with 3 tables

company (companyref, companyname, address, contact, phone etc)
employee(employeeref, employeename, dob, address, etc)
association(companyref, employeeref)

not ever so quick, but takes only a few minutes and you only only do it once


-------
i hope this all makes sense - once you get used to access, you will see whats happening


---------
there you go, golfer beat me to it

think about these numeric lookups though - its better in the long run to NOT store these long text fields as values in the join table

 

[TheDeal56]

Thanks, I'll give it a shot.

 

[TheDeal56]

What if I wanted to do the oppisite?

Starting DB:
Company Name..........Employee
Jones Electric............Kyle Brown
Jones Electric............Chris Ford
Jones Electric............Matt Daniels

I want it to end up like:
Company Name..........Employee..........Employee 2..........Employee 3
Jones Electric............Kyle Brown........Chris Ford............Matt Daniels

Thanks a lot, everyone.

 

[TheDeal56]

I started a new thread on this. Here is the link:

Link