[Hỏi] Kết nối Excel vs Access bằng ADODB - Truy vấn bị lỗi khi tên sheet trong file Excel không được đặt tên

[subasatran]

Xin chào A/C/E và mọi người.
Em có sử dụng kết nối ADODB kết nối Excel và Access để import data từ excel vào access để xử lý dữ liệu(code import em lấy từ internet).
Tuy nhiên, khi chạy code import thì bị gặp lỗi như sau :
Nếu tên Sheet của file Excel được đặt tên thì code chạy ổn(ví dụ như tên sheet là Data).

    Set rs = CreateObject("ADODB.Recordset")
    FilePath = CurrentProject.Path & "\" & FileName & ".xlsx"
    SQL = "select * from [Data$A1:AA]"
    Strconnect = ("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & FilePath _
                & ";Extended Properties=""Excel 12.0 Xml;HDR=Yes;"";")
    rs.Open SQL, Strconnect, 3, 1

Trong trường hợp nếu tên Sheet trong file excel không được đặt tên(có nghĩa là để nguyên mặc định Sheet1).
Thì code import báo lỗi ở câu lệnh SQL.

    Set rs = CreateObject("ADODB.Recordset")
    FilePath = CurrentProject.Path & "\" & FileName & ".xlsx"
    SQL = "select * from [Sheet1$A1:AA]"
    Strconnect = ("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & FilePath _
                & ";Extended Properties=""Excel 12.0 Xml;HDR=Yes;"";")
    rs.Open SQL, Strconnect, 3, 1

Vậy mọi người cho em hỏi là :
a. nguyên nhân báo lỗi là do đâu vậy ?
b. Làm sao để khi tên sheet của file excel để nguyên mặc dịnh là Sheet1 thì code vẫn chạy được ạ ?do các file excel này được exporrt từ hệ thống và rất nhiều file nên không thể mở từng file để đặt tên sheet được
Rất cám ơn A/C/E và mọi người giúp đỡ

 

[Maika8008]

Sao mà trong máy cả 2 nó thể hiện là Microsoft Excel Worksheet(.xlsx) nhỉ ?
Như Maika8008 thì file Test đuôi .xlsm
View attachment 296120
Vậy để sửa lại thì phải chỉnh như #8 mà bạ HeSanbi hướng dẫn ở trên để test lại.
Cám ơn rất nhiều.

Vấn đề ở chỗ khi file raw Test.xlsx đó khi xuất ra nó chưa phải là dạng Excel 12.0 xml (còn dạng gì thì tôi không biết). Chỉ khi mở lên, lưu lại thì nó mới đúng dạng. Báo lỗi là ở chỗ này chứ không phải ở chỗ cái tên Sheet1 kia.

Khi bạn đổi tên Sheet1 thành Data thì khi thực hiện thao tác lưu file chính là bạn đã vô tình làm cho file đó trở thành đúng định dạng.

 

[befaint]

Mắc mệt nhỉ.
Rõ ràng mình thấy có 1 bài (ở đoạn bài 5-10) nhắc gửi cái phai lỗi lầm lên thế mà bị mất tiêu.

Pho mát pho phiếc gì đâu. Cái phai xuất ra bị bung bét, có dùng được đâu.
Còn cái đuôi là gì không quan trọng, bởi cái đuôi đó dùng tay thay đổi được. Muốn xem cái phai là loại tập tin gì thì phải xem mấy cái bai đầu tiên đấy.

Phai xuất ra bị hỏng rồi thì thánh ích mờ lờ hay hát tờ mờ lờ thì cũng cuốn chiếu.

 

[HeSanbi]

@subasatran
Tệp test.xlsx có chứa tệp Sheet1.xml nhưng không tồn tại tên Table Sheet1, Tệp này ADODB không thể đọc hiểu.
Để đọc được tệp bạn chỉ có thể mở với Excel hoặc bung tập tin, vào tệp Sheet1.xml để lấy dữ liệu.

Bạn phải yêu cầu bên phần mềm kia xuất đúng chuẩn xlsx

 

[ongke0711]

Lâu quá tôi không dùng Access, nhưng trước kia tôi import table from Excel file (dữ liệu chuẩn và chỉ 1 sheet), only connection hoặc new table đều được và đơn giản. Đâu cần macro với SQL, connection, và recordset.

Đúng rồi bác. Nếu không cần xử lý gì cái bảng Excel trước khi import vào table Access thì chỉ cần dùng lệnh có của Access là được rồi.

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, "TenTableABC", "\duong dan toi file\Test.xls", True

Thường thì phải xử lý, lọc lựa dữ liệu gì đó thì mới thông qua ADO recordset để tiền xử lý trước.

 

[ptm0412]

Thường thì phải xử lý, lọc lựa dữ liệu gì đó thì mới thông qua ADO recordset để tiền xử lý trước.

Đúng như vậy, nhưng nếu tiền xử lý không phức tạp thì tôi lại dùng query, không quá lạm dụng code vì query là thế mạnh của access. Tất nhiên với điều kiện là dữ liệu gốc tương đối chuẩn thì "tiền xử lý" mới đơn giản được.

 

[subasatran]

Đúng như vậy, nhưng nếu tiền xử lý không phức tạp thì tôi lại dùng query, không quá lạm dụng code vì query là thế mạnh của access. Tất nhiên với điều kiện là dữ liệu gốc tương đối chuẩn thì "tiền xử lý" mới đơn giản được.

Cám ơn các A/C @ptm0412, @ongke0711 @HeSanbi , @befaint.
Do gà mờ về VBA nên cũng chưa hình dung được nội nguồn cấu trúc file hoặc định dạng file nó ảnh hưởng như thế nào đến việc import ?
Về cơ bản, các file rawdata này em nhận được từ system khác nên mặc nhiên không thể kiểm soát/ thay đổi được.
Nếu mà có giải pháp trực tiếp từ các file rawdata thì tốt quá.
Nếu không thì để em test và thực hiện từ "tiền xử lý" với các hướng dẫn như @ongke0711 hướng dẫn thử xem thế nào.

 

[befaint]

Về cơ bản, các file rawdata này em nhận được từ system khác nên mặc nhiên không thể kiểm soát/ thay đổi được.

Về siêu siêu tối tối cơ bản là xuất phai ra phải chuẩn chỉ. Hiện tại đang sai bét tè nhè.
Bạn gửi bài phân tích ở trên cho "bên kia", để họ biết mà sửa. Nhanh trí thì cắm mặt vào mà sửa luôn và ngay, chứ để "ai đó" phát hiện ra lỗi đó thì bị chửi cho thối đầu luôn.

 

[ongke0711]

Nếu không thì để em test và thực hiện từ "tiền xử lý" với các hướng dẫn như @ongke0711 hướng dẫn thử xem thế nào.

Như @befaint đã đề cập ở trên, nếu file gốc lỗi thì cái lệnh đơn giản trên cũng không chạy được nhé.
Còn không được nữa thì kiếm code convert hàng loạt file Excel sang CSV xem thử.

 

[subasatran]

Thân chào các A/C và các bạn, Tiếp nối chủ đề này,
Em có thêm một điều nhờ các A/C và các bạn hướng dẫn thêm.
Giả sử với code import từ excel vào access mà em đang làm(file đính kèm).
Giờ em muốn khi import vào access thì mình add thêm vào table trong Access một column để ghi nhận lại số thứ tự của dữ liệ đã import vào.Ví dụữ liệu từ excel có 10 record, được ghi theo thứ tự dòng 1, dòng 2, dòng 3, dòng 4.... theo thứ tự từ trên xuống dướithì khi import vào access sẽ add thêm một field để ghi nhận thứ tự dòng 1 là 1, dòng 2 là 2, dòng 3 là 3....
Vậy,Điều này có thể thực hiện được không và nếu được các A/C và các bạn có thể hướng dẫn mình với ạ.

 

[ongke0711]

Thân chào các A/C và các bạn, Tiếp nối chủ đề này,
Em có thêm một điều nhờ các A/C và các bạn hướng dẫn thêm.
Giả sử với code import từ excel vào access mà em đang làm(file đính kèm).
Giờ em muốn khi import vào access thì mình add thêm vào table trong Access một column để ghi nhận lại số thứ tự của dữ liệ đã import vào.Ví dụữ liệu từ excel có 10 record, được ghi theo thứ tự dòng 1, dòng 2, dòng 3, dòng 4.... theo thứ tự từ trên xuống dướithì khi import vào access sẽ add thêm một field để ghi nhận thứ tự dòng 1 là 1, dòng 2 là 2, dòng 3 là 3....
Vậy,Điều này có thể thực hiện được không và nếu được các A/C và các bạn có thể hướng dẫn mình với ạ.

Đối với CSDL, cột để đánh số thứ tự không có ý nghĩa gì cả ngoại trừ nó được dùng làm mã định danh ( Primary key) tự động. Theo như bạn mô tả thì nó không phải mã định danh, vậy mục đích cột này dùng để làm gì, có cần thiết hay không để khỏi tốn code, mất thời gian chạy hoặc có thể có giải pháp khác cho nhu cần của bạn.

 

[subasatrant]

Đối với CSDL, cột để đánh số thứ tự không có ý nghĩa gì cả ngoại trừ nó được dùng làm mã định danh ( Primary key) tự động. Theo như bạn mô tả thì nó không phải mã định danh, vậy mục đích cột này dùng để làm gì, có cần thiết hay không để khỏi tốn code, mất thời gian chạy hoặc có thể có giải pháp khác cho nhu cần của bạn.

Hi anh @ongke0711 .
Chia sẻ với anh là dữ liệu em import vào access của các file raw excel em cần select dòng đầu tiên của file excel. Các file excel được sắp xếp theo ngày nhưng có những record cùng ngày với nhau và khi export ra excel không có thời gian. Bình thường khi làm trên excel em sẽ lấy dòng đầu tiên(system extract đã sort, dòng đầu tiên là dòng sau cùng) khi import vào access thì em ko lấy được dòng này nữa. Nên mục đích em add thêm field trong access để có thể lọc được dòng có giao dịch sau cùng này.
Anh tư vấn cho em hoặc có giải pháp nào tinh gọn hơn ko ạ.
Cám ơn anh nhiều

 

[ongke0711]

Hi anh @ongke0711 .
Chia sẻ với anh là dữ liệu em import vào access của các file raw excel em cần select dòng đầu tiên của file excel. Các file excel được sắp xếp theo ngày nhưng có những record cùng ngày với nhau và khi export ra excel không có thời gian. Bình thường khi làm trên excel em sẽ lấy dòng đầu tiên(system extract đã sort, dòng đầu tiên là dòng sau cùng) khi import vào access thì em ko lấy được dòng này nữa. Nên mục đích em add thêm field trong access để có thể lọc được dòng có giao dịch sau cùng này.
Anh tư vấn cho em hoặc có giải pháp nào tinh gọn hơn ko ạ.
Cám ơn anh nhiều

File Excel để import của bạn thiết kế như thế nào mà khi import nó mất cột ngày? Bạn chụp cái hình cũng được.
Không xử lý từ dữ liệu Exxel trước khi import à?

 

[subasatran]

Hi anh @ongke0711.
- Về số lượng file excel import vào access của em khá là nhiều nên bước đầu data này em không có xử lý trước khi import.
- Ý em là không phải import mất cột ngày(việc import của em là đã thành công) mà là nhu cầu của em cần lấy dòng giao dịch sau cùng của một ngày có giao dịch. Em có ví dụ trong hình ảnh đính kèm.
Nhờ anh xem thử nhé. Cám ơn anh.

 

[ongke0711]

Hi anh @ongke0711.
- Về số lượng file excel import vào access của em khá là nhiều nên bước đầu data này em không có xử lý trước khi import.
- Ý em là không phải import mất cột ngày(việc import của em là đã thành công) mà là nhu cầu của em cần lấy dòng giao dịch sau cùng của một ngày có giao dịch. Em có ví dụ trong hình ảnh đính kèm.
Nhờ anh xem thử nhé. Cám ơn anh.
View attachment 296621

Bản thân file Excel cũng không có bất cứ dữ liệu gì để phân biệt, sắp xếp luôn hả bạn? Chỉ "nhìn" theo sắp xếp hiện thời của file mà bốc ra dòng đầu tiên thì độ chính xác là không đảm bảo rồi.

 

[subasatran]

Bản thân file Excel cũng không có bất cứ dữ liệu gì để phân biệt, sắp xếp luôn hả bạn? Chỉ "nhìn" theo sắp xếp hiện thời của file mà bốc ra dòng đầu tiên thì độ chính xác là không đảm bảo rồi.

Đúng rồi anh.
Bản thân file excel cũng sử dụng công nghệ AI(eye) phiên bản cá biệt.
Biết là độ chính xác không đảm bảo rồi nhưng tạm thời vẫn đang chạy theo business nên vẫn sử dụng như vậy ?
Và các file này từ partner nên cũng không đòi hỏi được luôn.

 

[ongke0711]

Code thêm cột số thứ tự thì không khó. Tôi hỏi nhiều để biết chắc là không còn giải pháp nào khác, mức độ tin tưởng của giải pháp này.
Để tối rảnh tôi mới kiểm tra xem khi đọc vào ADO recordset nó có giữ nguyên thứ tự của file Excel không? Tôi chưa gặp trường hợp này.

 

[ongke0711]

Hi anh @ongke0711.
- Về số lượng file excel import vào access của em khá là nhiều nên bước đầu data này em không có xử lý trước khi import.
- Ý em là không phải import mất cột ngày(việc import của em là đã thành công) mà là nhu cầu của em cần lấy dòng giao dịch sau cùng của một ngày có giao dịch. Em có ví dụ trong hình ảnh đính kèm.
Nhờ anh xem thử nhé. Cám ơn anh.
View attachment 296621

Bạn xem file đính kèm bên dưới. Chú ý chuỗi kết nối phần HDR = No.

 

[subasatran]

Bạn xem file đính kèm bên dưới. Chú ý chuỗi kết nối phần HDR = No.

Hi anh.
Em lấy file về test thử không hiểu sao bấm vào buton Get Rawdata thì ko ko chạy và nó cũng ko thông báo gì hết trơn.
Table chứa không được import dữ liệu.
Anh xem thử khi anh điều chỉnh code bên anh nó có chạy được không vậy ạ.
Cám ơn anh rất nhiều.

 

[ongke0711]

Hi anh.
Em lấy file về test thử không hiểu sao bấm vào buton Get Rawdata thì ko ko chạy và nó cũng ko thông báo gì hết trơn.
Table chứa không được import dữ liệu.
Anh xem thử khi anh điều chỉnh code bên anh nó có chạy được không vậy ạ.
Cám ơn anh rất nhiều.
View attachment 296639

Chạy ra kết quả bình thường mà bạn. Chạy Debug code xem có báo lỗi thư viện gì không rồi thêm vào.

 

[subasatran]

Chạy ra kết quả bình thường mà bạn. Chạy Debug code xem có báo lỗi thư viện gì không rồi thêm vào.

View attachment 296640

Mình debug code làm sao để biết được mã lỗi anh nhỉ.
Khi bấm vào button thì nó không có hoạt động hoặc hiện tượng gì