Tổng hợp dữ liệu từ nhiều file dbf khác nhau (1 người xem)

[khanh_nguyentat]

Chào các bác,

Em đã đọc nhiều bài tổng hợp dữ liệu từ nhiều file excel khác nhau. Tuy nhiên, hàng ngày em phải làm việc với nhiều file chạy ra ở dạng d-baseIV (dbf) có cấu trúc giống nhau đặt cùng tại 1 thư mục. File name thay đổi theo ngày VD data1905.dbf, data2005.dbf, data2105.dbf vân vân. Em muốn dùng macro copy tất cả data này vào một file excel tonghop.xls. Nhờ các bác chỉ giáo. Em xin cảm tạ

domfootwear: Lần sau bạn nhớ viết Tiếng Việt có dấu nhé

 

[thang24]

Phải có file ví dụ chứ bạn thì mới nhờ các anh giúp đỡ được chứ!
Thôi mình gửi file đính kèm dbf (bảng cân đối đây) (trong file này mình đã xoá dữ liẹu gốc và import vào dữ liệu mới vào) nên các bạn kéo xuống cuối file sẽ có số liệu đính kèm- về bản chất ko có j thay đổi)

 

[Thuyan.acc]

Chào các bác,

Em đã đọc nhiều bài tổng hợp dữ liệu từ nhiều file excel khác nhau. Tuy nhiên, hàng ngày em phải làm việc với nhiều file chạy ra ở dạng d-baseIV (dbf) có cấu trúc giống nhau đặt cùng tại 1 thư mục. File name thay đổi theo ngày VD data1905.dbf, data2005.dbf, data2105.dbf vân vân. Em muốn dùng macro copy tất cả data này vào một file excel tonghop.xls. Nhờ các bác chỉ giáo. Em xin cảm tạ

domfootwear: Lần sau bạn nhớ viết Tiếng Việt có dấu nhé

Em muốn dùng macro copy
Các files này đều có cấu trúc nhau.
Cách làm:
Mở 1 file bất kỳ, save as (copy to ...) cũng theo dạng dbf. Ví dụ aaa.dbf. Mục đích để không bị hư file gốc.

Sau đó, mở file aaa.dbf dùng lệnh append from của Fox (nối các tập tin) vào tập tin aaa.dbf. Lần lượt append from làm cho đến hết các tập tin có yêu cầu

Sau đó, convert file aaa.dbf sang aaa.xls. Từ Excel, gọi file này rồi muôn xào nấu, chế biến gì theo ý thích

 

[thang24]

thế thì nói làm j, cái quan trọng ở đây là dùng excel ý chứ qua fox thì mình cũng làm được rồi!

 

[cadafi]

thế thì nói làm j, cái quan trọng ở đây là dùng excel ý chứ qua fox thì mình cũng làm được rồi!

Bạn chép hai file (ctya.dbf và ctyb.dbf) đúng vào thư mục D:\VD_dbf\
Sau đó tải file excel này về. Click chuột phải vào ô A1 rồi chọn Refresh (excel 2003 là "Refresh Data"). Nếu đúng ý bạn như thế thì tính tiếp đến cách làm.

 

[thang24]

Chuẩn không cần chỉnh! xin nhờ bác hướng dẫn qua!

Nhờ ai đó giải thích dùm nguyên tắc Tổng hợp dữ liệu từ nhiều file dbf khác nhau của bác cadafi!

 

[khanh_nguyentat]

Em gửi ví dụ và yêu cầu trong file tổng hợp để các bác giúp em nhé

 

[thang24]

Xin nhờ các bác giải thích nguyên tắc lấy dữ liệu từ dbf!

 

[cadafi]

10 bước thực hiện việc tổng hợp dữ liệu từ nhiều bảng *.dbf vào Excel (phần 1)

.......giải thích dùm nguyên tắc Tổng hợp dữ liệu từ nhiều file dbf khác nhau của bác cadafi!

Sorry thang24 vì tôi khá nhiều việc nên chưa cập nhật kịp phần hướng dẫn. Xin vui lòng xem hướng dẫn bên dưới.

Với điều kiện các file dbf có cấu trúc bảng dữ liệu như nhau (cột và định dạng). Ta thực hiện việc tổng hợp vào file excel như sau:

1. Từ file excel, vào Data --> Get External Data --> From Other Sources --> From Microsoft Query

2. Trong cửa sổ [Choose Data Source] à Chọn: Visual FoxPro Tables*

3 . Trong cửa sổ [Configure Connection] gõ đường dẫn chứa các tập tin dbf vào ô [Path] hoặc click [Browse..] để chọn đường dẫn.

4. Trong cửa sổ [Query Wizard – Choose Columns], chọn lần lượt từng Table hiện trong phần [Available Tables and Columns] sau đó click nút [>] để chuyển qua [Columns in your query] à Sau đó click nút [Next]

Bạn sẽ thấy 1 thông báo lỗi như sau:

Đừng lo lắng, cứ việc click nút [OK]

5. Trong khung cửa sổ [ Microsoft Query – [Query from Visual FoxPro Tables]], click vào nút [SQL] để vào khung soạn thảo SQL statement

[FONT=&quot]

[/FONT]

(còn tiếp...)

 

[cadafi]

10 bước thực hiện việc tổng hợp dữ liệu từ nhiều bảng *.dbf vào Excel (phần 2)

(tiếp theo...)

6. Xóa hết đoạn code trong khung soạn thảo [SQL statement]

7. Gõ lại đoạn SQL như sau vào khung soạn thảo, sau đó ấn [OK]

  SELECT * FROM ctya
   
  UNION ALL
   
  SELECT * FROM ctyb

8. Khi đó bạn sẽ nhận được thông báo như hình bên dưới, tiếp tục chọn [OK] đi bạn.

9. Sau đó làm theo hình bên dưới để đưa dữ liệu ra Excel

10. Trong cửa sổ [Import Data], chọn ô đầu tiên để đặt dữ liệu import, sau đó ấn [OK] để thưởng thức.

Hết. Chúc các bạn thành công!

 

[thang24]

Xin hỏi cadafi một chút!
giải sử mình có tận 50 cty và có 50 dữ liệu dbf (dĩ nhiên là giống cấu trúc) vậy làm như cách trên có lâu quá ko cadafi
vì như vậy lúc nào mình cũng phải:

SELECT * FROM ctya

UNION ALL


SELECT * FROM ctyB
UNION ALL
SELECT * FROM ctyC
UNION ALL
......
vấn đề là thời gian, vậy có cách nào làm nhanh hơn được ko? có cách nào chỉ để chọn thư mục chứa file dbf là đủ ko? làm thế này mình phải chọn từng công ty 1.

 

[cadafi]

Xin hỏi cadafi một chút!
giải sử mình có tận 50 cty và có 50 dữ liệu dbf (dĩ nhiên là giống cấu trúc) vậy làm như cách trên có lâu quá ko cadafi
vì như vậy lúc nào mình cũng phải:

SELECT * FROM ctya
UNION ALL
SELECT * FROM ctyB
UNION ALL
SELECT * FROM ctyC
UNION ALL
......
vấn đề là thời gian, vậy có cách nào làm nhanh hơn được ko? có cách nào chỉ để chọn thư mục chứa file dbf là đủ ko? làm thế này mình phải chọn từng công ty 1.

Như vậy ta cần phải dùng đến VBA rồi.
Thuật toán như sau:
1. Yêu cầu người dùng khai báo thư mục chứa các file dbf (dùng InputBox)
2. Dùng vòng lặp quét qua các file có phần mở rộng là *.dbf trong thư mục khai báo và đưa vào câu lệnh SQL
(Tham khảo thêm các thủ thuật liên quan tại đây)
3. Tạo 1 kết nối với các bảng dbf và thông qua câu lệnh SQL để xuất dữ liệu ra Excel.

Như vậy ta có 2 Function cần viết như sau:
A. Tạo một Hàm Thiết lập câu lệnh SQL để Union tất cả các file dbf tìm được trong thư mục đã khai báo:

Function mSQL(strDirectory As String, Optional strExt As String = "dbf") As String
Dim objFiles As Object, objFile As Object
Dim fixSQL As String
On Error GoTo Err_Exit
fixSQL = "Union All" & Chr(13) & "SELECT * FROM "
''Tao doi tuong Scripting.FileSystemObject va dang muc File
Set objFiles = CreateObject("Scripting.FileSystemObject").GetFolder(strDirectory).Files
For Each objFile In objFiles
    If UCase(Right(objFile.Path, (Len(objFile.Path) - InStrRev(objFile.Path, ".")))) = UCase(strExt) Then
        mSQL = mSQL & fixSQL & Left(objFile.Name, Len(objFile.Name) + 1 - InStrRev(objFile.Name, ".")) & Chr(13)
    End If
Next objFile
mSQL = Mid(mSQL, 10, Len(mSQL) - 10)
Err_Exit:
    ''Giai phong bo nho:
    On Error Resume Next
    Set objFile = Nothing
    Set objFiles = Nothing
End Function

B. Tạo một Sub để kết nối tới các file dbf tìm được, thông qua câu lệnh SQL để xuất dữ liệu tổng hợp được ra Excel:

Sub mImportToExcel()
Dim mFolder As String, mFileCount As Long, iFileItem As Long
Dim mySQL As String
On Error GoTo Err_Control
''------------------------------------------------------------------------------
mFolder = InputBox("Nhap duong dan den Thu Mục chua cac File *.dbf" & Chr(13) & "Vi du: D:\VD_dbf", "Data Source", "D:\VD_dbf")
''------------------------------------------------------------------------------
If mFolder = "" Or IsNull(mFolder) Then
    MsgBox "Ban phai nhap ten duong dan cho dung!", vbCritical + vbOKOnly, "Error"
    Exit Sub
End If
''------------------------------------------------------------------------------
mySQL = mSQL(mFolder, "dbf")
a = MsgBox("Toan Bo Du Lieu cu se bi xoa. YES hay NO ?", vbInformation + vbYesNo, "Thong Bao")
''------------------------------------------------------------------------------------------------
If a = vbYes Then
ActiveSheet.Cells.Clear ''<== Sửa lại chỗ này
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array(Array( _
        "ODBC;DSN=Visual FoxPro Tables;UID=;;SourceDB=" & mFolder & _
        ";SourceType=DBF;Exclusive=No;BackgroundFetch=Yes;Collate=Machine;Null=Yes;Delet" _
        ), Array("ed=Yes;")), Destination:=Range("$A$2")).QueryTable
        .CommandText = Array(mySQL)
        .AdjustColumnWidth = False
        .ListObject.QueryTable.Refresh BackgroundQuery:=False
End With
Else
MsgBox "Du Lieu chua duoc Import. Chuong Trinh Ket thuc do nguoi dung!", vbInformation + vbOKOnly, "Thong Bao"
Exit Sub
End If

Exit_Err:
Exit Sub

Err_Control:
MsgBox "Ten duong dan khong dung hoac khong co file dbf nao trong duong dan ban cung cap. Vui long kiem tra lai", vbCritical + vbOKOnly, "Thong Bao"
Resume Exit_Err

End Sub

Bạn xem thêm file đính kèm. Khi click vào nút [Import data From dbf Files] sẽ có 1 inputBox hiện ra. Bạn gõ đường dẫn đến thư mục chứa các file dbf (ví dụ: D:\VD_dbf), sau đó click OK để thưởng thức.

Chúc bạn thành công.

 

[khanh_nguyentat]

Em copy dữ liệu vào một folder và chạy thử file test nhưng bị lỗi "Tên đường dẫn không đúng hoặc không có file dbf trong đường dẫn...". Các bác kiểm tra dùm em với

 

[cadafi]

Em copy dữ liệu vào một folder và chạy thử file test nhưng bị lỗi "Tên đường dẫn không đúng hoặc không có file dbf trong đường dẫn...". Các bác kiểm tra dùm em với

Cái dấu splash "\" cuối cùng bạn phải bỏ đi! Tên đường dẫn đến Folder chỉ cần gõ: D:\ThuMuc
Không phải là D:\ThuMuc\

Bạn thử lại xem sao!
---------------------------------------------
Lỗi là do câu lệnh xóa bảng query cũ, mình đã chỉnh lại rồi.

Bạn load lai file tại đây nhé!

 

[khanh_nguyentat]

Vẫn bị lỗi bạn ơi. Debug ra bị highlight ở chỗ này:

With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array(Array( _
"ODBC;DSN=Visual FoxPro Tables;UID=;;SourceDB=" & mFolder & _
";SourceType=DBF;Exclusive=No;BackgroundFetch=Yes;Collate=Machine;Null=Yes;Delet" _
), Array("ed=Yes;")), Destination:=ActiveSheet.Range("$A$2")).QueryTable

 

[cadafi]

Vẫn bị lỗi bạn ơi. Debug ra bị highlight ở chỗ này:

With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array(Array( _
"ODBC;DSN=Visual FoxPro Tables;UID=;;SourceDB=" & mFolder & _
";SourceType=DBF;Exclusive=No;BackgroundFetch=Yes;Collate=Machine;Null=Yes;Delet" _
), Array("ed=Yes;")), Destination:=ActiveSheet.Range("$A$2")).QueryTable

Lỗi do câu lệnh query lấy tên file chưa chính xác, mình đã chỉnh lại đoạn code như sau:

Function mSQL(strDirectory As String, Optional strExt As String = "dbf") As String
Dim objFiles As Object, objFile As Object
Dim fixSQL As String
On Error GoTo Err_Exit
fixSQL = "Union All" & Chr(13) & "SELECT * "
''Tao doi tuong Scripting.FileSystemObject va dang muc File
Set objFiles = CreateObject("Scripting.FileSystemObject").GetFolder(strDirectory).Files
For Each objFile In objFiles
    If UCase(Right(objFile.Name, 3)) = UCase(strExt) Then
        mSQL = mSQL & fixSQL & ", '" & objFile.Name & "' As FileName" & " From " & Left(objFile.Name, Len(objFile.Name) - 4) & Chr(13)
    End If
Next objFile
mSQL = Mid(mSQL, 10, Len(mSQL) - 10)
Err_Exit:
    'Giai phong bo nho:
    On Error Resume Next
    Set objFile = Nothing
    Set objFiles = Nothing
End Function

Bạn tải lại file đính kèm nhé.

 

[khanh_nguyentat]

Lần này bị báo lỗi " Run-time error '1004': Cannot connect to the server at this time. Changes to your data cannot be saved.

Debug bị highlighted như sau.

With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array(Array( _
"ODBC;DSN=Visual FoxPro Tables;UID=;;SourceDB=" & mFolder & _
";SourceType=DBF;Exclusive=No;BackgroundFetch=Yes;Collate=Machine;Null=Yes;Delet" _
), Array("ed=Yes;")), Destination:=ActiveSheet.Range("$A$2")).QueryTable

 

[cadafi]

Lần này bị báo lỗi " Run-time error '1004': Cannot connect to the server at this time. Changes to your data cannot be saved.

Debug bị highlighted như sau.

With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array(Array( _
"ODBC;DSN=Visual FoxPro Tables;UID=;;SourceDB=" & mFolder & _
";SourceType=DBF;Exclusive=No;BackgroundFetch=Yes;Collate=Machine;Null=Yes;Delet" _
), Array("ed=Yes;")), Destination:=ActiveSheet.Range("$A$2")).QueryTable

Bạn zip folder chứa dữ liệu *.dbf của bạn lên mình xem thử.

 

[khanh_nguyentat]

mình gửi file ví dụ đính kèm

 

[thang24]

Cadafi thân mến. file của bạn rất hay tuy nhiên nếu có từ 5 file dbf trở lên sẽ không thực hiện được và báo lỗi tại dòng:

.CommandText = Array(mySQL)