• Trang chủ click to expand contents 

  • VBA Cách xử lý file dữ liệu khi import file excel thô vào Table

  • VBA Cách xử lý file dữ liệu khi import file excel thô vào Table

    Đình Phán > 25-05-21, 05:43 PM

    Em chào các anh/chị/em trong diễn đàn.

    Em có câu hỏi này xin được nhờ các anh, chị, em giúp đỡ ạ. 
    Cụ thể em đang phải tổng hợp dữ liệu từ nhiều báo cáo xuất ra từ phần mềm. Sau đó xử lý dữ liệu để lên báo cáo.
    Em đang có ý tưởng là sẽ nhập dữ liệu lên Access sau đó sử dụng ADO để truy xuất ra excel, vừa nhẹ mà quản lý được dữ liệu.
    Trường hợp import vào Access, phải xử lý ở file excel thành dạng bảng chuẩn trước.
    Em muốn import luôn dữ liệu thô vào, sau đó dùng VBA trong acccess để xử lý dữ liệu thô đó về dữ liệu chuẩn dạng Table.

    Em xin anh chị giúp đỡ code từ khâu import vào Access đến khi xử lý dữ liệu cho 1 ví dụ cơ bản, có comment từng câu lệnh thì tốt ạ.

    Em cảm ơn!


    Mã:
    Public Sub update_reg()
    Dim strPath As String, strTable As String, strFile As String, strPathFile As String, tt As String, loai As String, sql As String
    Dim ngay As String, ngaydt As String, ngay1 As String, ngay2 As String, dd As String, mm As String, yy As String, kenh As String
    Dim DB As Database, Rec As Recordset, Rec1 As Recordset, Rec_setup As Recordset
    Dim maxdd As String

    DoCmd.SetWarnings 0
    Set DB = CurrentDb()
    kiemtra = True
    strPath = Application.CurrentProject.Path & "\Reg\" 'Đu?ng d?n d?n file Excel c?a b?n
    strFile = Dir(strPath & "*.xlsx")

    'MsgBox ngay
    Do While Len(strFile) > 0

    strPathFile = strPath & strFile
                If ton_tai_bang("tbl_temp") Then
                DoCmd.DeleteObject acTable, "tbl_temp"
                DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, "tbl_reg", strPathFile, -1  ' , "A6:A6"
                Else
                DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, "tbl_reg", strPathFile, -1  ' , "A6:A6"
                End If

    ngay = Left(strFile, 4)

    sql = "DELETE tbl_reg_accu.*, tbl_reg_accu.ngay FROM tbl_reg_accu WHERE (((tbl_reg_accu.ngay)='" & ngay & "'));"
    DoCmd.RunSQL sql

    sql = "INSERT INTO tbl_reg_accu ( ngay, BC_CODE, BTS_CODE, PRODUCT_CODE, [DATETIME], ISDN, IMEI, SHOP_CODE, STAFF_CODE, CHANNEL_NAME, NAME,  [ZONE], DISTRICT, PROVINCE )"
    sql = sql + " SELECT '" & ngay & "' AS  ngay, Q_BTS.BC_code, tbl_reg.BTS_CODE, tbl_reg.PRODUCT_CODE, tbl_reg.DATETIME, tbl_reg.ISDN, tbl_reg.IMEI, tbl_reg.SHOP_CODE, tbl_reg.STAFF_CODE, tbl_reg.CHANNEL_NAME, tbl_reg.NAME, tbl_reg.ZONE, tbl_reg.DISTRICT, tbl_reg.PROVINCE"
    sql = sql + " FROM Q_BTS INNER JOIN tbl_reg ON Q_BTS.BTS_code = tbl_reg.BTS_CODE;"
    'taoquery "Q_1", sql
    DoCmd.RunSQL sql

                If ton_tai_bang("tbl_reg") Then
                DoCmd.DeleteObject acTable, "tbl_reg"
                End If





    strFile = Dir()
    xoa_bang_loi

    DoCmd.SetWarnings 0





    'xuly_dulieu_reg

    Loop

    DoCmd.SetWarnings -1
    End Sub

    Mã:
    Public Sub xuly_dulieu_reg1()
    'On Error GoTo Err_Command135_Click
    DoCmd.SetWarnings 0
    'kenh = "Store"

    Dim strPathFile As String, strFile As String, strPath As String
    Dim strTable As String
    Dim blnHasFieldNames As Boolean
    Dim sql, sql1, sql2 As String
    Dim ma As String
    Dim DB As Database
    Dim Rec As Recordset
    Dim Rec1 As Recordset

    Dim i, WordLength

    Dim Character As String, LastCharacter As String, NewWord As String, Word As String
    Set DB = CurrentDb()
                Set Rec = DB.OpenRecordset("tbl_temp")
                Word = Rec("F1")
                WordLength = Len(Word)
                NewWord = ""
               
                For i = 1 To WordLength
                    Character = Mid(Word, i, 1)
                   
                   
                    If LastCharacter = "|" Or Character = "|" Then
                        sql = sql + "D" & NewWord & ", '' AS "
                        sql1 = sql1 + "D" & NewWord & ", "
                        NewWord = ""
                    Else
                        NewWord = NewWord & Character
                        LastCharacter = Character
                    End If
                Next i
                sql = "SELECT '' AS " & Left(sql, Len(sql) - 8) & " INTO tbl_reg;"
                sql1 = Left(sql1, Len(sql1) - 2)
                'taoquery "Q_1", sql
                DoCmd.RunSQL sql
                'Rec.Close

            sql = "DELETE tbl_reg.* FROM tbl_reg;"
            DoCmd.RunSQL sql

            Set Rec1 = DB.OpenRecordset("tbl_reg")
                Do Until Rec.EOF
                        Word = Rec("F1")
                        WordLength = Len(Word)
                        NewWord = ""
                        sql = ""
                        sql2 = ""
                        For i = 1 To WordLength
                            Character = Mid(Word, i, 1)
                           
                            If LastCharacter = "|" Or Character = "|" Then
                                sql2 = sql2 + "'" & NewWord & "', "
                                NewWord = ""
                            Else
                                NewWord = NewWord & Character
                                LastCharacter = Character
                            End If
                        Next i
                        sql = "INSERT INTO tbl_reg (" & sql1 & ") " + "select " & Left(sql2, Len(sql2) - 2) & ";"
                        'MsgBox sql1
                        DoCmd.RunSQL sql
                       
                Rec.MoveNext
                Loop
                Rec.Close
     
    DoCmd.SetWarnings 0

    End Sub


    Do không đính kèm được file excel thô nên mô tả hơi khó chút ạ

  • RE: VBA Cách xử lý file dữ liệu khi import file excel thô vào Table

    ongke0711 > 25-05-21, 11:53 PM

    (25-05-21, 05:43 PM)Đình Phán Đã viết: Do không đính kèm được file excel thô nên mô tả hơi khó chút ạ

    - Bạn upload file lên các trang miễn phí như: mediafire.com, googledrive... rồi copy link vào đây. Chia sẻ file Full thì mới download được.
    - Bộ code này của bạn có hoạt động không vậy? Tôi nghi ngờ quá. Vòng lặp Do While từ biến của lệnh Dir() làm sao lặp được.
    - Các code xử lý chuỗi (word, wordlength...) không có file cũng không hiểu chuỗi đó như thế nào, làm cái gì ...
    - File xuất từ phầm mềm khác sau không xuất ra .CSV cho gọn nhẹ.

  • RE: VBA Cách xử lý file dữ liệu khi import file excel thô vào Table

    Đình Phán > 26-05-21, 09:03 AM

    (25-05-21, 11:53 PM)ongke0711 Đã viết:
    (25-05-21, 05:43 PM)Đình Phán Đã viết: Do không đính kèm được file excel thô nên mô tả hơi khó chút ạ

    - Bạn upload file lên các trang miễn phí như: mediafire.com, googledrive... rồi copy link vào đây. Chia sẻ file Full thì mới download được.
    - Bộ code này của bạn có hoạt động không vậy? Tôi nghi ngờ quá. Vòng lặp Do While từ biến của lệnh Dir() làm sao lặp được.
    - Các code xử lý chuỗi (word, wordlength...) không có file cũng không hiểu chuỗi đó như thế nào, làm cái gì ...
    - File xuất từ phầm mềm khác sau không xuất ra .CSV cho gọn nhẹ.

    Em gửi đường dẫn file đính kèm ạ.

    https://drive.google.com/file/d/1SRcbbWz...sp=sharing

  • RE: VBA Cách xử lý file dữ liệu khi import file excel thô vào Table

    ongke0711 > 26-05-21, 07:35 PM

    (25-05-21, 05:43 PM)Đình Phán Đã viết: Cụ thể em đang phải tổng hợp dữ liệu từ nhiều báo cáo xuất ra từ phần mềm. Sau đó xử lý dữ liệu để lên báo cáo.
    Em đang có ý tưởng là sẽ nhập dữ liệu lên Access sau đó sử dụng ADO để truy xuất ra excel, vừa nhẹ mà quản lý được dữ liệu.
    Trường hợp import vào Access, phải xử lý ở file excel thành dạng bảng chuẩn trước.
    Em muốn import luôn dữ liệu thô vào, sau đó dùng VBA trong acccess để xử lý dữ liệu thô đó về dữ liệu chuẩn dạng Table.

    Việc import và xử lý dữ liệu sẽ đơn giản hơn nếu dữ liệu đầu vào tương đối chuẩn hoá chút. 
    Trước hết nói về dữ liệu đầu vào trước đi:
    - Tôi thấy các file Excel trong folder phần lớn là ở dạng báo cáo, có các dòng subTotal chứ không phải dữ liệu thô. Tốt nhất là bạn yêu cầu xuất dữ liệu thô dưới dạng file Text (.CSV), khi đó các bước xử lý cũng sẽ đơn giản hơn ít nhiều. File CSV có kích thước chỉ bằng 1/3 dung lượng so với file Exel. Việc Import file CSV có tốc độ rất nhanh, không bị import các  dữ liệu dư thừa như số dòng trống, cột trống như Import từ Excel (nếu không khai báo Range). Nếu khai báo Range thì lại phải tốn code, tốn thời gian xác đjnh Range trước khi Import.
    - Import từ Excel buộc file phải cố định  dòng bắt đầu lấy dữ liệu, nếu Chèn, Xoá dòng nào đó là lấy dữ liệu sẽ sai ngay. Nếu muốn giảm thiểu sai sót thì khi Import phải bao gồm dòng tiêu đề (Header row). Khi đó bạn xử lý Table dựa trên chính xác tên Field chứ không phải cái bí danh như F1, F2... Khi import có dòng tiêu đề thì dù file Excel có các cột sắp xếp lộn xộn, Access vẫn truy vấn đúng trường (field) dữ liệu cần lấy dựa trên tên trường.
    Nếu bạn muốn xây dụng cái app dùng lâu dài, ít phải sửa đổi code  thì nên xem lại nguồn dữ liệu, cách lấy dữ liệu sao cho các cấu trúc bảng dữ liệu thô chỉ có dòng và cột, nhất quán, không định dạng, không công thức, không merge cell v.v..

  • RE: VBA Cách xử lý file dữ liệu khi import file excel thô vào Table

    Đình Phán > 26-05-21, 11:16 PM

    Trích dẫn:Nếu bạn muốn xây dụng cái app dùng lâu dài, ít phải sửa đổi code  thì nên xem lại nguồn dữ liệu, cách lấy dữ liệu sao cho các cấu trúc bảng dữ liệu thô chỉ có dòng và cột, nhất quán, không định dạng, không công thức, không merge cell v.v..

    Đúng là em cũng muốn dữ liệu được chuẩn hoá trước khi đẩy vào Access, nhưng việc này lại không do em quyết định được.
    Em chỉ được dùng dữ liệu từ các báo cáo như thế này thôi ạ.
    Đây là file của 1 anh đồng nghiệp làm, em muốn học hỏi nhưng không được. Chính vì vậy em muốn hiểu việc xử lý dữ liệu thô trên Access như thế nào, do đọc code trên access em còn mù tịt quá.

    Cảm ơn anh.

  • RE: VBA Cách xử lý file dữ liệu khi import file excel thô vào Table

    ongke0711 > 26-05-21, 11:46 PM

    (26-05-21, 11:16 PM)Đình Phán Đã viết: Em chỉ được dùng dữ liệu từ các báo cáo như thế này thôi ạ.
    Đây là file của 1 anh đồng nghiệp làm, em muốn học hỏi nhưng không được. Chính vì vậy em muốn hiểu việc xử lý dữ liệu thô trên Access như thế nào, do đọc code trên access em còn mù tịt quá.

    Vậy bạn muốn giải thích code trong file này cho bạn hiểu phải không? Code quá trời, ngồi viết chú thích cũng đuối à.
    Code file này cũng thuộc dạng lâu đời đây.
    Về việc xử lý dữ liệu thô sau khi import vào Access, nó cũng giống như bạn làm thủ công trong Excel vậy nhưng thay vì làm bằng tay, bằng mắt thì dùng code VBA để thực hiện.
    Vd: Sheet Excel có các dòng merge SubTotal, khi import vào table Access, các dòng này không hợp lệ nên phải xoá nó -> khi đó thì dùng code tìm chuỗi "SubTotal" trong cột nào đó -> nếu có thì xoá dòng đó đi. Thực hiện việc này mỗi khi duyệt qua từng dòng (record) trong Table.
    Mỗi file Excel trong Folder của bạn thiết kế mỗi khác nên mỗi file có một cách xử lý khác nhau. CSDL không chuẩn hoá nó mệt chỗ đó.
    Mà bạn có biết chút ít gì về VBA không?
    File bạn gửi còn thiếu bảng tbl_reg.

  • RE: VBA Cách xử lý file dữ liệu khi import file excel thô vào Table

    Đình Phán > 27-05-21, 09:13 AM

    Trích dẫn:Vậy bạn muốn giải thích code trong file này cho bạn hiểu phải không? Code quá trời, ngồi viết chú thích cũng đuối à.
    Code file này cũng thuộc dạng lâu đời đây.
    Về việc xử lý dữ liệu thô sau khi import vào Access, nó cũng giống như bạn làm thủ công trong Excel vậy nhưng thay vì làm bằng tay, bằng mắt thì dùng code VBA để thực hiện.
    Vd: Sheet Excel có các dòng merge SubTotal, khi import vào table Access, các dòng này không hợp lệ nên phải xoá nó -> khi đó thì dùng code tìm chuỗi "SubTotal" trong cột nào đó -> nếu có thì xoá dòng đó đi. Thực hiện việc này mỗi khi duyệt qua từng dòng (record) trong Table.
    Mỗi file Excel trong Folder của bạn thiết kế mỗi khác nên mỗi file có một cách xử lý khác nhau. CSDL không chuẩn hoá nó mệt chỗ đó.
    Mà bạn có biết chút ít gì về VBA không?
    File bạn gửi còn thiếu bảng tbl_reg.

    Dạ vâng, em có biết chút chút phần VBA excel ạ.
    Ví dụ như code này của anh Le Phat Dom thì em hiểu. Nhưng đây đang là code trên file excel, và đẩy dữ liệu từ chính file đó vào Access
    Trong khi em lại đang muốn đẩy dữ liệu thô vào Access sau đó xử lý trên Access. Còn việc xuất trở lại về báo cáo của Excel thì tính sau, do việc khai thác Cơ sở dữ liệu thì tùy từng người.

    Mã:
    Sub LuuDL_HLMT()
        Dim strSQL As String
        With Sheet1
            strSQL = "INSERT INTO DataBanHang " & _
                        "SELECT F1 AS TENHANG,F2 AS DVT,F3 AS SOLUONG,F4 AS DONGIA,F5 AS THANHTIEN,F6 AS GHICHU,'" & _
                        .Range("G1") & "' AS SOHD,'" & .Range("G2") & "' AS NGAYHD,'" & .Range("B3") & "' AS KHACHHANG,'" & .Range("B4") & "' AS DIACHI,'" & .Range("F4") & "' AS DIENTHOAI " & _
                        "FROM [EXCEL 12.0;HDR=NO;Database=" & ThisWorkbook.FullName & "].[HD$B11:G25] WHERE F1 IS NOT NULL"
        End With
        With CreateObject("ADODB.Connection")
            .Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.Path & "\db.accdb"
            .Execute (strSQL)
        End With
    End Sub

  • RE: VBA Cách xử lý file dữ liệu khi import file excel thô vào Table

    ongke0711 > 27-05-21, 10:28 AM

    - Các file báo cáo Excel trong các folder của bạn có cố định thiết kế, vị trí cột, dòng bắt đầu dữ liệu không?
    - Tên file có cố định không? hay theo nguyên tắc gì?

  • RE: VBA Cách xử lý file dữ liệu khi import file excel thô vào Table

    Đình Phán > 27-05-21, 11:04 AM

    (27-05-21, 10:28 AM)ongke0711 Đã viết: - Các file báo cáo Excel trong các folder của bạn có cố định thiết kế, vị trí cột, dòng bắt đầu dữ liệu không?
    - Tên file có cố định không? hay theo nguyên tắc gì?

    Dạ có cố định thiết kế ạ (vị trí cột và dòng bắt đầu)
    Tên file theo em đọc thì đang không cố định, mà đang lấy theo tên của Folder ạ (ví dụ folder Anypay sẽ vào bảng Anypay)

    Em tìm hiểu file này thì đang thấy ý đồ của tác giả là:
    1. Tìm đường dẫn để lấy file excel (đường dẫn folder theo đường dẫn của CSDL)
    2. Đưa dữ liệu vào 1 bảng tạm
    - Kiểm tra xem có tồn tại bảng tạm không, nếu có rồi thì xóa đi
    - Đưa dữ liệu mới vào bảng tạm
    3. Xử lý dữ liệu thô tại bảng tạm
    (chỗ này đúng là với mỗi kiểu dữ liệu đưa vào thì cách xử lý sẽ khác nhau, thường là sẽ điền hết dữ liệu để về dạng dòng vd: lấp ô trống, ...)
    Phần Access không có Record Marco nên em không hiểu và F8 theo từng câu lệnh được. Và có nhiều thuộc tính em chưa biết. Đồng thời em cũng bị loạn giữa command và recordset.
    4. Trích lọc dữ liệu cần từ bảng tạm để đưa sang bảng chính

    Em cảm ơn anh.

  • RE: VBA Cách xử lý file dữ liệu khi import file excel thô vào Table

    ongke0711 > 28-05-21, 03:10 PM

    Tôi làm demo cho việc lấy dữ liệu từ folder "imei":
    - Sửa code import dữ liệu vào table "tbl_temp".  Code cũ khai báo các biến vô tội vạ, không dùng cũng khai báo. Việc khai báo này làm rối thêm cho người đọc, sửa code sau này.
    - Tinh chỉnh lại code xử lý dữ liệu của tbl_temp sau khi import để thành bảng dữ liệu chuẩn hoá.

    Xem code trong 2 module: Module_update_store, Module_xu_ly_store.

    Link file: https://www.mediafire.com/file/sci7zk5zn...accdb/file


    Nhưng bước xử lý kế tiếp để ra báo cáo của bạn thì tôi không biết nên tôi không xem qua nó được. Cụ thể:
    - Báo cáo gồm thông tin gì? Nhưng dữ liệu gì cần cho báo cáo?
    - Tổ chức xây dựng các bảng dữ liệu như thế nào để đủ thông tin cung cấp cho báo cáo.
    - Một cái quan trọng trong quản lý dữ liệu là tham số thời gian để có thể truy vấn dữ liệu năm này qua năm khác v.v.. mà trong các table liên quan đến imei, tôi không thấy thông tin thời gian?
    - Để xác định có import trùng dữ liệu hay không thì dựa trên thông tin nào? Ngày báo cáo? mã đơn hàng, mã gì khác...

    Nói chung nếu bạn nhảy ngang từ Excel qua viết cái ứng dụng Access thì buộc phải học lại từ căn bản Access thôi. Trong Access, bạn muốn thiết kế một cái Form, để chọn dữ liệu ra cái báo cáo (Report) thì phải đi từ cái Table như thế nào, các table được tạo quan hệ (relationship) với nhau ra sao, có dữ liệu gì -> Query như thế nào để ra dữ liệu yêu cầu -> Thiết kế Form tương tác với Table, Query như thế nào -> thiết kế Report . Nó bao gồm tất cả kiến thức của toàn bộ các đối tượng Access chứ không chỉ biết code VBA là đủ.