CSV File wierdness

[dkn]

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.

 

[audiopro]

Not up to speed on the coding but you would get zero if you put text into a numeric field.

Keith

http://www.studiosoft.co.uk

 

[dkn]

Keith,

Thanks for the reply. I was thinking along the same lines, but what confuses me is I haven't (and dont know how to) define data types when reading the CSV file.

This is the line of code that does all the work of creating a recordset from the CSV file :-

MySet.Open "SELECT * FROM data0021.txt", MyConn, adOpenForwardOnly, adLockReadOnly, adCmdText

If I check the values retrieved after this statement is executed, i.e. before I go anywhere near Access, I still get a blank field in the recordset where a non numeric value has been encountered.

I'm still confused...

David.

 

[audiopro]

Neither ASP or Access are my bag, I use Foxpro.
Looking at the list of fields, they all appear to be numeric which is possibly the default data type. Maybe you should be declaring field(5) as text in some way.

Keith

http://www.studiosoft.co.uk

 

[dkn]

I think I may be onto something....

Very useful article here :-

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnclinic/html/scripting03092004.asp

It looks like I can create a file called Schema.ini, were I can specify the data type for each field in the CSV file. Just need to work out were this should go ... would imagine in the same directory as the CSV file on the web server..

Thanks for the input Keith.

David.

 

[audiopro]

Creating a .INI file seem a long way round.
Take a look at

http://www.imf.org/external/help/csv.htm

There are references to data types there.


Keith

http://www.studiosoft.co.uk

 

[dkn]

OK, problem solved. Created a Schema.ini file and stuck it in the same same directory as the CSV file and hey presto, it works!

For anyone who is interested, here is the content of the Schema,ini file :-

[data0021.txt]
Format=CSVDelimited

Col1=RecNumber Long
Col2=UsageDate Text
Col3=UsageTime DateTime
Col4=Username Text
Col5=FIN Text
Col6=BTNum Text
Col7=Usage Single
Col8=OilPoint Text
Col9=CostCentre Text

Happy Programming!

David

 

[audiopro]

Glad you got it sorted but I thought there would be a way of setting var type within your original code.

Keith

http://www.studiosoft.co.uk