Import and process data

[JOKER_ZA]

Hi everyone

I am trying to build a database to store results output from a temperature logger. The logger outputs data in excel. I want to import that data to access, process it and only store the relevant processed data in the database. Example: I want to calculate the min and max temp from the output data and only store that. Also, I will need to use the import function frequently. Every time the logger is done logging, it creates a new excel spreadsheet. Thus one spreadsheet = one row in the database

[rpeare]

Is the data output to a different file name every time or does it overwrite what's there?
If the excel files are given a new name every time an export is completed, are they all going to the same directory?
Can you give an example of what your temperature output looks like (as it appears in your excel spreadsheet)

[JOKER_ZA]

Each output has a new file name. I have to keep every output. They all go into the same directory. Here is what the output looks like. Thanks

Plot Title: 10244136
#	Date Time, GMT+02:00	Temp, °C	Host Connected	Stopped	End Of File
	07/02/13 11:35:38 AM	32.9
	07/02/13 11:36:38 AM	33.0
	07/02/13 11:37:38 AM	38.9
	07/02/13 11:38:38 AM	48.9
	07/02/13 11:39:38 AM	57.3
	07/02/13 11:40:38 AM	63.7
	07/02/13 11:41:38 AM	68.8
	07/02/13 11:42:38 AM	72.8
	07/02/13 11:43:38 AM	76.5
	07/02/13 11:44:38 AM	79.5
	07/02/13 11:45:38 AM	82.5
	07/02/13 11:46:38 AM	85.6
	07/02/13 11:47:38 AM	88.7
	07/02/13 11:48:38 AM	91.6
	07/02/13 11:49:38 AM	94.1
	07/02/13 11:50:38 AM	96.5
	07/02/13 11:51:38 AM	98.9
	07/02/13 11:52:38 AM	101.0
	07/02/13 11:53:38 AM	103.0
	07/02/13 11:54:38 AM	105.0
	07/02/13 11:55:38 AM	107.0
	07/02/13 11:56:38 AM	109.0
	07/02/13 11:57:38 AM	111.0
	07/02/13 11:58:38 AM	113.0
	07/02/13 11:59:38 AM	114.8
	07/02/13 12:00:38 PM	116.6
	07/02/13 12:01:38 PM	118.2
	07/02/13 12:02:38 PM	119.7
	07/02/13 12:03:38 PM	120.6
	07/02/13 12:04:38 PM	120.8
	07/02/13 12:05:38 PM	121.1
	07/02/13 12:06:38 PM	121.1
	07/02/13 12:07:38 PM	121.1
	07/02/13 12:08:38 PM	121.1
	07/02/13 12:09:38 PM	121.1
	07/02/13 12:10:38 PM	121.1
	07/02/13 12:11:38 PM	121.1
	07/02/13 12:12:38 PM	121.1
	07/02/13 12:13:38 PM	121.1
	07/02/13 12:14:38 PM	121.1
	07/02/13 12:15:38 PM	121.1
	07/02/13 12:16:38 PM	121.1
	07/02/13 12:17:38 PM	120.8
	07/02/13 12:18:38 PM	119.9
	07/02/13 12:19:38 PM	118.6
	07/02/13 12:20:38 PM	117.4
	07/02/13 12:21:38 PM	116.2
	07/02/13 12:22:38 PM	115.2
	07/02/13 01:20:38 PM	34.6	Logged
	07/02/13 01:21:26 PM			Logged	Logged

[rpeare]

Ok so here are the pieces of information in this excel file:

File Name
Plot Title (unless plot title is the file name i.e. 10244136.xlsx)
Date
Time
Temperature

Which of these pieces of information do you need to keep (you said min/max temperature, do you need an avg as well or any other calculation?)

Can you post a sample file as well that would be very helpful so I don't have to create something from scratch.

[JOKER_ZA]

I assume you need a sample of a temp log file? I'll attach one now. I need to calculate the min and max temp where the temp is at least 100 C +-0.5C. I also need to calculate the time between the min and max temp. Thank you for your help

Temp log.zip

[rpeare]

So you don't need the low range you just need the min max of anything higher than 99.5C?

[JOKER_ZA]

Yes that's correct

[rpeare]

Joker_ZA.zip

Here's a simple example

The import process does the following:

Cycles through all excel files in the same directory as the database (unzip all these files to the same location to make this work, you can alter the source directory to something else by just changing the ssourcedir string)
For each .xls or .xlsx file it finds it cycles through the records finding the min and max temperatures and their related times
Appends the summary information to the table tblData

you can look at the query qryData to find out how to get the temperature difference and identify if the temperature difference is on the cooling or heating cycle.

[JOKER_ZA]

Thank you very much! I'll check it out and post again if I need any help... Thank you!

[rpeare]

one other note, the process moves files that have been imported to a secondary directory so that duplicates are avoided, to make that part work make sure you're working in a directory that you have read/write permissions, not just read permission.

[JOKER_ZA]

OK Thanks. The module you created is in VB right?

[rpeare]

yes there's one table, one query, one form and one module in that database, your options for where to look are limited

[JOKER_ZA]

Hi rpeare. There are a few more things I hope you can help me with? The minimum has to be at least 120 C. Sorry I made a mistake before. Also, I need to calculate the time between when it first reaches the min temp to when it drops below it again. That is the sterilization time. Hope you can help me Thanks

[rpeare]

look at the code.

it should be pretty clear where you need to change the lower bound of your temperature (right now it's 95.5)

that should be the only thing you need to change, you can calculate the duration in a query by using the datediff function. If you change the temperature threshold you'll end up with the first and last time stamp your process was above 120.