wrote below scripts to import site data when have multiple projects, similar scripts to import Txs and Cells.
Code:
'*************************************************************************************
Private Sub importSites(site_csvfile)
'************importing sites started*************
'getting the content of the Sites table
set Sites = doc.GetRecords("Sites",True)
' Open the CSV file for reading, file with new sites to be added
'site_csvfile = import_dir & "\01_Sites_202501.csv"
if fso.FileExists(site_csvfile) Then
Set site_file = fso.OpenTextFile(site_csvfile, 1)
LogMessage "Openning Site csv file:" & site_csvfile
writeLog Date & " " & Time & ": Openning Site csv file:" & site_csvfile & vbCrLf
Else
LogMessage "The file " & site_csvfile & " does not exist."
writeLog Date & " " & Time & ": The file " & site_csvfile & " does not exist." & vbCrLf
Exit Sub
End if
' Skip the header row
If Not site_file.AtEndOfStream Then
site_file.ReadLine
End If
LogMessage "existing Site number:" & Sites.RowCount
writeLog Date & " " & Time & ": Existing Site number:" & Sites.RowCount & vbCrLf
LogMessage "Adding/Updating Sites..."
writeLog Date & " " & Time & ": Adding/Updating Sites..." & vbCrLf
' Loop through each line in the file: sites
Dim site_row
Dim num_of_update
Dim num_of_add
Dim site_before
Dim site_after
num_of_update = 0
num_of_add = 0
site_before = Sites.RowCount
Do Until site_file.AtEndOfStream
' Read a line from the file
line = site_file.ReadLine
' Split the line into an array of values
values = Split(line, ",")
sitename = values(0)
'LogMessage sitename
site_lon = values(1)
site_lat = values(2)
site_type = values(1)
site_zone = values(2)
site_status = values(3)
site_SD = values(4)
site_row = Sites.FindPrimaryKey(sitename)
If site_row = -1 then 'not found
'Add new site
Sites.AddNew
Sites.SetValue "NAME",sitename
Sites.SetValue "LONGITUDE",site_lon
Sites.SetValue "LATITUDE",site_lat
Sites.SetValue "UTMZONE",site_zone
Sites.SetValue "SITE_STATUS",site_status
Sites.SetValue "SITE_TYPE",site_type
Sites.Update
num_of_add = num_of_add + 1
Else
'existing sitename, Modify other columns here
Sites.Edit(site_row)
Sites.SetValue 23,site_zone ' column number used, you can use column name as well
Sites.SetValue 42,site_status
Sites.SetValue 21,site_SD
Sites.Update
num_of_update = num_of_update + 1
End if
Loop
doc.Redraw
LogMessage "Added Sites number:" & num_of_add
writeLog Date & " " & Time & ": Added Sites number:" & num_of_add & vbCrLf
LogMessage "Updated Sites number:" & num_of_update
writeLog Date & " " & Time & ": Updated Sites number:" & num_of_update & vbCrLf
site_after = Sites.RowCount
LogMessage "Total Sites number after:" & site_after
writeLog Date & " " & Time & ": Total Sites number after:" & site_after & vbCrLf
new_siteno = site_after - site_before
if new_siteno = 0 then
LogMessage "No new sites added for this zone"
writeLog Date & " " & Time & ": No new sites added for this zone" & vbCrLf
else
LogMessage "New Sites added for this zone: " & new_siteno
writeLog Date & " " & Time & ": New Sites added for this zone: " & new_siteno & vbCrLf
End If
'Get All column names:
'For i = 1 to Sites.ColumnCount
' LogMessage "column " & i & ": " & Sites.GetValue(0,i)
'Next
'************importing sites ended*************
End sub
'*************************************************************************************
writeLog is a separate sub to write information to log file.
Bookmarks