How to read comma delimited files with missing column values
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 18
- Joined: Thu Dec 08, 2016 11:42 am
How to read comma delimited files with missing column values
Hello,
I have a new requirement to work with comma delimited files with no quotes for blank values but I have never come across with this kind of files to work with. For example:
D,0,SB,,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
D,1,IN,,0,0,0,0,90000000,0,0,0,0,0,0,0,0,0,0
D,4,FEDOUT,,0,0,0,0,90000000,0,0,0,0,0,0,0
D,6,EB,,0,0,0,0,0,0
D,7,DEP,LOC,0,0,0,0,90000000,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,90000000
How to read this kind of files??
TIA.
I have a new requirement to work with comma delimited files with no quotes for blank values but I have never come across with this kind of files to work with. For example:
D,0,SB,,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
D,1,IN,,0,0,0,0,90000000,0,0,0,0,0,0,0,0,0,0
D,4,FEDOUT,,0,0,0,0,90000000,0,0,0,0,0,0,0
D,6,EB,,0,0,0,0,0,0
D,7,DEP,LOC,0,0,0,0,90000000,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,90000000
How to read this kind of files??
TIA.
Last edited by k.prashanthi on Wed Jan 24, 2018 2:10 pm, edited 2 times in total.
-
- Participant
- Posts: 18
- Joined: Thu Dec 08, 2016 11:42 am
The server Sequential File stage will read a CSV file like your example. Set 'Outputs-Format-Missing columns action' to 'Map empty string', and 'Outputs-Columns-Incomplete Column' to 'Replace'.
If you need your CSV file to be an input to a parallel job, consider writing a pre-processor server job that reads your example CSV and writes a second CSV that becomes the parallel job input.
If you need your CSV file to be an input to a parallel job, consider writing a pre-processor server job that reads your example CSV and writes a second CSV that becomes the parallel job input.
-
- Participant
- Posts: 18
- Joined: Thu Dec 08, 2016 11:42 am
if all else fails, you could modify the input file.
https://askubuntu.com/questions/20414/f ... g-commands
Command:
sed -i 's/original/new/g' file.txt
SO something like this:
sed -i 's/,,/,"",/g' file.txt
https://askubuntu.com/questions/20414/f ... g-commands
Command:
sed -i 's/original/new/g' file.txt
SO something like this:
sed -i 's/,,/,"",/g' file.txt
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact: