{"id":2122,"date":"2013-03-08T19:13:52","date_gmt":"2013-03-08T10:13:52","guid":{"rendered":"http:\/\/fujiitoshiki.com\/improvesociety\/?p=2122"},"modified":"2014-07-31T13:48:48","modified_gmt":"2014-07-31T04:48:48","slug":"post-2122","status":"publish","type":"post","link":"https:\/\/www.fujiitoshiki.com\/improvesociety\/?p=2122","title":{"rendered":"EXCEL\u306e\u30ef\u30fc\u30af\u30b7\u30fc\u30c8\u3092\u30c6\u30fc\u30d6\u30eb\u306b\u633f\u5165\u3059\u308bSQL\u30b9\u30c6\u30fc\u30c8\u30e1\u30f3\u30c8\u30d5\u30a1\u30a4\u30eb\u3092\u4f5c\u6210\u3059\u308bVBA\u30b3\u30fc\u30c9"},"content":{"rendered":"

\u3000Excel \u306e\u30ef\u30fc\u30af\u30b7\u30fc\u30c8\u3092\u30c7\u30fc\u30bf\u30d9\u30fc\u30b9\u306b\u767b\u9332\u3059\u308b\u306b\u306f\u3044\u304f\u3064\u304b\u306e\u65b9\u6cd5\u304c\u3042\u308a\u307e\u3059\uff0etxt \u30d5\u30a1\u30a4\u30eb\u3092 BULK INSERT \u3059\u308b\u65b9\u6cd5\u3082\u3042\u308a\u307e\u3059\u304c\uff0c\u4eca\u56de\u306f\u30ef\u30fc\u30af\u30b7\u30fc\u30c8\u306e\u30bb\u30eb\u5185\u5bb9\u3092 INSERT \u3059\u308b\u30af\u30a8\u30ea\u3092 sql \u30d5\u30a1\u30a4\u30eb\u306b\u66f8\u304d\u51fa\u3059 VBA \u30b3\u30fc\u30c9\u3092\u8aac\u660e\u3057\u307e\u3059\uff0e\u30ef\u30fc\u30af\u30b7\u30fc\u30c8\u540d\u306f\u30c6\u30fc\u30d6\u30eb\u540d\u3068\u540c\u3058\u306b\u306a\u3063\u3066\u304a\u308a\uff0c\u30ef\u30fc\u30af\u30b7\u30fc\u30c8\u306e1\u884c\u76ee\u306f\u30c6\u30fc\u30d6\u30eb\u306e\u5c5e\u6027\u540d\u3068\u540c\u4e00\u306e\u9806\u756a\u306b\u306a\u3063\u3066\u3044\u308b\u3068\u306e\u524d\u63d0\u3067\u8a71\u3092\u9032\u3081\u307e\u3059\uff0eExcel \u306e\u30d5\u30a1\u30a4\u30eb\u3068\u540c\u4e00\u5834\u6240\u306b sql \u30d5\u30a1\u30a4\u30eb\u304c\u30b7\u30fc\u30c8\u306e\u6570\u3060\u3051\u4f5c\u6210\u3055\u308c\u307e\u3059\uff0e<\/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