Bài viết này minh họa cách nhập dữ liệu tệp văn bản có nhiều dấu phân cách vào trang tính Excel sử dụng 3 phương pháp khác nhau. Chúng tôi sẽ sử dụng các tính năng tích hợp sẵn của Excel và mã VBA để hoàn thành nhiệm vụ. Hãy đi sâu vào các ví dụ để hiểu rõ về các phương pháp.
3 Phương pháp Thích hợp để Nhập Tệp Văn bản có Nhiều Dấu phân cách vào Excel
Giả sử chúng tôi có tập dữ liệu có nhiều dấu phân cách trong tệp văn bản . Tập dữ liệu trong tệp văn bản trông như thế này-
Tập dữ liệu đại diện cho chi tiết bán hàng cho một cửa hàng. Bạn có thể tải xuống văn bản tệp để thực hành từ liên kết . Đây, từng trong số dữ liệu được tách biệt bằng dấu phẩy hoặc dấu chấm phẩy . Chúng tôi muốn nhập dữ liệu này từ tệp văn bản vào bảng tính Excel .
1. Nhập dữ liệu tệp văn bản với nhiều dấu phân cách bằng tính năng trình hướng dẫn kế thừa
Tính năng của trình hướng dẫn kế thừa để nhập tệp văn bản tạo điều kiện cho chúng tôi với nhiều tùy chọn để đặt cấu trúc mong muốn của tập dữ liệu đã nhập trong trang tính Excel .
Đi tới Trình hướng dẫn kế thừa
- Bắt đầu vào tab Dữ liệu từ Ruy-băng Excel .
- Nhấp trên Lấy dữ liệu tùy chọn.
- Di chuột chuột vào Trình hướng dẫn kế thừa tùy chọn.
- Chọn Từ Văn bản (Kế thừa) tùy chọn .
Nếu tùy chọn Từ Văn bản (Kế thừa) không khả dụng
Trong trường hợp Từ Văn bản (Kế thừa) tùy chọn không khả dụng trong tùy chọn Trình hướng dẫn kế thừa, hãy làm như sau để bật nó.
- Nhấn Alt + T + O trên bàn phím để mở Tùy chọn Excel.
- Nhấp trên Dữ liệu tab.
- Kiểm tra hộp có tên “Từ Văn bản (Kế thừa)” trong “ Hiển thị trình hướng dẫn nhập dữ liệu cũ ”Tùy chọn.
- Cuối cùng nhấn OK để lưu cài đặt.
Nhập tệp văn bản
Khi chúng tôi đã nhấp vào trên Từ Văn bản ( Kế thừa ) từ Trình hướng dẫn kế thừa , nó đã mở “ Nhập Tệp văn bản ” cửa sổ để chọn tệp văn bản . Điều hướng đến vị trí tệp và chọn để nhập nó.
Định cấu hình cài đặt
Bước 1/3:
- Chọn Được phân cách tùy chọn.
- Kiểm tra “ Dữ liệu của tôi có tiêu đề” tùy chọn, vì tập dữ liệu mẫu mà chúng tôi đang sử dụng có tiêu đề .
- Nhấp Tiếp theo nút.
Bước 2/3:
- Kiểm tra Dấu chấm phẩy và Dấu phẩy các tùy chọn trong Dấu phân cách tùy chọn.
Lưu ý : Tập dữ liệu của chúng tôi có hai này dấu phân cách. Có một số tùy chọn khác bao gồm “ Khác ”Tùy chọn cho dấu phân cách không có sẵn trong danh sách .
- Ngoài ra, kiểm tra " Coi các dấu phân tách liên tiếp là một ”Tùy chọn.
- Nhấp Tiếp theo nút.
Bước 3/3:
- Theo mặc định, định dạng dữ liệu cột là General . Trong ví dụ này, chúng tôi sẽ để nguyên như vậy.
- Cuối cùng nhấn vào Kết thúc nút.
Chọn mục tiêu
Sau tất cả các bước trên, hãy chọn vị trí để đặt dữ liệu đã nhập của bạn . Ở đây, chúng tôi đã chọn ô A1 trong trang tính hiện tại .
Tập dữ liệu đã nhập
Đọc thêm: Cách nhập dữ liệu vào Excel từ một tệp Excel khác (2 cách)
2. Chuẩn bị và sau đó nhập dữ liệu tệp văn bản với nhiều dấu phân cách bằng tính năng lấy và chuyển đổi dữ liệu
Trong hình minh họa này, chúng tôi sẽ sử dụng tính năng lấy và chuyển đổi dữ liệu của Excel để nhập dữ liệu có nhiều dấu phân cách từ tệp văn bản sang trang tính Excel . Nhưng lần này chúng ta cần chuẩn bị dữ liệu trước khi nhập nó sang Excel.
Chuẩn bị tập dữ liệu
Chúng tôi cần giảm nhiều dấu phân cách thành một dấu phân cách đến cơ quan với nhận và chuyển đổi tính năng. Đối với tập dữ liệu của chúng tôi, chúng tôi cần thay thế tất cả dấu chấm phẩy với dấu phẩy . Để làm điều đó, chúng tôi đã thực hiện như sau.
- Đã tạo một bản sao của văn bản gốc tệp có tên SampleDataModified. txt.
- Đã mở tệp và nhấn Ctrl + H.
- Trong Cửa sổ thay thế đặt dấu chấm phẩy trong “Tìm gì” hộp nhập liệu và dấu phẩy trong phần “ Thay thế với Hộp nhập liệu ”.
- Nhấp vào nút Thay thế tất cả nút.
- Bây giờ chúng ta chỉ có dấu phẩy dưới dạng dấu phân cách trong tập dữ liệu của chúng tôi .
Nhập tệp văn bản
Làm theo các bước đơn giản để nhập đã sửa đổi văn bản tệp bằng cách sử dụng get và biến đổi tính năng trong Excel.
- Bắt đầu vào Dữ liệu tab.
- Nhấp trên Lấy dữ liệu nút.
- Di chuột trên Từ tệp tùy chọn.
- Nhấp trên Từ Văn bản / CSV tùy chọn .
- Điều hướng đến vị trí tệp của SampleTextModified . txt và chọn tệp để nhập .
- Trong bản xem trước, tập dữ liệu được hiển thị dưới dạng bảng .
- Nhấp trên Tải nút.
Tập dữ liệu đã nhập
Đọc thêm: Cách nhập dữ liệu từ tệp văn bản vào Excel (3 phương pháp)
Bài đọc tương tự
- Nhập dữ liệu từ Excel vào Word tự động bằng VBA (2 cách)
- Cách trích xuất dữ liệu từ danh sách bằng công thức Excel (5 phương pháp)
- Excel Macro:Trích xuất Dữ liệu từ Nhiều Tệp Excel (4 Phương pháp)
- Cách trích xuất năm từ ngày trong Excel (3 cách)
- Cách trích xuất Tháng từ Ngày trong Excel (5 Cách Nhanh)
3. Chạy mã VBA để nhập dữ liệu tệp văn bản với nhiều dấu phân cách vào trang tính Excel
Chúng tôi sẽ sử dụng VBA Split và hàm InStr để định cấu hình mã VBA để nhập tệp văn bản vào trang tính Excel . Hãy giới thiệu chức năng đầu tiên.
Chức năng phân tách VBA: Chức năng phân tách trong Excel VBA được sử dụng để phân chia một chuỗi thành chuỗi con . Chức năng trả về mảng một chiều dựa trên 0 . Mỗi phần tử của mảng là một chuỗi con được phân tách bằng dấu phân cách xác định trước . Cú pháp của hàm VBA là-
Tách (biểu thức, [dấu phân cách, [giới hạn, [so sánh]]])
Đây,
biểu thức - Thông số bắt buộc này đại diện cho một chuỗi văn bản chứa chuỗi con và dấu phân cách . Nếu chuỗi trống , hàm cũng sẽ trả về một mảng trống .
dấu phân cách - Một ký tự chuỗi được sử dụng để phân chia chuỗi thành chuỗi con . Nếu bị bỏ qua, hàm sẽ sử dụng ký tự khoảng trắng làm dấu phân cách . Và nếu đó là chuỗi trống , nó sẽ trả về chuỗi ban đầu là đầu ra .
giới hạn - Nó đại diện cho số trong tổng số chuỗi con để trả lại trong đầu ra . Nếu bị bỏ qua, hàm sẽ trả về tất cả chuỗi con .
so sánh - Nó có một số giá trị . Chúng tôi có thể sử dụng vbBinaryCompare cho một trường hợp - nhạy cảm dấu phân cách và vbTextCompare cho dấu phân cách không phân biệt chữ hoa chữ thường trong chức năng Chia nhỏ.
Hàm VBA InStr: Chúng tôi sử dụng hàm InStr trong Excel VBA để tìm kiếm một chuỗi cụ thể trong một chuỗi đã cho từ một vị trí được xác định trước . Cú pháp là-
InStr ( [start], chuỗi 1, chuỗi 2, [so sánh] )
Nơi đây,
[ bắt đầu ] - Vị trí từ đó nó bắt đầu tìm kiếm . Mặc định là 1 nếu bỏ qua .
chuỗi 1- Chuỗi đã cho từ đó hàm tìm kiếm cho chuỗi mong muốn .
chuỗi 2- Chuỗi cụ thể rằng chức năng tìm kiếm trong chuỗi đã cho.
[ so sánh ] - Loại trong tổng số so sánh . Giá trị mặc định là So sánh nhị phân .
Để nhập dữ liệu tệp văn bản có nhiều dấu phân cách vào trang tính Excel , chúng tôi cần mở và viết mã VBA trong trình chỉnh sửa cơ bản trực quan. Làm theo các bước để mở trình chỉnh sửa cơ bản trực quan và viết một số mã ở đó.
- Đi tới Nhà phát triển từ Ruy-băng Excel .
- Nhấp tùy chọn Visual Basic.
- Trong Visual Basic For Applications , nhấp vào trình đơn thả xuống Chèn để chọn Mô-đun mới tùy chọn.
Bây giờ hãy đặt mã của bạn bên trong trình chỉnh sửa mã trực quan và nhấn F5 để chạy nó. mã sau đã được thay thế dấu chấm phẩy của tệp văn bản với dấu phẩy bằng cách sử dụng chức năng Thay thế VBA .
Sub ImportTextFileDatatoExcel()
Dim fileLocation As String, textData As String
Dim rowNum As Long
folderLocation = "D:\Exceldemy"
Set fso = CreateObject("Scripting.FileSystemObject")
Set folder = fso.GetFolder(folderLocation)
rowNum = 1
Close #1
For Each textFile In folder.Files
fileLocation = folder & "\" & textFile.Name
Open fileLocation For Input As #1
Do While Not EOF(1)
Line Input #1, textData
textData = Replace(textData, ";", ",")
If InStr(textData, ",") = 0 Then
Cells(rowNum, 1) = textData
Else
tArray = Split(textData, ",")
nColumn = 1
For Each element In tArray
Cells(rowNum, nColumn) = element
nColumn = nColumn + 1
Next element
End If
rowNum = rowNum + 1
Loop
Close #1
Next textFile
End SubTrong mã trên, hãy đặt vị trí thư mục của riêng bạn nắm giữ tệp văn bản trong mã. Ảnh chụp màn hình sau hiển thị vị trí tệp cho hình minh họa này .
Tập dữ liệu đã nhập
Đọc thêm:Chuyển đổi Excel thành tệp văn bản với dấu phân cách (2 cách tiếp cận dễ dàng)
Ghi chú
- Mã VBA chúng tôi đã sử dụng có thể hoạt động với nhiều văn bản tệp trong định mệnh thư mục hiệu quả.
- Nếu dấu phân cách được chỉ định không tồn tại trong chuỗi nguồn , Tách hàm sẽ trả về chuỗi như hiện tại .
- Nếu đối số so sánh của Tách chức năng bị bỏ qua , giá trị mặc định là vbBinaryCompare.
- Hàm InStrRev trả về 0 nếu chuỗi con không tồn tại trong chuỗi đã cho .
Kết luận
Bây giờ, chúng ta đã biết cách nhập dữ liệu tệp văn bản vào Excel với nhiều dấu phân cách trong Excel với sự trợ giúp của các ví dụ phù hợp. Hy vọng rằng nó sẽ giúp bạn sử dụng chức năng một cách tự tin hơn. Mọi thắc mắc hoặc đề xuất đừng quên để vào khung bình luận bên dưới.
Các bài viết có liên quan
- Trích xuất dữ liệu đã lọc trong Excel sang một trang tính khác (4 phương pháp)
- Cách Trích xuất Dữ liệu từ Trang tính Excel (6 Phương pháp Hiệu quả)
- Trích xuất dữ liệu từ hình ảnh sang Excel (Với các bước nhanh)
- Cách chuyển đổi Notepad sang Excel với các cột (5 phương pháp)
- Excel VBA:Tự động kéo dữ liệu từ trang web (2 phương pháp)
- Mã VBA để chuyển đổi tệp văn bản sang Excel (7 phương pháp)