IT工作備忘錄 – 第 3 頁 – 我的工作紀錄

測試使用chatgpt產生語法針對excel檔案按照某欄資料分組，各組資料複製到各自新的頁面

第一次指令 design VBA code to move data from Sheet1 to newly inserted sheets based on certain group

Sub MoveData()

    Dim lastRow As Long
    Dim i As Long
    Dim groupName As String
    Dim ws As Worksheet
    Dim newWs As Worksheet
    
    'Get last row of data in Sheet1
    lastRow = ThisWorkbook.Worksheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
    
    'Loop through each row of data in Sheet1
    For i = 2 To lastRow 'Assuming data starts in row 2
        
        'Get the group name from column A
        groupName = ThisWorkbook.Worksheets("Sheet1").Cells(i, 1).Value
        
        'Check if there is already a worksheet with the group name
        On Error Resume Next
        Set ws = ThisWorkbook.Worksheets(groupName)
        On Error GoTo 0
        
        'If there is no worksheet with the group name, insert a new worksheet
        If ws Is Nothing Then
            Set newWs = ThisWorkbook.Worksheets.Add(after:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count))
            newWs.Name = groupName
        Else
            Set newWs = ws
        End If
        
        'Copy the data from Sheet1 to the new worksheet
        ThisWorkbook.Worksheets("Sheet1").Rows(i).Copy Destination:=newWs.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
        
    Next i
    
End Sub

第二次指令 please use 4th column as group and copy first row as header to every new sheet

Sub MoveData()

    Dim lastRow As Long
    Dim i As Long
    Dim groupName As String
    Dim ws As Worksheet
    Dim newWs As Worksheet
    
    'Get last row of data in Sheet1
    lastRow = ThisWorkbook.Worksheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
    
    'Copy header row to every new sheet
    ThisWorkbook.Worksheets("Sheet1").Rows(1).Copy
    For Each ws In ThisWorkbook.Worksheets
        If ws.Name <> "Sheet1" Then
            ws.Rows(1).PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
        End If
    Next ws
    
    'Loop through each row of data in Sheet1
    For i = 2 To lastRow 'Assuming data starts in row 2
        
        'Get the group name from column D
        groupName = ThisWorkbook.Worksheets("Sheet1").Cells(i, 4).Value
        
        'Check if there is already a worksheet with the group name
        On Error Resume Next
        Set ws = ThisWorkbook.Worksheets(groupName)
        On Error GoTo 0
        
        'If there is no worksheet with the group name, insert a new worksheet
        If ws Is Nothing Then
            Set newWs = ThisWorkbook.Worksheets.Add(after:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count))
            newWs.Name = groupName
        Else
            Set newWs = ws
        End If
        
        'Copy the data from Sheet1 to the new worksheet
        ThisWorkbook.Worksheets("Sheet1").Rows(i).Copy Destination:=newWs.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
        
    Next i
    
End Sub

第三次指令 please change your procedure,first get distinct group from column d ,second create sheets for each group then copy header from sheet1 finally move data. 1.先分組 2.建立所有頁面 3.複製標題 4.複製相對資料

Sub MoveData()

    Dim lastRow As Long
    Dim i As Long
    Dim j As Long
    Dim groupName As String
    Dim groupNames() As String
    Dim ws As Worksheet
    Dim newWs As Worksheet
    Dim found As Boolean
    
    'Get last row of data in Sheet1
    lastRow = ThisWorkbook.Worksheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
    
    'Get distinct group names from column D
    ReDim groupNames(0 To 0)
    groupNames(0) = ThisWorkbook.Worksheets("Sheet1").Cells(2, 4).Value 'Assuming data starts in row 2
    For i = 2 To lastRow
        groupName = ThisWorkbook.Worksheets("Sheet1").Cells(i, 4).Value
        found = False
        For j = 0 To UBound(groupNames)
            If groupName = groupNames(j) Then
                found = True
                Exit For
            End If
        Next j
        If Not found Then
            ReDim Preserve groupNames(0 To UBound(groupNames) + 1)
            groupNames(UBound(groupNames)) = groupName
        End If
    Next i
    
    'Create new worksheet for each group name
    For i = 0 To UBound(groupNames)
        Set newWs = ThisWorkbook.Worksheets.Add(after:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count))
        newWs.Name = groupNames(i)
        ThisWorkbook.Worksheets("Sheet1").Rows(1).Copy
        newWs.Rows(1).PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    Next i
    
    'Loop through each row of data in Sheet1 and move to appropriate worksheet
    For i = 2 To lastRow 'Assuming data starts in row 2
        groupName = ThisWorkbook.Worksheets("Sheet1").Cells(i, 4).Value
        Set ws = ThisWorkbook.Worksheets(groupName)
        ThisWorkbook.Worksheets("Sheet1").Rows(i).Copy Destination:=ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
    Next i
    
End Sub

before creating a new worksheet check if alread exists

before insert header row clear datas in that sheet

Sub MoveData()

    Dim lastRow As Long
    Dim i As Long
    Dim j As Long
    Dim groupName As String
    Dim groupNames() As String
    Dim ws As Worksheet
    Dim newWs As Worksheet
    Dim found As Boolean
    
    'Get last row of data in Sheet1
    lastRow = ThisWorkbook.Worksheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
    
    'Get distinct group names from column D
    ReDim groupNames(0 To 0)
    groupNames(0) = ThisWorkbook.Worksheets("Sheet1").Cells(2, 4).Value 'Assuming data starts in row 2
    For i = 2 To lastRow
        groupName = ThisWorkbook.Worksheets("Sheet1").Cells(i, 4).Value
        found = False
        For j = 0 To UBound(groupNames)
            If groupName = groupNames(j) Then
                found = True
                Exit For
            End If
        Next j
        If Not found Then
            ReDim Preserve groupNames(0 To UBound(groupNames) + 1)
            groupNames(UBound(groupNames)) = groupName
        End If
    Next i
    
    'Create new worksheet for each group name if it doesn't already exist
    For i = 0 To UBound(groupNames)
        found = False
        For Each ws In ThisWorkbook.Worksheets
            If ws.Name = groupNames(i) Then
                found = True
                Set newWs = ws
                Exit For
            End If
        Next ws
        If Not found Then
            Set newWs = ThisWorkbook.Worksheets.Add(after:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count))
            newWs.Name = groupNames(i)
            newWs.Cells.ClearContents 'Clear any existing data in the new worksheet
            ThisWorkbook.Worksheets("Sheet1").Rows(1).Copy
            newWs.Rows(1).PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
        End If
    Next i
    
    'Loop through each row of data in Sheet1 and move to appropriate worksheet
    For i = 2 To lastRow 'Assuming data starts in row 2
        groupName = ThisWorkbook.Worksheets("Sheet1").Cells(i, 4).Value
        Set ws = ThisWorkbook.Worksheets(groupName)
        ThisWorkbook.Worksheets("Sheet1").Rows(i).Copy Destination:=ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
    Next i
    
End Sub

prompt to get the group column number before starting

Sub MoveData()

    Dim lastRow As Long
    Dim i As Long
    Dim j As Long
    Dim groupName As String
    Dim groupNames() As String
    Dim ws As Worksheet
    Dim newWs As Worksheet
    Dim found As Boolean
    Dim groupCol As Long
    
    'Prompt user for group column number
    groupCol = Application.InputBox("Enter the column number for the group:", Type:=1)
    
    'Get last row of data in Sheet1
    lastRow = ThisWorkbook.Worksheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
    
    'Get distinct group names from specified column
    ReDim groupNames(0 To 0)
    groupNames(0) = ThisWorkbook.Worksheets("Sheet1").Cells(2, groupCol).Value 'Assuming data starts in row 2
    For i = 2 To lastRow
        groupName = ThisWorkbook.Worksheets("Sheet1").Cells(i, groupCol).Value
        found = False
        For j = 0 To UBound(groupNames)
            If groupName = groupNames(j) Then
                found = True
                Exit For
            End If
        Next j
        If Not found Then
            ReDim Preserve groupNames(0 To UBound(groupNames) + 1)
            groupNames(UBound(groupNames)) = groupName
        End If
    Next i
    
    'Create new worksheet for each group name if it doesn't already exist
    For i = 0 To UBound(groupNames)
        found = False
        For Each ws In ThisWorkbook.Worksheets
            If ws.Name = groupNames(i) Then
                found = True
                Set newWs = ws
                Exit For
            End If
        Next ws
        If Not found Then
            Set newWs = ThisWorkbook.Worksheets.Add(after:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count))
            newWs.Name = groupNames(i)
            newWs.Cells.ClearContents 'Clear any existing data in the new worksheet
            ThisWorkbook.Worksheets("Sheet1").Rows(1).Copy
            newWs.Rows(1).PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
        End If
    Next i
    
    'Loop through each row of data in Sheet1 and move to appropriate worksheet
    For i = 2 To lastRow 'Assuming data starts in row 2
        groupName = ThisWorkbook.Worksheets("Sheet1").Cells(i, groupCol).Value
        Set ws = ThisWorkbook.Worksheets(groupName)
        ThisWorkbook.Worksheets("Sheet1").Rows(i).Copy Destination:=ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
    Next i
    
End Sub

最終結果與預期需求相符，感想：優點過程中打錯字或是文法不對，chatGPT還是能夠大致了解，感覺語文理解力很強，程式範例庫眾多，基礎語法與程式架構標準化。缺點是流程運行前後邏輯較差，透過子模組切分與流程先後串聯提示可改善。

bing + Chatgpt 4.0 不須指示先按group建立sheet，coding 能力似乎又強化

Sub MoveData()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long
    Dim groupCol As Long
    Dim sheetName As String
    
    'Delete all sheets except Sheet1
    Application.DisplayAlerts = False
    For Each ws In ThisWorkbook.Sheets
        If ws.Name <> "Sheet1" Then
            ws.Delete
        End If
    Next ws
    Application.DisplayAlerts = True
    
    'Move data from Sheet1 to newly inserted sheets based on group
    Set ws = ThisWorkbook.Sheets("Sheet1")
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    groupCol = Application.InputBox("Enter the column number of the group:", "Group Column", Type:=1)
    
    For i = 2 To lastRow
        sheetName = ws.Cells(i, groupCol).Value
        If Not SheetExists(sheetName) Then
            ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)).Name = sheetName
            ws.Rows(1).Copy Destination:=ThisWorkbook.Sheets(sheetName).Cells(1, 1)
        End If
        ws.Rows(i).Copy Destination:=ThisWorkbook.Sheets(sheetName).Cells(ThisWorkbook.Sheets(sheetName).Cells(ThisWorkbook.Sheets(sheetName).Rows.Count, 1).End(xlUp).Row + 1, 1)
    Next i
End Sub

Function SheetExists(sheetName As String) As Boolean
    Dim ws As Worksheet
    On Error Resume Next
    Set ws = ThisWorkbook.Sheets(sheetName)
    On Error GoTo 0
    SheetExists = Not ws Is Nothing
End Function

move_data 下載

自動彙整資料夾下所有Excel檔案資料到該檔案並增加計算欄位

Sub CombineExcelFilesFirstSheetExceptFirstRow()
Dim Path As String, Filename As String, Sheet As Worksheet, LastRow As Long, i As Integer, cFilename As String
Dim wb As Workbook, DestSheet As Worksheet, LastDestRow As Long, Owb As Workbook
ClearAllRowsExceptFirst
Application.ScreenUpdating = False
Application.CutCopyMode = False
‘ Get the path of the folder that contains the current workbook
Path = ThisWorkbook.Path & “\”
Set Owb = ActiveWorkbook
‘ Loop through all the Excel files in the folder
Filename = Dir(Path & “.xlsx“)
cFilename = ThisWorkbook.Name
Do While Filename <> “”
‘ Exclude the current workbook from the merge
If Filename <> cFilename Then
‘ Open each Excel file in the folder
Set wb = Workbooks.Open(Filename:=Path & Filename, ReadOnly:=True)
‘ Copy the data from the first worksheet of the Excel file, excluding the first row
With wb.Sheets(1)
LastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
If LastRow > 1 Then
.Range(“A2”).Resize(LastRow – 1, .UsedRange.Columns.Count).Copy Destination:=Owb.ActiveSheet.Range(“A” & Rows.Count).End(xlUp).Offset(1, 0)
End If
End With
‘ Close the Excel file
wb.Close
End If
‘ Move to the next Excel file in the folder
Filename = Dir()
Loop
‘ Adjust the column widths in the current worksheet
‘For i = 1 To ActiveSheet.Columns.Count
‘ ActiveSheet.Columns(i).AutoFit
‘Next i
AddConditionalFormulaToColumn
Application.ScreenUpdating = True
Application.CutCopyMode = True
End Sub

Sub ClearAllRowsExceptFirst()
With ActiveSheet
.Range(“A2:” & .Cells(.Rows.Count, .Columns.Count).Address).ClearContents
End With
End Sub

Sub AddConditionalFormulaToColumn()
Dim lastRow As Long
lastRow = Cells(Rows.Count, “A”).End(xlUp).Row ‘ Assumes data is in column A
Range(“U2:U” & lastRow).Formula = “=ROUND(IF(L2=””三聯式””,R2+S2,R2)*IF(B2=””STW0192″”,0.5,1),0)”
‘ Alternatively, you can use the FormulaR1C1 property to add the formula
‘ in R1C1 notation:
‘Range(“C2:C” & lastRow).FormulaR1C1 = “=IF(RC[-1]=””STW0192″”,0.5,1)”
End Sub

Excel公式加上大括號{}的用法

當一個公式中，含有返回一組數的表達式時，需使用數組進行運算。在公式輸入完成之後，按ctrl+shift+enter三鍵，會自動加上大括號{}{=SUM(IF(MOD(COLUMN($D4:$LB4),10)=4,$D4:$LB4,0))} 指加總範圍內每10欄的第四欄。

參考 https://www.dotblogs.com.tw/areswang/2020/10/26/Dynamic_array_formulas_2

CROSS join 常數table產生多筆資料如月份

select
m.site,
m.cal_year,
m.currency_id,
m.main_account,
m.sub_account,
m.project,
m.dept_no,
X.a,
0,
0,
0,
0,
‘XYZ’,
‘2023-02-21 12:00:00’
from
sybase64.erpdb.dbo.acc_balm m
left outer join sybase64.erpdb.dbo.acc_bald d on m.site = d.site
and m.cal_year = d.cal_year
and m.main_account = d.main_account
and m.currency_id = d.currency_id
and m.sub_account = d.sub_account
and m.project = d.project
and m.dept_no = d.dept_no cross
join (
VALUES
(’01’),
(’02’),
(’03’),
(’04’),
(’05’),
(’06’),
(’07’),
(’08’),
(’09’),
(’10’),
(’11’),
(’12’),
(’13’)
) AS X(a)
where
m.site = ‘CF’
and m.cal_year = ‘2023’
and m.main_account = ‘1122’
and d.site is null;

Excel 新增自訂 function 數字轉大寫中文

Function Num2Str(Str As String) As String
For i = 1 To Len(Str)
a = Mid(Str, i, 1)
Select Case True
Case a = 0
aa = aa & “　零”
Case a = 1
aa = aa & “　壹”
Case a = 2
aa = aa & “　貳”
Case a = 3
aa = aa & “　參”
Case a = 4
aa = aa & “　肆”
Case a = 5
aa = aa & “　伍”
Case a = 6
aa = aa & “　陸”
Case a = 7
aa = aa & “　柒”
Case a = 8
aa = aa & “　捌”
Case a = 9
aa = aa & “　玖”
Case Else
aa = aa & a
End Select
Next i
aa = Right(“零　零　零　零　零　零　零　零　零” & aa, 18)
bb = Left(aa, 6) & ” ” & Mid(aa, 7, 2) & ” ” & Mid(aa, 9, 2) & ” ” & Mid(aa, 11, 2) & Right(aa, 6)
Num2Str = bb
End Function

Excel Button Click with ERROR handler

Sub 按鈕1_Click()
‘On Error Resume Next
On Error GoTo MyErrorHandler:
s_line = Cells(1, 8)
e_line = Cells(1, 9)
If Not IsNumeric(s_line) Then
MsgBox (“請輸入數字”)
End
End If
If Not IsNumeric(e_line) Then
MsgBox (“請輸入數字”)
End
End If
If s_line > e_line Then
MsgBox (“請輸入正確起訖”)
End
End If
If e_line – s_line > 4 Then
MsgBox (“最多輸入5筆資料”)
End
End If
Range(“C5:F9”).Value = “”

For i = 1 To e_line - s_line + 1
    'Cells(4 + i, 3).Value = Worksheets("shipment").Cells(s_line + i - 1, 14).Value
    Cells(4 + i, 3).Value = Application.WorksheetFunction.VLookup(Worksheets("shipment").Cells(s_line + i - 1, 6).Value, Worksheets("item_ref").Range("A:E"), 2, False)
    Cells(4 + i, 5).Value = Worksheets("shipment").Cells(s_line + i - 1, 7).Value
    Cells(4 + i, 6).Value = Worksheets("shipment").Cells(s_line + i - 1, 9).Value
Next i

Exit Sub

MyErrorHandler:
If Err.Number = 1004 Then
MsgBox “第” & CStr(s_line + i – 1) & “行無參照品名”
Resume Next
ElseIf Err.Number = 13 Then
MsgBox “You have entered an invalid value.”
End If

End Sub

自訂 IsInt函數檢查是否是整數

Function IsInt(aValue as Variant) As Boolean
    On Error Resume Next
    IsInt = (CInt(aValue) = aValue)
    On Error Goto 0
End Function

Delete Duplicates From a Table in SQL Server

To delete the duplicate rows from the table in SQL Server, you follow these steps:

Find duplicate rows using GROUP BY clause or ROW_NUMBER() function.
Use DELETE statement to remove the duplicate rows.

Let’s set up a sample table for the demonstration.

Setting up a sample table

First, create a new table named sales.contacts as follows:

DROP TABLE IF EXISTS sales.contacts;

CREATE TABLE sales.contacts(
    contact_id INT IDENTITY(1,1) PRIMARY KEY,
    first_name NVARCHAR(100) NOT NULL,
    last_name NVARCHAR(100) NOT NULL,
    email NVARCHAR(255) NOT NULL,
);
Code language: SQL (Structured Query Language) (sql)

Second, insert some rows into the sales.contacts table:

INSERT INTO sales.contacts
    (first_name,last_name,email) 
VALUES
    ('Syed','Abbas','syed.abbas@example.com'),
    ('Catherine','Abel','catherine.abel@example.com'),
    ('Kim','Abercrombie','kim.abercrombie@example.com'),
    ('Kim','Abercrombie','kim.abercrombie@example.com'),
    ('Kim','Abercrombie','kim.abercrombie@example.com'),
    ('Hazem','Abolrous','hazem.abolrous@example.com'),
    ('Hazem','Abolrous','hazem.abolrous@example.com'),
    ('Humberto','Acevedo','humberto.acevedo@example.com'),
    ('Humberto','Acevedo','humberto.acevedo@example.com'),
    ('Pilar','Ackerman','pilar.ackerman@example.com');
Code language: SQL (Structured Query Language) (sql)

Third, query data from the sales.contacts table:

SELECT 
   contact_id, 
   first_name, 
   last_name, 
   email
FROM 
   sales.contacts;Code language: SQL (Structured Query Language) (sql)

The following picture shows the output of the query:

There are many duplicate rows (3,4,5), (6,7), and (8,9) for the contacts that have the same first name, last name, and email.

Delete duplicate rows from a table example

The following statement uses a common table expression (CTE) to delete duplicate rows:

WITH cte AS (
    SELECT 
        contact_id, 
        first_name, 
        last_name, 
        email, 
        ROW_NUMBER() OVER (
            PARTITION BY 
                first_name, 
                last_name, 
                email
            ORDER BY 
                first_name, 
                last_name, 
                email
        ) row_num
     FROM 
        sales.contacts
)
DELETE FROM cte
WHERE row_num > 1;
Code language: SQL (Structured Query Language) (sql)

In this statement:

First, the CTE uses the ROW_NUMBER() function to find the duplicate rows specified by values in the first_name, last_name, and email columns.
Then, the DELETE statement deletes all the duplicate rows but keeps only one occurrence of each duplicate group.

SQL Server issued the following message indicating that the duplicate rows have been removed.

(4 rows affected)

If you query data from the sales.contacts table again, you will find that all duplicate rows are deleted.

SELECT contact_id, 
       first_name, 
       last_name, 
       email
FROM sales.contacts
ORDER BY first_name, 
         last_name, 
         email;Code language: SQL (Structured Query Language) (sql)

https://www.sqlservertutorial.net/sql-server-basics/delete-duplicates-sql-server/

Setup A Excel file as A linked server in ms-sql

USE [master]
GO
sp_configure ‘show advanced options’, 1
GO
RECONFIGURE WITH OverRide
GO
sp_configure ‘Ad Hoc Distributed Queries’, 1
GO
RECONFIGURE WITH OverRide
GO

EXEC master . dbo. sp_MSset_oledb_prop N’Microsoft.ACE.OLEDB.12.0′ , N’AllowInProcess’ , 1
GO

EXEC master . dbo. sp_MSset_oledb_prop N’Microsoft.ACE.OLEDB.12.0′ , N’DynamicParameters’ , 1

EXEC sp_addLinkedServer
@server= ‘XLSX_MATRIX’,
@srvproduct = ‘ACE 12.0’,
@provider = ‘Microsoft.ACE.OLEDB.12.0’,
@datasrc =’D:\SQL\vn_item_no.xlsx’,
@provstr = ‘Excel 12.0; HDR=Yes’

SELECT * FROM OPENQUERY (XLSX_MATRIX, ‘Select * from [Application$]’)
SELECT * FROM OPENQUERY (XLSX_MATRIX, ‘Select * from [Device$]’)
SELECT * FROM OPENQUERY (XLSX_MATRIX, ‘Select * from [User$]’)

SELECT * FROM XLSX_MATRIX…[Application$]
SELECT * FROM XLSX_MATRIX…[Device$]
SELECT * FROM XLSX_MATRIX…[User$]

select b.*,a.item_no,a.ref_no from [XLSX_MATRIX]…[工作表1$] a right outter join [XLSX_MATRIX]…[‘原料(B倉)$’] b on a.f_2=b.f_2 where (b.ref_no like ‘%’+a.ref_no+’%’ or (a.ref_no) is null)
order by 1

select b.*,a.item_no,a.ref_no from [XLSX_MATRIX]…[‘原料(B倉)$’] b left outer join [XLSX_MATRIX]…[工作表1$] a on a.f_2=b.f_2
where (b.ref_no like ‘%’+a.ref_no+’%’ or (a.ref_no) is null) and sort_id is not null
order by sort_id

final**
select distinct b.*,a.item_no,a.ref_no from [XLSX_MATRIX]…[‘原料(B倉)$’] b left outer join [XLSX_MATRIX]…[工作表1$] a on a.f_2=b.f_2
and (b.ref_no like ‘%’+a.ref_no+’%’ or (a.ref_no) is null) where sort_id is not null
order by sort_id

select distinct b.*,a.item_no,a.ref_no,isnull(a.v_no,a.t_no) from [XLSX_MATRIX]…[‘原料(B倉)$’] b left outer join [XLSX_MATRIX]…[工作表1$] a on isnull(a.v_no,a.t_no)=b.f_2
and (b.ref_no like ‘%’+a.ref_no+’%’ or (a.ref_no) is null) where sort_id is not null
order by sort_id

AccessDatabaseEngine_X64 maybe needed for Microsoft.ACE.OLEDB.12.0 driver Microsoft Access Database Engine 2010 可轉散發套件

p.s.使用openrowset 可直接讀excel檔案，不用設置linked server
select a.* from
(SELECT * FROM OpenRowSet(‘Microsoft.ACE.OLEDB.12.0’,
‘Excel 12.0;HDR=Yes;IMEX=2;Database=c:\temp\Employee.xls’,[工作表1$])) a,
(SELECT * FROM OpenRowSet(‘Microsoft.ACE.OLEDB.12.0’,
‘Excel 12.0;HDR=Yes;IMEX=2;Database=c:\temp\Employee.xls’,[工作表1$])) b
where a.col1 = b.col1

select distinct b.*,a.item_no,a.ref_no,isnull(a.v_no,a.t_no) from
(SELECT * FROM OpenRowSet(‘Microsoft.ACE.OLEDB.12.0’,
‘Excel 12.0;HDR=Yes;IMEX=1;Database=D:\SQL\vn_item_no.xlsx’,[‘原料(B倉)$’])) b left outer join
(SELECT * FROM OpenRowSet(‘Microsoft.ACE.OLEDB.12.0’,
‘Excel 12.0;HDR=Yes;IMEX=1;Database=D:\SQL\vn_item_no.xlsx’,[工作表1$])) a on isnull(a.v_no,a.t_no)=b.f_2
and (b.ref_no like ‘%’+a.ref_no+’%’ or (a.ref_no) is null) where sort_id is not null
order by sort_id

(註1)SQL Server預設不允許使用 OPENROWSET 和 OPENDATASOURCE 進行特定分散式查詢。使用 OpenRowSet前，需使用sp_configure 來啟用 ‘Ad Hoc Distributed Queries’

EXEC sp_configure 'show advanced options','1'
RECONFIGURE 
EXEC sp_configure 'Ad Hoc Distributed Queries',1
RECONFIGURE

參照

Import data from Excel to SQL Server or Azure SQL Database

使用 OpenRowset、OpenDataSource 查詢EXCE

設定 IMEX=1 透過 OleDb 讀取 Excel 仍然出現 null 值的問題

如何跳過 Win11 強制連網?

1.在連到網路的頁面，不要連上網路，也把實體網路線拔除，這時候按下鍵盤上的「Shift + F10」就可以開啟 CMD 視窗，如果按下去沒有反應，請試試看「fn + Shift + F10」組合，應該就可以順利呼叫開啟 CMD。

2.開啟之後輸入「oobe\bypassnro」，並按下 Enter 鍵。之後系統會自動重新啟動，重開機後會再進入 OOBE 畫面一次，設定都會重新來過，請依照自己需求設定。

4.到連接網路畫面時，就可以看到「我沒有網際網路」的選項，點選後就可以跳過連網！

SQL語法針對 BOM減少一階後做用料量彙總

–bom2要被減少的階層此處用量皆為重量，算出總重量，屆時可依比例更新用量回主BOM，即bom1

update Cf_inv_bom_2
set Cf_inv_bom_2.sum_qty = t1.c_sum
from (select assy_no,sum(qty) c_sum from Cf_inv_bom_2 group by assy_no) t1
where Cf_inv_bom_2.assy_no = t1.assy_no

–bom1無下階先抓出來

select * from cf_inv_bom_1
where item_no not in (select assy_no from cf_inv_bom_2 )

–再結合有bom1有下階的資料(用量=>被替換子件原用量*(下階子件用量/下階用量之和))

select * from cf_inv_bom_1
where item_no not in (select assy_no from cf_inv_bom_2 )
union all
select T1.assy_no,T2.item_no,CAST(t1.qty*t2.qty/t2.sum_qty AS DECIMAL(10,4) ) d_qty,T1.sum_qty
from cf_inv_bom_2 T2,cf_inv_bom_1 T1
where T2.assy_no=T1.item_no order by 1,2

–由於合併後發現兩階間有共用子件因此多一層group來加總資件用量

select Tu.assy_no,tu.item_no,sum(tu.qty),max(Tu.sum_qty) from
(select [assy_no]
,[item_no]
,[qty]
,[sum_qty] from cf_inv_bom_1
where item_no not in (select assy_no from cf_inv_bom_2 )
union all
select T1.assy_no,T2.item_no,CAST(t1.qty*t2.qty/t2.sum_qty AS DECIMAL(10,4) ) d_qty,T1.sum_qty
from cf_inv_bom_2 T2,cf_inv_bom_1 T1
where T2.assy_no=T1.item_no) as Tu
group by Tu.assy_no,tu.item_no
order by 1,2