Echo Traceroute to CSV using SQLite3

tracert-to-csv.cmd

@echo off
Rem Set Environment and cleanup 
c:
cd\tracert
del trace.txt
del trace.sql
del trace.out
 
Rem Generate the Tracefile
tracert -d 65.221.28.106 > trace.txt
copy trace.txt "trace-%date:~4,2%-%date:~7,2%-%date:~10,4%_@_%time:~0,2%h%time:~3,2%m%time:~6,2%s%.txt"
 
Rem Generate the SQL script
echo .bail ON > trace.sql
echo -- Setting up tables for use in translating >> trace.sql
echo create table tracelines (lines TEXT); >> trace.sql
echo -- Import the text output of the traceroute >> trace.sql
echo .import trace.txt tracelines >> trace.sql
echo -- Add columns for the parsed data to go into >> trace.sql
echo alter table tracelines ADD COLUMN date DATE; >> trace.sql
echo alter table tracelines ADD COLUMN time TIME; >> trace.sql
echo alter table tracelines ADD COLUMN hop INT; >> trace.sql
echo alter table tracelines ADD COLUMN RTT1 INT; >> trace.sql
echo alter table tracelines ADD COLUMN RTT2 INT; >> trace.sql
echo alter table tracelines ADD COLUMN RTT3 INT; >> trace.sql
echo alter table tracelines ADD COLUMN Name TEXT; >> trace.sql
echo -- Add a date and time to the tracelines >> trace.sql
echo -- Note this isn't the eaxact time the trace was done, but when the file was parsed >> trace.sql
echo update tracelines set date = date('now','localtime'); >> trace.sql
echo update tracelines set time = time('now','localtime'); >> trace.sql
echo -- Break up the string of tracelines and parse to the columns required >> trace.sql
echo update tracelines set hop = substr(lines,2,2); >> trace.sql;
echo update tracelines set RTT1 = substr(lines,5,5); >> trace.sql;
echo update tracelines set RTT2 = substr(lines,14,5); >> trace.sql;
echo update tracelines set RTT3 = substr(lines,23,5); >> trace.sql;
echo update tracelines set Name = substr(lines,33,99); >> trace.sql;
echo -- Do some cleanup, such as removing less than sign and asterisks >> trace.sql
echo update tracelines set RTT1 = replace(RTT1,'^<',''); >> trace.sql;
echo update tracelines set RTT2 = replace(RTT2,'^<',''); >> trace.sql;
echo update tracelines set RTT3 = replace(RTT3,'^<',''); >> trace.sql;
echo update tracelines set RTT1 = replace(RTT1,'*','0'); >> trace.sql;
echo update tracelines set RTT2 = replace(RTT2,'*','0'); >> trace.sql;
echo update tracelines set RTT3 = replace(RTT3,'*','0'); >> trace.sql;
echo -- Output only the tracelines with usable data to csv file >> trace.sql
echo .mode csv >> trace.sql
echo .output trace.out >> trace.sql
echo select date,time,hop,rtt1,rtt2,rtt3,name from tracelines where rowid ^>= 4 and rowid ^<= (select count(*) from tracelines)-2; >> trace.sql
echo .q >> trace.sql
 
Rem run the script which generates trace.out
sqlite3 < trace.sql
 
Rem Add the output to file
copy trace.csv+trace.out trace.new
copy trace.new trace.csv

Rem Clean up the Environment
c:
cd\tracert
del trace.txt
del trace.sql
del trace.out
del trace.new