How to Determine if column of certain rows in a text file has data?

[Tony]

I am aware that Excel VBA is able to copy text files line by line.

However, is it possible to test that certain columns in the text file
has information and to store that data(1) in a variable.
Thereafter, on another line, get the data(2) and print both data(1)
and data(2) on one line in an output text file.

eg:

Original Aging Text File has

Cus No Customer Name Invoice No Total Current 1 Mth
10001 ABC Shoe Shop Pte Ltd
A1000101 100.00 100.00
A1000102 200.00 200.00

New Output Aging Text File (Or directly to Excel)

Cust No Customer Name Invoice No Total Current 1 Mth
10001 ABC Shoe Shop Pte Ltd A1000101 100.00 100.00
10001 ABC Shoe Shop Pte Ltd A1000102 200.00 200.00

Thanks in Advance
Tony S

[Patrick Molloy]

its certainly possible, but to a large degree it depends on how the data is
saved. Is it a CSV file? tab separated or space.
The important thing is how to identify a customer number as the "start" of a
block.
Looks like the customer number is numeric, while a continuation, as per your
example is textual. So you could test for that

DO UNTIL textfile.EOF
' read in a line
text =textfile.ReadLine
' get the first space
firstgap = instr(2,text," ")
' get the first word
firstword = left(text,firstgap-1)
if ISNUMERIC(firstword) THEN
' its a new customer
custnumber = firstword
custname = getsecodword
invoice = getinvoice
' etc
else
' its more records
getinvoice
etc
End If
' write the data to a new file/worksheet

' now for the next line
LOOP





"Tony" wrote:

> I am aware that Excel VBA is able to copy text files line by line.
>
> However, is it possible to test that certain columns in the text file
> has information and to store that data(1) in a variable.
> Thereafter, on another line, get the data(2) and print both data(1)
> and data(2) on one line in an output text file.
>
> eg:
>
> Original Aging Text File has
>
> Cus No Customer Name Invoice No Total Current 1 Mth
> 10001 ABC Shoe Shop Pte Ltd
> A1000101 100.00 100.00
> A1000102 200.00 200.00
>
> New Output Aging Text File (Or directly to Excel)
>
> Cust No Customer Name Invoice No Total Current 1 Mth
> 10001 ABC Shoe Shop Pte Ltd A1000101 100.00 100.00
> 10001 ABC Shoe Shop Pte Ltd A1000102 200.00 200.00
>
> Thanks in Advance
> Tony S
>