Extract data from text files with multiple columns of data

How does VB extract the numbers in the first column from a text file?

 

You can see the following statement in the previous post, but you cannot extract multiple columns of text.

Sub addall ()

Application. screenupdating = false

Dim Arr () as string, I as long, N as long, B () as byte, temp as string, lines () as string, X as long

Redim Arr (1 to 5, 1 to 65535) 'is a two-dimensional array with four columns. Excel has a maximum of 65535 rows,

Temp = Dir ([a1] & "/*. txt ")

While temp> ""

N = n + 1

Arr (1, N) = temp 'array (1, N) is the file name

Open [a1] & "/" & temp for binary as # 1' Use channel 1 # To store open files

Redim B (lof (1) 'gets the file length, defined as the length of the array

Get #1, B 'read the last number', read the opened text file to array B

Close # 1' close 1 # channel

On Error resume next 'jumps out if an error occurs and changes the error trap

Lines = Split (strconv (B, vbunicode), vbcrlf) 'creates an array. The array content is the content of the file: divide the text content into arrays based on the Terminator and line break,
'In this way, each element in the lines array is the content of each row in the text file. The first line is lines (0), the second line is lines (1), and so on.

Arr (2, n) = lines ([B1]) 'arr is a two-dimensional array, and Arr () is the name of the first file,
'Arr (7th) indicates the content of the first file's 9th rows. Arr () indicates the content of the first file's rows. Arr) is the content of the first 12th lines of the file, and so on.

Arr (3, n) = lines ([C1])

Arr (4, N) = lines ([D1])

Arr (5, n) = lines ([E1])

Temp = Dir () 'makes the loop end condition reach

Wend' end Loop

Redim preserve Arr (1 to 5, 1 to n)

[A2]. Resize (n, 5) = worksheetfunction. Transpose (ARR) 'array arr paste to the table

Application. screenupdating = true
End sub

 

//////////////////////////////////////// ///////////////////

 

Cells (1, J) is assumed to be 5

Lines (cells (1, J)-1) The 5th elements of lines (subscript starts from 0)

Split (lines (cells (1, J)-1) are separated by Spaces

Split (lines (cells (1, J)-1) (0) 1st Elements

Replace (split (lines (cells (1, J)-1) (1), "") Remove commas (,)

 

 

//////////////////////////////////////// /// // Change:

 

Sub addall ()
Application. screenupdating = false' prevents screen jitter and screen refresh is disabled.
Dim Arr () as string, I as long, J as long, N as long, temp as string, lines () as string, X as long
Redim Arr (1 to 10, 1 to 65535)
Temp = Dir ([a1] & "/*. txt ")
While temp> "" 'loop statement. Exit the loop after temp enumeration is complete.
N = n + 1
Redim preserve Arr (1 to 10, 1 to n) 'reassign the array size
Arr (1, N) = temp 'array (1, N) is the file name
On Error resume next 'when an error occurs, exit directly.
Open [a1] & "/" & temp for input as # 1' Use channel 1 # To store open files
Lines = Split (strconv (inputb (lof (1), #1), vbunicode), vbcrlf) 'writes the files opened through channel 1 to the lines array,
'Lines = Split (strconv (inputb (lof (1), 1), vbunicode), vbcrlf) #1 can also be written as 1.
'Variable = input (String Length, file number)
'Inputb: to copy the entire file to a variable, use the inputb function to copy bytes from the file to the variable.
'Because the inputb function returns an ASCII string, you must use the strcopy function to convert the ASCII string to a unicode string.
'Code: file = strcopy (input (lof (filenanum), filenum), vbunicode)
'Vbcrlf, when the carriage return returns a line break

Close # 1' close 1 # channel

For J = 2 to 10
Arr (J, n) = Replace (split (lines (cells (1, J)-1) (0 ),",","")
'Arr (J, n) = Split (lines (cells (1, J)-1) (0)
'Cells (1, J) is assumed to be 5
'Lines (cells (1, J)-1) The 5th elements of lines (subscript starts from 0)
'Split (lines (cells (1, J)-1) is separated by Spaces
'Split (lines (cells (1, J)-1) (0) 1st Elements
'Replace (split (lines (cells (1, J)-1) (1), "") Remove commas (,)

Next
Temp = Dir ()
Wend
[A2]. Resize (n, 10) = worksheetfunction. Transpose (ARR) 'transpose and paste
Application. screenupdating = true' allows screen refresh
End sub