chimney
04-29-2013, 01:31 AM
I've been working for a week on trying to get this vba code setup where I import a 2 column CSV file of time/temperature values. Im super close to having it completed I believe. I had some working code, and lost it in a vba crash, and I've been able to piece-meal this back together with some help. My CSV file contains bad data that needs to be parsed out, then after the string [CSV DATA], it has one line of headers (which I do not need). after the line after [CSV DATA] is my good data, with time in the first column, and temperature in the second.
Right now it gets to my second function (first is to import/parse, second is to append) and throws me a "user-defined type not defined" error, and I'm having a hard time on my own seeing where I've missed a declaration, or did so improperly. here is the sub/function I have so far:
Public Sub Parse_Data_File()
Dim hasMatchedText As Boolean
Dim results() As String
Dim i As Long
Dim importFile As String
Dim strTableName As String
Dim strPath As String
Dim TextRow As String
Dim startPoint As Boolean
'flag for when we have reached the data
startPoint = False
importFile = "C:\temp\TEMPERATURE.CSV"
Open importFile For Input As #1
Do While Not EOF(1)
Line Input #1, TextRow
'Debug.Print TextRow
If startPoint Then
results = Split(TextRow, ",")
AddTemperatureRow results(0), results(1)
ElseIf TextRow = "[CSV Data]," Then
Line Input #1, TextRow 'scrap next row as its the header
startPoint = True
End If
Loop
Close 1
End Sub
Public Function AddTemperatureRow(time As String, temp As String)
Dim cmd As New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdText
cmd.CommandText = "INSERT INTO tempTable ([Time], [Temperature]) VALUES(" & Chr(34) & time & Chr(34) & ", " & temp & ")"
Dim param As ADODB.Parameter
cmd.Execute
End Function
Right now it gets to my second function (first is to import/parse, second is to append) and throws me a "user-defined type not defined" error, and I'm having a hard time on my own seeing where I've missed a declaration, or did so improperly. here is the sub/function I have so far:
Public Sub Parse_Data_File()
Dim hasMatchedText As Boolean
Dim results() As String
Dim i As Long
Dim importFile As String
Dim strTableName As String
Dim strPath As String
Dim TextRow As String
Dim startPoint As Boolean
'flag for when we have reached the data
startPoint = False
importFile = "C:\temp\TEMPERATURE.CSV"
Open importFile For Input As #1
Do While Not EOF(1)
Line Input #1, TextRow
'Debug.Print TextRow
If startPoint Then
results = Split(TextRow, ",")
AddTemperatureRow results(0), results(1)
ElseIf TextRow = "[CSV Data]," Then
Line Input #1, TextRow 'scrap next row as its the header
startPoint = True
End If
Loop
Close 1
End Sub
Public Function AddTemperatureRow(time As String, temp As String)
Dim cmd As New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdText
cmd.CommandText = "INSERT INTO tempTable ([Time], [Temperature]) VALUES(" & Chr(34) & time & Chr(34) & ", " & temp & ")"
Dim param As ADODB.Parameter
cmd.Execute
End Function