convert file which opens in 1 row in text to multiple columns in excel

[james bond]

attached is link to file which opens in txt but when i import to excel comes in 1 row and column and shows not imported fully
can anyone please explain how i can import it as shown in the table
please someone said by power query but i really need to understand step by step as i really dont know and would be indebtited

LEDG-01.txt

drive.google.com

thanking you in anticipation regards

PQ Import TXT One Row Pivot AliGW (2).xlsx
	A	B	C
2	CRD0120001010197051000001/010197/CASH	SALES	0
3	CRD0120001010197053000001/010197/CASH	SALES	0
4	CRD0120001010197052000001/010197/CASH	SALES	0
5	CRD0120001010197054000001/010197/CASH	SALES	0
6	CRD0110001010197035000001/010197/CASH	SALES	0
7	CRD0120002020197051000002/020197/CASH	SALES	0
8	CRD0120002020197053000002/020197/CASH	SALES	0
9	CRD0120002020197054000002/020197/CASH	SALES	0
10	CRD0120002020197052000002/020197/CASH	SALES	0
11	CRD0110002020197035000002/020197/CASH	SALES	0
12	CRD0120003030197051000003/030197/CASH	SALES	0
13	CRD0120003030197053000003/030197/CASH	SALES	0
14	CRD0120003030197054000003/030197/CASH	SALES	0
15	CRD0120003030197052000003/030197/CASH	SALES	0
16	CRD0110003030197035000003/030197/CASH	SALES	0
17	CRD0120004040197051000004/040197/CASH	SALES	0
18	CRD0120004040197053000004/040197/CASH	SALES	0
19	CRD0120004040197054000004/040197/CASH	SALES	0
20	CRD0120004040197052000004/040197/CASH	SALES	0
21	CRD0110004040197035000004/040197/CASH	SALES	0
22	CRD012000505019705100INVOICE	CANCELLED	0
23	CRD012000505019705300INVOICE	CANCELLED	0
24	CRD012000505019705400INVOICE	CANCELLED	0
25	CRD012000505019705200INVOICE	CANCELLED	0
26	CRD011000505019703500INVOICE	CANCELLED	0
27	CSL0120501270197057000501/270197/CASH	SALES5401-5500	234000
28	CSL0120501270197057000501/270197/CASH	SALES5401-5500	19500
29	CSL0120501270197057000501/270197/CASH	SALES5401-5500	64500
30	CSL0120501270197057000501/270197/CASH	SALES5401-5500	302995
31	CSL0110501270197006000501/270197/CASH	SALES5401-5500	102349
32	CSL0110501270197035000501/270197/CASH	SALES5401-5500	921146
33	CSL0120502310197057000502/310197/CASH	SALES7701-7713	70000
34	CSL0120502310197057000502/310197/CASH	SALES7701-7713	23500
35	CSL0120502310197057000502/310197/CASH	SALES7701-7713	21500
36	CSL0120502310197057000502/310197/CASH	SALES7701-7713	31000
37	CSL0110502310197006000502/310197/CASH	SALES7701-7713	7300
38	CSL0110502310197035000502/310197/CASH	SALES7701-7713	138700
39	CSL0220001010297051000001/010297/CASH	SALES	26247440
40	CSL0220001010297053000001/010297/CASH	SALES	4146352
41	CSL0220001010297054000001/010297/CASH	SALES	3080682
42	CSL0210001010297035000001/010297/CASH	SALES	33474474
43	CSL0220002020297051000002/020297/CASH	SALES	25239646
44	CSL0220002020297053000002/020297/CASH	SALES	4972239
45	CSL0220002020297054000002/020297/CASH	SALES	3999690
46	CSL0210002020297035000002/020297/CASH	SALES	34211575
47	CSL0220003030297051000003/030297/CASH	SALES	17162177
48	CSL0220003030297053000003/030297/CASH	SALES	4031008
49	CSL0220003030297054000003/030297/CASH	SALES	2046780
50	CSL0220003030297052000003/030297/CASH	SALES	91424
51	CSL0210003030297035000003/030297/CASH	SALES	23331389
52	CSL0220004040297051000004/040297/CASH	SALES	19337181
53	CSL0220004040297053000004/040297/CASH	SALES	6019034
54	CSL0220004040297054000004/040297/CASH	SALES	2387520
1

 

[james bond]

Here is something to try that works fast:

Sub ImportTextData()
'
    Dim ArrayRow                As Long
    Dim ChunkFirstSpacePosition As Long
    Dim FileStringLength        As Long
    Dim FileChunkString         As String, FileString   As String
    Dim ResultArray()           As Variant
    Dim ws                      As Worksheet
'
    Const FilePath              As String = "C:\Users\Laptop\Downloads\LEDG-01.txt" ' <--- Set this to the location of your text file
    Const Column_C_Data_Start   As Long = 97                                        ' <--- Set this to the start position in the chunk for Column C data
    Const LengthOfChunk         As Long = 150                                       ' <--- Set this to the length for each chunk of data
    Set ws = Sheets("1")                                                            ' <--- Set this to the name of the worksheet to displaay the results into
'
    Open FilePath For Input As #1                                                   ' Open the text file
    FileString = Input$(LOF(1), 1)                                                  ' Read the contents of the file into FileString
    Close #1                                                                        ' Close the ext file
'
    ArrayRow = 1                                                                    ' Initialize ArrayRow
'
    FileStringLength = Len(FileString)                                              ' Get the length of data in the text file
'
    ReDim ResultArray(1 To FileStringLength / 150, 1 To 3)                          ' Establish the dimensions of the ResultArray
'
    Do While (ArrayRow - 1) * LengthOfChunk < FileStringLength                      ' Loop through the chunks of data from the text file
        FileChunkString = Mid(FileString, (ArrayRow - 1) * _
                LengthOfChunk + 1, LengthOfChunk)                                   '   Get the chunk of data to scrape data from
'
        ChunkFirstSpacePosition = InStr(1, FileChunkString, " ")                    '   Find the position of the first blank in the FileChunkString
'
        ResultArray(ArrayRow, 1) = Left(FileChunkString, ChunkFirstSpacePosition - 1) '   Save the data for column A into ResultArray
        ResultArray(ArrayRow, 2) = Trim(Mid(FileChunkString, ChunkFirstSpacePosition + 1, _
                Column_C_Data_Start - ChunkFirstSpacePosition - 1))                 '   Save the data for column B into ResultArray
        ResultArray(ArrayRow, 3) = Mid(FileChunkString, Column_C_Data_Start, _
                LengthOfChunk - Column_C_Data_Start)                                '   Save the data for column C into ResultArray
'
        ArrayRow = ArrayRow + 1                                                     '   Increment ArrayRow
    Loop                                                                            ' Loop back
'
    ws.Range("A2").Resize(UBound(ResultArray, 1), UBound(ResultArray, 2)) = ResultArray ' Display the results to the result sheet
'
    ws.UsedRange.EntireColumn.AutoFit                                               ' Autofit the width of the columns
End Sub

You will have to check the results to see if it works for you.

sir also ResultArray(ArrayRow, 1) = Left(FileChunkString, ChunkFirstSpacePosition - 1) shows subscript out of range

 

[Bosquedeguate]

sorry sir just have written before also if you could guide me as to how instead of number of characters if you can tell me how to add in new row everytime it starts with CRD
i am extremely sorry if i made a mistake

Not all the records start with CRD.
You will have to base your record length on 150 characters.

 

[Bosquedeguate]

Not all the records start with CRD.
You will have to base your record length on 150 characters.

Here are working versions of both the original code that I sent you and a modified version of the code submitted by james bond.
You will need to modify the filepath and the sheet name where data will be stored.
the code james bond submitted runs much faster because it reads the entire file into memory, where the other version reads and writes the text file line by line.
There are more than 6000 records in the source file.

There are also approximately 15 different record types (i.e. the first 3 characters: CRD, CLO, COM, ...)

Bosquedeguate Code

Option Explicit
Public Sub ImportData()
  Dim str
  Dim r As Long
  Dim rng As Range
  Open "C:/users/Forrest/Documents/Projects/MrExcel/LEDG-01.TXT" For Input As #1
  r = 1
  Do While Not EOF(1)
    str = Input(150, #1)
    With myRec
      Set rng = ActiveSheet.Range("A" & r & ":H" & r)
      rng(1, 1) = Left(str, 21)
      rng(1, 2) = Mid(str, 22, 75)
      rng(1, 3) = Val(Mid(str, 97, 12))
      rng(1, 4) = Mid(str, 109, 42)
    End With
    r = r + 1
  Loop
  Close #1
End Sub

James Bond code

Option Explicit
Sub ImportTextData()
'
    Dim ArrayRow                As Long
    Dim ChunkFirstSpacePosition As Long
    Dim FileStringLength        As Long
    Dim FileChunkString         As String, FileString   As String
    Dim ResultArray()           As Variant
    Dim ws                      As Worksheet
'
    Const FilePath              As String = "C:\Users\forrest\Documents\Projects\MrExcel\LEDG-01.txt" ' <--- Set this to the location of your text file
    Const Column_C_Data_Start   As Long = 97                                        ' <--- Set this to the start position in the chunk for Column C data
    Const LengthOfChunk         As Long = 150                                       ' <--- Set this to the length for each chunk of data
    Set ws = Sheets("Sheet2")                                                       ' <--- Set this to the name of the worksheet to displaay the results into
'
    Open FilePath For Input As #1                                                   ' Open the text file
    FileString = Input$(LOF(1), 1)                                                  ' Read the contents of the file into FileString
    Close #1                                                                        ' Close the ext file
'
    ArrayRow = 1                                                                    ' Initialize ArrayRow
'
    FileStringLength = Len(FileString)                                              ' Get the length of data in the text file
'
    ReDim ResultArray(1 To FileStringLength / 150, 1 To 4)                          ' Establish the dimensions of the ResultArray
'
    Do While (ArrayRow - 1) * LengthOfChunk < FileStringLength                      ' Loop through the chunks of data from the text file
        FileChunkString = Mid(FileString, (ArrayRow - 1) * _
                LengthOfChunk + 1, LengthOfChunk)                                   '   Get the chunk of data to scrape data from
'
        ChunkFirstSpacePosition = InStr(1, FileChunkString, " ")                    '   Find the position of the first blank in the FileChunkString
'
        ResultArray(ArrayRow, 1) = Left(FileChunkString, 21) '   Save the data for column A into ResultArray
        ResultArray(ArrayRow, 2) = Trim(Mid(FileChunkString, 22, 75))
        ResultArray(ArrayRow, 3) = Trim(Mid(FileChunkString, 97, 12))
        ResultArray(ArrayRow, 4) = Trim(Mid(FileChunkString, 109, 42))
'
        ArrayRow = ArrayRow + 1                                                     '   Increment ArrayRow
    Loop                                                                            ' Loop back
'
    ws.Range("A2").Resize(UBound(ResultArray, 1), UBound(ResultArray, 2)) = ResultArray ' Display the results to the result sheet
'
    ws.UsedRange.EntireColumn.AutoFit                                               ' Autofit the width of the columns
End Sub

 

[james bond]

Here are working versions of both the original code that I sent you and a modified version of the code submitted by james bond.
You will need to modify the filepath and the sheet name where data will be stored.
the code james bond submitted runs much faster because it reads the entire file into memory, where the other version reads and writes the text file line by line.
There are more than 6000 records in the source file.

There are also approximately 15 different record types (i.e. the first 3 characters: CRD, CLO, COM, ...)

Bosquedeguate Code

Option Explicit
Public Sub ImportData()
  Dim str
  Dim r As Long
  Dim rng As Range
  Open "C:/users/Forrest/Documents/Projects/MrExcel/LEDG-01.TXT" For Input As #1
  r = 1
  Do While Not EOF(1)
    str = Input(150, #1)
    With myRec
      Set rng = ActiveSheet.Range("A" & r & ":H" & r)
      rng(1, 1) = Left(str, 21)
      rng(1, 2) = Mid(str, 22, 75)
      rng(1, 3) = Val(Mid(str, 97, 12))
      rng(1, 4) = Mid(str, 109, 42)
    End With
    r = r + 1
  Loop
  Close #1
End Sub

James Bond code

Option Explicit
Sub ImportTextData()
'
    Dim ArrayRow                As Long
    Dim ChunkFirstSpacePosition As Long
    Dim FileStringLength        As Long
    Dim FileChunkString         As String, FileString   As String
    Dim ResultArray()           As Variant
    Dim ws                      As Worksheet
'
    Const FilePath              As String = "C:\Users\forrest\Documents\Projects\MrExcel\LEDG-01.txt" ' <--- Set this to the location of your text file
    Const Column_C_Data_Start   As Long = 97                                        ' <--- Set this to the start position in the chunk for Column C data
    Const LengthOfChunk         As Long = 150                                       ' <--- Set this to the length for each chunk of data
    Set ws = Sheets("Sheet2")                                                       ' <--- Set this to the name of the worksheet to displaay the results into
'
    Open FilePath For Input As #1                                                   ' Open the text file
    FileString = Input$(LOF(1), 1)                                                  ' Read the contents of the file into FileString
    Close #1                                                                        ' Close the ext file
'
    ArrayRow = 1                                                                    ' Initialize ArrayRow
'
    FileStringLength = Len(FileString)                                              ' Get the length of data in the text file
'
    ReDim ResultArray(1 To FileStringLength / 150, 1 To 4)                          ' Establish the dimensions of the ResultArray
'
    Do While (ArrayRow - 1) * LengthOfChunk < FileStringLength                      ' Loop through the chunks of data from the text file
        FileChunkString = Mid(FileString, (ArrayRow - 1) * _
                LengthOfChunk + 1, LengthOfChunk)                                   '   Get the chunk of data to scrape data from
'
        ChunkFirstSpacePosition = InStr(1, FileChunkString, " ")                    '   Find the position of the first blank in the FileChunkString
'
        ResultArray(ArrayRow, 1) = Left(FileChunkString, 21) '   Save the data for column A into ResultArray
        ResultArray(ArrayRow, 2) = Trim(Mid(FileChunkString, 22, 75))
        ResultArray(ArrayRow, 3) = Trim(Mid(FileChunkString, 97, 12))
        ResultArray(ArrayRow, 4) = Trim(Mid(FileChunkString, 109, 42))
'
        ArrayRow = ArrayRow + 1                                                     '   Increment ArrayRow
    Loop                                                                            ' Loop back
'
    ws.Range("A2").Resize(UBound(ResultArray, 1), UBound(ResultArray, 2)) = ResultArray ' Display the results to the result sheet
'
    ws.UsedRange.EntireColumn.AutoFit                                               ' Autofit the width of the columns
End Sub

very very obliged a big thanks for helping me out