Want to insert into a mysql table with two columns a txt file with tons of lines containing the following structure:
random1:random2:random3:random4
random1:random2:random3:random4:random5
random1:random2
random1:random2:random3:random4:randomN
...
Where the delimiter is ":"
And "random" represents a group of random characters.
In the first column should be the string random1
In the second column should be stored the rest of the string random2:random3:random4:randomN
To extract the substring for the first column, already tried:
echo "random1:random2:random3:random4:randomN" | awk -F":" '{print (NF>1)? $1 : ""}'
echo "random1:random2:random3:random4:randomN" | sed 's/:.*//'
echo "random1:random2:random3:random4:randomN" | cut -d ":" -f1
How can I extract the substring from the first delimiter ":" until the end of the line and store it in the mysql table?
Thank you in advance!
This will change the first ":" into a "," to make a CSV file:
sed -E "s/([^:]*):/\1,/" myfile.txt > myfile.csv
Then you could import it into your table.
Or, you could create an SQL script:
sed -E "s/([^:]*):(.*)/insert into mytable (col1, col2) values ('\1','\2');/" myfile.txt > myfile.sql
then run it:
mysql -u root mydatabase -s < myfile.sql
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments