New to VB, Filling Data Gaps?

[mathias1979]

Hello,

I'm a proficient Excel user, but I've never used VB beyond a few automatically created Macros. What I have is a lengthy data set (actually 20 years split into multpile files) with data reported every 10 minutes. Problem is, there are gaps and extra times in the data set. So sometimes a few hours will be missing, other times data will be present in increments greater than 10 minutes. Using VB, would I be able to create a script to monitor the increment between the date/time stamp and insert blank rows of data to fill in the gaps and delete extra readings? If not, what would you recommend I use to manipulate my data (it would need to be a freeware program)?

Thanks for any help,
Matt

 

[RonRepp]

VB will work. Use the DateDiff Function to return the difference in dates/times. This is an integer field.

Dim I as Integer

I=DateDiff("d",1/1/2007, 1/31/2007)  'should return 31

MsgBox I

I hope this helps

Ron Repp

If gray hair is a sign of wisdom, then I'm a genius.

 

[SBerthold]

I am not sure if I understand what you are doing, or why you have done it the way you have.

May be best to use a Database (the free SQL Server 2005 Express or even just a Jet Mdb) to store the data in the first place.
If you want a reporting for every 10 minutes for 20 years then you are talking about over a million records.
If you only wnat to view a day or week's worth of data, then that is different.

You could possibly create a temporary table in a db with a record for each increment, and then do a bulk update to insert the actual records (from the original DB table, or possibly from the Excel file) matching match those increments.

 

[mathias1979]

Thanks, but having never programmed VB before, what would my general structure have to look like? Essentially, I need to:

-Find time increment between cells A1 & A2
-If time increment < 10 min, delete Row 2, retest A1 & new A2
-If time increment > 10 min, insert row between Row1 & Row2 and move on to test A2 & A3
-end process at last cell

But I'm unsure of the syntax needed to do this. Is there a good online tutorial that would teach me what I need to know quickly?

 

[mathias1979]

SBerthold,

To answer your question, yes the dataset is too long to fit in Excel, but it will be taylored down to something that should fit within Excel. So for now, I'm assuming I can complete what I need to within Excel/VB...as this is a one time project.

 

[SBerthold]

Oh, then I did misunderstand it.

See Forum707 for help