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

Liên hệ QC
KVP

KVP

Thành viên thường trực
Tham gia
7/7/07
Bài viết
218
Được thích
301
Nghề nghiệp
Cộng đồng
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 :
Mã: 
[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ị.
 

File đính kèm

  • CapNhatDLSangWBKhac.rar
    13.8 KB · Đọc: 32
Lần chỉnh sửa cuối:
KVP đã viết:
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 :
Mã: 
[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ị.
Nhấp chuột vào đây để mở rộng...

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.
 
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.
Nhấp chuột vào đây để mở rộng...
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.
Nhấp chuột vào đây để mở rộng...
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 ?
 
KVP đã viết:
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 ?
Nhấp chuột vào đây để mở rộng...
Bạn dùng code như bên dưới nhé.

Mã: 
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
 

File đính kèm

  • CapNhatDLSangWBKhac.zip
    16.2 KB · Đọc: 95
strSQL = "INSERT INTO [up$] SELECT ma_sp, ten_sp,sl_sp FROM " & _
 "[EXCEL 8.0;Database=" & ThisWorkbook.FullName & ";HDR=Yes].[data$];"​
Nhấp chuột vào đây để mở rộng...

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 ạ!
 
KVP đã viết:
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 ạ!
Nhấp chuột vào đây để mở rộ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ợ.
 
Lần chỉnh sửa cuối:
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.
Nhấp chuột vào đây để mở rộng...

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
Nhấp chuột vào đây để mở rộng...
[/h]
 
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ể.
 
Hai Lúa Miền Tây đã viết:
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ể.
Nhấp chuột vào đây để mở rộng...
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
 
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 ạ??
 
loinguyen212 đã viết:
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 ạ??
Nhấp chuột vào đây để mở rộ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é.
 
Dữ liệu trong file đính kèm.
Nhờ anh giúp đỡ.
 

File đính kèm

  • GPE070815.rar
    26.6 KB · Đọc: 9
Hai Lúa Miền Tây đã viết:
Bạn dò và xoá chổ màu đỏ như sau thử nhé

Mã: 
With Sheets("NhatKyNX")        
   .Range("A4:N100").ClearContents
   .Range("A4[COLOR=#ff0000][B]:N4[/B][/COLOR]").CopyFromRecordset Rec
End With
Nhấp chuột vào đây để mở rộng...
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. +-+-+-++-+-+-++-+-+-+
 
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 ạ?
 
Hai Lúa Miền Tây đã viết:
Tự xem lại CSDL và tự nghiệm ra đi bạn.
Nhấp chuột vào đây để mở rộng...
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 đỡ.
 
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 ??
 
Hai Lúa Miền Tây đã viết:
Bạn dò và xoá chổ màu đỏ như sau thử nhé

Mã: 
With Sheets("NhatKyNX")        
   .Range("A4:N100").ClearContents
   .Range("A4[COLOR=#ff0000][B]:N4[/B][/COLOR]").CopyFromRecordset Rec
End With
Nhấp chuột vào đây để mở rộng...
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!
 
Bạn phải đăng nhập hoặc đăng ký để trả lời bài viết tại đây.
Web KT

Bài viết mới nhất

H
Giúp em đặt thêm công thức cho phép năm
    • Chấp nhận
Trả lời
4
Đọc
114
Hoàng Tuấn 868
H
8
  • Question
Xin giúp: Nhóm (group data) các dòng trong bảng tính
Trả lời
10
Đọc
366
DeTong
D
pupuchcha
  • Question
Viết code để tìm xích ma, mong mọi người giúp đỡ em ạ
Trả lời
6
Đọc
213
VetMini
V
longhungvnpost
kết hợp hàm vlookup và hlookup như thế nào, giúp e với
Trả lời
4
Đọc
75
longhungvnpost
longhungvnpost
Emyeuexcel
  • Question
VBA lưu file, in, và mở lại file gốc ban đầu
2
Trả lời
29
Đọc
523
Emyeuexcel
Emyeuexcel
Xem thêm…

Facebook

Giaiphapexcel.com

Group

DIỄN ĐÀN GIẢI PHÁP EXCEL Group 1
DIỄN ĐÀN GIẢI PHÁP EXCEL Group 2

Thành viên có số lượng bài viết cao nhất tháng

Thành viên có điểm tương tác cao nhất tháng

Back
Top Bottom