extract selective info from a flat text file

K

kas

Hi.

Not sure where to post this, so apologies if in the wrong slot.

I have a text file that, as well as having header and footer rows, has a
variable number of rows containing a string of characters and it is from
these rows that I need to extract selective data eg characters 55 to 63 and
characters 39 to 46 - the latter divided by 100. This info needs to form a
new text or a .csv file. Can anyone help me with this or do I need to employ
an expert (which I am not!)?

Many thanks
 
M

macropod

hi kas,

You'll need to tell us more about the data file. For starters:
.. how is it structured - fixed width lines, comma/tab separators, a data stream all in a single logical line
.. how do you know which characters to extract (eg are there distinguishing marks in the file, such as brackets etc).
 
B

Bob I

Your question is somewhat confusing. Do you mean ASCII characters 55-63
and 39-46? And if so, they would be individual characters not numbers
and so not divisible by 100. On the other hand if you want to treat them
as a number that would be characters 48-63, which you could prepend with
a ".0" and then they could be imported as a number that was 1/100th of
the original 0 through 9 found in the file.

If that isn't what you mean and are referring to the POSITION of the
characters in the row then you can do that very simply within Excel.

Import the file, and with the column of data in A go to Column B second
row put in =Mid(A2,55,9) and in Column C put in
=mid(a2,39,6)&"."&mid(a2,45,2)
Then copy the formulas down as many rows as you have.

Select the column B and C data and select Copy, and then Select Edit
Paste Special, values. Delete Column A and then save as CSV.
 
K

kas

Hi

The rows (after headers) look something like this, with no separators. They
are all same length and I want to get out, in this instance, 99999 and
00000100 - which in this instance is 1.00

0892501234567807808909697887935000000000000100CASH 99999
090191902000240902090120

Hope this makes it a bit clearer

macropod said:
hi kas,

You'll need to tell us more about the data file. For starters:
.. how is it structured - fixed width lines, comma/tab separators, a data stream all in a single logical line
.. how do you know which characters to extract (eg are there distinguishing marks in the file, such as brackets etc).

--
Cheers
macropod
[MVP - Microsoft Word]


kas said:
Hi.

Not sure where to post this, so apologies if in the wrong slot.

I have a text file that, as well as having header and footer rows, has a
variable number of rows containing a string of characters and it is from
these rows that I need to extract selective data eg characters 55 to 63 and
characters 39 to 46 - the latter divided by 100. This info needs to form a
new text or a .csv file. Can anyone help me with this or do I need to employ
an expert (which I am not!)?

Many thanks
Click to expand...
Click to expand...
 
K

kas

Sorry, don't really know about ASCII. I was talking about the position of
the characters in the row - see my response to other respondent for eg.
Using MID does it, but I was hoping there was another way, as this will be a
daily requirement to be carried out by someone who will not necessarily have
any Excel skills.

Thanks
 
B

Bob I

In reality, there is no need for Excel at all. CSV is a simply a TEXT
file with commas separating the row of text into fields. Most any
scripting utility will allow you to read in each line from the source
file, and then write the desired string subset to a destination file
named as desired. Folks in the CMDprompt group could probably write a
batch file to do this. You may want to ask them, and specifiy the
location of the period or decimal point in the output string.
 
M

macropod

Hi Kas,

If you user copies & pastes the data into column A of the workbook, then runs the following macro with the column A paste-range
selected, I think you'll get the results you're after:

Sub ProcessData()
Dim oCel As Range
With Selection
.TextToColumns Destination:=Range("A1"), DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 9), Array(39, 1), Array(46, 9), Array(55, 1), Array(63, 9))
For Each oCel In .Columns(1).Cells
With oCel
.Value = .Value / 100
.NumberFormat = "0.00"
End With
Next
End With
End Sub

--
Cheers
macropod
[MVP - Microsoft Word]


kas said:
Hi

The rows (after headers) look something like this, with no separators. They
are all same length and I want to get out, in this instance, 99999 and
00000100 - which in this instance is 1.00

0892501234567807808909697887935000000000000100CASH 99999
090191902000240902090120

Hope this makes it a bit clearer

macropod said:
hi kas,

You'll need to tell us more about the data file. For starters:
.. how is it structured - fixed width lines, comma/tab separators, a data stream all in a single logical line
.. how do you know which characters to extract (eg are there distinguishing marks in the file, such as brackets etc).

--
Cheers
macropod
[MVP - Microsoft Word]


kas said:
Hi.

Not sure where to post this, so apologies if in the wrong slot.

I have a text file that, as well as having header and footer rows, has a
variable number of rows containing a string of characters and it is from
these rows that I need to extract selective data eg characters 55 to 63 and
characters 39 to 46 - the latter divided by 100. This info needs to form a
new text or a .csv file. Can anyone help me with this or do I need to employ
an expert (which I am not!)?

Many thanks
Click to expand...
Click to expand...
Click to expand...
 
K

kas

Many thanks to you both for your helpful responses.

Where would I find the CMDprompt group folk?...
 
Top