Importing complex data from excel

[gunterbrink]

Good evening all. My question is, and I've been scouring the internet to find a solution but have been unsuccessful. Basically here is what I am needing:



I have an excel file that my employees fill out. We track all of the employees on a job, their hours out on the job, what equipment is on the job. Different crews perform different jobs. What I'm trying to do is import the excel file and have the data split between different tables ex: Employee table, equipment table, damaged equipment table, cost analysis table.

Is this possible, or am I going about this the wrong way. We may 20-30 jobs a day going on, so i am trying to automate this process. If is very time consuming trying to perform costs analysis and inventory control/ reorder manually. It would literally take me all day. Any suggestions would be appreciated.

Thanks

[CJ_London]

in principle, either link to the excel sheet or import to a temporary table. Then write various append queries to append data to the different tables.

If you link create an import folder to place the many excel files, you can create a vba routine to import each file and then move to an archive folder.

[ssanfu]

Yes, it is possible.

There are (at least) a couple of ways of doing this.
1) Link to the Excel workbook, then use queries or code to move the data
2) use automation to open, import the data & close the Excel workbook
3) export the Excel data to a CSV file and import

We used to do this, until the data started coming to us in CSV format (text file).

Ken Snell has examples of automation Importing/Exporting data to and from Access/Excel.
http://www.accessmvp.com/kdsnell/EXCEL_MainPage.htm

Under the IMPORT option, I like the header: Read Data from EXCEL File via Query (SQL Statement)
http://www.accessmvp.com/kdsnell/EXC...ort.htm#ImpSQL
once you have the data in a query, you can use code to process the data.

But see all of the examples in the IMPORT page........

You might (probably will) have a lot of code ... but it is doable.

[gunterbrink]

This is just a question, but can this forum be used to access free lance db programmers? This might be more than I can handle in the amount of time that it would take. If this is against the forum rules for even asking, I apologize in advance.

[CJ_London]

not against the rules so far as I know and there are freelance developers on this site (I'm one) and some perhaps will do it for free (but not me). You can PM me with what you are looking for if you like. But generally speaking, if you take up one of the suggestions provided by ssanfu and ask for help on specific issues we're happy to help.