Why Extented Regular expression works on command line input but not when reading from a file
1
Using Windows 10 developer mode. My regular expression works perfectly when I execute in command prompt. Substitutes mm/dd/yyyy hh:mm to yyyy-mm-dd within a string delimited by comma. It does not work when I am reading input from a file.
Execute a single line works fine.
cka_ubuntu@AFSWWM102QEIQ1:/mnt/c/tst$ echo '12639519F0011,P00001,89813.83,10/10/2018,10/10/2018 0:00,10/18/2018 0:00,10/18/2018 0:00,,12,10/10/2018 12:26' | sed -E 's,([0-9]{1}|[0-9]{2})/([0-9]{1}|[0-9]{2})/([0-9]{4}),3-2-1,g;s,s([0-9]{1}|[0-9]{2}):([0-9]{1}|[0-9]{2}),,g'
12639519F0011,P00001,89813.83,2018-10-10,2018-10-10,2018-18-10,2018-18-10,,12,2018-10-10
Issue. Does now work when I have the multiple lines in a file.
Command using (reading from input.csv and exporting test01.csv
cka_ubuntu@AFSWWM102QEIQ1:/mnt/c/tst$ sed -E 's,([0-9]{1}|[0-9]{2})/([0-9]{1}|[0-9]{2})/([0-9]{4}),3-2-1,g;s,s([0-9]{1}|[0-9]{2}):([0-9]{1}|[0-9]{2}),,g' input.csv > test01.csv
Input file:
award_id_piid,modification_number,potential_total_value_of_award,action_date,period_of_performance_start_date,period_of_performance_current_end_date,period_of_performance_potential_end_date,ordering_period_end_date,awarding_agency_code,last_modified_date
68HE0418F0516,P00001,48876.44,10/10/2018,10/10/2018 0:00,12/1/2019 0:00,12/1/2019 0:00,,68,10/10/2018 8:13
12639519F0011,P00001,89813.83,10/10/2018,10/10/2018 0:00,10/18/2018 0:00,10/18/2018 0:00,,12,10/10/2018 12:26
GS35F497CA,PM0011,475000,10/10/2018,10/10/2018 6:03,,,9/16/2020,47,10/10/2018 6:39
15B41918PTP440004,P00004,617912.96,10/10/2018,10/10/2018 0:00,10/10/2018 0:00,10/10/2018 0:00,,15,10/10/2018 12:36
15B31019PUA130001,0,23925,10/10/2018,10/1/2018 0:00,10/10/2018 0:00,10/10/2018 0:00,,15,10/10/2018 14:03
Anything that an I doing wrong?
command-line text-processing csv
edited Oct 30 '18 at 1:46
muru
1
asked Oct 30 '18 at 1:44
CKACKA
61
add a comment |
1
Using Windows 10 developer mode. My regular expression works perfectly when I execute in command prompt. Substitutes mm/dd/yyyy hh:mm to yyyy-mm-dd within a string delimited by comma. It does not work when I am reading input from a file.
Execute a single line works fine.
cka_ubuntu@AFSWWM102QEIQ1:/mnt/c/tst$ echo '12639519F0011,P00001,89813.83,10/10/2018,10/10/2018 0:00,10/18/2018 0:00,10/18/2018 0:00,,12,10/10/2018 12:26' | sed -E 's,([0-9]{1}|[0-9]{2})/([0-9]{1}|[0-9]{2})/([0-9]{4}),3-2-1,g;s,s([0-9]{1}|[0-9]{2}):([0-9]{1}|[0-9]{2}),,g'
12639519F0011,P00001,89813.83,2018-10-10,2018-10-10,2018-18-10,2018-18-10,,12,2018-10-10
Issue. Does now work when I have the multiple lines in a file.
Command using (reading from input.csv and exporting test01.csv
cka_ubuntu@AFSWWM102QEIQ1:/mnt/c/tst$ sed -E 's,([0-9]{1}|[0-9]{2})/([0-9]{1}|[0-9]{2})/([0-9]{4}),3-2-1,g;s,s([0-9]{1}|[0-9]{2}):([0-9]{1}|[0-9]{2}),,g' input.csv > test01.csv
Input file:
award_id_piid,modification_number,potential_total_value_of_award,action_date,period_of_performance_start_date,period_of_performance_current_end_date,period_of_performance_potential_end_date,ordering_period_end_date,awarding_agency_code,last_modified_date
68HE0418F0516,P00001,48876.44,10/10/2018,10/10/2018 0:00,12/1/2019 0:00,12/1/2019 0:00,,68,10/10/2018 8:13
12639519F0011,P00001,89813.83,10/10/2018,10/10/2018 0:00,10/18/2018 0:00,10/18/2018 0:00,,12,10/10/2018 12:26
GS35F497CA,PM0011,475000,10/10/2018,10/10/2018 6:03,,,9/16/2020,47,10/10/2018 6:39
15B41918PTP440004,P00004,617912.96,10/10/2018,10/10/2018 0:00,10/10/2018 0:00,10/10/2018 0:00,,15,10/10/2018 12:36
15B31019PUA130001,0,23925,10/10/2018,10/1/2018 0:00,10/10/2018 0:00,10/10/2018 0:00,,15,10/10/2018 14:03
Anything that an I doing wrong?
command-line text-processing csv
edited Oct 30 '18 at 1:46
muru
1
asked Oct 30 '18 at 1:44
CKACKA
61
What output do you get with the file?
– muru
Oct 30 '18 at 1:50
Thank you for the response. The regular expression works. When I print the file on bash console, that substitutions have taken place. When I drag and drop csv file into Excel the formatting changes. My best guess is how excel is setup and treats date formatting by default. Even further, excel shows changes only on a couple of dates. I am wondering in order to avoid those pitfalls whether there an opensource CVS editor. Thank you for your response and your time
– CKA
Oct 30 '18 at 11:38
If you're going to import this into Excel, it might be simplest to keep the existing format and change the date display settings in Excel. That way, the data remains untouched but you get to see what you want to see.
– muru
Oct 30 '18 at 11:41
add a comment |
1
1
1
Using Windows 10 developer mode. My regular expression works perfectly when I execute in command prompt. Substitutes mm/dd/yyyy hh:mm to yyyy-mm-dd within a string delimited by comma. It does not work when I am reading input from a file.
Execute a single line works fine.
cka_ubuntu@AFSWWM102QEIQ1:/mnt/c/tst$ echo '12639519F0011,P00001,89813.83,10/10/2018,10/10/2018 0:00,10/18/2018 0:00,10/18/2018 0:00,,12,10/10/2018 12:26' | sed -E 's,([0-9]{1}|[0-9]{2})/([0-9]{1}|[0-9]{2})/([0-9]{4}),3-2-1,g;s,s([0-9]{1}|[0-9]{2}):([0-9]{1}|[0-9]{2}),,g'
12639519F0011,P00001,89813.83,2018-10-10,2018-10-10,2018-18-10,2018-18-10,,12,2018-10-10
Issue. Does now work when I have the multiple lines in a file.
Command using (reading from input.csv and exporting test01.csv
cka_ubuntu@AFSWWM102QEIQ1:/mnt/c/tst$ sed -E 's,([0-9]{1}|[0-9]{2})/([0-9]{1}|[0-9]{2})/([0-9]{4}),3-2-1,g;s,s([0-9]{1}|[0-9]{2}):([0-9]{1}|[0-9]{2}),,g' input.csv > test01.csv
Input file:
award_id_piid,modification_number,potential_total_value_of_award,action_date,period_of_performance_start_date,period_of_performance_current_end_date,period_of_performance_potential_end_date,ordering_period_end_date,awarding_agency_code,last_modified_date
68HE0418F0516,P00001,48876.44,10/10/2018,10/10/2018 0:00,12/1/2019 0:00,12/1/2019 0:00,,68,10/10/2018 8:13
12639519F0011,P00001,89813.83,10/10/2018,10/10/2018 0:00,10/18/2018 0:00,10/18/2018 0:00,,12,10/10/2018 12:26
GS35F497CA,PM0011,475000,10/10/2018,10/10/2018 6:03,,,9/16/2020,47,10/10/2018 6:39
15B41918PTP440004,P00004,617912.96,10/10/2018,10/10/2018 0:00,10/10/2018 0:00,10/10/2018 0:00,,15,10/10/2018 12:36
15B31019PUA130001,0,23925,10/10/2018,10/1/2018 0:00,10/10/2018 0:00,10/10/2018 0:00,,15,10/10/2018 14:03
Anything that an I doing wrong?
command-line text-processing csv
edited Oct 30 '18 at 1:46
muru
1
asked Oct 30 '18 at 1:44
CKACKA
61
Using Windows 10 developer mode. My regular expression works perfectly when I execute in command prompt. Substitutes mm/dd/yyyy hh:mm to yyyy-mm-dd within a string delimited by comma. It does not work when I am reading input from a file.
Execute a single line works fine.
cka_ubuntu@AFSWWM102QEIQ1:/mnt/c/tst$ echo '12639519F0011,P00001,89813.83,10/10/2018,10/10/2018 0:00,10/18/2018 0:00,10/18/2018 0:00,,12,10/10/2018 12:26' | sed -E 's,([0-9]{1}|[0-9]{2})/([0-9]{1}|[0-9]{2})/([0-9]{4}),3-2-1,g;s,s([0-9]{1}|[0-9]{2}):([0-9]{1}|[0-9]{2}),,g'
12639519F0011,P00001,89813.83,2018-10-10,2018-10-10,2018-18-10,2018-18-10,,12,2018-10-10
Issue. Does now work when I have the multiple lines in a file.
Command using (reading from input.csv and exporting test01.csv
cka_ubuntu@AFSWWM102QEIQ1:/mnt/c/tst$ sed -E 's,([0-9]{1}|[0-9]{2})/([0-9]{1}|[0-9]{2})/([0-9]{4}),3-2-1,g;s,s([0-9]{1}|[0-9]{2}):([0-9]{1}|[0-9]{2}),,g' input.csv > test01.csv
Input file:
award_id_piid,modification_number,potential_total_value_of_award,action_date,period_of_performance_start_date,period_of_performance_current_end_date,period_of_performance_potential_end_date,ordering_period_end_date,awarding_agency_code,last_modified_date
68HE0418F0516,P00001,48876.44,10/10/2018,10/10/2018 0:00,12/1/2019 0:00,12/1/2019 0:00,,68,10/10/2018 8:13
12639519F0011,P00001,89813.83,10/10/2018,10/10/2018 0:00,10/18/2018 0:00,10/18/2018 0:00,,12,10/10/2018 12:26
GS35F497CA,PM0011,475000,10/10/2018,10/10/2018 6:03,,,9/16/2020,47,10/10/2018 6:39
15B41918PTP440004,P00004,617912.96,10/10/2018,10/10/2018 0:00,10/10/2018 0:00,10/10/2018 0:00,,15,10/10/2018 12:36
15B31019PUA130001,0,23925,10/10/2018,10/1/2018 0:00,10/10/2018 0:00,10/10/2018 0:00,,15,10/10/2018 14:03
Anything that an I doing wrong?
command-line text-processing csv
command-line text-processing csv
edited Oct 30 '18 at 1:46
muru
1
asked Oct 30 '18 at 1:44
CKACKA
61
edited Oct 30 '18 at 1:46
muru
1
asked Oct 30 '18 at 1:44
CKACKA
61
edited Oct 30 '18 at 1:46
muru
1
edited Oct 30 '18 at 1:46
muru
1
edited Oct 30 '18 at 1:46
muru
1
1
asked Oct 30 '18 at 1:44
CKACKA
61
asked Oct 30 '18 at 1:44
CKACKA
61
asked Oct 30 '18 at 1:44
CKACKA
61
61
What output do you get with the file?
– muru
Oct 30 '18 at 1:50
Thank you for the response. The regular expression works. When I print the file on bash console, that substitutions have taken place. When I drag and drop csv file into Excel the formatting changes. My best guess is how excel is setup and treats date formatting by default. Even further, excel shows changes only on a couple of dates. I am wondering in order to avoid those pitfalls whether there an opensource CVS editor. Thank you for your response and your time
– CKA
Oct 30 '18 at 11:38
If you're going to import this into Excel, it might be simplest to keep the existing format and change the date display settings in Excel. That way, the data remains untouched but you get to see what you want to see.
– muru
Oct 30 '18 at 11:41
add a comment |
What output do you get with the file?
– muru
Oct 30 '18 at 1:50
Thank you for the response. The regular expression works. When I print the file on bash console, that substitutions have taken place. When I drag and drop csv file into Excel the formatting changes. My best guess is how excel is setup and treats date formatting by default. Even further, excel shows changes only on a couple of dates. I am wondering in order to avoid those pitfalls whether there an opensource CVS editor. Thank you for your response and your time
– CKA
Oct 30 '18 at 11:38
If you're going to import this into Excel, it might be simplest to keep the existing format and change the date display settings in Excel. That way, the data remains untouched but you get to see what you want to see.
– muru
Oct 30 '18 at 11:41
What output do you get with the file?
– muru
Oct 30 '18 at 1:50
What output do you get with the file?
– muru
Oct 30 '18 at 1:50
Thank you for the response. The regular expression works. When I print the file on bash console, that substitutions have taken place. When I drag and drop csv file into Excel the formatting changes. My best guess is how excel is setup and treats date formatting by default. Even further, excel shows changes only on a couple of dates. I am wondering in order to avoid those pitfalls whether there an opensource CVS editor. Thank you for your response and your time
– CKA
Oct 30 '18 at 11:38
Thank you for the response. The regular expression works. When I print the file on bash console, that substitutions have taken place. When I drag and drop csv file into Excel the formatting changes. My best guess is how excel is setup and treats date formatting by default. Even further, excel shows changes only on a couple of dates. I am wondering in order to avoid those pitfalls whether there an opensource CVS editor. Thank you for your response and your time
– CKA
Oct 30 '18 at 11:38
If you're going to import this into Excel, it might be simplest to keep the existing format and change the date display settings in Excel. That way, the data remains untouched but you get to see what you want to see.
– muru
Oct 30 '18 at 11:41
If you're going to import this into Excel, it might be simplest to keep the existing format and change the date display settings in Excel. That way, the data remains untouched but you get to see what you want to see.
– muru
Oct 30 '18 at 11:41
add a comment |
2 Answers
2
active
oldest
votes
0
The issue, I was validating the csv file through Excel, by drag-drop csv file. Excel must format the date, based on its default configuration. Substitution using sed works fine. Data shows correctly when I view the file on the command prompt.
answered Oct 30 '18 at 11:41
CKACKA
61
add a comment |
0
with miller (http://johnkerl.org/miller/doc/), using regex with
mlr --csv put '$last_modified_date=gsub($last_modified_date,"^([0-9]{1,2})(/)([0-9]{1,2})(/)([0-9]{4})(.*)$","5-3-1");
$period_of_performance_start_date=gsub($period_of_performance_start_date,"^([0-9]{1,2})(/)([0-9]{1,2})(/)([0-9]{4})(.*)$","5-3-1");
$period_of_performance_potential_end_date=gsub($period_of_performance_potential_end_date,"^([0-9]{1,2})(/)([0-9]{1,2})(/)([0-9]{4})(.*)$","5-3-1");
$period_of_performance_current_end_date=gsub($period_of_performance_current_end_date,"^([0-9]{1,2})(/)([0-9]{1,2})(/)([0-9]{4})(.*)$","5-3-1")' input.csv
You have
award_id_piid,modification_number,potential_total_value_of_award,action_date,period_of_performance_start_date,period_of_performance_current_end_date,period_of_performance_potential_end_date,ordering_period_end_date,awarding_agency_code,last_modified_date
68HE0418F0516,P00001,48876.44,10/10/2018,2018-10-10,2019-1-12,2019-1-12,,68,2018-10-10
12639519F0011,P00001,89813.83,10/10/2018,2018-10-10,2018-18-10,2018-18-10,,12,2018-10-10
GS35F497CA,PM0011,475000,10/10/2018,2018-10-10,,,9/16/2020,47,2018-10-10
15B41918PTP440004,P00004,617912.96,10/10/2018,2018-10-10,2018-10-10,2018-10-10,,15,2018-10-10
15B31019PUA130001,0,23925,10/10/2018,2018-1-10,2018-10-10,2018-10-10,,15,2018-10-10
answered Dec 31 '18 at 13:47
aborrusoaborruso
1714
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "89"
};
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
});
}
});
draft saved
draft discarded
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%2faskubuntu.com%2fquestions%2f1088437%2fwhy-extented-regular-expression-works-on-command-line-input-but-not-when-reading%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
0
The issue, I was validating the csv file through Excel, by drag-drop csv file. Excel must format the date, based on its default configuration. Substitution using sed works fine. Data shows correctly when I view the file on the command prompt.
answered Oct 30 '18 at 11:41
CKACKA
61
add a comment |
0
The issue, I was validating the csv file through Excel, by drag-drop csv file. Excel must format the date, based on its default configuration. Substitution using sed works fine. Data shows correctly when I view the file on the command prompt.
answered Oct 30 '18 at 11:41
CKACKA
61
add a comment |
0
0
0
The issue, I was validating the csv file through Excel, by drag-drop csv file. Excel must format the date, based on its default configuration. Substitution using sed works fine. Data shows correctly when I view the file on the command prompt.
answered Oct 30 '18 at 11:41
CKACKA
61
The issue, I was validating the csv file through Excel, by drag-drop csv file. Excel must format the date, based on its default configuration. Substitution using sed works fine. Data shows correctly when I view the file on the command prompt.
answered Oct 30 '18 at 11:41
CKACKA
61
answered Oct 30 '18 at 11:41
CKACKA
61
answered Oct 30 '18 at 11:41
CKACKA
61
answered Oct 30 '18 at 11:41
CKACKA
61
61
add a comment |
add a comment |
0
with miller (http://johnkerl.org/miller/doc/), using regex with
mlr --csv put '$last_modified_date=gsub($last_modified_date,"^([0-9]{1,2})(/)([0-9]{1,2})(/)([0-9]{4})(.*)$","5-3-1");
$period_of_performance_start_date=gsub($period_of_performance_start_date,"^([0-9]{1,2})(/)([0-9]{1,2})(/)([0-9]{4})(.*)$","5-3-1");
$period_of_performance_potential_end_date=gsub($period_of_performance_potential_end_date,"^([0-9]{1,2})(/)([0-9]{1,2})(/)([0-9]{4})(.*)$","5-3-1");
$period_of_performance_current_end_date=gsub($period_of_performance_current_end_date,"^([0-9]{1,2})(/)([0-9]{1,2})(/)([0-9]{4})(.*)$","5-3-1")' input.csv
You have
award_id_piid,modification_number,potential_total_value_of_award,action_date,period_of_performance_start_date,period_of_performance_current_end_date,period_of_performance_potential_end_date,ordering_period_end_date,awarding_agency_code,last_modified_date
68HE0418F0516,P00001,48876.44,10/10/2018,2018-10-10,2019-1-12,2019-1-12,,68,2018-10-10
12639519F0011,P00001,89813.83,10/10/2018,2018-10-10,2018-18-10,2018-18-10,,12,2018-10-10
GS35F497CA,PM0011,475000,10/10/2018,2018-10-10,,,9/16/2020,47,2018-10-10
15B41918PTP440004,P00004,617912.96,10/10/2018,2018-10-10,2018-10-10,2018-10-10,,15,2018-10-10
15B31019PUA130001,0,23925,10/10/2018,2018-1-10,2018-10-10,2018-10-10,,15,2018-10-10
answered Dec 31 '18 at 13:47
aborrusoaborruso
1714
add a comment |
0
with miller (http://johnkerl.org/miller/doc/), using regex with
mlr --csv put '$last_modified_date=gsub($last_modified_date,"^([0-9]{1,2})(/)([0-9]{1,2})(/)([0-9]{4})(.*)$","5-3-1");
$period_of_performance_start_date=gsub($period_of_performance_start_date,"^([0-9]{1,2})(/)([0-9]{1,2})(/)([0-9]{4})(.*)$","5-3-1");
$period_of_performance_potential_end_date=gsub($period_of_performance_potential_end_date,"^([0-9]{1,2})(/)([0-9]{1,2})(/)([0-9]{4})(.*)$","5-3-1");
$period_of_performance_current_end_date=gsub($period_of_performance_current_end_date,"^([0-9]{1,2})(/)([0-9]{1,2})(/)([0-9]{4})(.*)$","5-3-1")' input.csv
You have
award_id_piid,modification_number,potential_total_value_of_award,action_date,period_of_performance_start_date,period_of_performance_current_end_date,period_of_performance_potential_end_date,ordering_period_end_date,awarding_agency_code,last_modified_date
68HE0418F0516,P00001,48876.44,10/10/2018,2018-10-10,2019-1-12,2019-1-12,,68,2018-10-10
12639519F0011,P00001,89813.83,10/10/2018,2018-10-10,2018-18-10,2018-18-10,,12,2018-10-10
GS35F497CA,PM0011,475000,10/10/2018,2018-10-10,,,9/16/2020,47,2018-10-10
15B41918PTP440004,P00004,617912.96,10/10/2018,2018-10-10,2018-10-10,2018-10-10,,15,2018-10-10
15B31019PUA130001,0,23925,10/10/2018,2018-1-10,2018-10-10,2018-10-10,,15,2018-10-10
answered Dec 31 '18 at 13:47
aborrusoaborruso
1714
add a comment |
0
0
0
with miller (http://johnkerl.org/miller/doc/), using regex with
mlr --csv put '$last_modified_date=gsub($last_modified_date,"^([0-9]{1,2})(/)([0-9]{1,2})(/)([0-9]{4})(.*)$","5-3-1");
$period_of_performance_start_date=gsub($period_of_performance_start_date,"^([0-9]{1,2})(/)([0-9]{1,2})(/)([0-9]{4})(.*)$","5-3-1");
$period_of_performance_potential_end_date=gsub($period_of_performance_potential_end_date,"^([0-9]{1,2})(/)([0-9]{1,2})(/)([0-9]{4})(.*)$","5-3-1");
$period_of_performance_current_end_date=gsub($period_of_performance_current_end_date,"^([0-9]{1,2})(/)([0-9]{1,2})(/)([0-9]{4})(.*)$","5-3-1")' input.csv
You have
award_id_piid,modification_number,potential_total_value_of_award,action_date,period_of_performance_start_date,period_of_performance_current_end_date,period_of_performance_potential_end_date,ordering_period_end_date,awarding_agency_code,last_modified_date
68HE0418F0516,P00001,48876.44,10/10/2018,2018-10-10,2019-1-12,2019-1-12,,68,2018-10-10
12639519F0011,P00001,89813.83,10/10/2018,2018-10-10,2018-18-10,2018-18-10,,12,2018-10-10
GS35F497CA,PM0011,475000,10/10/2018,2018-10-10,,,9/16/2020,47,2018-10-10
15B41918PTP440004,P00004,617912.96,10/10/2018,2018-10-10,2018-10-10,2018-10-10,,15,2018-10-10
15B31019PUA130001,0,23925,10/10/2018,2018-1-10,2018-10-10,2018-10-10,,15,2018-10-10
answered Dec 31 '18 at 13:47
aborrusoaborruso
1714
with miller (http://johnkerl.org/miller/doc/), using regex with
mlr --csv put '$last_modified_date=gsub($last_modified_date,"^([0-9]{1,2})(/)([0-9]{1,2})(/)([0-9]{4})(.*)$","5-3-1");
$period_of_performance_start_date=gsub($period_of_performance_start_date,"^([0-9]{1,2})(/)([0-9]{1,2})(/)([0-9]{4})(.*)$","5-3-1");
$period_of_performance_potential_end_date=gsub($period_of_performance_potential_end_date,"^([0-9]{1,2})(/)([0-9]{1,2})(/)([0-9]{4})(.*)$","5-3-1");
$period_of_performance_current_end_date=gsub($period_of_performance_current_end_date,"^([0-9]{1,2})(/)([0-9]{1,2})(/)([0-9]{4})(.*)$","5-3-1")' input.csv
You have
award_id_piid,modification_number,potential_total_value_of_award,action_date,period_of_performance_start_date,period_of_performance_current_end_date,period_of_performance_potential_end_date,ordering_period_end_date,awarding_agency_code,last_modified_date
68HE0418F0516,P00001,48876.44,10/10/2018,2018-10-10,2019-1-12,2019-1-12,,68,2018-10-10
12639519F0011,P00001,89813.83,10/10/2018,2018-10-10,2018-18-10,2018-18-10,,12,2018-10-10
GS35F497CA,PM0011,475000,10/10/2018,2018-10-10,,,9/16/2020,47,2018-10-10
15B41918PTP440004,P00004,617912.96,10/10/2018,2018-10-10,2018-10-10,2018-10-10,,15,2018-10-10
15B31019PUA130001,0,23925,10/10/2018,2018-1-10,2018-10-10,2018-10-10,,15,2018-10-10
answered Dec 31 '18 at 13:47
aborrusoaborruso
1714
edited Jan 20 at 21:22
answered Dec 31 '18 at 13:47
aborrusoaborruso
1714
answered Dec 31 '18 at 13:47
aborrusoaborruso
1714
answered Dec 31 '18 at 13:47
aborrusoaborruso
1714
1714
add a comment |
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Ask Ubuntu!
- 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.
draft saved
draft discarded
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%2faskubuntu.com%2fquestions%2f1088437%2fwhy-extented-regular-expression-works-on-command-line-input-but-not-when-reading%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
What output do you get with the file?
– muru
Oct 30 '18 at 1:50
Thank you for the response. The regular expression works. When I print the file on bash console, that substitutions have taken place. When I drag and drop csv file into Excel the formatting changes. My best guess is how excel is setup and treats date formatting by default. Even further, excel shows changes only on a couple of dates. I am wondering in order to avoid those pitfalls whether there an opensource CVS editor. Thank you for your response and your time
– CKA
Oct 30 '18 at 11:38
If you're going to import this into Excel, it might be simplest to keep the existing format and change the date display settings in Excel. That way, the data remains untouched but you get to see what you want to see.
– muru
Oct 30 '18 at 11:41