Database Design/Data Import Question

[bluize]

I'm a newbie, but have been working my way through Alpha 5 Made Easy and Database Design for Mere Mortals by Hernandez. I had posted a question before regarding a Grants Training Database project that I inherited that is in Access 2007 (non-relational). I am starting to figure out what needs to go where for building the Tables/Sets. Since the data that I receive comes in the form of an Excel Spreadsheet each month that shows what training the students had that month (training hrs existing courses, new courses, new students, terminations, etc.) There really isn't a need for data entry screens since all the data for each grant comes on its own spreadsheet. What I need to know is when I receive a new updated spreadsheet (some data doesn't change, while other info might on that student), do I have to break the spreadsheet into different components that corresponds to the various Tables/Sets that I have defined in Alpha? Or can I just import the whole thing and Alpha 9 figures out where it is supposed to go to update all the indexes and records?

Thanks in advance for any answers,

Ben

[Stan Mathews]

Re: Database Design/Data Import Question

Alpha doesn't know how to import anything from any source until you design the import. Since you can only import into one table at a time and you have no control over which rows/lines/etc are imported you will likely find it easiest to do the data separation in excel and import the resulting separate spreadsheets.

[bluize]

Re: Database Design/Data Import Question

Thanks Stan ... Lots of extra work, but if it has to be done, then it will be done.

Thanks,

Ben

[Stan Mathews]

Re: Database Design/Data Import Question

You can automate the separation and preparation process to some degree with Alpha's ole methods. If you have a multitab spreadsheet you can open it under Alpha's control and save each tab as a separate workbook. You can rename columns to fit the fieldnames in your tables. Should save you a lot of time.

[bluize]

Re: Database Design/Data Import Question

Great, I didn't think about that ...

Thanks Stan,

Ben

[Stan Mathews]

Re: Database Design/Data Import Question

To get you started.

FUNCTION ex_save_individual AS C (excelname AS C ) 
Dim xlApp as p 
xlApp = ole.create("Excel.Application") 
xlApp.Workbooks.Open(excelname) 
'xlApp.Visible = .T. 'comment this line out if you don't want to see Excel 
count = xlApp.Worksheets.Count
xlapp.DisplayAlerts = .F.
for x = 1 to count
	xlApp.Sheets(x).Select()
	barename = xlApp.ActiveSheet.Name
	fullname = "C:\TEMP"+CHR(92)+barename
	xlApp.ActiveWorkbook.SaveAs(fullname,33) '33 is a constant for Excel 4.0, dbf4 is 11
next x
xlApp.Workbooks.Close()
xlapp.DisplayAlerts = .T.
xlApp.Quit() 
END FUNCTION

You might also look at designing your import with the ADO Import Genie. I believe it supports importing from a specific tab of a workbook.

[bluize]

Re: Database Design/Data Import Question

Thank you so very much Stan. Great help.

Ben