Text Manipulation

[initroot]

Hi everyone,

Can someone possibly assist.

I have two options, output in text file or in excel.

So basically I have several sets of data as such:
e9 3 0 0 0 20 10 5 2 0 0 0 0 0 10 0
0 0 10 0 2 0 0 0 0 0 10 0 0 0 10 0
0 0 0 0 e9 3 3 0 e0 15 e0 15 a8 16 bc 1b
0 19 18 15 9c 18 c8 19 78 1e e8 3 a3 0c 9c 18
14 1e 7c 15 94 11 70 17 0 0 88 13 0a 0 20 0
f5 0 eb 3 0 0 0 0 0 0 0 1 0 b8 6c 1
2 0 10 1 0 0 0 0 0 0 42 65 6e 0 0 0
0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
0 0 0 48 65 6e 64 65 72 73 6f 6e 0 0 0 0
0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
0 0 0 0 0 75 0 0 ea 3 0 0 0 60 e8 4
2 0 0 0 0 0 10 0 0 0 10 0 2 0 0 0
0 0 10 0 0 0 10 0 0 0 0 0 e9 3 3 0
d4 17 44 16 50 14 0c 17 78 1e f4 1a f4 1a f4 1a
ec 13 d0 7 d0 7 58 1b fc 21 78 1e c0 12 14 1e
0 0 88 13 11 1 11 1 11 1 eb 3 49 0 0 0
0 0 0 1 0 b7 6e 1 2 0 0 4 0 0 0 0
1 0 41 6e 64 72 65 77 0 0 0 0 0 0 0 0
0 0 0 0 0 0 0 0 0 0 0 48 6f 72 65 0
0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
0 0 0 0 0 0 0 0 0 0 0 0 0 75 0 0
eb 3 0 0 0 78 39 5 4 0 0 0 2 0 0 0
0 0 10 0 4 0 0 0 2 0 0 0 0 0 10 0
0 0 0 0 e9 3 2 0 90 1a e0 15 0c 17 44 16
20 1c 84 1c 34 21 58 1b 30 11 d0 7 d0 7 0 19
14 1e 38 18 68 10 58 1b 0 0 88 13 da 0 0 0
0 0 eb 3 12 0 0 0 0 0 0 1 0 b7 76 1
3 0 0 5 0 0 0 0 1 0 4a 6f 68 6e 0 0
0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
0 0 0 41 66 6f 61 0 0 0 0 0 0 0 0 0
0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
0 0 0 0 0 75 0 0 ec 3 0 0 0 b0 7b 5
4 0 0 0 0 0 10 0 0 0 10 0 4 0 0 0
0 0 10 0 0 0 10 0 0 0 0 0 e9 3 3 0
dc 1e 4a 15 0c 17 e6 14 34 21 1a 1d c2 1a c8 19
12 16 34 8 34 8 26 1b 22 24 26 1b 38 18 64 19
0 0 88 13 d0 0 0 0 0 0 eb 3 8 0 25 0
0 0 0 1 0 b9 77 1 3 1 8 1 0 0 0 0
1 0 4f 77 65 6e 0 0 0 0 0 0 0 0 0 0

Now every 184 characters or bytes represent an entity. Marked the first one.
I need to bulk modify values here for instance on every 90th character of each entity needs to change to a different value etc.

How would one do this? I can probably use python but no expert with it.

 

[initroot]

Any help guys?

 

[Hamish McPanji]

VBA in excel
Are those carriage returns, or is it one long string of characters?

 

[Neoprod]

I would stick into a temp table in a database probably...that data is a terrible shape btw. Why isn't each 184 bytes it's own record? The line lengths are all weird and stuff.

Apologies - looks like it's 16 bytes per line.

 

[[)roi(]]

Is this is how the text file looks?

If so, then it's apparent the numbers are space delimited with newline break every 16 values; + values are hexadecimal.
Start as follows:
1. Strip the newline characters
2. Split the values to an array of String; space is the delimiter
3. Convert from hexadecimal String to Integer; end result is an array of Integer.
4. Optional, if you want to specifically partition the 184 values, you could split the 1D array into a 2D array (rows x 184)

All of the above can be achieved quite easily in python, with either list iteration or the higher order function map

 

[initroot]

VBA in excel
Are those carriage returns, or is it one long string of characters?

carriage return

 

[initroot]

I would stick into a temp table in a database probably...that data is a terrible shape btw. Why isn't each 184 bytes it's own record? The line lengths are all weird and stuff.

Apologies - looks like it's 16 bytes per line.

It's not each its own record cause this is a database from HEX.

 

[initroot]

Is this is how the text file looks?

If so, then it's apparent the numbers are space delimited with newline break every 16 values; + values are hexadecimal.
Start as follows:
1. Strip the newline characters
2. Split the values to an array of String; space is the delimiter
3. Convert from hexadecimal String to Integer; end result is an array of Integer.
4. Optional, if you want to specifically partition the 184 values, you could split the 1D array into a 2D array (rows x 184)

All of the above can be achieved quite easily in python, with either list iteration or the higher order function map

Thanks, I have the following:
1. Read textfile
2. Strip newlines
3. Insert into array
4. Insert into 2D array each 184 characters
5. Loop through 2d Array modify every 90th value
6. Output 2d array

But how to put this into code is my problem.

 

[initroot]

VBA in excel
Are those carriage returns, or is it one long string of characters?

How woud I do this in VBA? I've actually pasted the values into excel already

 

[Hamster]

If all the text is in a single field then read it into a character array, start with position 90, modify and increment with 184?

for i = 90; i < len(data); i += 184 {
    data[i] = 'x'
}

Maybe I misunderstood. I'm still half asleep

 

[Hamster]

Thanks, I have the following:
1. Read textfile
2. Strip newlines
3. Insert into array
4. Insert into 2D array each 184 characters
5. Loop through 2d Array modify every 90th value
6. Output 2d array

But how to put this into code is my problem.

data = file.Open("data.txt")
data = data.replace("\n", "")

for i = 90; i < len(data); i += 184 {
    data[i] = 'x'
}

Cellphone coding! ...meh

 

[[)roi(]]

Thanks, I have the following:
1. Read textfile
2. Strip newlines
3. Insert into array
4. Insert into 2D array each 184 characters
5. Loop through 2d Array modify every 90th value
6. Output 2d array

But how to put this into code is my problem.

If this is a regular requirement, then I would suggest you consider the process. Extracting and then copying into excel, running VBA is quite manually restrictive.

Start with the extraction part and decide what is the easiest way to automate this. As to making sense of the data; I prefer the text output, unless of course the Excel version has already converted some of the hex values and/or grouped the values as you required.

For a code example:
1. Pick your language; you mentioned Python, but also VBA. With these 2 options Python is the certainly the more flexible one.
2. Can you make a demo file available?
3. What's also unclear is what you intend to do with the result; knowing this could completely change the advice given.


Anyway in the absence of clarity, here is a code example in python:

def modifyvalue(value):
      return value / 2

file = open("test.txt", "r")
data = map(lambda h: int(h, 16), file.read().replace("\n", " ").split())
file.close()
for index in xrange(89, len(data)-1, 90):
     data[index] = modifyvalue(data[index])

print "result = ", data

newdata = map(lambda i: hex(i).replace("0x",""), data)
output = ""
for index in xrange(0, len(newdata)-1):
     output += newdata[index] + "\n" if (index+1)%16==0 else newdata[index] + " "

print "result new = ", output