Posted By

jeffreality on 04/24/08


Tagged

mysql database excel conversion


Versions (?)

Who likes this?

3 people have marked this snippet as a favorite

jeffreality
joelhaus
darfix


Excel spreadsheet to MySQL Table inserts


 / Published in: Visual Basic
 

  1. Sub Excel2MySQL()
  2.  
  3. ' Excel2MySQL
  4. ' Versions:
  5. ' 1.0 - Jeffrey Berthiaume - October 26, 2005 - Initial version
  6.  
  7. ' How to use:
  8. ' - name the worksheet the name of the Table you want to import into
  9. ' - name each of the columns (row 1) the name of the row
  10. ' - run the macro.
  11. ' the sql file will be saved to c:\[Tablename].sql
  12.  
  13. Open "c:\" & Sheet1.Name & ".sql" For Output As #1
  14.  
  15. totalrows = ActiveSheet.UsedRange.Rows.Count
  16. totalcols = ActiveSheet.UsedRange.Columns.Count
  17.  
  18. colnames = ""
  19. For y = 1 To totalcols
  20. colnames = colnames & Cells(1, y)
  21. If y < totalcols Then
  22. colnames = colnames & ","
  23. End If
  24. Next y
  25.  
  26. For x = 2 To totalrows
  27. s = "INSERT INTO " & Sheet1.Name & " (" & colnames & ") VALUES ("
  28. For y = 1 To totalcols
  29. s = s & "'" & Replace(Cells(x, y).Value, "'", "\'") & "'"
  30. If y < totalcols Then
  31. s = s & ","
  32. Else
  33. s = s & ");"
  34. Print #1, s
  35. End If
  36. Next y
  37. Next x
  38.  
  39. Close #1
  40.  
  41. End Sub

Report this snippet  

You need to login to post a comment.