{"id":2125,"date":"2013-03-08T19:14:11","date_gmt":"2013-03-08T10:14:11","guid":{"rendered":"http:\/\/fujiitoshiki.com\/improvesociety\/?p=2125"},"modified":"2014-07-31T13:48:23","modified_gmt":"2014-07-31T04:48:23","slug":"how-to-export-excel-worksheets-to-database-with-sql-statement-file","status":"publish","type":"post","link":"https:\/\/www.fujiitoshiki.com\/improvesociety\/?p=2125","title":{"rendered":"How to export Excel worksheets to database with SQL statement file."},"content":{"rendered":"

When you’d like to register Excel worksheets in database, you could ‘BULK INSERT’ txt file into database. In this article, I’d like to explain VBA code that exports Excel worksheets to sql file. It’s assumed that worksheet name is same as table name and first column order is same as table attribute order. <\/p>\n

Option Explicit\r\n\r\nSub EXPORT_SQL()\r\n    Dim mySht   As Worksheet\r\n    Dim myAr    As Variant\r\n    Dim i       As Long\r\n    Dim j       As Long\r\n    Dim mySQL   As String\r\n    Dim SQLAr() As String\r\n    Dim myFSO   As Object\r\n    Dim myTS    As Object\r\n    Dim myPath  As String\r\n    Dim n       As Long\r\n    myPath = ThisWorkbook.Path\r\n    For Each mySht In Worksheets\r\n        myAr = mySht.UsedRange.Resize(mySht.UsedRange.Rows.Count - 1).Offset(1)\r\n        ReDim SQLAr(LBound(myAr) To UBound(myAr))\r\n        For i = LBound(myAr) To UBound(myAr)\r\n            For j = LBound(myAr, 2) To UBound(myAr, 2)\r\n                If myAr(i, j) = Empty Then\r\n                    mySQL = mySQL & \"NULL, \"\r\n                Else\r\n                    mySQL = mySQL & \"'\" & myAr(i, j) & \"', \"\r\n                End If\r\n            Next j\r\n            mySQL = \"INSERT INTO \" & mySht.Name & \" VALUES (\" & Left(mySQL, Len(mySQL) - 2) & \")\"\r\n            SQLAr(i) = mySQL\r\n            mySQL = \"\"\r\n        Next i\r\n        Set myFSO = CreateObject(\"Scripting.FileSystemObject\")\r\n        Set myTS = myFSO.CreateTextFile(Filename:=myPath & \"\\\" & mySht.Name & \".sql\", Overwrite:=True)\r\n        For n = LBound(SQLAr) To UBound(SQLAr)\r\n            myTS.writeline SQLAr(n)\r\n        Next n\r\n        myTS.Close\r\n        Set myFSO = Nothing\r\n        Set myTS = Nothing\r\n    Next mySht\r\nEnd Sub\r\n<\/pre>\n