Lấy dữ liệu từ nhiều file (mỗi file có 3 đến 4 sheet) đưa vào file tổng hợp

LuuGiaPhúc · 28/7/21

Chào các anh chị, em là thành viên mới, xin cho em hỏi là em muốn lấy dữ liệu từ nhiều file report , mỗi file có từ 3 đến 4 sheet , mỗi sheet có khoảng 60.000 ~ 65.000 dòng) , đưa vào file tổng hợp.
Mỗi sheet em chỉ lấy cột A , C: D, G:H , K:M , T , X:Y.
Nhờ các anh chị giúp em đoạn code import data để không cần phải copy paste bằng tay nữa ạ.
Em cảm ơn rất nhiều.

Chú ý, dữ liệu của các sheet hay có tình trạng hàng cuối cùng bị thiếu (do các văn phòng nhập liệu trên phần mềm bị thiếu). Nhưng khi đưa về file tổng hợp thì mình vẫn phải đưa về y chang.

ptm0412 · 28/7/21

Tham khảo chủ đề tương tự Gộp dữ liệu từ nhiều sheet

LuuGiaPhúc · 28/7/21

ptm0412 đã viết:
Tham khảo chủ đề tương tự Gộp dữ liệu từ nhiều sheet

hic, em vẫn chưa làm được ạ

ptm0412 · 29/7/21

Do nhiều file, mỗi file nhiều sheet nên hơi khác. Có lẽ phải làm tuần tự mỗi file thành 1 sheet trong file, sau đó file tổng hợp mới tập hợp các file con. Do dữ liệu nhiều nên chỉ có thể làm bằng Power query.

HUONGHCKT · 29/7/21

LuuGiaPhúc đã viết:
Chào các anh chị, em là thành viên mới, xin cho em hỏi là em muốn lấy dữ liệu từ nhiều file report , mỗi file có từ 3 đến 4 sheet , mỗi sheet có khoảng 60.000 ~ 65.000 dòng) , đưa vào file tổng hợp.
Mỗi sheet em chỉ lấy cột A , C: D, G:H , K:M , T , X:Y.
Nhờ các anh chị giúp em đoạn code import data để không cần phải copy paste bằng tay nữa ạ.
Em cảm ơn rất nhiều.

Chú ý, dữ liệu của các sheet hay có tình trạng hàng cuối cùng bị thiếu (do các văn phòng nhập liệu trên phần mềm bị thiếu). Nhưng khi đưa về file tổng hợp thì mình vẫn phải đưa về y chang.

View attachment 263219

Trong khi chờ các anh chị em khác trợ giúp bằng các giải pháp khác. hãy thử giải pháp bằng VBA theo cách củ chuối này xem sao. Kết quả trả về đang để từ ô A7 đến ..... trong Sh TONG HOP nhé
Nhắc bạn: Nên thay đổi tên file nên lấy đuôi là .xlsx thay vì .xls ( nếu thay thì mỏe code ra và thay vào dòng tôi đã đánh dấu).
Đề tài này trên diễn đàn rất rất nhiều bạn nên tìm kiếm và tham khảo rồi bí thì mới nhờ trợ giúp những chỗ không làm được.

Hai Lúa Miền Tây · 29/7/21

LuuGiaPhúc đã viết:
Chào các anh chị, em là thành viên mới, xin cho em hỏi là em muốn lấy dữ liệu từ nhiều file report , mỗi file có từ 3 đến 4 sheet , mỗi sheet có khoảng 60.000 ~ 65.000 dòng) , đưa vào file tổng hợp.
Mỗi sheet em chỉ lấy cột A , C: D, G:H , K:M , T , X:Y.
Nhờ các anh chị giúp em đoạn code import data để không cần phải copy paste bằng tay nữa ạ.
Em cảm ơn rất nhiều.

Chú ý, dữ liệu của các sheet hay có tình trạng hàng cuối cùng bị thiếu (do các văn phòng nhập liệu trên phần mềm bị thiếu). Nhưng khi đưa về file tổng hợp thì mình vẫn phải đưa về y chang.

View attachment 263219

Bạn dùng code sau thử nhé.

Mã:

Sub GopDL_HLMT()
    Dim strFileName() As Variant, arrSht() As Variant
    Dim strSQL As String, strPath As String
    Dim iWht As Integer, iSht  As Integer
    strFileName = Array("Attendance_Report_28_07_2021 - SD.xls", "Attendance_Report_28_07_2021 - SBW.xls")
    arrSht = Array("Page 1", "Page 2", "Page 3")
    strPath = ThisWorkbook.Path & "\"
    For iWht = LBound(strFileName) To UBound(strFileName)
        For iSht = LBound(arrSht) To UBound(arrSht)
            strSQL = strSQL & " Union Select F1,F3,F4,F7,F8,F11,F12,F13,F20,F24,F25,'" & arrSht(iSht) & "' as SheetName, '" & _
                    strFileName(iWht) & "' as Workbook from [EXCEL 12.0;HDR=No;Database=" & strPath & strFileName(iWht) & "].[" & arrSht(iSht) & "$A6:Y65536]"
        Next
    Next
    strSQL = Right(strSQL, Len(strSQL) - 7)
    With CreateObject("ADODB.Connection")
        .Open ("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.FullName & ";Extended Properties=""Excel 12.0;HDR=No""")
        Sheet1.Range("A2").CopyFromRecordset .Execute("Select * From (" & strSQL & ") Order By Workbook,SheetName")
    End With
End Sub

Lưu ý file chạy code chung thư mục với 2 file cần tổng hợp.

Cá ngừ F1 · 29/7/21

Nếu tổng hợp từ nhiều file, mà mỗi file lại tổng hợp từ nhiều Sheet. Làm bằng Power Query va theo trình tự:
1. Tại mỗi File, tạo 1 sheet tổng hợp để append từ các sheet con.
2. Làm một file tổng hợp để Collect dữ liệu từ các File trên qua sheet đã tổng hợp.

Tôi làm thử từ 1 File con, bạn đọc tài liệu và thử làm với File khác xem sao.

ptm0412 · 29/7/21

Cái khó của chủ đề này là dữ liệu nhiều, nhiều file nhiều sheet có khi vượt quá số dòng của Excel work sheet. File tổng chỉ có thể làm bằng Power query và Data model.

truongvu317 · 29/7/21

Hai Lúa Miền Tây đã viết:

Bạn dùng code sau thử nhé.

Mã:

Sub GopDL_HLMT()
    Dim strFileName() As Variant, arrSht() As Variant
    Dim strSQL As String, strPath As String
    Dim iWht As Integer, iSht  As Integer
    strFileName = Array("Attendance_Report_28_07_2021 - SD.xls", "Attendance_Report_28_07_2021 - SBW.xls")
    arrSht = Array("Page 1", "Page 2", "Page 3")
    strPath = ThisWorkbook.Path & "\"
    For iWht = LBound(strFileName) To UBound(strFileName)
        For iSht = LBound(arrSht) To UBound(arrSht)
            strSQL = strSQL & " Union Select F1,F3,F4,F7,F8,F11,F12,F13,F20,F24,F25,'" & arrSht(iSht) & "' as SheetName, '" & _
                    strFileName(iWht) & "' as Workbook from [EXCEL 12.0;HDR=No;Database=" & strPath & strFileName(iWht) & "].[" & arrSht(iSht) & "$A6:Y65536]"
        Next
    Next
    strSQL = Right(strSQL, Len(strSQL) - 7)
    With CreateObject("ADODB.Connection")
        .Open ("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.FullName & ";Extended Properties=""Excel 12.0;HDR=No""")
        Sheet1.Range("A2").CopyFromRecordset .Execute("Select * From (" & strSQL & ") Order By Workbook,SheetName")
    End With
End Sub

Lưu ý file chạy code chung thư mục với 2 file cần tổng hợp.

Có phải là ado sẽ không chấp nhận chuỗi sql quá dài không anh, vì nó nghĩ chuỗi đó quá phức tạp? Anh sẽ làm thế nào để vượt qua giới hạn đó nếu anh gặp tình huống đó?

Cá ngừ F1 · 29/7/21

ptm0412 đã viết:
Cái khó của chủ đề này là dữ liệu nhiều, nhiều file nhiều sheet có khi vượt quá số dòng của Excel work sheet. File tổng chỉ có thể làm bằng Power query và Data model.

Nếu 1 khóa học/năm học của 1 trường mà đến 1 triệu học sinh thì đúng là chịu hẳn.

LuuGiaPhúc · 29/7/21

dữ liệu nhiều kinh khủng , cái này là của công ty đào tạo trực tuyến nên số dòng trong file tổng hợp của em nhiều khi lên đến hơn 500.000 dòng

Cá ngừ F1 đã viết:
Nếu 1 khóa học/năm học của 1 trường mà đến 1 triệu học sinh thì đúng là chịu hẳn.

Hai Lúa Miền Tây · 29/7/21

truongvu317 đã viết:
Có phải là ado sẽ không chấp nhận chuỗi sql quá dài không anh, vì nó nghĩ chuỗi đó quá phức tạp? Anh sẽ làm thế nào để vượt qua giới hạn đó nếu anh gặp tình huống đó?

Nếu nhiều quá thì chia nhỏ ra. Chịu khó làm từng phần rồi ghép vào thôi bạn.

Cá ngừ F1 · 29/7/21

LuuGiaPhúc đã viết:
dữ liệu nhiều kinh khủng , cái này là của công ty đào tạo trực tuyến nên số dòng trong file tổng hợp của em nhiều khi lên đến hơn 500.000 dòng

Nếu Tổng hợp mà 500k dòng thì Excel xử lý tốt bạn. Theo hướng POWER QUERY là được bạn.

Tôi cũng góp ý một chút:

- Ví dụ File này: Attendance_Report_28_07_2021 - SD.xls => thực tế nó là 1 báo cáo chứ không phải là 1 Cơ sở dữ liệu. Thực sự, việc lại đi tổng hợp từ 1 File Báo cáo thì quá khó khăn, như thực trạng File đang có các ô Tiêu đề, tên ngay trường đầu tiên TRAINEE_ID tự nhiên có 2 ký tự xuống dòng (chưa hiểu để làm gì).

- Nếu bỏ đi các tiêu đề của báo cáo, chuẩn lại Tên trường, thì việc tổng hợp bằng PQ chắc chỉ vài giây. Thử với nguồn bài #12 https://www.giaiphapexcel.com/diend...không-cần-mở-file-dongia.156725/#post-1038338

Chúc thành công.

LuuGiaPhúc · 29/7/21

Cá ngừ F1 đã viết:
Nếu Tổng hợp mà 500k dòng thì Excel xử lý tốt bạn. Theo hướng POWER QUERY là được bạn.

Tôi cũng góp ý một chút:

- Ví dụ File này: Attendance_Report_28_07_2021 - SD.xls => thực tế nó là 1 báo cáo chứ không phải là 1 Cơ sở dữ liệu. Thực sự, việc lại đi tổng hợp từ 1 File Báo cáo thì quá khó khăn, như thực trạng File đang có các ô Tiêu đề, tên ngay trường đầu tiên TRAINEE_ID tự nhiên có 2 ký tự xuống dòng (chưa hiểu để làm gì).

- Nếu bỏ đi các tiêu đề của báo cáo, chuẩn lại Tên trường, thì việc tổng hợp bằng PQ chắc chỉ vài giây. Thử với nguồn bài #12 https://www.giaiphapexcel.com/diendan/threads/gộp-dữ-liệu-trên-các-sheet-trên-file-dongia-để-đưa-vào-1-mãng-trên-file-b-mà-không-cần-mở-file-dongia.156725/#post-1038338

Chúc thành công.

Cái file Attendance_Report_28_07_2021 - SD.xls này là xuất từ web của công ty xuống ==> vậy theo ý của anh là mình sẽ vào sửa lại tên của các trường này trước rồi mới tới việc tổng hợp nó lại hả anh.

Bài đã được tự động gộp: 29/7/21

HUONGHCKT đã viết:
Trong khi chờ các anh chị em khác trợ giúp bằng các giải pháp khác. hãy thử giải pháp bằng VBA theo cách củ chuối này xem sao. Kết quả trả về đang để từ ô A7 đến ..... trong Sh TONG HOP nhé
Nhắc bạn: Nên thay đổi tên file nên lấy đuôi là .xlsx thay vì .xls ( nếu thay thì mỏe code ra và thay vào dòng tôi đã đánh dấu).
Đề tài này trên diễn đàn rất rất nhiều bạn nên tìm kiếm và tham khảo rồi bí thì mới nhờ trợ giúp những chỗ không làm được.

Em chọn 2 file Attendance report xong, nó báo lỗi :

Với lại khi tổng hợp về, thì cái trường CMND và Mã số Đại Lý có thể để nó ở chế độ TEXT không ạ ? vì nó bắt đầu là số 0 (ví du 0135254) với lại em sẽ dùng mã số đại lý để vlookup sau này

LuuGiaPhúc · 29/7/21

Hai Lúa Miền Tây đã viết:

Bạn dùng code sau thử nhé.

Mã:

Sub GopDL_HLMT()
    Dim strFileName() As Variant, arrSht() As Variant
    Dim strSQL As String, strPath As String
    Dim iWht As Integer, iSht  As Integer
    strFileName = Array("Attendance_Report_28_07_2021 - SD.xls", "Attendance_Report_28_07_2021 - SBW.xls")
    arrSht = Array("Page 1", "Page 2", "Page 3")
    strPath = ThisWorkbook.Path & "\"
    For iWht = LBound(strFileName) To UBound(strFileName)
        For iSht = LBound(arrSht) To UBound(arrSht)
            strSQL = strSQL & " Union Select F1,F3,F4,F7,F8,F11,F12,F13,F20,F24,F25,'" & arrSht(iSht) & "' as SheetName, '" & _
                    strFileName(iWht) & "' as Workbook from [EXCEL 12.0;HDR=No;Database=" & strPath & strFileName(iWht) & "].[" & arrSht(iSht) & "$A6:Y65536]"
        Next
    Next
    strSQL = Right(strSQL, Len(strSQL) - 7)
    With CreateObject("ADODB.Connection")
        .Open ("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.FullName & ";Extended Properties=""Excel 12.0;HDR=No""")
        Sheet1.Range("A2").CopyFromRecordset .Execute("Select * From (" & strSQL & ") Order By Workbook,SheetName")
    End With
End Sub

Lưu ý file chạy code chung thư mục với 2 file cần tổng hợp.

Bạn có thể sửa code 1 chút , kiểu cho nó hiện lên cái bảng hỏi file report ở đâu được không, vì mỗi ngảy mình tải file về, mình phải lưu lại với tên khác nhau (để sau này cần truy cứu lại). Code của bạn bắt buộc phải đặt đúng tên, nếu không nó báo lỗi vậy nè. Còn nữa , mỗi file report có thể có 2 sheet là page 1 và page 2 , cũng có file có 3 sheet hoặc 4 sheet , không cố định. Mình nhìn code của bạn hình như là khóa cứng 3 sheet luôn thì mình nghĩ nó không an toàn.

HUONGHCKT · 29/7/21

LuuGiaPhúc đã viết:
Cái file Attendance_Report_28_07_2021 - SD.xls này là xuất từ web của công ty xuống ==> vậy theo ý của anh là mình sẽ vào sửa lại tên của các trường này trước rồi mới tới việc tổng hợp nó lại hả anh.

Bài đã được tự động gộp: 29/7/21

Em chọn 2 file Attendance report xong, nó báo lỗi :
View attachment 263277

Với lại khi tổng hợp về, thì cái trường CMND và Mã số Đại Lý có thể để nó ở chế độ TEXT không ạ ? vì nó bắt đầu là số 0 (ví du 0135254) với lại em sẽ dùng mã số đại lý để vlookup sau này

Bạn xem lại tên file nếu đuối là .xls thì được Nếu là đuôi khác thì phải đổi cho phù hợp. 2 file bạn up lên trên diễn đàn mình chạy thử thấy ok mà.
thay thử chay với file này xem sao. vẫn chính là các file của bạn thôi

truongvu317 · 29/7/21

Hai Lúa Miền Tây đã viết:
Nếu nhiều quá thì chia nhỏ ra. Chịu khó làm từng phần rồi ghép vào thôi bạn.

cách này tuyệt vời quá, kết quả của mỗi lần chia sẻ nhỏ nhét vào một file excel, cuối cùng ghép lại là sẽ ra kết quả.

LuuGiaPhúc · 29/7/21

HUONGHCKT đã viết:
Bạn xem lại tên file nếu đuối là .xls thì được Nếu là đuôi khác thì phải đổi cho phù hợp. 2 file bạn up lên trên diễn đàn mình chạy thử thấy ok mà.
thay thử chay với file này xem sao. vẫn chính là các file của bạn thôi

file thì chắc chắn là .xls, vì nó là xuất trên phần mềm xuống mà, chỉ là mỗi ngày tải xuống mình sẽ sửa tên VD Atteandance 29_07_2021.xls .... .
Mình mới chạy thử, nó vẫn báo lỗi

hay là có khi nào do máy mình đang dùng Office 365 bản 64bit bản quyền hay không, có thể code bạn viết chạy trên office 2013 hoặc 2016 ? mình đoán vậy thôi. Bạn giúp mình nhé , Cảm ơn bạn

ptm0412 · 29/7/21

LuuGiaPhúc đã viết:
file thì chắc chắn là .xls, vì nó là xuất trên phần mềm xuống mà, chỉ là mỗi ngày tải xuống mình sẽ sửa tên VD Atteandance 29_07_2021.xls .... .
Mình mới chạy thử, nó vẫn báo lỗi
View attachment 263291

hay là có khi nào do máy mình đang dùng Office 365 bản 64bit bản quyền hay không, có thể code bạn viết chạy trên office 2013 hoặc 2016 ? mình đoán vậy thôi. Bạn giúp mình nhé , Cảm ơn bạn

Có thể do kết quả vượt 65 ngàn dòng

HUONGHCKT · 30/7/21

LuuGiaPhúc đã viết:
file thì chắc chắn là .xls, vì nó là xuất trên phần mềm xuống mà, chỉ là mỗi ngày tải xuống mình sẽ sửa tên VD Atteandance 29_07_2021.xls .... .
Mình mới chạy thử, nó vẫn báo lỗi
View attachment 263291

hay là có khi nào do máy mình đang dùng Office 365 bản 64bit bản quyền hay không, có thể code bạn viết chạy trên office 2013 hoặc 2016 ? mình đoán vậy thôi. Bạn giúp mình nhé , Cảm ơn bạn

Như anh PTM đã hướng dẫn bạn xem lại cái file để chứa kết quả tổng hợp xem nó có bao nhiêu dòng. nếu là Office 2010 thì có hơn 1000000 dòng, các office khác hình như chỉ có 65536 dòng thôi. Trong code mình đã để mảng kết quả là Redim KQ( 1 to 1000000, 1 to 11) rồi. Code mình viết trên office 2010 bản 32bit thôi mà.

Lấy dữ liệu từ nhiều file (mỗi file có 3 đến 4 sheet) đưa vào file tổng hợp

Thành viên hoạt động

File đính kèm

Bad Excel Member

Thành viên hoạt động

Bad Excel Member

Zalo 0986997214

File đính kèm

❆❆❆❆❆❆❆❆

( ͡° ͜ʖ ͡°)

File đính kèm

Bad Excel Member

Thành viên tích cực

( ͡° ͜ʖ ͡°)

Thành viên hoạt động

❆❆❆❆❆❆❆❆

( ͡° ͜ʖ ͡°)

Thành viên hoạt động

Thành viên hoạt động

Zalo 0986997214

File đính kèm

Thành viên tích cực

Thành viên hoạt động

Bad Excel Member

Zalo 0986997214

Đếm ngược thời gian