Bằng đối tượng QueryTable của VBA excel ta lấy dữ liệu csv

Euler

Administrator
Thành viên BQT
Trong chủ đề này, chúng ta sẽ cùng nhau bàn luận về thao tác với file CSV.
1.
2.
3.
4.
5.
6.
7.
 

giaiphapvba

Administrator
Thành viên BQT
csv là gì? Những chú ý khi thao tác csv bằng excel.
Chào mọi người. Em đến từ diễn đàn tuhocvba.net, đây không phải là diễn đàn VBA đầu tiên em tham gia, nhưng là diễn đàn em được mọi người quý mến, giao cho trọng trách cùng các anh chị dịch bài.

Mọi người chắc đã quen thuộc với file csv, nhất là các anh làm bên thiết kế bên điện, các phần mềm như cabling hay là từ web xuất ra file csv rất nhiều phải không ạ?
Nếu mọi người google thì cũng thấy rằng, csv là định dạng tệp tin phổ biến để xuất dữ liệu từ nhiều dịch vụ và các chương trình quản lý khác nhau trên thế giới.
Nó có tính tương đối so với excel, chúng ta ít khi phân biệt được. Tuy nhiên khi mở nó, thực thi copy hay paste, hay thực hiện những công việc tương tự trên excel, như là lập bảng tính, thì chúng ta thấy nó thật rắc rối.
Ngay như việc mở dữ liệu cũng thấy rắc rối, mỗi người mở có khi lại cho một bảng dữ liệu hiển thị khác nhau ấy chứ.
Vậy thì chúng ta hãy bắt đầu từng bước một, trong việc tự động hóa bằng VBA để thu lấy thông tin từ file csv.

1. File csv là gì?
1.1 Chúng ta thử mở file csv.
2. Lý do chúng ta không thể dùng excel để mở file csv một cách bình thường được?
2.1 Số 0 ở đầu dãy ký tự bị biến mất.
2.2 Tự ý phán đoán dữ liệu ngày tháng và thay đổi nội dung hiển thị.
3. Tổng kết.


Đầu tiên ta đi vào mục số 1.
1. File csv là gì?
Csv là các chữ cái đầu của "Comma-Separated Values" : Giá trị phân tách bởi dấu phẩy ",".
Nói cho chùng thì csv được định nghĩa đầy đủ như sau:

Là một loại định dạng dữ liệu, các cột dữ liệu được định nghĩa phân tách bởi dấu phẩy ",".
Những phần mềm bảng tính hay là cơ sở dữ liệu có các kiểu định dạng file khác nhau. Và để trao đổi dữ liệu giữa các phần mềm khác nhau đó, người ta nghĩ ra một định dạng chung, đó là csv. Nó có thể hiển thị dưới dạng text thông thường nếu chúng ta mở nó bằng phần mềm đọc text như notepad. Chúng ta cũng có thể biên tập nó, xem dữ liệu bên trong bằng các phần mềm khác nhau, ví dụ như Excel.


Như vậy chúng ta đi đến kết luận, csv không phải là file excel, đó là một định dạng file text.
Dữ liệu được phân tách bởi dấu phẩy ",".
Bản ghi được phân cách bởi các mục (cột). Hiểu như là một dòng trên bảng tính excel, nhưng còn được phân cách bởi các cột.
Và cuối cùng, nó là loại dữ liệu để các phần mềm trao đổi thông tin với nhau.

1.1 Thử mở file csv
Chúng ta thử mở một file csv sẽ thấy chúng có rất nhiều bản ghi (dòng dữ liệu), các dữ liệu được phân tách bởi dấu phẩy ",".
Bạn cần đăng nhập để thấy hình ảnh

Nếu chúng ta mở bằng excel với thiết định phân tách dấu phẩy, chúng ta sẽ thấy nó là các cột dữ liệu.
Bạn cần đăng nhập để thấy hình ảnh

2.Lý do chúng ta không thể mở file csv bằng excel một cách thông thường
Ví dụ như double click file csv. Hoặc là mở excel lên rồi kéo file csv vào, rất có thể sẽ xảy ra lỗi ngoài ý muốn.
Nào, bây giờ chúng ta thử code sau, khá giống code của anh Snow24.
Mã:
Sub openCSV()

Workbooks.Open "C:\tuhocvba.net\file01.csv"

End Sub
Tất nhiên việc mở này là có thể được. Nó giống hoàn toàn với thao tác bằng tay, double click file csv. Và tất nhiên cũng có trường hợp không mong muốn xảy ra, lỗi hiển thị như của anh Euler.
2.1 Mất số 0 ở đầu
Chúng ta cùng nhìn dữ liệu dưới đây, chúng đều có số 0 ở đầu.
Bạn cần đăng nhập để thấy hình ảnh

Các bản ghi (dòng dữ liệu) đều bắt đầu bằng các số như là 001,002,003,...
Và bây giờ mở bằng excel nhé:
Bạn cần đăng nhập để thấy hình ảnh

Chúng ta thấy số 0 ở đầu đã bị biến mất như hình minh họa trên.
Chưa hết đâu, còn một cái tội to đùng, là tự ý phán đoán, tự tiện thay đổi dữ liệu hiển thị.
2.2 Tự ý phán đoán ngày tháng và hiển thị
Hãy nhìn cột dữ liệu khoanh đỏ dưới đây, chúng được ghi như là 01-01, 01-02,...
Bạn cần đăng nhập để thấy hình ảnh

Và khi mở bằng excel thì hiển thị thành ngày tháng như thế này đây.
Anh chị dùng VBA thì dùng Workbook.Open là thấy ngay nhé:
Bạn cần đăng nhập để thấy hình ảnh

3.Tổng kết
Có thể sử dụng VBA để mở file csv thu lấy thông tin từ nó. Việc này chúng ta sẽ bàn sau.
Trên đây đã liệt kê hàng loạt chú ý để mọi người cẩn thận khi xử lý dữ liệu csv.
Chú ý: Bài viết này đúng ra là được đăng trước bài trên của anh Euler.
Nhưng do em dịch chậm, nên đăng muộn. Cả nhà thông cảm nhé.
Nguồn (Phần 1):
 

vbano1

SMod
Thành viên BQT
Chương trình đơn giản để lấy dữ liệu file csv
Chào mọi người, đội ngũ dịch thuật của tuhocvba.net tiếp tục biên dịch chuyên đề về csv. Hi vọng bài viết sẽ có ích cho các bạn.
Ở bài viết trước, giaiphapvba đã cho chúng ta biết csv là gì, các chú ý gặp phải khi mở file csv.
Ở bài viết này, mình sẽ cung cấp cho các bạn một chương trình đơn giản để lấy dữ liệu csv.

1. Chương trình lấy từng dòng dữ liệu trên file csv.
1.1 Các bước để lấy từng dòng dữ liệu trên file csv.
1.2. Kết quả thực tế.
2. Mệnh lệnh thao tác với file csv.
2.1 Mở file csv (Open csv file)
2.2 Đọc từng dòng dữ liệu trên file csv.
2.3 Chạy vòng lặp Do Until EOF(1) ~ Loop cho tới khi gặp dòng cuối cùng kết thúc file.
2.4 Close file (Đóng file)
3. Tổng kết


Sau đây lần lượt chúng ta sẽ đi vào từng mục.
1. Chương trình lấy từng dòng dữ liệu trên file csv.
Chúng ta sẽ cho hiển thị lần lượt từng dòng dữ liệu csv trên cửa sổ Immediate.
Mã:
'Đọc từng dòng dữ liệu trên file csv và ghi ra trên cửa sổ Debug
Sub openCSV()

Dim strPath As String
strPath = "C:\tuhocvba.net\file01.csv"

Open strPath For Input As #1 'Mở file csv, chú ý cách thức đọc file này sẽ lỗi nếu đường link có dấu tiếng việt.

Dim i As Long
Dim strLine As String

Do Until EOF(1)

    Line Input #1, strLine
    Debug.Print strLine

Loop

Close #1

End Sub
1.1 Các bước để lấy từng dòng dữ liệu trên file csv.
#1 hay EOF, tôi nghĩ nó là những thứ không thể thấy bằng mắt thường, tuy nhiên nó không khó đâu.
Sau đây tôi sẽ thuyết minh một cách đơn giản chu trình trên.
  1. Mở file csv
  2. Đọc từng dòng trên file csv cho tới khi kết thúc file.
  3. Đóng file csv.
Chu trình trên thật đơn giản, phải không nào?
1.2. Kết quả thực tế.
Xem ảnh minh họa sau:
Bạn cần đăng nhập để thấy hình ảnh

Chú thích người dịch tuhocvba.net: Cửa số Immediate để hiển thị, các bạn click như ảnh dưới đây để lấy ra.
Bạn cần đăng nhập để thấy hình ảnh

Như vậy chúng ta cảm thấy kết quả như trên có vẻ ổn rồi đúng không nào?
2. Mệnh lệnh thao tác với file csv.
Ở bài viết trước, giaiphapvba đã cho chúng ta biết file csv là loại file text. Như vậy chúng ta sẽ sử dụng VBA đối xử với nó như là một file text.
Ở bài viết này, tôi sẽ thuyết minh từng chút từng chút một để các bạn hiểu các mệnh lệnh VBA làm việc với nó nhé.
2.1 Mở file csv (Open csv file)
Ở đoạn code trên chắc các bạn đã thấy lệnh Open, và đã có thể mường tượng ra phần nào rồi phải không?
Cấu trúc của nó là:
Open đường_dẫn_file_csv For Mục_đích As #Số_hiệu
Đây không phải là chúng ta đang mở trên excel đâu nhé, tôi nghĩ rằng, đó là một ngôn ngữ để làm việc với nội dung file bên trong.
  • Input:Chúnngg ta mở file để đọc (đọc thôi chứ không ghi gì lên file đó nhé), coi nó như là input (đầu vào)
  • Output:Chúnngg ta mở để ghi đè vào file đó
  • Append:Chúnng ta mở và ghi thêm dữ liệu vào file đó
Như vậy, các bạn đã hiểu ý nghĩa tại sao chúng ta để là Input rồi phải không? Là vì chúng ta chỉ muốn đọc dữ liệu thôi.
Đối với trường hợp chúng ta ghi đè, nhằm tạo ra file mới thì chú ý:
Workbook_Đối_tượng.SaveAs Filename:=Tên_File, FileFormat:=”xlCSV”
Tóm lại, tạm thời bạn chỉ cần nhớ tham số Input là được, vì mục đích lần này của chúng ta là chỉ đọc dữ liệu mà thôi.
Đối với tham số Số_hiệu, đó là bạn muốn mở đồng thời bao nhiêu lần(Lời người dịch tuhocvba.net: Đoạn này mình không chắc, có thể là đồng thời có nhiều file đang được mở, và chúng ta gán file này mang số hiệu 1 để không nhầm với file khác), tuy nhiên tôi không nghĩ đó là điều thường gặp. Do đó bạn cứ để là #1, là được rồi.
2.2 Đọc từng dòng dữ liệu trên file csv.
Chúng ta còn thấy trên đoạn code trên có phần:
Line Input #Số_hiệu, Tham_số
File mà đang mở (Open) ứng với số_hiệu, chúng ta đọc từng dòng và cất dữ liệu đọc được đó vào Tham_số.
Ở đây có một điểm quan trọng và tuyệt diệu, nó sẽ đọc và lấy dữ liệu dòng thứ nhất, rồi từ dòng đó, nó di chuyển sang dòng tiếp theo.
Bằng tính năng này, chúng ta cho nó vào vòng lặp Loop, chúng ta có thể lấy được toàn bộ dữ liệu một cách thật đơn giản phải không nào?
2.3 Chạy vòng lặp Do Until EOF(1) ~ Loop cho tới khi gặp dòng cuối cùng kết thúc file.
Như đã nói, chúng ta có thể đọc từng dòng dữ liệu và tự động đưa con trỏ tới dòng dữ liệu tiếp theo. Để đọc toàn bộ nội dung file, chúng ta phải cho vào vòng lặp Loop.
Mã:
Do Until Điều_Kiện
 ~Xử lý bên trong~
Loop
Khi Điều_Kiện được thành lập thì kết thúc thực thi code xử lý bên trong. Điều kiện lần này của chúng ta là EOF(1).
Điều kiện được thành lập khi điểm đọc file số_hiệu 1 tới End Of File thì kết thúc.
2.4 Close file (Đóng file)
Chúng ta đóng file có số_hiệu1.
Mã:
Close #số_hiệu
3. Tổng kết
Như vậy ở trên tôi đã thuyết minh xong việc đọc dữ liệu từ file csv, ghi ra cửa sổ Debug. Qui trình của nó là: Mở file -> Đọc từng dòng cho tới khi kết thúc file -> Đóng file.
Nguồn (Phần 2):
 

tuhocvba

Administrator
Thành viên BQT
Bằng VBA, chúng ta lấy dữ liệu CSV, bỏ đi dấu phẩy "," và cho hiển thị trên worksheet.
Chúng ta đã được giới thiệu ở bài trước bằng cách đọc từng dòng dữ liệu trên file csv.
Tuy nhiên, thực tế là chúng ta muốn lấy dữ liệu lên excel. Vậy thì cách nghĩ là, từng dòng dữ liệu đã được đọc ra đó, ta sẽ tách ra để cho hiển thị trên excel.
Lần này, tôi sẽ giới thiệu mọi người phương pháp lấy dữ liệu, bằng cách đọc từng dòng dữ liệu CSV sau đó chúng ta tách các ký tự bởi dấu phẩy "," bằng lệnh Split.
Nào, chúng ta cùng thử nhé.
Đầu tiên, chúng ta cùng quay lại code của bài trước, về việc đọc từng dòng dữ liệu csv:
Mã:
'Doc du lieu tung dong tren file csv
Sub openCSV()

Dim strPath As String
strPath = "C:\Users\Noriaki\Dropbox\filetest.csv"

Open strPath For Input As #1 'Open file csv

Dim i As Long
Dim strLine As String

Do Until EOF(1)

  Line Input #1, strLine
  Debug.Print strLine

Loop

Close #1
End Sub
Chương trình trên không phải là dài, vì vậy tôi mong các bạn hãy nhớ lấy nó: Open,Close,Do Until EOF(1),Line Input.
Dữ liệu thực tế được thể hiện như dưới đây:
Bạn cần đăng nhập để thấy đính kèm


Mã:
Sub getCSV()

Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets(1)

Dim strPath As String
strPath = "C:\Users\Noriaki\Dropbox\filetest.csv"

Dim i As Long, j As Long
Dim strLine As String
Dim arrLine As Variant 'Dung lenh Split de tach cac ky tu bang dau phay "," sau do cat no vao mang nay

Open strPath For Input As #1 'Open file csv

i = 1
Do Until EOF(1)
 
    Line Input #1, strLine
    arrLine = Split(strLine, ",") 'Tach chuoi ky tu strLine bang lenh split, ky tu phan cach la ","-sau do cat vao mang
 
    For j = 0 To UBound(arrLine)
     
        ws.Cells(i, j + 1).Value = arrLine(j)
 
    Next j
    i = i + 1
Loop

Close #1

End Sub
Bây giờ, chúng ta sẽ phân tích code ở bên trên nhé.
Chương trình trên được khái quát theo chu trình dưới đây:
1. Nó đọc từng dòng dữ liệu và cất vào strLine. Tuy nhiên chuỗi ký tự này là: A,B,C,... tức là các ký tự phân cách bằng dấu phẩy.
2. Chúng ta dùng lệnh split để phân tách strLine mà ở đó ký tự phân cách là dấu phẩy ","
3. Chúng ta cất các ký tự phân cách được cho vào mảng arrLine. Sau đó ghi lên file excel nội dung của mảng arrLine.
Quá trình trên tiếp diễn cho tới khi đọc tới dòng cuối cùng của file csv.
Tôi minh họa bằng hình dưới đây:
Bạn cần đăng nhập để thấy đính kèm


Hàm split được diễn giải như sau:
Mã:
Mảng = Split(chuỗi_ký_tự, ký_tự_phân_cách)
Ta có thể ví von mảng giống như một dãy các hộp để chứa giá trị.
Các hộp này được đánh số từ Lbound(mảng) tới Ubound(mảng).
Đối với Split, thì hộp đầu tiên sẽ được mang số 0.
Do đó ở trên ta đã viết:
Mã:
 For j = 0 To UBound(arrLine)
Sẽ có người thắc mắc, ơ thế sao lại khai báo mảng như thế này:
Mã:
Dim arrLine As Variant
Vậy Variant là gì? Hiểu đơn giản, variant là kiểu dữ liệu gì cũng được. Ủa, nó đa năng đến như vậy ư? Nhưng tôi khuyên bạn, chỉ nên khai báo mảng bằng Variant, còn đối với số và ký tự, chúng ta không nên lạm dụng, mà hãy khai báo tường minh như trước đây.

Kết quả của code trên được thể hiện như dưới đây:
Bạn cần đăng nhập để thấy đính kèm


Diễn đàn tuhocvba.net tham khảo và dịch từ (Phần 3) :
 
Phương pháp lấy dữ liệu CSV trong tình huống dữ liệu không xuống dòng
Ở phần trước chúng ta đã được giới thiệu cách lấy từng dòng dữ liệu của file csv.
Tuy nhiên, còn một tình huống đó là file csv không có xuống dòng, khi đó việc lấy dữ liệu phải xử lý như thế nào?

1. Nhắc lại cách lấy từng dòng dữ liệu file csv
2. Trường hợp file csv xuống dòng không thuận lợi
2.1 Ta có ba trường hợp xuống dòng
2.2 Lý do không xuống dòng
3. Chương trình phán đoán LF và sử dụng Split để phân tách dữ liệu



1. Nhắc lại cách lấy từng dòng dữ liệu file csv
Ở phần trước chúng ta đã thực hiện lấy dữ liệu từng dòng của file csv sau đó dùng lệnh split để phân tách dữ liệu bởi dấu phẩy ",".
Mã:
Sub getCSV()

Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets(1)

Dim strPath As String
strPath = "C:\test\tuhocvba.csv"

Dim i As Long, j As Long
Dim strLine As String
Dim arrLine As Variant 'Phan tach boi dau phay roi cat vao mang nay

Open strPath For Input As #1 'Open file csv

i = 1
Do Until EOF(1)

  Line Input #1, strLine
  arrLine = Split(strLine, ",") 'Phan tach strLine boi dau phay, roi cat vao mang arrLine

  For j = 0 To UBound(arrLine)

  ws.Cells(i, j + 1).Value = arrLine(j)

  Next j
  i = i + 1
Loop

Close #1

End Sub
Trình tự code trên là:
-Mở file csv
-Đọc từng dòng dữ liệu
-Dòng dữ liệu bên trong được ngăn cách bởi các dấu phẩy nên dùng split để phân tách dấu phẩy rồi lấy dữ liệu.
-Dữ liệu lấy được sẽ ghi ra cells
-Đóng file csv

2. Trường hợp file csv xuống dòng không thuận lợi
Nếu file csv ở tình huống như dưới đây:
Bạn cần đăng nhập để thấy hình ảnh

Khi đó code trên không có tác dụng. Bây giờ bạn mở NotePad++ để xác nhận dữ liệu csv, thì bạn vẫn thấy dữ liệu là bình thường.
Trông như thế này:
Bạn cần đăng nhập để thấy hình ảnh

Nguyên nhân là do trong nội dung csv, tại một cells dữ liệu bên trong có ký tự xuống dòng.
Bạn cần đăng nhập để thấy hình ảnh

Điều này không chỉ trong file csv, đôi khi trong dữ liệu input Excel ta cũng gặp những tình huống như thế này, đòi hỏi phải có những xử lý đặc biệt.
Việc này giống như khi bạn đang nhập dữ liệu cho một cells và bạn sử dụng phím ALT+Enter để xuống dòng.

2.1 Chúng ta có 3 loại dấu xuống dòng.
Bạn có biết là có bao nhiêu loại dấu xuống dòng hay không?
Nào hãy thử tìm kiếm:
Ký tự xuống dòng là một ký tự để chuyển xuống dòng tiếp theo trong file text. Nói chính xác là chuyển xuống dòng tiếp theo và đưa con trỏ về đầu của dòng đó. Nó không phụ thuộc vào hệ thống mã ký tự vì ban đầu nó có hai mã CF ( Carriage Return: Chuyển về đầu dòng) và LF (Line Feed : xuống dòng). Windows sử dụng CR + LF, Mac OS sử dụng CR, Unix sử dụng LF.
Nguồn:

2.2 Lý do không xuống dòng
Ở trên, ta biết rằng file CSV trong đó đã sử dụng ký tự xuống dòng LF. Vì vậy trong code của chúng ta sử dụng Line Input để trích xuất từng dòng dữ liệu, nó sử dụng CR để phán đoán một dòng dữ liệu đã kết thúc hay chưa, và khi gặp ký tự LF trog cells, nó không hiểu được đây là ký tự kết thúc của một dòng.
Như bạn đã thấy ký tự LF là ký tự ngắt dòng trong một ô.
Sau khi đã hiểu được nguyên nhân, chúng ta sẽ xử lý trường hợp này như dưới đây.

3. Chương trình phán đoán LF và sử dụng Split để phân tách dữ liệu
Dưới đây là đoạn code đã được chỉnh sửa lại để xử lý trường hợp này:
Mã:
'Doc file CSV, phan tach du lieu bang ky tu LF => phan tach du lieu bang dau phay ","
Sub getCSV2()

Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets(1)

Dim strPath As String
strPath = "D:\VBA\tuhocvba_vbLf.csv"

Dim i As Long, j As Long
Dim strLine As String
Dim tmp As Variant 'Phan tach du lieu bang LF roi cat vao mang nay
Dim arrLine As Variant 'Phan tach du lieu bang dau phay "," va cat vao mang nay

Open strPath For Input As #1 'Open file csv
    Line Input #1, strLine 'File csv khong co ky tu CR nen chi can doc mot dong du lieu la lay het duoc du lieu file csv
Close #1

tmp = Split(strLine, vbLf) 'Phan tach du lieu strLine bang LF va cat vao mang tmp.

For i = 0 To UBound(tmp)
    arrLine = Split(tmp(i), ",") 'Phan tach tmp(i) boi dau phay "," roi cat vao mang arrLine
    For j = 0 To UBound(arrLine)
        ws.Cells(i + 1, j + 1).Value = arrLine(j)
    Next j
Next i

End Sub
Trình tự code trên được diễn giải như sau:
-Dùng Line Input để đọc dữ liệu file CSV và cất vào strLine.
-Phân tách strLine bằng LF và cất các dòng dữ liệu phân tách được vào mảng tmp
-Với mỗi dòng dữ liệu, phân tách bằng dấu phẩy "," và cất vào mảng arrLine
-Với mỗi phần tử của mảng arrLine thì thực hiện ghi vào cells.

Với file CSV sử dụng ký tự xuống dòng LF thì khi đọc dữ liệu CSV chúng ta chỉ cần đọc dòng dữ liệu đầu tiên là đủ.
Vì vậy ở dòng code 15~17 chúng ta có:
Mã:
Open strPath For Input As #1 'Open file csv
    Line Input #1, strLine 'File csv khong co ky tu CR nen chi can doc mot dong du lieu la lay het duoc du lieu file csv
Close #1
Ở phần trước chúng ta biết rằng, để đọc dữ liệu CSV chúng ta phải sử dụng vòng lặp, đọc cho tới khi nào phát hiện ký tự kết thúc văn bản:
Do Until EOF(1)~Loop , nhưng lần này chúng ta không phải làm điều đó nữa.
Ở dòng code 19 chúng ta có:
Mã:
tmp = Split(strLine, vbLf)
Bằng cách tách chuỗi ký tự thu được bằng dấu phân tách là ký tự LF mà cụ thể trong code ta viết là vbLf, chúng ta thu được từng dòng dữ liệu mà chúng ta mong muốn. Đây là hằng số đã được chương trình tự định nghĩa. Tôi tổng hợp 4 hằng số xuống dòng chúng ta có là:
Hằng sốPlatform
vbCrMac OS
vbLfUNIX
vbCrLfWindows
vbNewLine-

Nguồn tham khảo (Phần 4):
 

Euler

Administrator
Thành viên BQT
Phương pháp lấy dữ liệu CSV trong tình huống chúng chứa dấu ngoặc kép
Đối với file csv, có biết bao là tình huống có thể gặp phải.
Ở các phần trước chúng ta đã gặp các tình huống như dữ liệu chứa dấu phẩy ngăn cách, chúng ta đã tìm cách loại bỏ nó để hiển thị trên Excel.
Hay như tình huống dữ liệu có dấu xuống dòng, chúng ta cũng đã tìm cách loại bỏ nó.
Và trong lần này, chúng ta sẽ găp tình huống dữ liệu chứa dấu ngoặc kép.

Nhắc lại: Chương trình phổ biến để lấy dữ liệu CSV
Mã:
Sub getCSV()

Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets(1)

Dim strPath As String
strPath = "C:\tuhocvba.net\tuhocvba.csv"

Dim i, j As Long
Dim strLine As String
Dim arrLine As Variant 'phan tach du lieu bang dau phay bang split va cat vao mang nay

Open strPath For Input As #1 'Open file csv

i = 1
Do Until EOF(1)

    Line Input #1, strLine
    arrLine = Split(strLine, ",") 'Phan tach du lieu 1 dong boi dau phay va cat vao mang

    For j = 0 To UBound(arrLine)

        ws.Cells(i, j + 1).Value = arrLine(j)

    Next j
    i = i + 1
Loop

Close #1

End Sub
Ở cách trên, chúng ta sẽ đọc từng dòng dữ liệu. Nhưng trong một dòng dữ liệu, các cột dữ liệu bị phân tách bởi dấu phẩy, cho nên chúng ta phải xử lý rườm rà một chút là dùng Split để phân tách và cất vào mảng.
Đối với dữ liệu có chứa dấu ngoặc kép, thì cách trên sẽ cho ra kết quả như sau:
Bạn cần đăng nhập để thấy hình ảnh


Dữ liệu này mà cho hiển thị trên Excel sẽ trông như thế này:
Bạn cần đăng nhập để thấy hình ảnh


Vì vậy để loại bỏ dấu ngoặc kép này, chúng ta cần có bước xử lý phụ được thể hiện ở dòng code 20:
Mã:
'Lay du lieu file CSV bang cach doc tung dong du lieu
Sub getCSV()

Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets(1)

Dim strPath As String
strPath = "C:\tuhocvba.net\tuhocvba.csv"

Dim i, j As Long
Dim strLine As String
Dim arrLine As Variant 'Dung split phan tach dau phay va cat vao mang nay

Open strPath For Input As #1 'Open file csv

i = 1
Do Until EOF(1)

    Line Input #1, strLine
    arrLine = Split(Replace(strLine, """", ""), ",") 'Loai bo dau ngoac kep. Sau do dung Split phan tach dau phay

    For j = 0 To UBound(arrLine)

        ws.Cells(i, j + 1).Value = arrLine(j)

    Next j
    i = i + 1
Loop

Close #1

End Sub
Lần này, để xóa bỏ dấu ngoặc kép, chúng ta đã làm như sau:
Mã:
Replace(strLine, """", "")
Trong dòng code 20, liên quan tới dấu ngoặc kép, nếu bạn cảm thấy khó hiểu thì có thể tham khảo .

Kết quả:
Bạn cần đăng nhập để thấy hình ảnh

Nguồn tham khảo ( Phần 5) :
 

Euler

Administrator
Thành viên BQT
Lấy dữ liệu CSV bằng Excel VBA mà dữ liệu được bao bọc bởi dấu ngoặc kép
File Csv thường là do phần mềm tạo ra và nó có rất nhiều các định dạng khác nhau.
Lần này, chúng ta sẽ xem, với data csv mà chúng được ngăn cách bởi các dấu ngoặc kép thì phải xử lý như thế nào bằng VBA Excel để đọc chúng.
1. Dữ liệu Csv thông thường được xử lý như thế nào.
Mã:
Sub getCSV()

Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets(1)

Dim strPath As String
strPath = "D:\VBA\test\file_dq.csv"

Dim i, j As Long
Dim strLine As String
Dim arrLine As Variant 'Phân tách dấu phẩy và cất từng dòng dữ liệu vào mảng này

Open strPath For Input As #1 'Open file csv

i = 1
Do Until EOF(1)

    Line Input #1, strLine
    arrLine = Split(strLine, ",") 'Cắt strLine bởi dấu phẩy , và cất vào arrLine

    For j = 0 To UBound(arrLine)

        ws.Cells(i, j + 1).Value = arrLine(j)

    Next j
    i = i + 1
Loop

Close #1

End Sub
Ở đoạn code trên ta đọc từng dòng dữ liệu của file csv và cất nó vào biến strLine.
Tuy nhiên dữ liệu trong cùng một dòng lại phân tách bởi dấu phẩy "," do đó cần dùng split tách chúng ra và cất vô mảng arrLine .

Để dễ hình dung, csv của chúng ta có dạng như sau:
Bạn cần đăng nhập để thấy đính kèm

Nhìn vào ta thấy data được bao bọc bởi dấu ngoặc kép " ".
Nếu cho hiển thị trên Excel thì trông nó sẽ như sau:
Bạn cần đăng nhập để thấy đính kèm


Với dữ liệu như thế này thì code trên không đáp ứng được.
2. Xử lý dữ liệu mà data được bao bọc bởi dấu ngoặc kép ""
Mã:
'Đọc từng dòng file CSV
Sub getCSV()

Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets(1)

Dim strPath As String
strPath = "D:\VBA\test\file_dq.csv"

Dim i, j As Long
Dim strLine As String
Dim arrLine As Variant 'Phân tách dấu phẩy và cất từng dòng dữ liệu vào mảng này

Open strPath For Input As #1 'Open file Csv

i = 1
Do Until EOF(1)

    Line Input #1, strLine
    arrLine = Split(Replace(strLine, """", ""), ",") 'Phân tách strLine bởi dấu phẩy và cất vào mảng arrLine

    For j = 0 To UBound(arrLine)

        ws.Cells(i, j + 1).Value = arrLine(j)

    Next j
    i = i + 1
Loop

Close #1

End Sub
Mấu chốt ở code trên là dòng code:
Mã:
Replace(strLine, """", "")
Ta đã vứt bỏ hết dấu ngoặc kép rồi mới đưa vào phân tách bởi dấu phẩy ",".
Bạn cần đăng nhập để thấy đính kèm

Nguồn :
 
Lấy dữ liệu CSV bằng Excel VBA mà dữ liệu bên trong chứa dấu phẩy ","
Đầu tiên ta nhắc lại đoạn code của phần trước
Mã:
'Đọc từng dòng file CSV
Sub getCSV()

Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets(1)

Dim strPath As String
strPath = "D:\VBA\test\file_dq.csv"

Dim i, j As Long
Dim strLine As String
Dim arrLine As Variant 'Phân tách dấu phẩy và cất từng dòng dữ liệu vào mảng này

Open strPath For Input As #1 'Open file Csv

i = 1
Do Until EOF(1)

    Line Input #1, strLine
    arrLine = Split(Replace(strLine, """", ""), ",") 'Phân tách strLine bởi dấu phẩy và cất vào mảng arrLine

    For j = 0 To UBound(arrLine)

        ws.Cells(i, j + 1).Value = arrLine(j)

    Next j
    i = i + 1
Loop

Close #1

End Sub
Bằng Line Input , chương trình đọc dữ liệu từng dòng. Sau đó dùng Replace để xóa dấu ngoặc kép ".
Cuối cùng dùng Split để tách phân tách dấu phẩy ",".
Bây giờ chúng ta hãy xem xét dữ liệu có dạng sau:
Bạn cần đăng nhập để thấy đính kèm

Vì giá niêm yết của mì xá xíu là 1.000 yên nên dữ liệu CSV bao gồm dữ liệu bao gồm "1,000 yên" bên trong dữ liệu chứa dấu phẩy.
Nếu xử lý file csv bằng code ở trên, sẽ thành ra như thế này:
Bạn cần đăng nhập để thấy đính kèm

Các ô trong hàng đã bị sai lệch so với mong muốn. Quả nhiên là không ổn rồi.
Tôi sẽ cải thiện code như sau:
Mã:
'Đọc dữ liêu file CSV, trong dữ liệu chứa dấu phẩy
Sub getCSV_camma()

Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets(1)

Dim strPath As String
strPath = "D:\VBA\comma.csv"

Dim i, j As Long
Dim strLine As String
Dim arrLine As Variant 'Cất data sau khi phân tách dấu hai chấm : bằng split

Open strPath For Input As #1 'Open file csv

i = 1
Do Until EOF(1)

    Line Input #1, strLine

    arrLine = Split(Replace(replaceColon(strLine), """", ""), ":") 'Phân tách strLine cất vào arrLine

    For j = 0 To UBound(arrLine)

        ws.Cells(i, j + 1).Value = arrLine(j)

    Next j
    i = i + 1
Loop

Close #1

End Sub
Split ở trên không phân tách dấu phẩy "," mà phân tách dấu ":" .
Ngoài ra trong code trên còn có một hàm bí ẩn là ReplaceColon, nó nhận strLine làm đối số đầu vào.
Đây là hàm tự tạo mà tôi sẽ thuyết minh sau.

Hàm ReplaceColon thực thi một số thay đổi sau:
Chuyển đổi dấu phẩy "," (thực ra không có trong data) thành dấu hai chấm ":" .
Hàm này trả về chuỗi ký tự chứa các dấu hai chấm ":", nên sau đó ta thông qua split để phân tách data bằng dấu hai chấm ":" .
Mã:
'Thay thế dấu phẩy trong chuỗi đã nhận bằng dấu hai chấm
'Không thay đổi dấu phẩy bị bao bọc bởi dấu ngoặc kép
Function replaceColon(ByVal str As String) As String

Dim strTemp As String
Dim quotCount As Long

Dim l As Long
For l = 1 To Len(str) 'chạy tới độ dài của chuỗi str

    strTemp = Mid(str, l, 1) 'Lấy ra từng ký tự từ chuỗi str

    If strTemp = """" Then  ' Nếu strTemp là dấu ngoặc kép

        quotCount = quotCount + 1  'Đếm số ngoặc kép

    ElseIf strTemp = "," Then  'Nếu strTemp là dấu phẩy

        If quotCount Mod 2 = 0 Then  'Nếu là bội của 2

            str = Left(str, l - 1) & ":" & Right(str, Len(str) - l)  'Chuyển ý tự hiện tại thành dấu hai chấm

        End If

    End If

Next l

replaceColon = str

End Function
Kết quả :
Bạn cần đăng nhập để thấy đính kèm

Nguồn :
 

giaiphapvba

Administrator
Thành viên BQT
Văn bản Csv bị biến đổi, lấy dữ liệu Csv bằng UTF-8
Nhắc lại phần trước:
Mã:
'Đọc dữ liêu file CSV, trong dữ liệu chứa dấu phẩy
Sub getCSV_camma()

Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets(1)

Dim strPath As String
strPath = "D:\VBA\comma.csv"

Dim i, j As Long
Dim strLine As String
Dim arrLine As Variant 'Cất data sau khi phân tách dấu hai chấm : bằng split

Open strPath For Input As #1 'Open file csv

i = 1
Do Until EOF(1)

    Line Input #1, strLine

    arrLine = Split(Replace(replaceColon(strLine), """", ""), ":") 'Phân tách strLine cất vào arrLine

    For j = 0 To UBound(arrLine)

        ws.Cells(i, j + 1).Value = arrLine(j)

    Next j
    i = i + 1
Loop

Close #1

End Sub
Quá trình trên được tóm tắt như sau:
1. Mở file csv
2. Lấy từng dòng dữ liệu trong file csv
2.1 Thay đổi các dấu phẩy "," có trong dữ liệu thành dấu hai chấm ":"
2.2 Xóa các dấu ngoặc kép "
2.3 Phân tách dữ liệu bằng dấu hai chấm ":" và cất vào mảng.
3. Lấy dữ liệu từ mảng ghi ra sheet.

Vấn đề lần này chúng ta gặp phải là dữ liệu bị biến đổi do định dạng UTF-8 :
Bạn cần đăng nhập để thấy đính kèm

Dữ liệu hiển thị rất lung tung.
Lệnh Line Input đọc dữ liệu như thể nó là , bất kể nó có hiện diện hay không.

Sử dụng ADODB.Stream để đọc dữ liệu bằng UTF-8
Bạn cần thiết định thư viện ADO :
Bạn cần đăng nhập để thấy đính kèm

Tham khảo .

Mã:
Sub getCSV_utf8()

Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets(1)

Dim strPath As String
strPath = "D:\VBA\test_utf8.csv"

Dim i As Long, j As Long
Dim strLine As String
Dim arrLine As Variant 'Phan tach bang dau phay split va nap vao bien nay

'ADODB.Stream Object
Dim adoSt As Object
Set adoSt = CreateObject("ADODB.Stream")

i = 1
With adoSt
    .Charset = "UTF-8"        'bang Stream thiet dinh utf-8
    .Open                             'Stream Open
    .LoadFromFile (strPath) 'Lay du lieu tu file csv

    Do Until .EOS           'Vong lap thuc hien cho toi khi toi ky tu ket thuc cua Stream

        strLine = .Readtext(adReadLine) 'Lay tung dong du lieu tu Stream

        arrLine = Split(Replace(replaceColon(strLine), """", ""), ":") 'Phan tach strLine boi dau phay va nap vao arrLine

        For j = 0 To UBound(arrLine)

            ws.Cells(i, j + 1).Value = arrLine(j)

        Next j
        i = i + 1
    Loop

    .Close
End With

End Sub
Kết quả:
Bạn cần đăng nhập để thấy đính kèm


Quá trình trên có thể tóm tắt như sau:
1. Tạo ADODB.Stream Object
2. Thiết định string code cho ADODB.Stream Object (utf-8)
3. Open ADODB.Stream Object và lấy dữ liệu từ file csv
4. Đọc từng dòng dữ liệu của bản ghi từ ADODB.Stream Object
4.1 Chuyển dấu phẩy "," thành dấu hai chấm ":"
4.2 Xóa dấu ngoặc kép "
4.3 Phân tách dấu hai chấm ":" và nạp dữ liệu vào mảng
5. Ghi dữ liệu mảng ra sheet

Phần code tạo ADODB.Stream Object, bạn không cần lý giải gì nhiều, copy paste và sử dụng là được.
Thuộc tính và phương thứ của ADODB.Stream Object :
Charset: Thiết định mã ký tựcho stream object
Open: Open stream Object
LoadFromFile: Lấy dữ liệu từ file chỉ định thông qua Stream Object
EOS: Nó sẽ cho biết đã tới điểm kết thúc của Stream hay chưa. Nếu đã tới điểm kết thúc thì giá trị trả về là True.
Readtext: Nó đọc dữ liệu lấy được từ Stream Object. Nếu đọc từng dòng dữ liệu thì sử dụng adReadLine. Nếu đọc toàn bộ một lượt adReadAll.
Close: Close Stream Object.
Nguồn :
 
Top