insert statement dropping values

[bbolte]

this is driving me batty! i'm reading in a .csv file and then inserting the data into a mssql db. batchid sometimes shows up blank. the values that are disappearing begin with the letter "N", such as "NP2338" or "N00312". outside of those that begin with "N", the rest are a five digit number. the table field is varchar. why is it dropping those that begin with "N"?

edit: the csv doesn't have any blanks in those fields btw...

for i = 1 to ubound(arrData,2)
		if not isnull(arrData(3,i)) and trim(arrData(3,i)) <> "" then
			gname = replace(trim(arrData(3,i)),"'", "''")
		else
			gname = "-"
		end if
		if not isnull(arrData(4,i)) and trim(arrData(4,i)) <> "" then
			idesc = replace(trim(arrData(4,i)),"'", "''")
		else
			idesc = "-"
		end if
		batchid = arrData(1,i)
		grpid = arrData(2,i)
		iSQL = "insert into mytable (eDate, Batch, GroupID, GroupName, ItemDescription, Received, Accepted, UnitValue, ExtValue, NoRejected, DateImported) values ('" & arrData(0,i) & "', '" & batchid & "', '" & grpid & "', '" & gname & "', '" & idesc & "', '" & arrData(5,i) & "', '" & arrData(6,i) & "', '" & arrData(7,i) & "', '" & arrData(8,i) & "', '" & arrData(9,i) & "', '" & Now() & "');"
		'//response.write isql & "<br>"
		objConn.Execute(iSQL)
	next

[bbolte]

here is one of the lines from the csv

9/2/2005,NP0577, R1055,CARL HAYDEN - RADIO CLUB ,FAILED INKJET 6578D ,1,0,0,0,1

it's interesting to note that i have this exact script running on a different page using a different but similar csv and it's not dropping the second value...

[TGullen]

Are you using ADO to connect?

[bbolte]

yep...

[bbolte]

i'm wondering if there is a size limit of the .csv file that i'm reading in. anyone know?

i'm reading that in with an ado text call and dumping it into an array (arrData) with getrows...

[bbolte]

ok, here's the ado part where i'm reading in the .csv file:

Set objConn= Server.CreateObject("ADODB.Connection")
Set objRS = CreateObject("ADODB.Recordset")
objConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & uploadsDirVar & ";" & "Extended Properties=""text;HDR=NO;FMT=Delimited"""
objRS.Open "SELECT * FROM opra.csv", objConn
arrData = objRS.GetRows
objRS.close
objConn.close

now i'm writing out the insert statement, the ones beginning with "N" are empty in that field. why is the array empty there?

insert into mytable (eDate, Batch, GroupID, GroupName, ItemDescription, Received, Accepted, UnitValue, ExtValue, NoRejected, DateImported)
values ('9/30/2005', '', '890', 'FATHER LOPEZ HIGH SCHOOL', 'CELLPHONE NOKIA 3360', '1', '0', '0', '0', '1', '10/31/2005 8:15:57 PM');

[bbolte]

just in case anyone is interested. i had to have a schema.ini file describing my .csv. it seems that ADO was making an assumption on the data from the fields and deciding it was numeric, which made it drop those that began with "N". you can read more about the schema file here: http://msdn.microsoft.com/library/de...a_ini_file.asp