Replace all hexadecimal numbers in a file
I would like to replace all hexadecimal ones with their decimal equivalent in the .sql file.
I have hundreds of thousands of lines like this:
INSERT INTO TEST (TEST_ID, VEHICLE_ID, TEST_TYPE_ID, TYPE_ID, NUM_TEST, TEST_DATE, HOUR_START, HOUR_END, ....) VALUES (844, 504, 3, 1, 3, CAST(0x0000991400000000 AS DateTime), CAST(0x00009914008FC76C AS DateTime), CAST(0x0000991400906924 AS DateTime), ......);
And I would like to use this sql file to create a SQLite database. But SQLite doesn't recognize hex. So the goal is to replace the entire hex file.
I found this solution to replace a specific hex:
decimal = int("0x0000991400000000", 0)
And I found this solution to replace all occurrences of a word with something else:
for line in fileinput.input():
line = re.sub('wordToReplace','newWord', line.rstrip())
I tried using the first in the second, but 'newWord' outputs a string and I give an int. What could be the way to do what I want?
Thank you for your time!
source to share
Use a replace function with regex:
import re
data = "INSERT INTO TEST (TEST_ID, VEHICLE_ID, TEST_TYPE_ID, TYPE_ID, NUM_TEST, TEST_DATE, HOUR_START, HOUR_END, ....) VALUES (844, 504, 3, 1, 3, CAST(0x0000991400000000 AS DateTime), CAST(0x00009914008FC76C AS DateTime), CAST(0x0000991400906924 AS DateTime), ......);"
data_dec = re.sub("(0x[\dA-F]+)",lambda m : str(int(m.group(1),16)),data)
print(data_dec)
result:
INSERT INTO TEST (TEST_ID, VEHICLE_ID, TEST_TYPE_ID, TYPE_ID, NUM_TEST, TEST_DATE, HOUR_START, HOUR_END, ....) VALUES (844, 504, 3, 1, 3, CAST(168311178395648 AS DateTime), CAST(168311187818348 AS DateTime), CAST(168311187859748 AS DateTime), ......);
When a hexadecimal number ( "(0x[\dA-F]+)"
) is found, the call lambda
is invoked with the match argument. Take the first and only group, parse it as an integer -16, then convert it back to a string.
source to share