Thắc mắc về sử lý định dạng số khi cập nhật, truy cấn dữ liệu bằng ADO (4 người xem)

[KVP]

Xin chào các anh, chị

Tôi có một vấn đề vướng mắc sử lý dữ liệu khi sử dụng ADO cập nhật, truy vấn dữ liệu, cụ thể:

1.Khi thêm dữ liệu vào CSDL (Excel), trường hợp dữ liệu cần cập nhật có nhiều trường (30 Filed) thì cách viết code như thế nào cho gọn.
Xin mượn code của anh Hai Lúa Miền Tây để minh họa :

[FONT=Verdana]Public cnn As New ADODB.Connection[/FONT]Sub CapNhat()
Dim rs As New ADODB.Recordset
Dim i, r As Integer
Set cnn = New ADODB.Connection
r = Range("A65000").End(xlUp).Row
  With cnn
    .ConnectionString = "Provider= Microsoft.Jet.OLEDB.4.0; data source=" & ThisWorkbook.Path & "\" & Range("F1").Value & ".xls;Extended Properties=Excel 8.0;"
    .CursorLocation = adUseClient
    .Open
  End With


Set rs = Nothing
rs.Open "Select * from [UP$]", cnn, 1, 3
 For i = 2 To r
    With rs
       .AddNew
         ![MA_SP] = Range("A" & i).Value
         !TEN_SP = Range("B" & i).Value
         !SL_SP = Range("C" & i).Value
[COLOR=#ff0000]         !..........[/COLOR]
[COLOR=#ff0000]         !..........[/COLOR]
[COLOR=#ff0000]         !..........
         30 Filed

[/COLOR]
       .Update
    End With
 Next
 Range("A2:C65000").ClearContents
Set rs = Nothing
cnn.Close
Set cnn = Nothing
End Sub

2. Khi thêm dữ liệu định dạng số vào CSDL (Excel), định dạng này trở thành dạng Text. Khi truy vấn ngược lại (ADO) từ CSDL việc định dạng Text gây khó khăn cho việc tính toán. Như vậy, phải sử lý định dạng Filed của Recordset hoặc sử lý số liệu trong CSDL excel ?
Tôi muốn hỏi cách sử lý dữ liệu này như thế nào?

Rất mong nhận được sự giúp đỡ của các anh chị.

 

[Hai Lúa Miền Tây]

Xin chào các anh, chị

Tôi có một vấn đề vướng mắc sử lý dữ liệu khi sử dụng ADO cập nhật, truy vấn dữ liệu, cụ thể:

1.Khi thêm dữ liệu vào CSDL (Excel), trường hợp dữ liệu cần cập nhật có nhiều trường (30 Filed) thì cách viết code như thế nào cho gọn.
Xin mượn code của anh Hai Lúa Miền Tây để minh họa :

[FONT=Verdana]Public cnn As New ADODB.Connection[/FONT]Sub CapNhat()
Dim rs As New ADODB.Recordset
Dim i, r As Integer
Set cnn = New ADODB.Connection
r = Range("A65000").End(xlUp).Row
  With cnn
    .ConnectionString = "Provider= Microsoft.Jet.OLEDB.4.0; data source=" & ThisWorkbook.Path & "\" & Range("F1").Value & ".xls;Extended Properties=Excel 8.0;"
    .CursorLocation = adUseClient
    .Open
  End With


Set rs = Nothing
rs.Open "Select * from [UP$]", cnn, 1, 3
 For i = 2 To r
    With rs
       .AddNew
         ![MA_SP] = Range("A" & i).Value
         !TEN_SP = Range("B" & i).Value
         !SL_SP = Range("C" & i).Value
[COLOR=#ff0000]       !..........[/COLOR]
[COLOR=#ff0000]       !..........[/COLOR]
[COLOR=#ff0000]       !..........
         30 Filed

[/COLOR]
       .Update
    End With
 Next
 Range("A2:C65000").ClearContents
Set rs = Nothing
cnn.Close
Set cnn = Nothing
End Sub

2. Khi thêm dữ liệu định dạng số vào CSDL (Excel), định dạng này trở thành dạng Text. Khi truy vấn ngược lại (ADO) từ CSDL việc định dạng Text gây khó khăn cho việc tính toán. Như vậy, phải sử lý định dạng Filed của Recordset hoặc sử lý số liệu trong CSDL excel ?
Tôi muốn hỏi cách sử lý dữ liệu này như thế nào?

Rất mong nhận được sự giúp đỡ của các anh chị.

Câu 1: Bạn có thể thay thế cách nhập từng field như trên thành nhập nguyên bảng, nếu code gọn thì làm 2 bảng có cùng cấu trúc, mình chỉ cần select * là xong, ngược lại bạn muốn nhập theo từng field theo ý thì chỉ có cách là gõ từng field theo ý thích của bạn.
Câu 2: Vấn đề nằm ở chổ dữ liệu ở record đầu tiên của bạn nó là text thì các dòng sau nó sẽ ngầm hiểu field đó là text, vậy cách khắc phục là khi nhập dữ liệu vào dòng đầu tiên thì phải xác định rõ field nào là dạng nào.

 

[KVP]

Câu 1: Bạn có thể thay thế cách nhập từng field như trên thành nhập nguyên bảng, nếu code gọn thì làm 2 bảng có cùng cấu trúc, mình chỉ cần select * là xong, ngược lại bạn muốn nhập theo từng field theo ý thì chỉ có cách là gõ từng field theo ý thích của bạn.

Vậy là nếu không có cách nào khác thì chỉ còn cách tổ chức lại cơ sở dữ liệu đầu vào.

Câu 2: Vấn đề nằm ở chổ dữ liệu ở record đầu tiên của bạn nó là text thì các dòng sau nó sẽ ngầm hiểu field đó là text, vậy cách khắc phục là khi nhập dữ liệu vào dòng đầu tiên thì phải xác định rõ field nào là dạng nào.

Anh ơi, hình như tất cả các dòng đầu tiên dạng số khi đưa vào đều bị định dạng Text toàn bộ.
Nếu không có cách nào để sử lý trên Recordset thì có cách nào sử lý tổng hợp để đưa một số Field về dạng số trong CSDL ?

 

[Hai Lúa Miền Tây]

Vậy là nếu không có cách nào khác thì chỉ còn cách tổ chức lại cơ sở dữ liệu đầu vào.


Anh ơi, hình như tất cả các dòng đầu tiên dạng số khi đưa vào đều bị định dạng Text toàn bộ.
Nếu không có cách nào để sử lý trên Recordset thì có cách nào sử lý tổng hợp để đưa một số Field về dạng số trong CSDL ?

Bạn dùng code như bên dưới nhé.

Dim cnn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Sub CapNhat()
If cnn.State = 1 Then cnn.Close
If rs.State = 1 Then rs.Close
  With cnn
    .ConnectionString = "Provider= Microsoft.Jet.OLEDB.4.0; data source=" & ThisWorkbook.Path & _
                        "\" & Range("F1").Value & ".xls;Extended Properties=Excel 8.0;"
    .CursorLocation = adUseClient
    .Open
  End With
On Error GoTo loi
    Dim strSQL As String
     strSQL = "INSERT INTO [up$] SELECT ma_sp, ten_sp,sl_sp FROM " & _
              "[EXCEL 8.0;Database=" & ThisWorkbook.FullName & ";HDR=Yes].[data$];"
     
     rs.Open strSQL, cnn, 1, 3
    
    MsgBox "Da nhap xong du lieu !", vbInformation
   ' Range("A2:C65000").ClearContents
    Set rs = Nothing
    cnn.Close
    Set cnn = Nothing
    Exit Sub
loi:
MsgBox Err.Description, vbCritical, "Error"
End Sub

 

[KVP]

strSQL = "INSERT INTO [up$] SELECT ma_sp, ten_sp,sl_sp FROM " & _
"[EXCEL 8.0;Database=" & ThisWorkbook.FullName & ";HDR=Yes].[data$];"​

Cái chuỗi SQL này kết hợp hay thật. Từ trước tới giờ em cứ nghĩ là ADO chỉ thực hiện được duy nhất "Select .." trong excel
Vì em đã từng thắc mắc tại đây
Em sẽ phải thử đối với các câu lệnh truy vấn khác của Excel. Cho em hỏi chút: Ngoại trừ Delete, update như thắc mắc trên anh thường dùng những câu lệnh SQL nào nữa không ạ!

 

[Hai Lúa Miền Tây]

Cái chuỗi SQL này kết hợp hay thật. Từ trước tới giờ em cứ nghĩ là ADO chỉ thực hiện được duy nhất "Select .." trong excel<BR>Vì em đã từng thắc mắc <A href="http://www.giaiphapexcel.com/forum/showthread.php?43174-Th%E1%BA%AFc-m%E1%BA%AFc-v%E1%BB%81-ADO-v%C3%A0-CSDL-Excel" target=_blank>tại đây</A> <BR>Em sẽ phải thử đối với các câu lệnh truy vấn khác của Excel. Cho em hỏi chút: Ngoại trừ Delete, update như thắc mắc trên anh thường dùng những câu lệnh SQL nào nữa không ạ!

1.) Các câu lệnh truy vấn căn bản như sau:

• SQL select
• SQL Where
• SQL and & or
• SQL Between
• SQL Distinct
• SQL Order By
• SQL Insert
• SQL Update
• SQL Delete
• SQL Count

2.) Các câu lệnh nâng cao

• Các hàm
• SQL Group By
• Bí danh SQL
• SQL Join
• SQL Create
• SQL Alter

Tuy nhiên trong Excel ADO có 1 số câu lệnh không được hổ trợ.

 

[KVP]

Tuy nhiên trong Excel ADO có 1 số câu lệnh không được hổ trợ.

Cái em muốn biết là cái này

 

[KVP]

Câu 1: Bạn có thể thay thế cách nhập từng field như trên thành nhập nguyên bảng, nếu code gọn thì làm 2 bảng có cùng cấu trúc, mình chỉ cần select * là xong, ngược lại bạn muốn nhập theo từng field theo ý thì chỉ có cách là gõ từng field theo ý thích của bạn.
Câu 2: Vấn đề nằm ở chổ dữ liệu ở record đầu tiên của bạn nó là text thì các dòng sau nó sẽ ngầm hiểu field đó là text, vậy cách khắc phục là khi nhập dữ liệu vào dòng đầu tiên thì phải xác định rõ field nào là dạng nào.

Em mới tìm hiểu thêm được vài vấn đề về ADO trong Excel (nguồn)

"...Loại dữ liệu
Không giống như một cơ sở dữ liệu truyền thống, there's no way trực tiếp để xác định các loại dữ liệu cho cột trong Excel bảng. Thay vào đó, các nhà cung cấp OLE DB quét một số giới hạn các hàng trong cột "đoán" kiểu dữ liệu cho trường. Số hàng để quét mặc định là tám (8) hàng; bạn có thể thay đổi số hàng để quét bằng cách xác định giá trị từ một (1) và mười sáu (16) cho các thiết lập MAXSCANROWS trong các thuộc tính mở rộng của chuỗi kết nối của bạn..."

(Dịch từ Google)

[h=4]

How to Change Excel Data: Edit, Add, and Delete[/h]Edit

You can edit Excel data with the normal ADO methods. Recordset fields which correspond to cells in the Excel worksheet containing Excel formulas (beginning with "=") are read-only and cannot be edited. Remember that an ODBC connection to Excel is read-only by default, unless you specify otherwise in your connection settings. See earlier under "Using the Microsoft OLE DB Provider for ODBC Drivers."

Add

You can add records to your Excel recordsource as space allows. However, if you add new records outside the range that you originally specified, these records are not visible if you requery on the original range specification. See earlier under "A caution about specifying ranges."

In certain circumstances, when you use the AddNew and Update methods of the ADORecordset object to insert new rows of data into an Excel table, ADO may insert the data values into the wrong columns in Excel. For additional information, click the article number below to view the article in the Microsoft Knowledge Base:[h=4]314763 FIX: ADO Inserts Data into Wrong Columns in Excel

[/h]

 

[Hai Lúa Miền Tây]

Cập nhật, thêm mới hay truy vấn đều được tuy nhiên đối với những record có công thức thì không thể cập nhật và bạn muốn xoá thì lại càng không thể.

 

[lethanhnhan]

Cập nhật, thêm mới hay truy vấn đều được tuy nhiên đối với những record có công thức thì không thể cập nhật và bạn muốn xoá thì lại càng không thể.

Ngoài ra còn một số vấn đề khác nữa. Chính vì vậy sau khi ngâm cứu cái ADO + Excel, tôi quyết định không sử dụng mà chuyển sang ADO + Access hay ADO + SQLServer.

Le Thanh Nhan

 

[loinguyen212]

Khi em truy vấn từ một file excel khác sang thì bị lỗi là toàn bộ dữ liệu vừa được lấy sang chuyển hết sang dạng "Date", mặc dù chỉ có nguyên cột đầu là dữ liệu ngày tháng thôi. Em thử bỏ đi để lại một ít dữ liệu ngắn hoặc chỉ lấy một vài trường thôi nhưng kết quả vẫn vậy. Có ai xử lý được trường hợp này không ạ??

 

[Hai Lúa Miền Tây]

Khi em truy vấn từ một file excel khác sang thì bị lỗi là toàn bộ dữ liệu vừa được lấy sang chuyển hết sang dạng "Date", mặc dù chỉ có nguyên cột đầu là dữ liệu ngày tháng thôi. Em thử bỏ đi để lại một ít dữ liệu ngắn hoặc chỉ lấy một vài trường thôi nhưng kết quả vẫn vậy. Có ai xử lý được trường hợp này không ạ??

Có thể những cột bên file data đã được định dạng là kiểu ngày? Hoặc có thể những dòng dữ liệu đầu tiên là dạng ngày.
Bạn đưa file xem thử nhé.

 

[loinguyen212]

Dữ liệu trong file đính kèm.
Nhờ anh giúp đỡ.

 

[Hai Lúa Miền Tây]

Dữ liệu trong file đính kèm.
Nhờ anh giúp đỡ.

Bạn dò và xoá chổ màu đỏ như sau thử nhé

With Sheets("NhatKyNX")        
   .Range("A4:N100").ClearContents
   .Range("A4[COLOR=#ff0000][B]:N4[/B][/COLOR]").CopyFromRecordset Rec
End With

 

[loinguyen212]

Bạn dò và xoá chổ màu đỏ như sau thử nhé

With Sheets("NhatKyNX")        
   .Range("A4:N100").ClearContents
   .Range("A4[COLOR=#ff0000][B]:N4[/B][/COLOR]").CopyFromRecordset Rec
End With

Hay quá, nhưng cho em thắc mắc một chút là tại sao? Tại sao các loại khác em vẫn dùng như thế nhưng không bị ảnh hưởng, nhưng với CSDL như thế lại bị lỗi.

 

[Hai Lúa Miền Tây]

Hay quá, nhưng cho em thắc mắc một chút là tại sao? Tại sao các loại khác em vẫn dùng như thế nhưng không bị ảnh hưởng, nhưng với CSDL như thế lại bị lỗi.

Tự xem lại CSDL và tự nghiệm ra đi bạn.

 

[loinguyen212]

Vẫn chưa được, em thử thì khi mở file nguồn lên thì ok, nhưng khi đóng lại và lấy dữ liệu thì excel báo: external table is not the expected format.
Trường hợp này có thể xử lý thế nào được ạ?

 

[loinguyen212]

Tự xem lại CSDL và tự nghiệm ra đi bạn.

Nếu nói là nghiệm ra thì em chưa nghiệm được, nhưng em nghĩ do CSDL của em còn trống nhiều, chưa phải là một CSDL chuẩn. Nhưng cũng vì thế mà em vẫn còn những thắc mắc và muốn hiểu thêm về CSDL, mong anh giải đáp thêm.
Chân thành cảm ơn anh Hai Lúa đã giúp đỡ.

 

[loinguyen212]

Em tìm hiểu thì hình như do file nguồn có mật khẩu, trường hợp này thì phải làm ntn ??

 

[loinguyen212]

Bạn dò và xoá chổ màu đỏ như sau thử nhé

With Sheets("NhatKyNX")        
   .Range("A4:N100").ClearContents
   .Range("A4[COLOR=#ff0000][B]:N4[/B][/COLOR]").CopyFromRecordset Rec
End With

Chào a Hai Lúa,

Khi sử dụng code trên thì tốc độ rất chậm, có thể do code mất thời gian chuyển cột đầu tiên sang dạng ngày tháng (cả 10.000 dòng, trong khi dữ liệu có vài trăm dòng thì code cũng tự động định dạng cột đầu tiên sang dạng ngày tháng). Trường hợp này không biết cách khắc phục thế nào, mong anh chỉ giáo.

Trân trọng!