Phân tích dữ liệu với Microsoft Power BI 1 Section2 Lessons10 Weeks
Đưa dữ liệu trong thư mục vào sqlserver
Tạo Stored Procedure tự động quét cả thư mục. Thủ tục này từ việc:
– Tạo biến thư mục
– Đọc dữ liệu trong thư mục
– Tạo bảng tạm để lưu dữ liệu lấy từ các file trong folder.
– Dữ liệu trùng loại ra
USE [1_TCHQ];
GO
CREATE OR ALTER PROCEDURE dbo.usp_ImportNewDeclarationCSV
@Folder nvarchar(4000)
AS
BEGIN
SET NOCOUNT ON;
------------------------------------------------------------
-- 1. Danh sách file
------------------------------------------------------------
CREATE TABLE #Files
(
FileName nvarchar(512),
Depth int,
IsFile bit
);
INSERT INTO #Files
EXEC master.sys.xp_dirtree @Folder, 1, 1;
------------------------------------------------------------
-- 2. Staging table
------------------------------------------------------------
CREATE TABLE #Stage
(
DeclarationID varchar(100),
DeclarationDate varchar(50),
EnterpriseKey varchar(50),
CustomsOfficeKey varchar(50),
HSCodeKey varchar(50),
CountryKey varchar(50),
ImportExportType varchar(50),
CustomsValue varchar(100),
TaxAmount varchar(100),
ClearanceHours varchar(100),
RiskLevel varchar(100)
);
------------------------------------------------------------
-- 3. Các biến
------------------------------------------------------------
DECLARE
@FileName nvarchar(512),
@FullPath nvarchar(4000),
@SQL nvarchar(max),
@RowsInFile int,
@RowsInserted int;
------------------------------------------------------------
-- 4. Chỉ lấy những CSV CHƯA import thành công
------------------------------------------------------------
DECLARE FileCursor CURSOR LOCAL FAST_FORWARD
FOR
SELECT F.FileName
FROM #Files F
WHERE
F.IsFile = 1
AND LOWER(F.FileName) LIKE '%.csv'
AND NOT EXISTS
(
SELECT 1
FROM dbo.CSV_ImportLog L
WHERE L.FileName = F.FileName
AND L.Status = 'SUCCESS'
)
ORDER BY F.FileName;
OPEN FileCursor;
FETCH NEXT FROM FileCursor INTO @FileName;
------------------------------------------------------------
-- 5. Xử lý từng file mới
------------------------------------------------------------
WHILE @@FETCH_STATUS = 0
BEGIN
BEGIN TRY
TRUNCATE TABLE #Stage;
----------------------------------------------------
-- Ghép đường dẫn
----------------------------------------------------
SET @FullPath =
CASE
WHEN RIGHT(@Folder,1) IN ('\','/')
THEN @Folder + @FileName
ELSE @Folder + '\' + @FileName
END;
PRINT '------------------------------------------';
PRINT 'Dang import: ' + @FullPath;
----------------------------------------------------
-- BULK INSERT
----------------------------------------------------
SET @SQL = N'
BULK INSERT #Stage
FROM ''' + REPLACE(@FullPath,'''','''''') + N'''
WITH
(
FORMAT = ''CSV'',
FIRSTROW = 2,
FIELDQUOTE = ''"'',
CODEPAGE = ''65001'',
ROWTERMINATOR = ''0x0a'',
TABLOCK
);';
EXEC sys.sp_executesql @SQL;
----------------------------------------------------
-- Đếm số dòng trong file
----------------------------------------------------
SELECT @RowsInFile = COUNT(*)
FROM #Stage;
----------------------------------------------------
-- Chèn dữ liệu nhưng KHÔNG chèn DeclarationID cũ
----------------------------------------------------
;WITH CleanData AS
(
SELECT
LTRIM(RTRIM(DeclarationID))
AS DeclarationID,
TRY_CONVERT
(
date,
LTRIM(RTRIM(DeclarationDate))
)
AS DeclarationDate,
TRY_CONVERT
(
int,
LTRIM(RTRIM(EnterpriseKey))
)
AS EnterpriseKey,
TRY_CONVERT
(
int,
LTRIM(RTRIM(CustomsOfficeKey))
)
AS CustomsOfficeKey,
TRY_CONVERT
(
int,
LTRIM(RTRIM(HSCodeKey))
)
AS HSCodeKey,
TRY_CONVERT
(
int,
LTRIM(RTRIM(CountryKey))
)
AS CountryKey,
LTRIM(RTRIM(ImportExportType))
AS ImportExportType,
TRY_CONVERT
(
decimal(19,2),
LTRIM(RTRIM(CustomsValue))
)
AS CustomsValue,
TRY_CONVERT
(
decimal(19,2),
LTRIM(RTRIM(TaxAmount))
)
AS TaxAmount,
TRY_CONVERT
(
decimal(10,2),
LTRIM(RTRIM(ClearanceHours))
)
AS ClearanceHours,
LTRIM
(
RTRIM
(
REPLACE
(
RiskLevel,
CHAR(13),
''
)
)
)
AS RiskLevel,
ROW_NUMBER() OVER
(
PARTITION BY
LTRIM(RTRIM(DeclarationID))
ORDER BY
(SELECT NULL)
)
AS RN
FROM #Stage
)
INSERT INTO dbo.Declarations
(
DeclarationID,
DeclarationDate,
EnterpriseKey,
CustomsOfficeKey,
HSCodeKey,
CountryKey,
ImportExportType,
CustomsValue,
TaxAmount,
ClearanceHours,
RiskLevel,
SourceFile,
ImportedAt
)
SELECT
C.DeclarationID,
C.DeclarationDate,
C.EnterpriseKey,
C.CustomsOfficeKey,
C.HSCodeKey,
C.CountryKey,
C.ImportExportType,
C.CustomsValue,
C.TaxAmount,
C.ClearanceHours,
C.RiskLevel,
@FileName,
SYSDATETIME()
FROM CleanData C
WHERE
C.RN = 1
AND C.DeclarationID IS NOT NULL
AND NOT EXISTS
(
SELECT 1
FROM dbo.Declarations D
WHERE D.DeclarationID = C.DeclarationID
);
SET @RowsInserted = @@ROWCOUNT;
----------------------------------------------------
-- Ghi log thành công
----------------------------------------------------
IF EXISTS
(
SELECT 1
FROM dbo.CSV_ImportLog
WHERE FileName = @FileName
)
BEGIN
UPDATE dbo.CSV_ImportLog
SET
ImportedAt = SYSDATETIME(),
RowsInFile = @RowsInFile,
RowsInserted = @RowsInserted,
Status = 'SUCCESS',
ErrorMessage = NULL
WHERE FileName = @FileName;
END
ELSE
BEGIN
INSERT INTO dbo.CSV_ImportLog
(
FileName,
ImportedAt,
RowsInFile,
RowsInserted,
Status
)
VALUES
(
@FileName,
SYSDATETIME(),
@RowsInFile,
@RowsInserted,
'SUCCESS'
);
END;
PRINT
'Thanh cong: '
+ @FileName
+ ' - '
+ CAST(@RowsInserted AS varchar(20))
+ ' dong moi.';
END TRY
--------------------------------------------------------
-- Nếu lỗi thì ghi log FAILED
--------------------------------------------------------
BEGIN CATCH
DECLARE @Error nvarchar(4000);
SET @Error = ERROR_MESSAGE();
IF EXISTS
(
SELECT 1
FROM dbo.CSV_ImportLog
WHERE FileName = @FileName
)
BEGIN
UPDATE dbo.CSV_ImportLog
SET
ImportedAt = SYSDATETIME(),
Status = 'FAILED',
ErrorMessage = @Error
WHERE FileName = @FileName;
END
ELSE
BEGIN
INSERT INTO dbo.CSV_ImportLog
(
FileName,
ImportedAt,
Status,
ErrorMessage
)
VALUES
(
@FileName,
SYSDATETIME(),
'FAILED',
@Error
);
END;
PRINT
'LOI: '
+ @FileName
+ ' - '
+ @Error;
END CATCH;
FETCH NEXT FROM FileCursor INTO @FileName;
END;
CLOSE FileCursor;
DEALLOCATE FileCursor;
------------------------------------------------------------
-- Kết quả
------------------------------------------------------------
SELECT
FileName,
ImportedAt,
RowsInFile,
RowsInserted,
Status,
ErrorMessage
FROM dbo.CSV_ImportLog
ORDER BY ImportedAt DESC;
END;
GO
Tạo xong thủ tục chạy thực thi thủ tục cho vị trí folder chứa thư mục tổng hợp
USE [1_TCHQ];
GO
EXEC dbo.usp_ImportNewDeclarationCSV
@Folder = N'D:\TCHQ_DATA';
GO
Kiểm tra file đã được nhập vào chưa
SELECT *
FROM dbo.CSV_ImportLog
ORDER BY ImportedAt DESC;
Xem dư liệu
SELECT TOP 100 *
FROM dbo.Declarations
ORDER BY DeclarationDate DESC;
Thống kê
SELECT
SourceFile,
COUNT(*) AS SoDong
FROM dbo.Declarations
GROUP BY SourceFile
ORDER BY SourceFile;
Mỗi tháng chỉ việc copy file mới vào thư mục rồi chạy:
EXEC dbo.usp_ImportNewDeclarationCSV
@Folder = N'D:\TCHQ_DATA';