SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    Xbox why have you forsaken me? moospot's Avatar
    Join Date
    Feb 2001
    Location
    Clearwater, FL
    Posts
    3,615
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Upload and Parse CSV file?

    Has anyone ever done this sort of thing? I was wondering what the best approach to it would be. I just purchased the aspSmartUpload and aspSmartImage component to facilitate the self-management of our vendors. Now, I want them to have the ability to upload a CSV file for bulk submitting their products into our database.

    Here is how I planned to do it.
    1. Have them upload the file
    2. Use FSO to open the file
    3. loop through each line, using the split function to create an array
    4. Update the DB with the values from the array
    I have some code here. Is there a more efficient way of doing this? I was going to limit the file size to 1 MB, but I have yet to test it out.

    Code:
    Const ForReading = 1
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    Set objTextFile = objFSO.OpenTextFile("upload.csv", ForReading)
    
    Do While objTextFile.AtEndOfStream <> True 
    
    If inStr(objtextFile.Readline, ",") Then 
    arrDHCPRecord = split(objTextFile.Readline, ",") 
    
    'INSERT records here
    Else 
    objTextFile.Skipline 
    End If 
    i = i + 1
    Loop
    objTextFile.Close

  2. #2
    ☆★☆★ silver trophy vgarcia's Avatar
    Join Date
    Jan 2002
    Location
    in transition
    Posts
    21,236
    Mentioned
    1 Post(s)
    Tagged
    1 Thread(s)
    moospot,

    I see one bit of inefficiency, albeit insignificant. Where you have this:
    Code:
    Do While objTextFile.AtEndOfStream <> True
    ...
    Loop
    Change it to this:
    Code:
    Do Until objTextFile.AtEndOfStream
    ...
    Loop
    Yeah, it only saves 8 bytes, but every little bit matters!

  3. #3
    Xbox why have you forsaken me? moospot's Avatar
    Join Date
    Feb 2001
    Location
    Clearwater, FL
    Posts
    3,615
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for the tip!

  4. #4
    SitePoint Wizard wdmny's Avatar
    Join Date
    Jul 2000
    Location
    Here
    Posts
    1,010
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    If you write a simple file access component in VB/C++ you will get better performance. I actually have one (VB6) for another purpose that supports writing, appending, reading, and deleting. If you want, just let me know and I can get it to you, plus a class that will interface it. Email me from my post's email link.

  5. #5
    Xbox why have you forsaken me? moospot's Avatar
    Join Date
    Feb 2001
    Location
    Clearwater, FL
    Posts
    3,615
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    emailed

  6. #6
    The doctor is in... silver trophy MarcusJT's Avatar
    Join Date
    Jan 2002
    Location
    London
    Posts
    3,509
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I would have thought that rather than using the FSO you'd be much better off using ADO to connect directly to the CSV file (see here), and then looping through the resulting recordset, or perhaps using GetRows to convert to an array and looping through that...

    Or, even better (but untested), given that by its very nature the CSV contains one record per line and each field separated by commas, you could use Split() to convert the line into a 1D array and then use the RecordSet.AddNew FieldNames, FieldValues syntax to add a row at a time. ADO *should* automatically convert between datatypes, since each array element will be a variant string.

    e.g.
    Code:
    CSVLine = "13,frog,2.2,0.5"
    FieldValues = Split(CSVLine,",")
    FieldNames = Array("age","type","length","weight")
    RecordSet.AddNew FieldNames, FieldValues
    Of course this would happen inside the inner loop, with FieldNames only being set once and each CSVLine being a new line read from the CSV, but I'm sure you get the gist. Give that a shot.

    MarcusJT
    - former ASP web developer / former SPF "ASP Guru"
    - *very* old blog with some useful ASP code

    - Please think, Google, and search these forums before posting!

  7. #7
    Xbox why have you forsaken me? moospot's Avatar
    Join Date
    Feb 2001
    Location
    Clearwater, FL
    Posts
    3,615
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I actually am splitting the rows into an array and usign recordsets to update the database. But, I am not using ADO as you suggested. I didn't know you could do that with a text file! That seems a lot easier and its probably a heckuva lot faster, too.

  8. #8
    Xbox why have you forsaken me? moospot's Avatar
    Join Date
    Feb 2001
    Location
    Clearwater, FL
    Posts
    3,615
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Code:
    Sub ProcessCSV(filename)
     'Open Database
     set rs = server.CreateObject("adodb.recordset")
     set objConn = Server.CreateObject("ADODB.connection") 
     
     'define path to csv file
     path = Server.MapPath("/pathgoeshere" & filename)
     'response.write path
     
     'Open CSV file
     Const ForReading = 1
     Set objFSO = CreateObject("Scripting.FileSystemObject")
     Set objTextFile = objFSO.OpenTextFile(path)
     
     
     Do While objTextFile.AtEndOfStream = false
     
      txt = objtextFile.Readline
     
      If inStr(txt, "Dept_ID") then
       'skip the line
      Else
     
       If inStr(txt, ",") Then
    	arrRecord = split(txt, ",")   
    	
    	
    	'loop through array elements to make sure there are no null values
    	'while we are at it, trim the good elements to eliminate any extra spaces
    	UpdateFlag = True
    	
    	For x = LBound(arrRecord) To UBound(arrRecord)
    	 if IsNull(arrRecord(x)) or len(arrRecord(x)) <= 0 then
    	  'go to the next record
    	  UpdateFlag = False
    	 else
    	  arrRecord(x) = Trim(arrRecord(x))
    	 end if
    	Next
    	
    	If UpdateFlag = True then
    	
    	 'assign variables to make sense out of array
    	 dept_id = arrRecord(0)
    	 item_number = arrRecord(1)
    	 name = arrRecord(2)
    	 description = arrRecord(3)
    	 image_file = arrRecord(4)
    	 ac = arrRecord(5)
    	 in_stock = arrRecord(6)
    	 weight = arrRecord(7)
    	
    	
    	 'Do necessary calculations and concatenations   
    	 parent_id = Left(dept_id, 1)
    	 
    	 if parent_id = "5" then
    	  sku = VendorID & "-" & arrRecord(1) & "-" & width & "-" & size
    	 else
    	  sku = VendorID & "-" & arrRecord(1)
    	 end if
    	
    	 list_price = (ac / (1 - markup)) + shipping
    	 
    	 'This is where you update the DB
    	 cmdTemp.CommandText = Replace("SELECT * FROM store_product WHERE sku = ?", "?", "'" & Replace(sku,"'","''") & "'")
    	 Set rsExe = Server.CreateObject("ADODB.Recordset")
    	 On Error Resume Next
    	 rsExe.Open cmdTemp, , adOpenStatic, adLockOptimistic
    	 
    	 if Err.Number <> 0 then
    	  Set errorList = Server.CreateObject("Commerce.SimpleList")
    	  errorList.Add "The table could not be found or the database connection failed."
    	 end if
    	 
    	 rsExe.AddNew
    	 rsExe("sku").value = sku
    	 rsExe("name").Value = name
    	 rsExe("description").Value = description
    	 rsExe("list_price").Value = list_price
    	 rsExe("image_file").Value = image_file
    	 rsExe("vendorid").Value = VendorID
    	 rsExe("weight").Value = weight
    	 rsExe("ac").Value = ac
    	 rsExe("in_stock").Value = in_stock
    	 rsExe("item_number").value = item_number
    	 rsExe("date_entered").value = FormatDateTime(Date, 2)  
    	 rsExe.Update
    	
    	 if Err.Number <> 0 then
    	  if Err.Number = UNIQUE_CONSTRAINT then
    	   errorList.Add "A product with that sku already exists."
    	  else
    	   errorList.Add "Database error when adding product: " & Err.Number & " (0x" & Hex(Err.Number) & ")<BLOCKQUOTE>" & Err.Description  & "</BLOCKQUOTE>"
    	  end if
    	 end if
    	 'err.Clear
    	
    	 cmdTemp.CommandText = Replace("DELETE FROM store_dept_prod WHERE sku = ?", "?", "'" & Replace(sku,"'","''") & "'")
    	 Set rsExe = Server.CreateObject("ADODB.Recordset")
    	 rsExe.Open cmdTemp, , adOpenStatic, adLockOptimistic
       
    	 sqlText = "INSERT INTO store_dept_prod VALUES (:1, :2)"
    	 sqlText = Replace(sqlText, ":1", dept_id) 'dept_id
    	 sqlText = Replace(sqlText, ":2", "'" & Replace(sku,"'","''") & "'") 'sku
    	 cmdTemp.CommandText = sqlText
    	 Set rsExe = Server.CreateObject("ADODB.Recordset")
    	 rsExe.Open cmdTemp, , adOpenStatic, adLockOptimistic
      
    	
    	 if Err.Number <> 0 then
    	  errorList.Add "Database error when adding departments: " & Err.Number & "(0x" & Hex(Err.Number) & ")<BLOCKQUOTE>" & Err.Description  & "</BLOCKQUOTE>"
    	 end if
    	 'err.Clear
    	 
    	 
    	 Select Case parent_id
    	 
    	  Case "4"
    	   if UBound(arrRecord) = 13 then
    		p_name = arrRecord(9)
    		p_manf = arrRecord(10)
    		p_size = arrRecord(11)
    		p_type = arrRecord(12)
    		p_gender = arrRecord(13)
    	  
    		Call UpdatePerfumeTable(sku, p_name, p_manf, p_size, p_type, p_gender)
    	   end if	
    	  
    	  Case "5"
    	   if UBound(arrRecord) = 15 then
    		s_width = arrRecord(9)
    		s_brand = arrRecord(9)
    		s_style = arrRecord(11)
    		s_category = arrRecord(12)
    		s_size = arrRecord(13)
    		s_color = arrRecord(14)
    		s_gender = arrRecord(15)
    	   
    		Call UpdateShoeTable(sku, s_width, s_brand, s_style, s_category, s_size, s_color, s_gender)
    	   end if
    	   
    	  Case Else
    	   'do nothing
    	
    	 End Select 
    	 
    	 if errorList.Count = 0 then
    	  AddProd = True
    	  i = i + 1
    	 else
    	  for each err in errorList
    	   response.write (err & "<br />")
    	  next
    	 end if
    	 
    	End If ' End UpdateFlag Check
    	
       End If ' End If inStr(txt, ",")
       
      End If ' End If inStr(txt, "Dept_ID")
      
     Loop
     objTextFile.Close
     
     
     set objConn = Nothing
     set rs = Nothing
     response.write (i & " records added.")   
     
    End Sub


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •