How to extract strings before and after a delimiter and store in mysql?

Carlos

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!

Bohemian

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.

edited at
0

Comments

0 comments
Login to comment

Related

TOP Ranking

HotTag

Archive