How to remove extra pipes in text sources

[garsan]

Hello,

I hope someone can help me.

I have a text source file, let says with this structure separated with pipes (|):

ID|COLOR|MODEL|VEHICLE

And I need to save the data to a table, but some times the data comes with some extra pipes and this causes the data to move one or several places, for example:

1|RED|2014|VW

2||RED|2013|GM

3|BLUE||2017||||GM

4|GOLD||2016|||GM|

5|GOLD|   |2016| ||GM|

Is there a way to remove those extra pipes to accomodate the data?

Any help would be appreciated!

Thanks!

[Ana GH]

That's a hard input to treat, the standard answer would be "reject the file or the incorrect lines at least" you need to get a proper input with a defined structure to process it.
Anyway, if the input always has 4 fields, you can try cleaning each line as a whole using regular expressions and/or text operation steps, for example:

• Clean white spaces (do your color and model fields contain white spaces also? Or is easier and you can delete all white spaces in the line?)
• Replace || with |

Once you have your line with the proper format, you can process it as usual.

If your input doesn't always have the 4 fields, or the fields are mixed, so you can't define a logic to clean the lines, then either you talk with the people providing the input file so they generate a standard output, or if that's not possible, process the lines you can, and generate an error file with the lines you can't process.
Regards

[johanhammink]

Ask for enclosures around the separator

[Sparkles]

That looks like a real challenge! You got 4 attributes (ID, COLOR, MODEL, VEHICLE). Let's call them a1, a2, a3, a4. The pipes are not the issue here (unless the attribute values can consist of pipes also). One could simply replace them with commas or treat them like commas. The format is the problem here. You seem to always have a complete set of all 4 attributes, but there may be any number of additional empty attributes! The attributes also seem to always be ordered in the same sequence.

a1,a2,a3,a4
a1,,a2,a3,a4
a1,a2,,a3,,,,a4
a1,a2,,a3,,,a4,
a1,a2,,a3, ,,a4,

It should be feasible to handle this in java code:
1) continue looking for attributes until you found all 4
2) count the number of attributes so you know which one the current/next will be
3) skip empty attributes

[garsan]

Hello, thank you!

In this case, the example i posted here is just 4 columns, the original file have more than 30!

All the fields come with spaces "RED DEVIL", "GENERAL MOTOR COMPANY", etc. So, I think i cant remove all the spaces

If I replace || with | how can I identify if the next column is really empty and not an txtra pipe?

Thank you Ana!

That's a hard input to treat, the standard answer would be "reject the file or the incorrect lines at least" you need to get a proper input with a defined structure to process it.
Anyway, if the input always has 4 fields, you can try cleaning each line as a whole using regular expressions and/or text operation steps, for example:

• Clean white spaces (do your color and model fields contain white spaces also? Or is easier and you can delete all white spaces in the line?)
• Replace || with |

Once you have your line with the proper format, you can process it as usual.

If your input doesn't always have the 4 fields, or the fields are mixed, so you can't define a logic to clean the lines, then either you talk with the people providing the input file so they generate a standard output, or if that's not possible, process the lines you can, and generate an error file with the lines you can't process.
Regards

[garsan]

Hello,

How can I count the columns with Java (JS right?)?

After a full review of the file, all the columns comes with the same lenght, for example, if the color have 10 chars, even if empty comes with 10 spaces. Maybe this can help?

Thanks!

That looks like a real challenge! You got 4 attributes (ID, COLOR, MODEL, VEHICLE). Let's call them a1, a2, a3, a4. The pipes are not the issue here (unless the attribute values can consist of pipes also). One could simply replace them with commas or treat them like commas. The format is the problem here. You seem to always have a complete set of all 4 attributes, but there may be any number of additional empty attributes! The attributes also seem to always be ordered in the same sequence.

a1,a2,a3,a4
a1,,a2,a3,a4
a1,a2,,a3,,,,a4
a1,a2,,a3,,,a4,
a1,a2,,a3, ,,a4,

It should be feasible to handle this in java code:
1) continue looking for attributes until you found all 4
2) count the number of attributes so you know which one the current/next will be
3) skip empty attributes

[gutlez]

If I replace || with | how can I identify if the next column is really empty and not an extra pipe?

You should really go back to your source system with that issue.
What happens when you have a field with no data, and a field that has erroneous pipes in it? (ie. multiple places where you have ||, but one is real, and one is not)
How would you do this if you were doing it manually? Are there some key fields (like column 3 and column 4 in your first example) that you could use in a RegEx type pattern?

[Sparkles]

Would something like this work?

String line = "5|GOLD| |2016| ||GM|";
String[] fields = line.split("|");
ArrayList<String> result = new ArrayList();

for(int i = 0; i < fields.length; i++)
{
.....String field = fields[i];
.....if(field contains something other than spaces)
.....{
..........result.add(fields[i]);
.....}
}

[karan Arora]

Its easy. first set the value for maximum number of pipes. then calculate the no of pipes in each line using loop concept. delete the line which doesnt match the max no of pipes variable value and move the lines which are matching to a new file. let me know if you need help in shell script coding.

[Sparkles]

Easier than the 10 lines of code above? Why hassle with os-dependent shell script or additional temp files?