pexp
10-03-2008, 09:23 AM
Hello All,
I am trying to upload the .csv file record to server using classic ASP. Following code uploads the file but with no datas. My .csv file has around 300 rows and 57 column fields.File size is around 600kb
Does anybody have any idea.
Please help!
***********************************
<%
Response.Buffer = true
path = server.mapPath("./import")
fatalError = false
if pageError <> true then
if Request.QueryString("step2") <> "" then
if Request.Form("uploadType") = "1" then
categoryID = request.form("category")
for i = 1 to categoryDepth
if request.form("subCategory" & i) = "-1" then
exit for
else
categoryID = request.form("subCategory" & i)
end if
next
if not isNumberValid(categoryID) then categoryID = 0
end if
excelDbStr = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & Request.Form("filePath") & ";" & _
"Extended Properties=""text;HDR=Yes;FMT=Delimited;"";"
SQLQuery = "SELECT * FROM importProducts.csv;"
Set txtds = Server.CreateObject("ADODB.Recordset")
txtds.Open SQLQuery,excelDbStr,3,1,&H0001
If Err.Number <> 0 and fatalError = False then
errorMSG = "<b>Data file does not exist.</b>"
fatalError = true
else
if not txtds.EOF and not txtds.BOF then
Set objConn = Server.CreateObject("ADODB.Connection")
Set ds = Server.CreateObject("ADODB.Recordset")
Set ds2 = Server.CreateObject("ADODB.Recordset")
objConn.Open dbConnectionStr
do until txtds.EOF
loadProduct()
err.clear
if productID > 0 then
if deleteProduct = true then
' Delete Product
SQLQuery = "DELETE FROM tblProducts WHERE productID=" & productID & ";"
objConn.Execute(SQLQuery)
SQLQuery = "DELETE FROM tblProducts_Categories WHERE productID=" & productID & ";"
objConn.Execute(SQLQuery)
SQLQuery = "DELETE FROM tblProduct_ProductOptions WHERE productID=" & productID & ";"
objConn.Execute(SQLQuery)
SQLQuery = "DELETE FROM tblMembershipDiscounts WHERE productID=" & productID & ";"
objConn.Execute(SQLQuery)
if err <> 0 then
errorMSG = errorMSG & "<li>Error deleting product " & productName & " (" & productCode & ")"
productErrors = productErrors + 1
else
productsDeleted = productsDeleted + 1
end if
else
' Check if entry exists
SQLQuery = "SELECT productID FROM tblProducts WHERE productID=" & productID & ";"
set ds = objConn.Execute(SQLQuery)
if not ds.bof AND not ds.eof then
ds.close
set ds = nothing
updateProduct()
else
ds.close
set ds = nothing
addProduct()
end if
end if
else
'product does not exist -> insert
addProduct()
end if 'productID > 0
txtds.MoveNext
loop
Set ds2 = Nothing
Set ds = Nothing
objConn.Close
Set objConn = Nothing
actionComplete = True
If Err.Number <> 0 and fatalError = False then
Response.Write err.Description
errorMSG = "<b>Data file invalid.</b><br>Your data file is invalid, please ensure the format is identical to the 'Download products' file. (1)<!--" & Err.description & "-->"
fatalError = true
End If
end if 'txtds.EOF or txtds.BOF
'txtds.Close
removeFiles()
End If
'Set txtds = Nothing
else ' step2Error <> ""
If request.queryString("Action")="1" then
removeFiles()
Response.Clear
byteCount = Request.TotalBytes
requestBin = Request.BinaryRead(byteCount)
Set uploadRequest = CreateObject("Scripting.Dictionary")
'BuildUpload(requestBin)
If uploadRequest.Item("blob").Item("Value") <> "" Then
contentType = uploadRequest.Item("blob").Item("ContentType")
filepathname = uploadRequest.Item("blob").Item("FileName")
'filename will always be the same
filename = "importProducts.csv" 'Right(filepathname,Len(filepathname)-InstrRev(filepathname,"\"))
'filename = Right(filepathname,Len(filepathname)-InstrRev(filepathname,"\"))
value = uploadRequest.Item("blob").Item("Value")
filename = path & "\" & filename
Set objFSO = Server.CreateObject("Scripting.FileSystemObject")
' if upload directory doesn't exist returns error here
on error resume next
err.clear
Set objFile = objFSO.CreateTextFile(fileName)
if err.number <> 0 then
if err.number = 70 then
errorMSG = "<b>Permission denied.</b><br>Please check the manual for information on setting permissions to upload."
fatalError = true
elseIf err.number = 76 then
errorMSG = "<b>An error occurred.</b><br>Please ensure that you select a file to upload."
fatalError = true
else
errorMSG = "<b>An error occurred.</b><br>The error code is " & err.number
fatalError = true
end if
end if
if fatalError <> true then
For i = 1 to LenB(value)
objFile.Write chr(AscB(MidB(value,i,1)))
Next
objFile.Close
Set objFile = Nothing
Set objFSO = Nothing
End If
Set uploadRequest = Nothing
Set objFSO = Nothing
'write out schema file
writeSchema path, filename, returnDelimiter(request.queryString("d"))
' create connection string for excel file
excelDbStr = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & path & ";" & _
"Extended Properties=""text;HDR=Yes;FMT=Delimited;"";"
SQLQuery = "SELECT * FROM importProducts.csv;"
Set txtds = Server.CreateObject("ADODB.Recordset")
txtds.Open SQLQuery,excelDbStr,3,1,&H0001
'Response.Write "dfdf="
'Response.Write txtds.Fields.Count & "=" & fieldCount
'Response.end
if txtds.Fields.Count = fieldCount then
if not txtds.EOF and not txtds.BOF then
step2 = true
else
errorMSG = "<b>File empty.</b><br>Your data file is valid, however it contains no product information."
fatalError = true
end if
elseIf not txtds.Fields.Count = fieldCount and fatalError = false then
if txtds.Fields.Count = 0 and fatalError = false then
errorMSG = "<b>Error.</b><br>Please ensure that the IUSR_MACHINENAME account has read/write access on the 'Import' directory."
fatalError = true
else
errorMSG = "<b>Data file invalid.</b><br>Your data file is invalid, please ensure the format is identical to the 'Download products' file. (2)<!-- field count " & txtds.Fields.Count & " -->"
fatalError = true
end if
end if
txtds.Close
Set txtds = Nothing
If Err > 0 and fatalError = False then
errorMSG = "<b>An error occurred.</b><br>" & err.number & " - " & err.description
fatalError = true
end if
else
errorMSG = "<b>File not found.</b><br>Unable to upload file because it does not exist."
fatalError = true
end if
end If
end if ' step2Error
end if ' pageError
Function BuildUpload(RequestBin)
'Get the boundary
Dim startPos, endPos, boundary, boundaryPos, pos, posFile, posBound, fieldName
startPos = 1
endPos = InstrB(startPos,RequestBin,getByteString(chr(13)))
boundary = MidB(RequestBin,startPos,endPos-startPos)
boundaryPos = InstrB(1,RequestBin,boundary)
'Get all data inside the boundaries
Do until (boundaryPos=InstrB(RequestBin,boundary & getByteString("--")))
'Members variable of objects are put in a dictionary object
Dim UploadControl
Set UploadControl = CreateObject("Scripting.Dictionary")
'Get an object name
pos = InstrB(BoundaryPos,RequestBin,getByteString("Content-Disposition"))
pos = InstrB(Pos,RequestBin,getByteString("name="))
startPos = pos+6
endPos = InstrB(startPos,RequestBin,getByteString(chr(34)))
fieldName = getString(MidB(RequestBin,startPos,endPos-startPos))
posFile = InstrB(BoundaryPos,RequestBin,getByteString("filename="))
posBound = InstrB(endPos,RequestBin,boundary)
'Test if object is of file type
If posFile<>0 AND (PosFile<PosBound) Then
'Get Filename, content-type and content of file
startPos = posFile + 10
endPos = InstrB(startPos,RequestBin,getByteString(chr(34)))
FileName = getString(MidB(RequestBin,startPos,endPos-startPos))
'Add filename to dictionary object
UploadControl.Add "FileName", FileName
pos = InstrB(endPos,RequestBin,getByteString("Content-Type:"))
startPos = pos+14
endPos = InstrB(startPos,RequestBin,getByteString(chr(13)))
'Add content-type to dictionary object
contentType = getString(MidB(RequestBin,startPos,endPos-startPos))
UploadControl.Add "ContentType", contentType
'Get content of object
startPos = endPos + 4
endPos = InstrB(startPos,RequestBin,boundary) - 2
Value = MidB(RequestBin,startPos,endPos-startPos)
else
'Get content of object
pos = InstrB(Pos,RequestBin,getByteString(chr(13)))
startPos = pos + 4
endPos = InstrB(startPos,RequestBin,boundary) - 2
Value = getString(MidB(RequestBin,startPos,endPos-startPos))
End If
UploadControl.Add "Value" , Value
UploadRequest.Add fieldName, UploadControl
BoundaryPos=InstrB(BoundaryPos+LenB(boundary),RequestBin,boundary)
loop
End Function
Function getByteString(StringStr)
Dim char
For i = 1 to Len(StringStr)
char = mid(StringStr,i,1)
getByteString = getByteString & chrB(AscB(char))
Next
End Function
Function getString(StringBin)
getString =""
For i = 1 to LenB(StringBin)
getString = getString & chr(AscB(MidB(StringBin, i, 1)))
Next
End Function
%>
**********************************
I am trying to upload the .csv file record to server using classic ASP. Following code uploads the file but with no datas. My .csv file has around 300 rows and 57 column fields.File size is around 600kb
Does anybody have any idea.
Please help!
***********************************
<%
Response.Buffer = true
path = server.mapPath("./import")
fatalError = false
if pageError <> true then
if Request.QueryString("step2") <> "" then
if Request.Form("uploadType") = "1" then
categoryID = request.form("category")
for i = 1 to categoryDepth
if request.form("subCategory" & i) = "-1" then
exit for
else
categoryID = request.form("subCategory" & i)
end if
next
if not isNumberValid(categoryID) then categoryID = 0
end if
excelDbStr = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & Request.Form("filePath") & ";" & _
"Extended Properties=""text;HDR=Yes;FMT=Delimited;"";"
SQLQuery = "SELECT * FROM importProducts.csv;"
Set txtds = Server.CreateObject("ADODB.Recordset")
txtds.Open SQLQuery,excelDbStr,3,1,&H0001
If Err.Number <> 0 and fatalError = False then
errorMSG = "<b>Data file does not exist.</b>"
fatalError = true
else
if not txtds.EOF and not txtds.BOF then
Set objConn = Server.CreateObject("ADODB.Connection")
Set ds = Server.CreateObject("ADODB.Recordset")
Set ds2 = Server.CreateObject("ADODB.Recordset")
objConn.Open dbConnectionStr
do until txtds.EOF
loadProduct()
err.clear
if productID > 0 then
if deleteProduct = true then
' Delete Product
SQLQuery = "DELETE FROM tblProducts WHERE productID=" & productID & ";"
objConn.Execute(SQLQuery)
SQLQuery = "DELETE FROM tblProducts_Categories WHERE productID=" & productID & ";"
objConn.Execute(SQLQuery)
SQLQuery = "DELETE FROM tblProduct_ProductOptions WHERE productID=" & productID & ";"
objConn.Execute(SQLQuery)
SQLQuery = "DELETE FROM tblMembershipDiscounts WHERE productID=" & productID & ";"
objConn.Execute(SQLQuery)
if err <> 0 then
errorMSG = errorMSG & "<li>Error deleting product " & productName & " (" & productCode & ")"
productErrors = productErrors + 1
else
productsDeleted = productsDeleted + 1
end if
else
' Check if entry exists
SQLQuery = "SELECT productID FROM tblProducts WHERE productID=" & productID & ";"
set ds = objConn.Execute(SQLQuery)
if not ds.bof AND not ds.eof then
ds.close
set ds = nothing
updateProduct()
else
ds.close
set ds = nothing
addProduct()
end if
end if
else
'product does not exist -> insert
addProduct()
end if 'productID > 0
txtds.MoveNext
loop
Set ds2 = Nothing
Set ds = Nothing
objConn.Close
Set objConn = Nothing
actionComplete = True
If Err.Number <> 0 and fatalError = False then
Response.Write err.Description
errorMSG = "<b>Data file invalid.</b><br>Your data file is invalid, please ensure the format is identical to the 'Download products' file. (1)<!--" & Err.description & "-->"
fatalError = true
End If
end if 'txtds.EOF or txtds.BOF
'txtds.Close
removeFiles()
End If
'Set txtds = Nothing
else ' step2Error <> ""
If request.queryString("Action")="1" then
removeFiles()
Response.Clear
byteCount = Request.TotalBytes
requestBin = Request.BinaryRead(byteCount)
Set uploadRequest = CreateObject("Scripting.Dictionary")
'BuildUpload(requestBin)
If uploadRequest.Item("blob").Item("Value") <> "" Then
contentType = uploadRequest.Item("blob").Item("ContentType")
filepathname = uploadRequest.Item("blob").Item("FileName")
'filename will always be the same
filename = "importProducts.csv" 'Right(filepathname,Len(filepathname)-InstrRev(filepathname,"\"))
'filename = Right(filepathname,Len(filepathname)-InstrRev(filepathname,"\"))
value = uploadRequest.Item("blob").Item("Value")
filename = path & "\" & filename
Set objFSO = Server.CreateObject("Scripting.FileSystemObject")
' if upload directory doesn't exist returns error here
on error resume next
err.clear
Set objFile = objFSO.CreateTextFile(fileName)
if err.number <> 0 then
if err.number = 70 then
errorMSG = "<b>Permission denied.</b><br>Please check the manual for information on setting permissions to upload."
fatalError = true
elseIf err.number = 76 then
errorMSG = "<b>An error occurred.</b><br>Please ensure that you select a file to upload."
fatalError = true
else
errorMSG = "<b>An error occurred.</b><br>The error code is " & err.number
fatalError = true
end if
end if
if fatalError <> true then
For i = 1 to LenB(value)
objFile.Write chr(AscB(MidB(value,i,1)))
Next
objFile.Close
Set objFile = Nothing
Set objFSO = Nothing
End If
Set uploadRequest = Nothing
Set objFSO = Nothing
'write out schema file
writeSchema path, filename, returnDelimiter(request.queryString("d"))
' create connection string for excel file
excelDbStr = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & path & ";" & _
"Extended Properties=""text;HDR=Yes;FMT=Delimited;"";"
SQLQuery = "SELECT * FROM importProducts.csv;"
Set txtds = Server.CreateObject("ADODB.Recordset")
txtds.Open SQLQuery,excelDbStr,3,1,&H0001
'Response.Write "dfdf="
'Response.Write txtds.Fields.Count & "=" & fieldCount
'Response.end
if txtds.Fields.Count = fieldCount then
if not txtds.EOF and not txtds.BOF then
step2 = true
else
errorMSG = "<b>File empty.</b><br>Your data file is valid, however it contains no product information."
fatalError = true
end if
elseIf not txtds.Fields.Count = fieldCount and fatalError = false then
if txtds.Fields.Count = 0 and fatalError = false then
errorMSG = "<b>Error.</b><br>Please ensure that the IUSR_MACHINENAME account has read/write access on the 'Import' directory."
fatalError = true
else
errorMSG = "<b>Data file invalid.</b><br>Your data file is invalid, please ensure the format is identical to the 'Download products' file. (2)<!-- field count " & txtds.Fields.Count & " -->"
fatalError = true
end if
end if
txtds.Close
Set txtds = Nothing
If Err > 0 and fatalError = False then
errorMSG = "<b>An error occurred.</b><br>" & err.number & " - " & err.description
fatalError = true
end if
else
errorMSG = "<b>File not found.</b><br>Unable to upload file because it does not exist."
fatalError = true
end if
end If
end if ' step2Error
end if ' pageError
Function BuildUpload(RequestBin)
'Get the boundary
Dim startPos, endPos, boundary, boundaryPos, pos, posFile, posBound, fieldName
startPos = 1
endPos = InstrB(startPos,RequestBin,getByteString(chr(13)))
boundary = MidB(RequestBin,startPos,endPos-startPos)
boundaryPos = InstrB(1,RequestBin,boundary)
'Get all data inside the boundaries
Do until (boundaryPos=InstrB(RequestBin,boundary & getByteString("--")))
'Members variable of objects are put in a dictionary object
Dim UploadControl
Set UploadControl = CreateObject("Scripting.Dictionary")
'Get an object name
pos = InstrB(BoundaryPos,RequestBin,getByteString("Content-Disposition"))
pos = InstrB(Pos,RequestBin,getByteString("name="))
startPos = pos+6
endPos = InstrB(startPos,RequestBin,getByteString(chr(34)))
fieldName = getString(MidB(RequestBin,startPos,endPos-startPos))
posFile = InstrB(BoundaryPos,RequestBin,getByteString("filename="))
posBound = InstrB(endPos,RequestBin,boundary)
'Test if object is of file type
If posFile<>0 AND (PosFile<PosBound) Then
'Get Filename, content-type and content of file
startPos = posFile + 10
endPos = InstrB(startPos,RequestBin,getByteString(chr(34)))
FileName = getString(MidB(RequestBin,startPos,endPos-startPos))
'Add filename to dictionary object
UploadControl.Add "FileName", FileName
pos = InstrB(endPos,RequestBin,getByteString("Content-Type:"))
startPos = pos+14
endPos = InstrB(startPos,RequestBin,getByteString(chr(13)))
'Add content-type to dictionary object
contentType = getString(MidB(RequestBin,startPos,endPos-startPos))
UploadControl.Add "ContentType", contentType
'Get content of object
startPos = endPos + 4
endPos = InstrB(startPos,RequestBin,boundary) - 2
Value = MidB(RequestBin,startPos,endPos-startPos)
else
'Get content of object
pos = InstrB(Pos,RequestBin,getByteString(chr(13)))
startPos = pos + 4
endPos = InstrB(startPos,RequestBin,boundary) - 2
Value = getString(MidB(RequestBin,startPos,endPos-startPos))
End If
UploadControl.Add "Value" , Value
UploadRequest.Add fieldName, UploadControl
BoundaryPos=InstrB(BoundaryPos+LenB(boundary),RequestBin,boundary)
loop
End Function
Function getByteString(StringStr)
Dim char
For i = 1 to Len(StringStr)
char = mid(StringStr,i,1)
getByteString = getByteString & chrB(AscB(char))
Next
End Function
Function getString(StringBin)
getString =""
For i = 1 to LenB(StringBin)
getString = getString & chr(AscB(MidB(StringBin, i, 1)))
Next
End Function
%>
**********************************