Advanced Search

Excel spreadsheet to MySQL Table inserts

/ Published in: Visual Basic
Save to your folder(s)

Expand | Embed | Plain Text

Copy this code and paste it in your HTML 
  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


Comments

RSS Icon Subscribe to comments

You need to login to post a comment.