Extracting data from a spreadsheet

[rickgalty]

Hi everyone.... This is more of an Excell question, really, but I hope someone can help.

I am ready to populate the database for an application I have written. My HR dept have sent me the data they currently have in the form of an Excell spreadsheet. Each row of the spreadsheet has an employee number as the first column, then a list of dates - each date is in a column headed by the name of a class or training session. For my database though, the table I have set up is just three columns - employee number, class number, and date. Short of manually doing it, is there a quick and easy way anyone can think of to convert this Excell spreadshhet to the format I need?

Richard

 

[jebenson]

In Excel, create a macro to iterate through each column in each row that has data. Have this macro connect to the database via ADO. Then as you iterate through each column of each row, write the appropriate data to the database (i.e., employee number, class type and date).



I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

 

[Sheco]

The quickest might be to use MS Access to import the spreadsheet into a new table.

Then you can connect Access to your application database and run an INSERT query.

 

[SkipVought]

Hi,

Check out this FAQ. Your data need to be normalized. The PivotTable Wizard can help in Excel.

NORMALIZE Your Table using the PivotTable Wizard faq68-5287

Skip,

_{[red]Be Advised![/red] The Vinyards of Texas have produced a wine with diuretic dimishment and urethric relief...
Pinot More!}