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

 

[Bosquedeguate]

There are no delimiters in the text file and it appears that there are no new line characters. You are dealing with a fixed length record and fixed length fields.
Do you have a document that describes the file format? And field lengths?

 

[Bosquedeguate]

There are no delimiters in the text file and it appears that there are no new line characters. You are dealing with a fixed length record and fixed length fields.
Do you have a document that describes the file format? And field lengths?

After working with the file you appear to have 150 character, fixed-length records. Here is a worksheet with all records imported, 3 columns of data.
I hope you can work with this.
The macro code to read the file is below: (change the file path and name if you have other files to import.)

Option Explicit
Public Sub ImportData()
  Dim myRec As myRecord
  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

Link to Workbook with imported data
LEDG-01 Workbook

 

[jolivanes]

I can't see why someone would need a 6,700 KB text file to test their solution on.
A couple hundred lines should be sufficient.

 

[james bond]

After working with the file you appear to have 150 character, fixed-length records. Here is a worksheet with all records imported, 3 columns of data.
I hope you can work with this.
The macro code to read the file is below: (change the file path and name if you have other files to import.)

Option Explicit
Public Sub ImportData()
  Dim myRec As myRecord
  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

Link to Workbook with imported data
LEDG-01 Workbook

sir can you guide me it is showing compile error user-defined type not defined which refrence i have to add in in tools-refrence
iam sorry asking you this but am not having much knowledge but trying
thanks in anticipattion

 

[johnnyL]

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.

 

[james bond]

thank you sir did follow your advice and went superrr fast however can you please help me and please guide me as i am having two problems
1) ResultArray(ArrayRow, 1) = Left(FileChunkString, ChunkFirstSpacePosition - 1) shows run time error 5 invalid procedure or call argument
2) if i want data to change in new row with change in word like "CRD" and data after that till next CRD what changes to be made

i am attaching code what i copied
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 = "d:\LEDG.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
Const Column_C_Data_Start As Long = 47 ' <--- Set this to the start position in the chunk for Column C data
Const LengthOfChunk As Long = 47 ' <--- Set this to the length for each chunk of data
Set ws = ThisWorkbook.Sheets("Sheet1")
' 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, 1) = Left(FileChunkString, ChunkFirstSpacePosition - 1)
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

please help
thanks

 

[Bosquedeguate]

sir can you guide me it is showing compile error user-defined type not defined which refrence i have to add in in tools-refrence
iam sorry asking you this but am not having much knowledge but trying
thanks in anticipattion

Delete that line, it is not needed
This is for the version of a import sub that I submitted.

 

[johnnyL]

@james bond, Those changes that you made to the code that I submitted are causing the code to error. What were you trying to accomplish by changing the code:

' 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
   Const Column_C_Data_Start   As Long = 47                                        ' <--- Set this to the start position in the chunk for Column C data
   Const LengthOfChunk         As Long = 47                                       ' <--- Set this to the length for each chunk of data

 

[james bond]

@james bond, Those changes that you made to the code that I submitted are causing the code to error. What were you trying to accomplish by changing the code:

' 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
   Const Column_C_Data_Start   As Long = 47                                        ' <--- Set this to the start position in the chunk for Column C data
   Const LengthOfChunk         As Long = 47                                       ' <--- Set this to the length for each chunk of data

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