Hi all,
I'm currently working on a reporting application that get its data from a CSV file. The data from the CSV file is read and placed in an Access DB table.
Here is the code I am using...
Set MyConn = Server.CreateObject("ADODB.Connection")
Set MyConn2 = Server.CreateObject("ADODB.Connection")
strDBPath = Server.MapPath("OilReporting.mdb")
' the directory goes in the connection string
MyConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" &_
"Data Source=F:\Site_Pages\ptoOilReporting\;" &_
"Extended Properties=""text;HDR=No;FMT=Delimited"""
' Connect to OilReporting Access database:
MyConn2.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strDBPath & ";"
Set MySet = Server.CreateObject("ADODB.Recordset")
Set MySet2 = Server.CreateObject("ADODB.Recordset")
' the filename goes in the SQL
MySet.Open "SELECT * FROM data0021.txt", MyConn, adOpenForwardOnly, adLockReadOnly, adCmdText
MySet2.Open "tblRawData", MyConn2, 1, 2, adCmdTableDirect
' We will now transfer all data from the CSV file into the Access DB
Do While Not MySet.EOF
Myset2.AddNew
Myset2.Fields("RecNumber") = Myset.Fields(0)
Myset2.Fields("UsageDate") = Myset.Fields(1)
Myset2.Fields("UsageTime") = Myset.Fields(2)
Myset2.Fields("Username") = Myset.Fields(3)
Myset2.Fields("FIN") = Myset.Fields(4)
Myset2.Fields("BTNumber") = Myset.Fields(5)
Myset2.Fields("UsageQty") = Myset.Fields(6)
Myset2.Fields("OilPoint") = Myset.Fields(7)
Myset2.Fields("CostCentre") = Myset.Fields(8)
MySet2.Update
MySet.MoveNext
Loop
It all works fine with one exception. The values contained in field(5) in the CSV file comes in two distinct formats
Either 5 digits e.g 12345 or a letter followed by 4 digits e.g P1112
When the CSV file is processed, and field(5) contains a value that has a letter as a prefix, a blank field is retrieved from the CSV file. If the field is purely numeric, the correct value is returned.
The problem is definitely not with Access, as I have checked the values being read before I store them in Access.
Anyone any ideas, I'm a little befuddled by this!
TIA
David.
I'm currently working on a reporting application that get its data from a CSV file. The data from the CSV file is read and placed in an Access DB table.
Here is the code I am using...
Set MyConn = Server.CreateObject("ADODB.Connection")
Set MyConn2 = Server.CreateObject("ADODB.Connection")
strDBPath = Server.MapPath("OilReporting.mdb")
' the directory goes in the connection string
MyConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" &_
"Data Source=F:\Site_Pages\ptoOilReporting\;" &_
"Extended Properties=""text;HDR=No;FMT=Delimited"""
' Connect to OilReporting Access database:
MyConn2.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strDBPath & ";"
Set MySet = Server.CreateObject("ADODB.Recordset")
Set MySet2 = Server.CreateObject("ADODB.Recordset")
' the filename goes in the SQL
MySet.Open "SELECT * FROM data0021.txt", MyConn, adOpenForwardOnly, adLockReadOnly, adCmdText
MySet2.Open "tblRawData", MyConn2, 1, 2, adCmdTableDirect
' We will now transfer all data from the CSV file into the Access DB
Do While Not MySet.EOF
Myset2.AddNew
Myset2.Fields("RecNumber") = Myset.Fields(0)
Myset2.Fields("UsageDate") = Myset.Fields(1)
Myset2.Fields("UsageTime") = Myset.Fields(2)
Myset2.Fields("Username") = Myset.Fields(3)
Myset2.Fields("FIN") = Myset.Fields(4)
Myset2.Fields("BTNumber") = Myset.Fields(5)
Myset2.Fields("UsageQty") = Myset.Fields(6)
Myset2.Fields("OilPoint") = Myset.Fields(7)
Myset2.Fields("CostCentre") = Myset.Fields(8)
MySet2.Update
MySet.MoveNext
Loop
It all works fine with one exception. The values contained in field(5) in the CSV file comes in two distinct formats
Either 5 digits e.g 12345 or a letter followed by 4 digits e.g P1112
When the CSV file is processed, and field(5) contains a value that has a letter as a prefix, a blank field is retrieved from the CSV file. If the field is purely numeric, the correct value is returned.
The problem is definitely not with Access, as I have checked the values being read before I store them in Access.
Anyone any ideas, I'm a little befuddled by this!
TIA
David.