Excel spreadsheet to MySQL Table inserts - Visual Basic Snipplr Social Repository

Revision: 6074

at April 24, 2008 17:41 by jeffreality

Initial Code

Sub Excel2MySQL()

  ' Excel2MySQL
  ' Versions:
  ' 1.0 - Jeffrey Berthiaume - October 26, 2005 - Initial version

  ' How to use:
  ' - name the worksheet the name of the Table you want to import into
  ' - name each of the columns (row 1) the name of the row
  ' - run the macro.
  ' the sql file will be saved to c:\[Tablename].sql

  Open "c:\" & Sheet1.Name & ".sql" For Output As #1
  
  totalrows = ActiveSheet.UsedRange.Rows.Count
  totalcols = ActiveSheet.UsedRange.Columns.Count
  
  colnames = ""
  For y = 1 To totalcols
    colnames = colnames & Cells(1, y)
    If y < totalcols Then
      colnames = colnames & ","
    End If
  Next y
  
  For x = 2 To totalrows
    s = "INSERT INTO " & Sheet1.Name & " (" & colnames & ") VALUES ("
    For y = 1 To totalcols
      s = s & "'" & Replace(Cells(x, y).Value, "'", "\'") & "'"
      If y < totalcols Then
        s = s & ","
      Else
        s = s & ");"
        Print #1, s
      End If
    Next y
  Next x
  
  Close #1
  
End Sub

Initial URL

Initial Description

Initial Title

Excel spreadsheet to MySQL Table inserts

Initial Tags

mysql, database, excel

Initial Language

Visual Basic

Choose a language for easy browsing: