how to import text file into mysql table from bash using while loop The Next CEO of Stack OverflowHow to check if a program exists from a Bash script?How do I tell if a regular file does not exist in Bash?How can I redirect and append both stdout and stderr to a file with Bash?Syntax for a single-line Bash infinite while loopLooping through the content of a file in BashHow to import an SQL file using the command line in MySQL?ZF2 Select Statement: Change columns order with joined tablesDelete duplicate records compare 5 columns in tablemysql column name increment by 1How to display ALL the Non-Null and ALL the Non-Empty records without mentioning ALL the column-name in the where clause using a MySql query?
Should I tutor a student who I know has cheated on their homework?
Opposite of a diet
Why does standard notation not preserve intervals (visually)
How to be diplomatic in refusing to write code that breaches the privacy of our users
Term for the "extreme-extension" version of a straw man fallacy?
How to count occurrences of text in a file?
Inappropriate reference requests from Journal reviewers
What is the point of a new vote on May's deal when the indicative votes suggest she will not win?
How to make a software documentation "officially" citable?
Return the Closest Prime Number
Can a caster that cast Polymorph on themselves stop concentrating at any point even if their Int is low?
Is there a good way to store credentials outside of a password manager?
Describing a person. What needs to be mentioned?
How do I solve this limit?
Too much space between section and text in a twocolumn document
What makes a siege story/plot interesting?
How can I get through very long and very dry, but also very useful technical documents when learning a new tool?
How do I go from 300 unfinished/half written blog posts, to published posts?
% symbol leads to superlong (forever?) compilations
Unreliable Magic - Is it worth it?
Can the Reverse Gravity spell affect the Meteor Swarm spell?
How do I construct this japanese bowl?
Why is there a PLL in CPU?
I believe this to be a fraud - hired, then asked to cash check and send cash as Bitcoin
how to import text file into mysql table from bash using while loop
The Next CEO of Stack OverflowHow to check if a program exists from a Bash script?How do I tell if a regular file does not exist in Bash?How can I redirect and append both stdout and stderr to a file with Bash?Syntax for a single-line Bash infinite while loopLooping through the content of a file in BashHow to import an SQL file using the command line in MySQL?ZF2 Select Statement: Change columns order with joined tablesDelete duplicate records compare 5 columns in tablemysql column name increment by 1How to display ALL the Non-Null and ALL the Non-Empty records without mentioning ALL the column-name in the where clause using a MySql query?
I have a text file containing iana port listing. I want to import that file into mysql table from bash. but so far my columns gets mixed up. and mysql statement ends up inserting or reading data from the file and insert into wrong column in table. here's what the text file looks like. here's link to file I'm trying to import https://www.iana.org/assignments/service-names-port-numbers/service-names-port-numbers.xhtml
mftp 349 tcp mftp [Dave_Feinleib] [Dave_Feinleib]
mftp 349 udp mftp [Dave_Feinleib] [Dave_Feinleib]
matip-type-a 350 tcp MATIP Type A [Alain_Robert] [Alain_Robert] [RFC2351]
matip-type-a 350 udp MATIP Type A [Alain_Robert] [Alain_Robert] [RFC2351]
matip-type-b 351 tcp MATIP Type B [Alain_Robert] [Alain_Robert] [RFC2351]
matip-type-b 351 udp MATIP Type B [Alain_Robert] [Alain_Robert] [RFC2351]
bhoetty 351 tcp bhoetty [John_Kelly] [John_Kelly] This entry records an unassigned but widespread use (added
here's my script
cat service-names.txt |
while IFS=$'t' read col1 col2 col3 col4 col5 col6; do
echo "INSERT INTO ports (id, service, number, proto, description, assign, notes) VALUES (DEFAULT, $col1, $col2, $col3, $col4, $col5, $col6);"
done | sudo mysql -uroot -ppassword dbname
how to properly read the file... so I get a column per variable in my while loop... thanks
mysql bash parsing while-loop
asked Mar 21 at 16:30
miatechmiatech
83152142
add a comment |
I have a text file containing iana port listing. I want to import that file into mysql table from bash. but so far my columns gets mixed up. and mysql statement ends up inserting or reading data from the file and insert into wrong column in table. here's what the text file looks like. here's link to file I'm trying to import https://www.iana.org/assignments/service-names-port-numbers/service-names-port-numbers.xhtml
mftp 349 tcp mftp [Dave_Feinleib] [Dave_Feinleib]
mftp 349 udp mftp [Dave_Feinleib] [Dave_Feinleib]
matip-type-a 350 tcp MATIP Type A [Alain_Robert] [Alain_Robert] [RFC2351]
matip-type-a 350 udp MATIP Type A [Alain_Robert] [Alain_Robert] [RFC2351]
matip-type-b 351 tcp MATIP Type B [Alain_Robert] [Alain_Robert] [RFC2351]
matip-type-b 351 udp MATIP Type B [Alain_Robert] [Alain_Robert] [RFC2351]
bhoetty 351 tcp bhoetty [John_Kelly] [John_Kelly] This entry records an unassigned but widespread use (added
here's my script
cat service-names.txt |
while IFS=$'t' read col1 col2 col3 col4 col5 col6; do
echo "INSERT INTO ports (id, service, number, proto, description, assign, notes) VALUES (DEFAULT, $col1, $col2, $col3, $col4, $col5, $col6);"
done | sudo mysql -uroot -ppassword dbname
how to properly read the file... so I get a column per variable in my while loop... thanks
mysql bash parsing while-loop
asked Mar 21 at 16:30
miatechmiatech
83152142
Don't change IFS
– Kevin
Mar 21 at 19:55
There are no TABs in the file you posted - so why do you try to separate the values toreadwith TAB?
– Armali
Mar 22 at 10:37
@Armali thought they were tabs.. how do you know the separators?... is there any editor or way to see this?... using space as a separator is not the best option
– miatech
Mar 22 at 14:25
In the data you posted above, there are no TABs, as we can see if we e. g. click on edit and move the cursor through the source of the post (if there were TABs, it'd probably jump at certain column positions) or copy the source somewhere where TABs are displayed (as with input tocat -A). Of course I cannot know whether TABs are in your originalservice-names.txt, but if there are, you'd have to find a way to post that here (perhaps as a hexdump of a few lines).
– Armali
Mar 22 at 15:03
for those interested here's the file I'm trying to import iana.org/assignments/service-names-port-numbers/…
– miatech
Mar 23 at 17:03
add a comment |
I have a text file containing iana port listing. I want to import that file into mysql table from bash. but so far my columns gets mixed up. and mysql statement ends up inserting or reading data from the file and insert into wrong column in table. here's what the text file looks like. here's link to file I'm trying to import https://www.iana.org/assignments/service-names-port-numbers/service-names-port-numbers.xhtml
mftp 349 tcp mftp [Dave_Feinleib] [Dave_Feinleib]
mftp 349 udp mftp [Dave_Feinleib] [Dave_Feinleib]
matip-type-a 350 tcp MATIP Type A [Alain_Robert] [Alain_Robert] [RFC2351]
matip-type-a 350 udp MATIP Type A [Alain_Robert] [Alain_Robert] [RFC2351]
matip-type-b 351 tcp MATIP Type B [Alain_Robert] [Alain_Robert] [RFC2351]
matip-type-b 351 udp MATIP Type B [Alain_Robert] [Alain_Robert] [RFC2351]
bhoetty 351 tcp bhoetty [John_Kelly] [John_Kelly] This entry records an unassigned but widespread use (added
here's my script
cat service-names.txt |
while IFS=$'t' read col1 col2 col3 col4 col5 col6; do
echo "INSERT INTO ports (id, service, number, proto, description, assign, notes) VALUES (DEFAULT, $col1, $col2, $col3, $col4, $col5, $col6);"
done | sudo mysql -uroot -ppassword dbname
how to properly read the file... so I get a column per variable in my while loop... thanks
mysql bash parsing while-loop
asked Mar 21 at 16:30
miatechmiatech
83152142
I have a text file containing iana port listing. I want to import that file into mysql table from bash. but so far my columns gets mixed up. and mysql statement ends up inserting or reading data from the file and insert into wrong column in table. here's what the text file looks like. here's link to file I'm trying to import https://www.iana.org/assignments/service-names-port-numbers/service-names-port-numbers.xhtml
mftp 349 tcp mftp [Dave_Feinleib] [Dave_Feinleib]
mftp 349 udp mftp [Dave_Feinleib] [Dave_Feinleib]
matip-type-a 350 tcp MATIP Type A [Alain_Robert] [Alain_Robert] [RFC2351]
matip-type-a 350 udp MATIP Type A [Alain_Robert] [Alain_Robert] [RFC2351]
matip-type-b 351 tcp MATIP Type B [Alain_Robert] [Alain_Robert] [RFC2351]
matip-type-b 351 udp MATIP Type B [Alain_Robert] [Alain_Robert] [RFC2351]
bhoetty 351 tcp bhoetty [John_Kelly] [John_Kelly] This entry records an unassigned but widespread use (added
here's my script
cat service-names.txt |
while IFS=$'t' read col1 col2 col3 col4 col5 col6; do
echo "INSERT INTO ports (id, service, number, proto, description, assign, notes) VALUES (DEFAULT, $col1, $col2, $col3, $col4, $col5, $col6);"
done | sudo mysql -uroot -ppassword dbname
how to properly read the file... so I get a column per variable in my while loop... thanks
mysql bash parsing while-loop
mysql bash parsing while-loop
asked Mar 21 at 16:30
miatechmiatech
83152142
asked Mar 21 at 16:30
miatechmiatech
83152142
edited Mar 23 at 17:04
miatech
asked Mar 21 at 16:30
miatechmiatech
83152142
asked Mar 21 at 16:30
miatechmiatech
83152142
asked Mar 21 at 16:30
miatechmiatech
83152142
83152142
Don't change IFS
– Kevin
Mar 21 at 19:55
There are no TABs in the file you posted - so why do you try to separate the values toreadwith TAB?
– Armali
Mar 22 at 10:37
@Armali thought they were tabs.. how do you know the separators?... is there any editor or way to see this?... using space as a separator is not the best option
– miatech
Mar 22 at 14:25
In the data you posted above, there are no TABs, as we can see if we e. g. click on edit and move the cursor through the source of the post (if there were TABs, it'd probably jump at certain column positions) or copy the source somewhere where TABs are displayed (as with input tocat -A). Of course I cannot know whether TABs are in your originalservice-names.txt, but if there are, you'd have to find a way to post that here (perhaps as a hexdump of a few lines).
– Armali
Mar 22 at 15:03
for those interested here's the file I'm trying to import iana.org/assignments/service-names-port-numbers/…
– miatech
Mar 23 at 17:03
add a comment |
Don't change IFS
– Kevin
Mar 21 at 19:55
There are no TABs in the file you posted - so why do you try to separate the values toreadwith TAB?
– Armali
Mar 22 at 10:37
@Armali thought they were tabs.. how do you know the separators?... is there any editor or way to see this?... using space as a separator is not the best option
– miatech
Mar 22 at 14:25
In the data you posted above, there are no TABs, as we can see if we e. g. click on edit and move the cursor through the source of the post (if there were TABs, it'd probably jump at certain column positions) or copy the source somewhere where TABs are displayed (as with input tocat -A). Of course I cannot know whether TABs are in your originalservice-names.txt, but if there are, you'd have to find a way to post that here (perhaps as a hexdump of a few lines).
– Armali
Mar 22 at 15:03
for those interested here's the file I'm trying to import iana.org/assignments/service-names-port-numbers/…
– miatech
Mar 23 at 17:03
Don't change IFS
– Kevin
Mar 21 at 19:55
Don't change IFS
– Kevin
Mar 21 at 19:55
There are no TABs in the file you posted - so why do you try to separate the values to
read with TAB?– Armali
Mar 22 at 10:37
There are no TABs in the file you posted - so why do you try to separate the values to
read with TAB?– Armali
Mar 22 at 10:37
@Armali thought they were tabs.. how do you know the separators?... is there any editor or way to see this?... using space as a separator is not the best option
– miatech
Mar 22 at 14:25
@Armali thought they were tabs.. how do you know the separators?... is there any editor or way to see this?... using space as a separator is not the best option
– miatech
Mar 22 at 14:25
In the data you posted above, there are no TABs, as we can see if we e. g. click on edit and move the cursor through the source of the post (if there were TABs, it'd probably jump at certain column positions) or copy the source somewhere where TABs are displayed (as with input to
cat -A). Of course I cannot know whether TABs are in your original service-names.txt, but if there are, you'd have to find a way to post that here (perhaps as a hexdump of a few lines).– Armali
Mar 22 at 15:03
In the data you posted above, there are no TABs, as we can see if we e. g. click on edit and move the cursor through the source of the post (if there were TABs, it'd probably jump at certain column positions) or copy the source somewhere where TABs are displayed (as with input to
cat -A). Of course I cannot know whether TABs are in your original service-names.txt, but if there are, you'd have to find a way to post that here (perhaps as a hexdump of a few lines).– Armali
Mar 22 at 15:03
for those interested here's the file I'm trying to import iana.org/assignments/service-names-port-numbers/…
– miatech
Mar 23 at 17:03
for those interested here's the file I'm trying to import iana.org/assignments/service-names-port-numbers/…
– miatech
Mar 23 at 17:03
add a comment |
0
active
oldest
votes
Your Answer
StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");
StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);
else
createEditor();
);
function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);
);
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55285128%2fhow-to-import-text-file-into-mysql-table-from-bash-using-while-loop%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55285128%2fhow-to-import-text-file-into-mysql-table-from-bash-using-while-loop%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Don't change IFS
– Kevin
Mar 21 at 19:55
There are no TABs in the file you posted - so why do you try to separate the values to
readwith TAB?– Armali
Mar 22 at 10:37
@Armali thought they were tabs.. how do you know the separators?... is there any editor or way to see this?... using space as a separator is not the best option
– miatech
Mar 22 at 14:25
In the data you posted above, there are no TABs, as we can see if we e. g. click on edit and move the cursor through the source of the post (if there were TABs, it'd probably jump at certain column positions) or copy the source somewhere where TABs are displayed (as with input to
cat -A). Of course I cannot know whether TABs are in your originalservice-names.txt, but if there are, you'd have to find a way to post that here (perhaps as a hexdump of a few lines).– Armali
Mar 22 at 15:03
for those interested here's the file I'm trying to import iana.org/assignments/service-names-port-numbers/…
– miatech
Mar 23 at 17:03