Hàm tự động chuyển txt sang file excel (3 người xem)

[ginkgo36]

Chào cả nhà,

Em thường xuyên mở file txt, và khai báo các tham số khi ở txt đều cùng một kiểu, em đang cố gắng tập viết 1 macro để nó tự động chuyển file txt qua excel với các tham số do mình định sẵn. Mọi người giúp em.
Các bước và tham số em chọn như sau:
Đây là bước 1:


Bước 2:


Bước 3:



Sub openText()
Workbooks.OpenText Filename:="E:/Test_2/sample.txt", _
DataType:=xlDelimited,_
File origin: 65001: Unicode (UTF-8)_ --> Cái này em chưa biết chọn thế nào.
TextQualifier:={none},_
ConsecutiveDelimiter:=False,_
Tab:=True,_
......
End sub

Ở Bước 3: Chọn tất cả các columns trong data preview rồi sau đó chọn Column data format là text. Bước này em không biết chọn thế nào hết.

Mong mọi người giúp đỡ.
Em tóm lại như thế này nhe:
Input: đường dẫn chứa các file txt (có 1 hoặc nhiều file txt trong đó)

Output: là các file *xlsx cùng tên file txt. và được chọn các tham số như ở 3 bức hình trên.

Gửi kèm cho mọi người file đính kèm
Cám ơn mọi người

 

[ndu96081631]

Chào cả nhà,

Em thường xuyên mở file txt, và khai báo các tham số khi ở txt đều cùng một kiểu, em đang cố gắng tập viết 1 macro để nó tự động chuyển file txt qua excel với các tham số do mình định sẵn. Mọi người giúp em.

Viết 1 Sub để lấy Text File vào Excel

Sub ImportTextFile(ByVal FileName As String, ByVal Target As Range)
  On Error Resume Next
  With Target.Parent.QueryTables.Add("TEXT;" & FileName, Target)
    .TextFileOtherDelimiter = vbTab
    .Refresh BackgroundQuery:=False
  End With
End Sub

Và Sub chính để Run:

Sub Main()
  Dim txtFile, Target As Range
  On Error Resume Next
  Set Target = ActiveSheet.Range("A1")
  Target.Parent.UsedRange.ClearContents
  txtFile = Application.GetOpenFilename("Text File, *.txt")
  If TypeName(txtFile) = "String" Then ImportTextFile txtFile, Target
End Sub

Copy cả 2 code cho vào Module, xong ra ngoài bảng tính, bấm Alt + F8, chọn sub Main để chạy nhé
-------------------
Code trên dùng để đưa dữ liệu từ txt vào Excel, còn chuyện Save thế nào thì bạn tự mình làm nhé

 

[ginkgo36]

Cám ơn thầy, hàm của thầy chưa đúng với mục đính của em.
Một số vấn đề như sau:
- Em muốn định dạng của file là Text chứ không phải genaral. - Vì dạng genaral có thể làm thay đổi giá trị của cell. ( Em ví dụ, sau khi convert file, cell của e có giá trị là 0001 - đúng với giá trị gốc. Nhưng nếu e tác động vào cell đó ví dụ như nhấn F2 để để thêm số 2 vô sau cùng thành 00012 thì nó sẽ đưa về là 12, do file đang ở format genaral --> Sai format với giá trị gốc. Nên vì vậy e mới muốn nó là dạng text).r
- Nếu chỉ có 1 2 file thì em có thể làm bằng tay không vấn đề gì hết. Nhưng do 1 lần em mở có thể là 10 hoặc 20 file. Nên e muốn nó tự save as từ txt qua xlsx . Mình chỉ cần đưa dường dẫn chưa folder file txt là nó có thể xử lý cho mình.
- Em cũng cần chọn cả optinal này nữa 65001: Unicode (UTF-8). Do file ban đầu nó phải phải là Unicode (UTF-8), có thể là ANSII. Nên em phải chọn những option như ở 3 bức hình trên.

Cám ơn thầy

 

[ndu96081631]

Cám ơn thầy, hàm của thầy chưa đúng với mục đính của em.
Một số vấn đề như sau:
- Em muốn định dạng của file là Text chứ không phải genaral. - Vì dạng genaral có thể làm thay đổi giá trị của cell. ( Em ví dụ, sau khi convert file, cell của e có giá trị là 0001 - đúng với giá trị gốc. Nhưng nếu e tác động vào cell đó ví dụ như nhấn F2 để để thêm số 2 vô sau cùng thành 00012 thì nó sẽ đưa về là 12, do file đang ở format genaral --> Sai format với giá trị gốc. Nên vì vậy e mới muốn nó là dạng text).r

Cái này làm được nhưng bạn phải cho lên đây file tổng quát chút để thí nghiệm chứ (chẳng hạn trong file txt phải có những giá trị như bạn vừa nêu)

- Nếu chỉ có 1 2 file thì em có thể làm bằng tay không vấn đề gì hết. Nhưng do 1 lần em mở có thể là 10 hoặc 20 file. Nên e muốn nó tự save as từ txt qua xlsx . Mình chỉ cần đưa dường dẫn chưa folder file txt là nó có thể xử lý cho mình.

Tức là bạn muốn xử lý 1 lần nhiều file txt ---> Mỗi file sẽ save thành 1 file xlsx, đúng không?

- Em cũng cần chọn cả optinal này nữa 65001: Unicode (UTF-8). Do file ban đầu nó phải phải là Unicode (UTF-8), có thể là ANSII. Nên em phải chọn những option như ở 3 bức hình trên.

Nếu txt file đang thuộc kiểu ANSI thì bạn nói xem làm sao chuyển thành Unicode? Ví dụ chuổi tiếng Việt có dấu, khi bạn lưu thành file txt với Encording = ANSI thì các ký tự có dấu sẽ tự động bị mất sạch, giờ làm sao cho nó có dấu trở lại được
Tóm lại: File txt như thế nào thì import vào Excel như thế nấy, có chăng ta chỉ chỉnh được phần định dạng General thành Text thôi
----------------------------
Cuối cùng: Để thuận tiện cho việc thí nghiệm, vui lòng gửi lên vài file txt có dữ liệu tương đối tồng quát chút

 

[ginkgo36]

Cái này làm được nhưng bạn phải cho lên đây file tổng quát chút để thí nghiệm chứ (chẳng hạn trong file txt phải có những giá trị như bạn vừa nêu)

Tức là bạn muốn xử lý 1 lần nhiều file txt ---> Mỗi file sẽ save thành 1 file xlsx, đúng không?

Dạ đúng rồi. Ví dụ 2 file sample.txt và sample_2.txt, sau khi chạy xong mình sẽ có thêm 2 file sample.xlsx và sample_2.xlsx. File txt của mình không thay đổi gì cả.

Nếu txt file đang thuộc kiểu ANSI thì bạn nói xem làm sao chuyển thành Unicode? Ví dụ chuổi tiếng Việt có dấu, khi bạn lưu thành file txt với Encording = ANSI thì các ký tự có dấu sẽ tự động bị mất sạch, giờ làm sao cho nó có dấu trở lại được
Tóm lại: File txt như thế nào thì import vào Excel như thế nấy, có chăng ta chỉ chỉnh được phần định dạng General thành Text thôi
----------------------------
Cuối cùng: Để thuận tiện cho việc thí nghiệm, vui lòng gửi lên vài file txt có dữ liệu tương đối tồng quát chút

Theo lí thuyết thì file gốc ban đầu sẽ không có tiếng việt có dấu. Nhưng trong qua trình chuyển đổi từ txt qua file excel và xử lý trên file excel đó, mình có nhập từ tiếng việt có dấu vào nên để encoding = utf8 luôn cho tiện đó thầy .

Em gửi thầy file original. file gốc có rất là nhiều dòng nên rất nặng. Nên e đã cắt bớt cho nhẹ.

 

[ndu96081631]

Theo lí thuyết thì file gốc ban đầu sẽ không có tiếng việt có dấu. Nhưng trong qua trình chuyển đổi từ txt qua file excel và xử lý trên file excel đó, mình có nhập từ tiếng việt có dấu vào nên để encoding = utf8 luôn cho tiện đó thầy .
.

Chổ này tôi không hiểu!
Bạn chỉnh sửa gì trong file Excel (sau khi import) thì liên quan gì đến encoding = utf8? Bạn thích gõ tiếng Việt thì cứ gõ, lưu file lại thì chuổi tiếng Việt nó vẫn còn chứ có mất đâu mà lo (Excel nó đâu có giống Text file)

 

[ginkgo36]

Chổ này tôi không hiểu!
Bạn chỉnh sửa gì trong file Excel (sau khi import) thì liên quan gì đến encoding = utf8? Bạn thích gõ tiếng Việt thì cứ gõ, lưu file lại thì chuổi tiếng Việt nó vẫn còn chứ có mất đâu mà lo (Excel nó đâu có giống Text file)

Em cũng thực sự không biết là chọn encoding = utf8 và để mặc định giống với file orginal thì nó khác biệt nhau như thế nào . Nếu nó không ảnh hưởng gì thì thầy bỏ qua chỗ đó giùm em. Cám ơn thầy ạ

 

[ginkgo36]

Em đã hiểu sự khác biệt khi không chọn utf-8, khi mình xuất file ra dạng txt (từ database hoặc file từ người khác đưa cho mình). Có thể trong file đó chưa tiếng việt có dấu hoặc kí tự đặc biệt, nhưng endcoding của nó không phải là utf8. Khi đó mình chuyển phải chọn là 65001: Unicode (UTF-8) thì nó mới hiện thị đúng. CÒn không thì thì những kí tự đặc biệt đó nó sẽ nhảy thành 1 kiểu khác. Khác hoàn toàn với dữ liệu của mình.
Thầy có thể set nó là 65001: Unicode (UTF-8) giùm e được ko ạ.

Cám ơn thầy

 

[ndu96081631]

Em đã hiểu sự khác biệt khi không chọn utf-8, khi mình xuất file ra dạng txt (từ database hoặc file từ người khác đưa cho mình). Có thể trong file đó chưa tiếng việt có dấu hoặc kí tự đặc biệt, nhưng endcoding của nó không phải là utf8. Khi đó mình chuyển phải chọn là 65001: Unicode (UTF-8) thì nó mới hiện thị đúng. CÒn không thì thì những kí tự đặc biệt đó nó sẽ nhảy thành 1 kiểu khác. Khác hoàn toàn với dữ liệu của mình.
Thầy có thể set nó là 65001: Unicode (UTF-8) giùm e được ko ạ.

Cám ơn thầy

Tôi cũng chẳng biết mấy vụ này nữa vì cách tôi làm khác với cách của bạn
Thôi thì tải file dưới đây về thí nghiệm xem thế nào rồi nói tiếp nhé!
Code trong file:
1> Code lấy tất cả tên file trong thư mục:

Function GetListFiles(ByVal Folder As String, ByVal Search As String, ByVal InSub As Boolean)
  Dim sComm As String, tmp As String, tmpFile, Arr
  On Error Resume Next
  If Right(Folder, 1) <> "\" Then Folder = Folder & "\"
  Folder = """" & Folder & """"
  With CreateObject("Scripting.FileSystemObject")
    tmpFile = .GetTempName
    sComm = "DIR " & Folder & Search & " /ON /B /A-D " & IIf(InSub, "/S", " ") & " >" & tmpFile
    CreateObject("Wscript.Shell").Run "cmd /u /c " & sComm, 0, True
    With .OpenTextFile(tmpFile, 1, , -2)
      tmp = Trim(.ReadAll)
      If Right(tmp, 2) = vbCrLf Then tmp = Left(tmp, Len(tmp) - 2)
      If Len(tmp) Then GetListFiles = Split(tmp, vbCrLf)
      .Close
    End With
  End With
  Kill tmpFile
End Function

2> Code để import txt file vào Excel:

Sub ImportTextFile(ByVal FileName As String, ByVal Target As Range)
  Dim[COLOR=#ff0000] [B]aFormat(1 To 500)[/B][/COLOR], i As Long, wks As Worksheet
  On Error Resume Next
  Set wks = Target.Parent
  [B][COLOR=#ff0000]For i = 1 To 500
    aFormat(i) = 2
  Next[/COLOR][/B]
  On Error Resume Next
  With wks.QueryTables.Add("TEXT;" & FileName, Target)
    .TextFileOtherDelimiter = vbTab
    [COLOR=#ff0000][B].TextFileColumnDataTypes = aFormat[/B][/COLOR]
    .Refresh BackgroundQuery:=False
  End With
End Sub

2> Code chính để chạy:

Sub Main()
  Dim txtFile As String, sFileSave As String, Folder
  Dim aTextFiles, fleItem
  Dim Target As Range, wkb As Workbook, cnn As WorkbookConnection
  On Error Resume Next
  Application.ScreenUpdating = False
  Folder = CreateObject("Shell.Application").BrowseForFolder(0, "", 1).Self.Path
  If TypeName(Folder) = "String" Then
    aTextFiles = GetListFiles(Folder, "*.txt", True)
    If IsArray(aTextFiles) Then
      For Each fleItem In aTextFiles
        txtFile = CStr(fleItem)
        Set wkb = Workbooks.Add
        Set Target = wkb.ActiveSheet.Range("A1")
        ImportTextFile txtFile, Target
        sFileSave = Left(txtFile, Len(txtFile) - 4)
        For Each cnn In wkb.Connections: cnn.Delete: Next
        wkb.SaveAs sFileSave, 51: wkb.Close
      Next
    End If
  End If
  Application.ScreenUpdating = True
  MsgBox "Finish!"
End Sub

Chạy Sub Main, 1 hộp Browse for folder sẽ mở ra cho bạn chọn thư mục. Sau khi chọn thư mục xong thì lập tức toàn bộ các file text sẽ được chuyển thành file xlsx
---------------------------------------
Lưu ý chổ màu đỏ: Tôi áng chừng sau khi import thì file Excel sẽ có khoảng 500 columns ---> Tôi Format toàn bộ 500 field này thành Text. Vậy nếu số lượng columns thật có lớn hơn thì hãy sửa số 500 này cho phù hợp nhé

 

[ginkgo36]

Cám ơn thầy, em thấy code như thế này chạy là ok rồi ạ. Nhưng có 1 vấn đề e muốn hỏi, số columns có bị giới hạn ko thầy? E nâng nó lên khoảng 65k dòng thì vẫn chạy ok. Nhưng e nâng hơn nữa thì format vẫn là genaral. Không còn la text nữa. Em có thể fix con số đó khoảng 800k đến 1 triệu dòng được không ạ?

 

[ndu96081631]

Cám ơn thầy, em thấy code như thế này chạy là ok rồi ạ. Nhưng có 1 vấn đề e muốn hỏi, số columns có bị giới hạn ko thầy? E nâng nó lên khoảng 65k dòng thì vẫn chạy ok. Nhưng e nâng hơn nữa thì format vẫn là genaral. Không còn la text nữa. Em có thể fix con số đó khoảng 800k đến 1 triệu dòng được không ạ?

Cái số 500 ấy là số cột cơ mà, đâu phải số dòng
----------------------------------------------------------------------------
Ngoài ra, xin lỗi vì code trên chưa chuẩn lắm (về cái vụ Unicode)
Cụ thể trong code Import phải sửa lại thành vầy:

Sub ImportTextFile(ByVal FileName As String, ByVal Target As Range)
  Dim aFormat(1 To 500), i As Long, wks As Worksheet
  On Error Resume Next
  Set wks = Target.Parent
  For i = 1 To 500
    aFormat(i) = 2
  Next
  On Error Resume Next
  With wks.QueryTables.Add("TEXT;" & FileName, Target)
    [COLOR=#ff0000][B].TextFilePlatform = 65001[/B][/COLOR]
    .TextFileColumnDataTypes = aFormat
    .Refresh BackgroundQuery:=False
  End With
End Sub

Thêm dòng màu đỏ TextFilePlatform = 65001 vào rồi thì file Excel mới nhận được chuổi Unicode
Bạn thí nghiệm lại xem

 

[ginkgo36]

Em cám ơn thầy nhiều. Trong hàm main em có sửa 1 chút trong cách lấy đường dẫn tới file txt. Và em thấy hình như nó chạy chậm hơn cách của thầy, hoặc là do máy của e nên e có cảm giác chậm. Thầy coi giùm e chút.

2 cách này khác nhau thế nào vậy thầy?

Sub Main()
Dim txtFile As String, sFileSave As String, Folder
Dim aTextFiles, fleItem
Dim Target As Range, wkb As Workbook, cnn As WorkbookConnection
Dim fs
On Error Resume Next
Application.ScreenUpdating = False
folderspec = InputBox(Prompt)
Set fs = CreateObject("Scripting.FileSystemObject")
Folder = fs.GetFolder(folderspec)
'Folder = CreateObject("Shell.Application").BrowseForFolder(0, "", 1).Self.Path
If TypeName(Folder) = "String" Then
aTextFiles = GetListFiles(Folder, "*.txt", True)
If IsArray(aTextFiles) Then
For Each fleItem In aTextFiles
txtFile = CStr(fleItem)
Set wkb = Workbooks.Add
Set Target = wkb.ActiveSheet.Range("A1")
ImportTextFile txtFile, Target
sFileSave = Left(txtFile, Len(txtFile) - 4)
For Each cnn In wkb.Connections: cnn.Delete: Next
wkb.SaveAs sFileSave, 51: wkb.Close
Next
End If
End If
Application.ScreenUpdating = True
MsgBox "Finish!"
End Sub

Còn 1 cái nữa, sau khi mở được file txt trên excel, e có thể cho chạy 1 macro có sẵn (gọi tên macro có sẵn) để làm 1 số việc rồi mới save as file có được không thầy? Và chèn nó ở chỗ nào được?

Cám ơn thầy

 

[ndu96081631]

Em cám ơn thầy nhiều. Trong hàm main em có sửa 1 chút trong cách lấy đường dẫn tới file txt. Và em thấy hình như nó chạy chậm hơn cách của thầy, hoặc là do máy của e nên e có cảm giác chậm. Thầy coi giùm e chút.

2 cách này khác nhau thế nào vậy thầy?

Sub Main()
Dim txtFile As String, sFileSave As String, Folder
Dim aTextFiles, fleItem
Dim Target As Range, wkb As Workbook, cnn As WorkbookConnection
Dim fs
On Error Resume Next
Application.ScreenUpdating = False

folderspec = InputBox(Prompt)
Set fs = CreateObject("Scripting.FileSystemObject")
Folder = fs.GetFolder(folderspec)

If TypeName(Folder) = "String" Then
..................
For Each cnn In wkb.Connections: cnn.Delete: Next
'' Muốn làm gì với wkb, hãy chèn code vào đây
wkb.SaveAs sFileSave, 51: wkb.Close
..................
End Sub

Hộp Browse for folder khỏe thân vậy không chịu, lại đi gõ bằng tay trên InputBox cho nhọc thân (chẳng hiểu)

Còn 1 cái nữa, sau khi mở được file txt trên excel, e có thể cho chạy 1 macro có sẵn (gọi tên macro có sẵn) để làm 1 số việc rồi mới save as file có được không thầy? Và chèn nó ở chỗ nào được?

Cám ơn thầy

Giữa 2 dòng code màu xanh, bạn muốn chèn gì tùy thích. Với wkb chính là workbook đã được Import từ txt