AWK print records to separate files by the column and replace that column with value when null

up vote
0
down vote

favorite

I need to break a large file up by the first column value and when that column is null, replace the null with a value and still create a file from those bad records.
I tried something like

awk -F'|' 'print match($1,/^ /) > $1 : "BAD" $FILENAME

but of course that doesn't work.

A|123|zxy
B|321|zyx
|345|abc
A|456|zys

Create 3 files: A, B, and BAD

edited Mar 6 at 20:17

Kusalananda

103k13202318

asked Mar 6 at 20:14

mrlddst

what are you trying to do with the match() there?
â€“Â ilkkachu
Mar 6 at 20:22

I was trying to match a null value. I am using bash
â€“Â mrlddst
Mar 6 at 22:34

add a commentÂ |Â

up vote
0
down vote

favorite

I need to break a large file up by the first column value and when that column is null, replace the null with a value and still create a file from those bad records.
I tried something like

awk -F'|' 'print match($1,/^ /) > $1 : "BAD" $FILENAME

but of course that doesn't work.

A|123|zxy
B|321|zyx
|345|abc
A|456|zys

Create 3 files: A, B, and BAD

edited Mar 6 at 20:17

Kusalananda

103k13202318

asked Mar 6 at 20:14

mrlddst

what are you trying to do with the match() there?
â€“Â ilkkachu
Mar 6 at 20:22

I was trying to match a null value. I am using bash
â€“Â mrlddst
Mar 6 at 22:34

add a commentÂ |Â

up vote
0
down vote

favorite

I need to break a large file up by the first column value and when that column is null, replace the null with a value and still create a file from those bad records.
I tried something like

awk -F'|' 'print match($1,/^ /) > $1 : "BAD" $FILENAME

but of course that doesn't work.

A|123|zxy
B|321|zyx
|345|abc
A|456|zys

Create 3 files: A, B, and BAD

edited Mar 6 at 20:17

Kusalananda

103k13202318

asked Mar 6 at 20:14

mrlddst

I need to break a large file up by the first column value and when that column is null, replace the null with a value and still create a file from those bad records.
I tried something like

awk -F'|' 'print match($1,/^ /) > $1 : "BAD" $FILENAME

but of course that doesn't work.

A|123|zxy
B|321|zyx
|345|abc
A|456|zys

Create 3 files: A, B, and BAD

edited Mar 6 at 20:17

Kusalananda

103k13202318

asked Mar 6 at 20:14

mrlddst

edited Mar 6 at 20:17

Kusalananda

103k13202318

edited Mar 6 at 20:17

Kusalananda

103k13202318

edited Mar 6 at 20:17

Kusalananda

103k13202318

asked Mar 6 at 20:14

mrlddst

asked Mar 6 at 20:14

mrlddst

asked Mar 6 at 20:14

mrlddst

what are you trying to do with the match() there?
â€“Â ilkkachu
Mar 6 at 20:22

I was trying to match a null value. I am using bash
â€“Â mrlddst
Mar 6 at 22:34

add a commentÂ |Â

what are you trying to do with the match() there?
â€“Â ilkkachu
Mar 6 at 20:22

I was trying to match a null value. I am using bash
â€“Â mrlddst
Mar 6 at 22:34

what are you trying to do with the match() there?
â€“Â ilkkachu
Mar 6 at 20:22

I was trying to match a null value. I am using bash
â€“Â mrlddst
Mar 6 at 22:34

add a commentÂ |Â

1 Answer
1

active

oldest

votes

up vote
3
down vote

accepted

awk -F '|' ' fname = $1 ? $1 : "BAD"; print >fname ' file

This sets the variable fname to the value of the first column if it exists, otherwise to BAD. The line is then printed to that filename.

If you have a lot af different values in the first column, then you may want to close the output files between each print statement as to not run out of file descriptors:

awk -F '|' ' fname = $1 ? $1 : "BAD"; print >>fname; close fname ' file

Note that we now must open the file in append mode with >>.

answered Mar 6 at 20:20

Kusalananda

103k13202318

It says it can't open the file....
â€“Â mrlddst
Mar 7 at 12:42

@MRLDDST Which file? The output file or the input file? I'm using file as a placeholder for whatever filename you are feeding into the awk command. You may want to replace this by "$FILENAME" if that's the variable you have the input filename in.
â€“Â Kusalananda
Mar 7 at 12:45

The source file. I am passing the input file name where you have 'file' I also tried the append mode and received the same error as well. "Can't open file "filename" source line number 1. My first record has a null value in the first column.
â€“Â mrlddst
Mar 7 at 13:10

@MRLDDST In that case you are simply passing awk a filename it can not find. You could echo the filename to the terminal before calling awk just to verify that it's the filename that you expect it to be. I don't know your setup or where your files are or what they are called, so I can't really help you with that particular thing.
â€“Â Kusalananda
Mar 7 at 13:13

1

Sorry...oversight....didn't realize I didn't add the directory when I tested the code. Yes, it works as you stated! Thanks!
â€“Â mrlddst
Mar 7 at 13:24

add a commentÂ |Â

Your Answer

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "106"
;
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',
convertImagesToLinks: false,
noModals: false,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);

);

draft saved

draft discarded

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2funix.stackexchange.com%2fquestions%2f428601%2fawk-print-records-to-separate-files-by-the-column-and-replace-that-column-with-v%23new-answer', 'question_page');

);

Post as a guest

Name

1 Answer
1

active

oldest

votes

1 Answer
1

active

oldest

votes

up vote
3
down vote

accepted

awk -F '|' ' fname = $1 ? $1 : "BAD"; print >fname ' file

This sets the variable fname to the value of the first column if it exists, otherwise to BAD. The line is then printed to that filename.

If you have a lot af different values in the first column, then you may want to close the output files between each print statement as to not run out of file descriptors:

awk -F '|' ' fname = $1 ? $1 : "BAD"; print >>fname; close fname ' file

Note that we now must open the file in append mode with >>.

answered Mar 6 at 20:20

Kusalananda

103k13202318

It says it can't open the file....
â€“Â mrlddst
Mar 7 at 12:42

@MRLDDST Which file? The output file or the input file? I'm using file as a placeholder for whatever filename you are feeding into the awk command. You may want to replace this by "$FILENAME" if that's the variable you have the input filename in.
â€“Â Kusalananda
Mar 7 at 12:45

The source file. I am passing the input file name where you have 'file' I also tried the append mode and received the same error as well. "Can't open file "filename" source line number 1. My first record has a null value in the first column.
â€“Â mrlddst
Mar 7 at 13:10

@MRLDDST In that case you are simply passing awk a filename it can not find. You could echo the filename to the terminal before calling awk just to verify that it's the filename that you expect it to be. I don't know your setup or where your files are or what they are called, so I can't really help you with that particular thing.
â€“Â Kusalananda
Mar 7 at 13:13

1

Sorry...oversight....didn't realize I didn't add the directory when I tested the code. Yes, it works as you stated! Thanks!
â€“Â mrlddst
Mar 7 at 13:24

add a commentÂ |Â

up vote
3
down vote

accepted

awk -F '|' ' fname = $1 ? $1 : "BAD"; print >fname ' file

This sets the variable fname to the value of the first column if it exists, otherwise to BAD. The line is then printed to that filename.

If you have a lot af different values in the first column, then you may want to close the output files between each print statement as to not run out of file descriptors:

awk -F '|' ' fname = $1 ? $1 : "BAD"; print >>fname; close fname ' file

Note that we now must open the file in append mode with >>.

answered Mar 6 at 20:20

Kusalananda

103k13202318

It says it can't open the file....
â€“Â mrlddst
Mar 7 at 12:42

@MRLDDST Which file? The output file or the input file? I'm using file as a placeholder for whatever filename you are feeding into the awk command. You may want to replace this by "$FILENAME" if that's the variable you have the input filename in.
â€“Â Kusalananda
Mar 7 at 12:45

The source file. I am passing the input file name where you have 'file' I also tried the append mode and received the same error as well. "Can't open file "filename" source line number 1. My first record has a null value in the first column.
â€“Â mrlddst
Mar 7 at 13:10

@MRLDDST In that case you are simply passing awk a filename it can not find. You could echo the filename to the terminal before calling awk just to verify that it's the filename that you expect it to be. I don't know your setup or where your files are or what they are called, so I can't really help you with that particular thing.
â€“Â Kusalananda
Mar 7 at 13:13

1

Sorry...oversight....didn't realize I didn't add the directory when I tested the code. Yes, it works as you stated! Thanks!
â€“Â mrlddst
Mar 7 at 13:24

add a commentÂ |Â

up vote
3
down vote

accepted

awk -F '|' ' fname = $1 ? $1 : "BAD"; print >fname ' file

This sets the variable fname to the value of the first column if it exists, otherwise to BAD. The line is then printed to that filename.

If you have a lot af different values in the first column, then you may want to close the output files between each print statement as to not run out of file descriptors:

awk -F '|' ' fname = $1 ? $1 : "BAD"; print >>fname; close fname ' file

Note that we now must open the file in append mode with >>.

answered Mar 6 at 20:20

Kusalananda

103k13202318

awk -F '|' ' fname = $1 ? $1 : "BAD"; print >fname ' file

This sets the variable fname to the value of the first column if it exists, otherwise to BAD. The line is then printed to that filename.

If you have a lot af different values in the first column, then you may want to close the output files between each print statement as to not run out of file descriptors:

awk -F '|' ' fname = $1 ? $1 : "BAD"; print >>fname; close fname ' file

Note that we now must open the file in append mode with >>.

answered Mar 6 at 20:20

Kusalananda

103k13202318

answered Mar 6 at 20:20

Kusalananda

103k13202318

answered Mar 6 at 20:20

Kusalananda

103k13202318

answered Mar 6 at 20:20

Kusalananda

103k13202318

It says it can't open the file....
â€“Â mrlddst
Mar 7 at 12:42

@MRLDDST Which file? The output file or the input file? I'm using file as a placeholder for whatever filename you are feeding into the awk command. You may want to replace this by "$FILENAME" if that's the variable you have the input filename in.
â€“Â Kusalananda
Mar 7 at 12:45

The source file. I am passing the input file name where you have 'file' I also tried the append mode and received the same error as well. "Can't open file "filename" source line number 1. My first record has a null value in the first column.
â€“Â mrlddst
Mar 7 at 13:10

@MRLDDST In that case you are simply passing awk a filename it can not find. You could echo the filename to the terminal before calling awk just to verify that it's the filename that you expect it to be. I don't know your setup or where your files are or what they are called, so I can't really help you with that particular thing.
â€“Â Kusalananda
Mar 7 at 13:13

1

Sorry...oversight....didn't realize I didn't add the directory when I tested the code. Yes, it works as you stated! Thanks!
â€“Â mrlddst
Mar 7 at 13:24

add a commentÂ |Â

It says it can't open the file....
â€“Â mrlddst
Mar 7 at 12:42

@MRLDDST Which file? The output file or the input file? I'm using file as a placeholder for whatever filename you are feeding into the awk command. You may want to replace this by "$FILENAME" if that's the variable you have the input filename in.
â€“Â Kusalananda
Mar 7 at 12:45

The source file. I am passing the input file name where you have 'file' I also tried the append mode and received the same error as well. "Can't open file "filename" source line number 1. My first record has a null value in the first column.
â€“Â mrlddst
Mar 7 at 13:10

@MRLDDST In that case you are simply passing awk a filename it can not find. You could echo the filename to the terminal before calling awk just to verify that it's the filename that you expect it to be. I don't know your setup or where your files are or what they are called, so I can't really help you with that particular thing.
â€“Â Kusalananda
Mar 7 at 13:13

1

Sorry...oversight....didn't realize I didn't add the directory when I tested the code. Yes, it works as you stated! Thanks!
â€“Â mrlddst
Mar 7 at 13:24

It says it can't open the file....
â€“Â mrlddst
Mar 7 at 12:42

@MRLDDST Which file? The output file or the input file? I'm using file as a placeholder for whatever filename you are feeding into the awk command. You may want to replace this by "$FILENAME" if that's the variable you have the input filename in.
â€“Â Kusalananda
Mar 7 at 12:45

The source file. I am passing the input file name where you have 'file' I also tried the append mode and received the same error as well. "Can't open file "filename" source line number 1. My first record has a null value in the first column.
â€“Â mrlddst
Mar 7 at 13:10

@MRLDDST In that case you are simply passing awk a filename it can not find. You could echo the filename to the terminal before calling awk just to verify that it's the filename that you expect it to be. I don't know your setup or where your files are or what they are called, so I can't really help you with that particular thing.
â€“Â Kusalananda
Mar 7 at 13:13

Sorry...oversight....didn't realize I didn't add the directory when I tested the code. Yes, it works as you stated! Thanks!
â€“Â mrlddst
Mar 7 at 13:24

add a commentÂ |Â

draft saved

draft discarded

draft saved

draft discarded

Post as a guest

Name

搜尋此網誌

mjhjmtu