Get imported data into right format

[Rachael]

Hi All,

I was wondering if someone could take the time to give me a few clues on the best way to go about this problem programatically.

Attached is a text file that is exported from a soil moisture monitoring program. I want to get the soil moisture measurements in to my access 2000 database. So far I have been able to do a TransferText command to get the data from the text file to a table in Access. I discovered the Save Specifications thing when manually importing to then be used for automatically importing so rows aren’t dropped out if the info is a bit dodgey.

DoCmd.TransferText acImportDelim, "MoistImpSpec", "MoistImp", "C:\Program Files\VinRec\MoistureData.txt"

Later I will use a dialog control etc in place of the actual files path and name.

The table structures in my access program that I need this text file data to get into are

TableSiteInfo
SiteID autonmuber
SiteName Text

TableMoistureData
Moist ID autonumber
Date Date/Time
Hour Date/Time
SiteNameID Long Integer
Reading1 number
Reading2 number
Reading3 number
Reading4 number

But I can change it if its not good?

So, my dilemma is, each site has 4 sensors, the text file lists the data with each moisture reading in its own column despite the SiteName and the sensors are called Sensor1, Sensor2, sensor3, sensor4, sensor5 etc despite its site name. How do I get the site info into my access table for storing the soil moisture data? And how then, do I get the actual data for each site in matching up. I will also need it to tell the user if a sitename doesn't exist in the access database.

This text file is only a small example of what the soil moisture program can do, it can hold information for 100 sensors.

Any ideas on how to go about this would be greatly appreciated. And thankyou very much to all who have a look. I don't expect a whole solution but if you feel like giving one I would love it. I need a push in the right direction.

Kind regards,

Rachael

 

[WayneRyan]

Rachael,

Before getting too involved in this, let's look at your table structure:

tblSiteInfo
===========
SiteID - AutoNumber
SiteName (GBug, GT Bug)
GBugID (96, 564)

tblSensorInfo
=============
SensorID - AutoNumber
SensorName (Sensor 1, Sensor 2 ...)
Depth (30 cm, 60 cm, 90 cm)
SensorType (GBL, TEMP)
Refill ????
Full ????

tblSensorData
=============
ReadingID - AutoNumber
ReadingDate (23-10-2003, 24-10-2003 ...)
SensorID - FK (To SensorInfo)
ReadingValue (14, 200, 200)

Questions
=========
Don't understand the Refill/Full fields.

Need more info on the 100 sensor capability. Does that just mean more
sites introduced in the header and a corresponding increase in the number
of columns in the data part of the file? In other words, by reading the
header, can you predict the structure of the body?

Do subsequent reading files contain already processed data? If so, you
need to account for that.

Design
======
I introduced the third table, because when you're researching data your
two-table design leads to stuff like:

If Site = 1 And (Reading1 > 10 Or Reading2 > 10 Or Reading3 > 10 Or Reading4 > 10)
==================================================================================

That gets really cumbersome. If the Readings are in a seperate table you now have:

If Site = 1 And Reading > 10
============================

Much easier.

Thoughts
========
Anyway, if you give it some thought before you start out it will be easier in
the long run.

Just some thoughts,
Wayne

 

[Rachael]

Hi Wayne,

Thanks so much for your thoughts, I really appreciate it.

To answer some of your questions;

Don't worry about Refill and Full - they are number readings set in by the vineyard manager to determine at what level the soil dry ie needs refilling and wet ie no watering needed.

You are correct with your 100 sensor thoughts - I'm not even sure now that access can have 103 columns - will test this shortly?

It is likely that the sensors could be a sample of only 20 of them or could be the whole 100. Whichever, the heading info can be used to determine which sensors relate to what.

Yes subsequent readings files will already contain processed data.

I had thought about changing my table structure and will definately do it as it doesn't make any difference to anything other than this issue, so why not!! I agree.

Now that all this is sorted, how do I get the imported data into this table structure. I know a little VBA language - Its like speaking spanish - I know alot of the words but am sometimes a little unsure on how to put them together into a sentence.

How do I tell access to look for the correct sensor ID inorder to append info into the tblSensorData. I'm guessing I have to use .MoveNext and all that stuff - its not a For Each...Next scenario, I don't think - there you go I'm pretty hopeless. I don't expect you to write the whole thing for me (but feel free to) Just an example I can follow is all I need. Thankyou very much for your help - I dont even know where to start.

Kind regards,

Rachael

 

[WayneRyan]

Rachael,

I used the following table structures. I was just writing the code from the
top of my head, but it should work the first time. You probably won't even
have to use the debugger on it. Just attach it to a command button and watch
the data pour in!

Just kidding.

This should hopefully convert your "spreadsheet style" input data into something
that you can work with in Access.

At least it's a start. Keep me posted.

tblSiteInfo
===========
SiteID - AutoNumber
SiteName (GBug, GT Bug)
GBugNumber (96, 564)

tblSensorInfo
=============
SensorID - AutoNumber
SensorSiteID - FK
SensorName (Sensor 1, Sensor 2 ...)
Depth (30 cm, 60 cm, 90 cm)
SensorType (GBL, TEMP)
Refill ????
Full ????

tblSensorData
=============
ReadingID - AutoNumber
ReadingDate (23-10-2003, 24-10-2003 ...)
ReadingHour (2, 4, 6, 8 ...)
SensorID - FK (To SensorInfo)
ReadingValue (14, 200, 200)

Psychology is:
1) Read until the group definitions are found
2) Read the groups, adding them to your tables if they're not there already
3) Read the "Date", "Hour", "Sensor........" heading to see file width
4) Read the data, if date/hour not already entered, enter it

Dim dbs As DAO.Database  '<-- Need to Assign DAO Reference
Dim rst As DAO.Recordset '    Code in Design View
Dim sql As String        '    Tools --> References (Select Microsoft DAO 3.6)
'
' Site/Sensor Information
'
Dim CurrentSite As String
Dim CurrentSiteNumber As Long
Dim CurrentSiteID As Long
'
Dim CurrentSensor As String
Dim CurrentSensorID As Long
'
Dim buffer As String
Dim varCells As Variant 
Dim varHeading As Variant

Dim NumSensors As Long
Dim i As Long

On Error GoTo ErrHandler

Set dbs = CurrentDb

Open "C:\MoistureData.txt" For Input As #1
'
' Read until sites
'
Line Input #1, buffer
While Not EOF(1) And InStr(1, buffer, "SiteName") = 0
   Line Input #1, buffer
   wend
'
' If EOF (i.e. no "SiteName", file is bad
'
If EOF(1) Then
   MsgBox("Something is wrong here.")
   Close #1
   Exit Sub
End If
'
' Process sites until (Buffer = "Readings")
' Just want to make sure that we have "seen" all sites
'
Line Input #1, buffer
While Not EOF(1) And InStr(1, buffer, "Readings")
   '
   ' Break up the buffer 
   ' Remove quotes from SiteName
   '
   varCells = Split(buffer, ",")
   varCells(0) = Mid(varCells(0), 2, Len(varCells(0))  - 2)
   '
   ' Have We seen this site before?
   ' If not, add it
   ' If yes, get SiteID (may need it to insert sensor)
   '
   Set rst = dbs.OpenRecordSet("Select * From tblSiteInfo Where SiteName = '" & varCells(0) & "'")
   If rst.EOF And rst.BOF Then
      rst.AddNew
      rst!SiteName = varCells(0)
      CurrentSiteID = rst!SiteID  ' Save the AutoNumber
      rst!GBugNumber = varCells(1)
      rst.Update
   Else
      CurrentSiteID = rst!SiteID
   End If
   rst.Close
   Set rst = Nothing
   '
   ' Have We seen this Sensor before?
   ' If not, add it
   '
   CurrentSensor = Mid(varCells(2), 2, Len(varCells(2) - 2))
   Set rst = dbs.OpenRecordSet("Select * From tblSensorInfo " & _
                               "Where SiteID = " & CurrentSiteID & " And " & _
                               "      SensorName = '" & CurrentSensor & "'")
   If rst.EOF And rst.BOF Then
      rst.AddNew
      rst!SiteID = CurrentSiteID
      rst!SensorName = CurrentSensor
      rst!Depth = varCells(3)
      rst!SensorType =  varCells(4)
      rst!Refill= varCells(5)
      rst!Full =  varCells(6)
      rst.Update
   End If
  Wend
rst.Close
Set rst = Nothing
'
' By now, the parent Site/Sensor tables have all entries.
' Just read in the data
'
If EOF(1) Then
   MsgBox("Something is wrong here.")
   Close #1
   Exit Sub
End If
'
' This input line has the headings ...
'
Line Input #1, buffer
varHeading = Split(buffer, ",")
'
' Generate an error by traversing varHeading to see how "wide" the file is
' Error will be #9
'
For i = 0 to 1000
   CurrentSite = varHeading(i)
   Next i
'
' When we resume after the error, assign NumSensors
'
AssignNumber:
  NumSensors = i - 1
'
' Now, read data
'
Line Input #1, buffer
While Not EOF(1)
   varCells = Split(buffer, ",")
   Set rst = dbs.OpenRecordset("Select * From tblSensorData " & _
                               "Where  ReadingDate = #" & varCells(0) & "# And " & _
                               "       ReadingHour = " & varCells(1))
   If rst.EOF And rst.BOF Then
      '
      ' Add all sensors (Readings in Cell numbers are offset; because of date/hour fields)
      '
      For i = 2 to NumSensors + 1
         CurrentSensor = Mid(varHeading(i), 2, Len(varHeading(i)) - 2)
         CurrentSensorID = DLookUp("[SensorID]", "tblSensorInfo", "[SensorName] = '" & CurrentSensor & "'")
         rst.AddNew
         rst!ReadingDate = varCells(0)
         rst!ReadingHour = varCells(1)
         rst!SensorID = CurrentSensorID
         rst!ReadingValue = varCells(i)
         rst.Update
         Next i
   Else
      ' Already read in
   End If
   Line Input #1, buffer
   Wend
rst.Close
Set rst = Nothing
Close #1
MsgBox("Complete.")
Exit Sub

ErrHandler:
   If ERR = 9 Then 
      Resume AssignNumber
   End If
   MsgBox("Error #" & ERR)
   Exit Sub

hth,
Wayne

 

[Rachael]

Hi Wayne,

thank you so much for the code you posted and sorry for not replying sooner.

Well, Iv'e been having a go! and am learning heaps and beginning to understand this type of thing.

However, I'm a bit stuck. I've got the sites and sensor names reading in without a problem (although I had to change a few things to get it to work, will explain in a minute). Anyway I'm stuck on an openrecordset statement.

Heres the code and what I'm up to;

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim sql As String
'
' Site/Sensor Information
'
Dim CurrentSite As String
Dim CurrentSiteNumber As Long
Dim CurrentSiteID As Long
'
Dim CurrentSensor As String
Dim CurrentSensorID As Long
'
Dim buffer As String
Dim varCells As Variant
Dim varHeading As Variant

Dim NumSensors As Long
Dim i As Long

On Error GoTo ErrHandler

Set dbs = CurrentDb

Open "C:\MoistureData.txt" For Input As #1
'
' Read until sites
'
Line Input #1, buffer
While Not EOF(1) And InStr(1, buffer, "Site Name") = 0
Line Input #1, buffer
Wend
'
' If EOF (i.e. no "Site Name", file is bad) EOF(1) is checking for end of file
'
If EOF(1) Then
MsgBox ("Something is wrong here with file.")
Close #1
Exit Sub
End If
'
'Process sites until (Buffer = Readings)"
' Just want to make sure that we have "seen" all sites
'

******here I had to add the =0 to the end of the next line and put the line input on the next line???????? dont think its right though

While Not EOF(1) And InStr(1, buffer, "Readings") = 0
Line Input #1, buffer
' Break up the buffer
' Remove quotes from SiteName
'
varCells = Split(buffer, ",")

*******had to do these here didn't like it when they were part of the rst.addnew below

varCells(0) = Mid(varCells(0), 2, Len(varCells(0)) - 2)
varCells(2) = Mid(varCells(2), 2, Len(varCells(2)) - 2)
varCells(3) = Mid(varCells(3), 2, Len(varCells(3)) - 2)
'Have We seen this site before?"
' If not, add it
' If yes, get SiteID (may need it to insert sensor)
'
Set rst = dbs.OpenRecordset("Select * From [Moisture site] Where Site = '" & varCells(0) & "'")
If rst.EOF And rst.BOF Then
rst.AddNew
rst!Site = varCells(0)
CurrentSiteID = rst![Moisture ID] ' Save the AutoNumber
rst!Comments = "GBug ID is " & varCells(1)
rst.Update
Else
CurrentSiteID = rst![Moisture ID]

End If
rst.Close
Set rst = Nothing
'
'Have We seen this Sensor before?"
' If not, add it
'
CurrentSensor = varCells(2)

Set rst = dbs.OpenRecordset("Select * From MoistureSensor Where SiteID = " & CurrentSiteID & " And " & "SensorName = '" & CurrentSensor & "'")

If rst.EOF And rst.BOF Then
rst.AddNew
rst!SiteID = CurrentSiteID
rst!SensorName = CurrentSensor
rst!Depth = varCells(3)
rst!Type = varCells(4)
rst!Refill = varCells(5)
rst!Full = varCells(6)
rst.Update
End If
rst.Close
Set rst = Nothing
Wend
'
'By now, the parent Site/Sensor tables have all entries."
' Just read in the data
'
If EOF(1) Then
MsgBox ("Something is wrong here with the data.")
Close #1
Exit Sub
End If
'
' This input line has the headings ...
'
Line Input #1, buffer
varHeading = Split(buffer, ",")


' Generate an error by traversing varHeading to see how "wide" the file is
' Error will be #9
'
For i = 0 To 1000
CurrentSite = varHeading(i)

Next i
'
' When we resume after the error, assign NumSensors
'
AssignNumber:
NumSensors = i - 1
' CurrentSiteIDName = CurrentSiteID & CurrentSensor
' Now, read data


Line Input #1, buffer
While Not EOF(1)

varCells = Split(buffer, ",")



******Stuck on this !!!!!! Syntax error in SQL

Set rst = dbs.OpenRecordset("Select * From [Moisture readings] WHERE [ReadDate] = # " & varCells(0) & " # AND [ReadHour] =" & varCells(1))

If rst.EOF And rst.BOF Then
'
' Add all sensors (Readings in Cell numbers are offset; because of date/hour fields)
'
For i = 2 To NumSensors + 1
CurrentSensor = Mid(varHeading(i), 2, Len(varHeading(i)) - 2)
CurrentSensorID = DLookup("[SensorID]", "Moisture readings", "[SensorName] = '" & CurrentSensor & "'")
rst.AddNew
rst!ReadDate = varCells(0)
rst!ReadHour = varCells(1)
rst!SensorID = CurrentSensorID
rst!Reading1 = varCells(i)
rst.Update
Next i
Else
'Already read in"
End If
Line Input #1, buffer
Wend
rst.Close
Set rst = Nothing
Close #1
MsgBox ("Complete.")
Exit Sub

ErrHandler:
If Err = 9 Then
Resume AssignNumber
Else
MsgBox Err.Description & Err.Number
Close #1
Exit Sub

End If




Where I'm stuck I thinks its trying to read the next line after "Readings" which is the Date, Hour, Sensor 1 headings. The Syntax error is saying [ReadDate]=#"Date"# and [ReadHour]="Hour"'

I have also put some other notes in the code on things I had to change, i signified them with ********

Thankyou very much for your help so far.

Kind regards,

Rachael

 

[WayneRyan]

Rachael,

Good to hear back from you. I'm heading off to work now. Will look back on
this later. The good news is that I have a couple of days off.

If you could post your database here.

Tools --> Database Utilities --> Compact/Repair
Then ZIP into a file
Then attach

It'd make it a lot easier. I have an input file.

See you later,
Wayne

 

[Rachael]

Hi Wayne,

thanks for your reply. We are going camping for the weekend so won't be able to get back to you til monday, sorry. the database is huge so will have to rip out only the moisture stuff, i'll do this also on monday and send.


Rachael

 

[Rachael]

Hi Wayne,

attached database. Sorry for the delay, we had too much fun camping and 4wdriving in Victoria's High country.

Here it is - I put a command button on the menu form, sure you'll figure that out. Thank you very much

Rachael

 

[WayneRyan]

Rachael,

Here's the latest,

Gotta go to work.
Wayne

 

[Rachael]

Cheers Wayne, you are a legend, works fabulously. I like the unbound text boxes with the date I think I might leave it visible as it give the user an idea of what is happening with the import, a bit like a status bar but showing dates. Thank you very much, I've learnt alot about text files and added to my (small) VB language vocabularly.

Kind regards,

Rachael

 

[WayneRyan]

Rachael,

Glad to help!

I think that with the table structure, you can do some really nice things
with queries. I hope you post back when you're experimenting with how
to use the data.

A couple points, though:

I took the spaces out of the table/field names. They really make life tough.

It would be nice to hook up a "browse for file" for subsequent imports. There
are examples here (look for ghudson) if you use the Search Facility and look
for "Browse".

Along with everyone else (it seems), I've been really sick the last few days,
so we may not have addressed all of the issues.

Hope to here from you again,
Wayne

 

[Rachael]

Hi Wayne,

Sorry to hear you've been sick, I hope you get better soon.

I have already in the past used Browse examples from ghudson and they wrok really well. I am planning to put a browse function into this.

I know spaces are big drama, but unfortunatley the database was designed by me about five years ago when I knew far less then distributed so I have very limited opportunity to rectify some of these things. If I have to do things to clients back-end when I do an upgrade, yes I now leave out the spaces, but I don't see fixing all of them as a big priority - don't fix something that aint broke! Anyway, my next big issue is how to present the data on a form and graph it.

The problem is that the end user might use the moisture monitoring Gbug and just import in the fashion we have just acheived, but then I have some clients who read there moisture monitoring devices manually every day or few days - they need to be able to come in and type in the info (these vineyards are quite small so they wont be doing alot of data entry - my biggest challenge is always to satisfy the 10 acre vineyard and the 1000 acre vineyard and have enough flexibility in data entry)

With the new table structure I see my only option to present data is in a cross-tab type set-up, but with a little experimentation I have already run into and issue with the column headings and having to specify them in the query properties.

I would appreciate your thoughts on how you think I would go about it.

I hav attached a screen snap of how the data was presented in the old version.

I realise that cross-tab won't let you add data from a form but thought I would have a data entry pop-up that run an update query and refreshed the main form.

Hope you feel better soon and thank you so much for your help.

Rachael