Chuyện bé Pi & File TSV Kỳ Bí

<!-- CODE VBA ĐẦY ĐỦ – COPY ĐƯỢC NGAY 😎 -->

Sub Import_Auto_Universal(control As IRibbonControl)
  Dim fd As FileDialog
  Dim filePath As String, fileExt As String, folderPath As String
  Dim ws As Worksheet, qt As QueryTable
  Dim FileName As String, TenSheet As String
  Dim i As Long, delimiter As String

  '==================================================
  ' 1. Chọn file bất kỳ (CSV, TSV, TXT, LOG…)
  '==================================================
  Set fd = Application.FileDialog(msoFileDialogFilePicker)
  With fd
    .Title = "Chọn file dữ liệu cần mở"
    .Filters.Clear
    .Filters.Add "Text Data", "*.csv; *.tsv; *.txt; *.log; *.dat", 1
    .AllowMultiSelect = False
    If .Show <> -1 Then Exit Sub
    filePath = .SelectedItems(1)
  End With

  folderPath = Left(filePath, InStrRev(filePath, "\") - 1)

  '==================================================
  ' 2. Nhận biết delimiter bằng AI logic
  '==================================================
  delimiter = DetectDelimiter(filePath)

  If delimiter = "" Then
    MsgBox "Không thể nhận diện delimiter!", vbCritical
    Exit Sub
  End If

  '==================================================
  ' 3. Tạo sheet từ tên file
  '==================================================
  FileName = Split(filePath, "\")(UBound(Split(filePath, "\")))
  TenSheet = Left(FileName, InStrRev(FileName, ".") - 1)
  TenSheet = Left(TenSheet, 30)

  On Error Resume Next
  Set ws = ThisWorkbook.Sheets(TenSheet)
  On Error GoTo 0

  If ws Is Nothing Then
    Set ws = ThisWorkbook.Sheets.Add(After:=Sheets(Sheets.Count))
    ws.Name = TenSheet
  Else
    Set ws = ThisWorkbook.Sheets.Add(After:=Sheets(Sheets.Count))
    ws.Name = TenSheet & "_" & Format(Now, "hhmmss")
  End If

  '==================================================
  ' 4. Dọn dữ liệu cũ
  '==================================================
  ws.Cells.Clear
  For Each qt In ws.QueryTables
    qt.Delete
  Next qt

  Application.ScreenUpdating = False

  '==================================================
  ' 5. Import file
  '==================================================
  With ws.QueryTables.Add(Connection:="TEXT;" & filePath, Destination:=ws.Range("A1"))
    .TextFileParseType = xlDelimited

    .TextFileCommaDelimiter = False
    .TextFileSemicolonDelimiter = False
    .TextFileTabDelimiter = False
    .TextFileOtherDelimiter = False

    Select Case delimiter
      Case ",": .TextFileCommaDelimiter = True
      Case ";": .TextFileSemicolonDelimiter = True
      Case vbTab: .TextFileTabDelimiter = True
      Case "|": .TextFileOtherDelimiter = True: .TextFileOtherDelimiter = "|"
      Case Else: .TextFileOtherDelimiter = True: .TextFileOtherDelimiter = delimiter
    End Select

    Dim TextCols(1 To 500) As Long
    For i = 1 To 500
      TextCols(i) = xlTextFormat
    Next i
    .TextFileColumnDataTypes = TextCols

    .TextFilePlatform = 65001
    .TextFileTextQualifier = xlTextQualifierDoubleQuote
    .Refresh BackgroundQuery:=False
  End With

  ws.UsedRange.NumberFormat = "@"
  ws.Columns.AutoFit

  Application.ScreenUpdating = True

  '==================================================
  ' 6. Mở folder chứa file
  '==================================================
  Shell "explorer.exe """ & folderPath & """", vbNormalFocus

  '==================================================
  ' 7. Thông báo
  '==================================================
  MsgBox "Đã import thành công!" & vbCrLf & vbCrLf & _
      "File: " & FileName & vbCrLf & _
      "Delimiter nhận diện: " & Replace(delimiter, vbTab, "TAB") & vbCrLf & _
      "Sheet mới: " & ws.Name, _
      vbInformation, "THÀNH CÔNG"

End Sub

'===========================================================
' HÀM TỰ ĐỘNG NHẬN BIẾT DELIMITER (AI LOGIC)
'===========================================================
Function DetectDelimiter(path As String) As String
  Dim f As Integer: f = FreeFile
  Dim firstLine As String
  Dim dCandidates As Variant
  Dim d As Variant, count As Long, bestCount As Long
  Dim bestDelimiter As String

  dCandidates = Array(",", ";", vbTab, "|")

  On Error GoTo ErrHandler

  Open path For Input As #f
  Line Input #f, firstLine
  Close #f

  bestDelimiter = ""
  bestCount = 0

  For Each d In dCandidates
    count = UBound(Split(firstLine, d))
    If count > bestCount Then
      bestCount = count
      bestDelimiter = d
    End If
  Next d

  DetectDelimiter = bestDelimiter
  Exit Function

ErrHandler:
  DetectDelimiter = ""
End Function

' (Code ở cuối trang — mở lên là IQ tăng 3 điểm 😆)