会python真的可以为所欲为0(回)
334天前
这里还有人吗1(回)
519天前这里还有人吗0(回)
519天前
每天面对着电脑屏幕，敲打键盘。我所面对的并不只是代码，而是一种生活方式。0(回)
743天前到处都是羊，不想上班0(回)
823天前
鸽子
0(回)
828天前Public Sub CreateAllSheetsInsertScript() |
Dim Row As Long |
Dim Col As Integer |
|
'To store all the columns available in the all of the worksheets |
Dim ColNames(100) As String |
Dim ColCount As Integer |
Dim MaxRow As Long |
Dim CellColCount As Integer |
Dim StringStore As String 'Temporary variable to store partial statement |
Dim filePath As String, DBname As String |
DBname = "DB2" |
Application.ScreenUpdating = False |
'File to save the generated insert statements |
filePath = "C:\\import.sql" |
File = filePath |
fHandle = FreeFile() |
Open File For Output As fHandle |
Dim sh As Worksheet |
For Each sh In ActiveWorkbook.Sheets |
With sh |
.Select |
Col = 1 |
Row = 1 |
ColCount = 0 |
'Get Columns from the sheet |
Do Until .Cells(Row, Col) = "" 'Loop until you find a blank. |
ColNames(ColCount) = "[" & .Cells(Row, Col) & "]" |
ColCount = ColCount + 1 |
Col = Col + 1 |
Loop |
ColCount = ColCount - 1 |
'Inputs for the starting and ending point for the rows |
Row = 2 |
MaxRow = .[A1].End(xlDown).Row |
Do While Row <= MaxRow |
CellColCount = 0 |
'.Name will give the current active sheet name |
'this can be treated as table name in the database |
StringStore = "INSERT INTO [dbo].[Material_price_new] ( " |
Do While CellColCount <= ColCount |
StringStore = StringStore & ColNames(CellColCount) |
'To avoid "," after last column |
If CellColCount <> ColCount Then |
StringStore = StringStore & " , " |
End If |
CellColCount = CellColCount + 1 |
Loop |
'Here it will print "insert into [TableName] ( [Col1] , [Col2] , ..." |
Print #fHandle, StringStore & " ) " |
|
'For printing the values for the above columns |
StringStore = " VALUES ( " |
CellColCount = 0 |
Do While CellColCount <= ColCount |
StringStore = StringStore & IIf(Len(Trim(.Cells(Row, CellColCount + 1).Value)) = 0, "NULL", " '" & Replace(CStr(.Cells(Row, CellColCount + 1)), "'", "''") & "'") |
If CellColCount <> ColCount Then |
StringStore = StringStore & ", " |
End If |
CellColCount = CellColCount + 1 |
Loop |
'Here it will print "values( 'value1', 'value2', ..." |
Print #fHandle, StringStore & ")" & vbCrLf & IIf(Row Mod 5000 = 1, "GO" & vbCrLf, "") & " " |
Row = Row + 1 |
Loop |
End With |
Next sh |
Close #fHandle |
Application.ScreenUpdating = True |
Shell "CMD /C OSQL -E -d " & DBname & " -i """ & filePath & """ > nul" |
MsgBox ("Successfully Done") |
End Sub |
by: 发表于：2017-12-01 14:57:08 顶(0) | 踩(0) 回复
??
回复评论