Import text file data into excel

Hello,

I have a text file with data that I would like to import into excel and make it look like database entries. The idea is that once the text file data is cleaned and properly sorted in excel, it then will be imported into Access.

I have cleaning by trying IF statements and then applying filters manually but I require a better solution.

Any help is appreciated.


Arvin

As usual, work from a blank sheet. Obviously, you might need to change the path and filename for the text file's variable values.

Option Explicit
'http://vbaexpress.com/kb/getarticle.php?kb_id=1035
Public glb_origCalculationMode As Integer

' http://www.excelforum.com/excel-programming/807401-import-text-file-data-into-excel.html
Sub GetNRevData()
  Dim nRevPath As String, nRevFN As String
  Dim sTXT As String, aTXT() As String
  Dim line As Long, s As String
  Dim nr As Long, a() As String
  Dim i As Integer, ub As Integer, j As Integer
  
  nRevPath = ThisWorkbook.Path & "\"
  nRevFN = nRevPath & "nrev1.txt"
  
  'Cells.ClearContents

  If Dir(nRevFN) = "" Then
    MsgBox nRevFN & vbLf & "Set the correct path to the text file.", vbCritical, "Incorrect Path"
    Exit Sub
  End If
  
  On Error GoTo EndSub
  SpeedOn
  
  Range("A1").Value2 = "ACNA"
  Range("B1").Value2 = "RAC"
  Range("C1").Value2 = "RAC_CODE_AND_DESCRIPTION"
  Range("D1").Value2 = "BILLED_ADJUSTMENT_AMOUNT"
  Range("E1").Value2 = "BILLED_USOC_REVENUE"
  Range("F1").Value2 = "BILLED_USAGE_REVENUE"
  
  sTXT = StrFromTXTFile(nRevFN)
  aTXT() = Split(sTXT, vbLf)
  nr = 1
  For line = LBound(aTXT) To UBound(aTXT)
    s = aTXT(line)
    If Len(s) = 95 And Left(s, 2) = "  " _
      And Right(s, 2) <> "=" & vbCr _
      Then _
      'Debug.Print line, Right(s, 2) 'Len(aTXT(line)), aTXT(line)
      nr = nr + 1
      Do
        s = Replace(Trim(s), "  ", " ")
      Loop Until InStr(s, "  ") = 0
      a() = Split(s, " ")
      i = 0
      If Not (IsNumeric(a(0))) Then
        i = i + 1
        Range("A" & nr).Value = a(0)
        Else
        Range("A" & nr).Value = Range("A" & (nr - 1)).Value
      End If
      ub = UBound(a)
      Range("B" & nr).Value = a(i)
      Range("F" & nr).Value = Val(Replace(a(ub), ",", ""))
      Range("E" & nr).Value = Val(Replace(a(ub - 1), ",", ""))
      Range("D" & nr).Value = Val(Replace(a(ub - 2), ",", ""))
      s = ""
      For j = 2 To (ub - 3)
        s = s & a(j) & " "
      Next j
      Range("C" & nr).Value = Left(s, Len(s) - 1)
    End If
  Next line
EndSub:
  SpeedOff
End Sub

Function StrFromTXTFile(filePath As String) As String
  Dim str As String, hFile As Integer
  
  If Dir(filePath) = "" Then
    StrFromTXTFile = "NA"
    Exit Function
  End If
  
  hFile = FreeFile
  Open filePath For Binary Access Read As #hFile
  str = Input(LOF(hFile), hFile)
  Close hFile
  
  StrFromTXTFile = str
End Function

Sub SpeedOn(Optional StatusBarMsg As String = "Running macro...")
  glb_origCalculationMode = Application.Calculation
  With Application
    .Calculation = xlCalculationManual
    .ScreenUpdating = False
    .EnableEvents = False
    .DisplayAlerts = False
    .Cursor = xlWait
    .StatusBar = StatusBarMsg
    .EnableCancelKey = xlErrorHandler
  End With
End Sub

Sub SpeedOff()
  With Application
    .Calculation = glb_origCalculationMode
    .ScreenUpdating = True
    .EnableEvents = True
    .DisplayAlerts = True
    .CalculateBeforeSave = True
    .Cursor = xlDefault
    .StatusBar = False
    .EnableCancelKey = xlInterrupt
  End With
End Sub