0

I have over 150 CSV files with inconsistent columns on MAC (Unix) e.g:

Sheet A

ID Col mobile email secondary mobile Name
v v v v v v
v v v v v v

Sheet B

ID Col Col Col Col Col
v v v v v v
v v v v v v

Sheet C

col Email MOBILE name Col col
v v v v v v
v v v v v v

Sheet D

ID col col col Mobile col
v v v v v v
v v v v v v

I need to DERIVE a new-sheet from each of the aformentioned sheet with the following columns (in case any exists)

ID, NAME, EMAIL, MOBILE, SECONDARY_MOBILE

Then I will combine the newly derived sheets into one sheet using CAT. So that I can upload the data into Oracle DB.

So, When it comes to SHEET D, I suppose, the derived sheet should be

ID NAME EMAIL MOBILE SECONDARY_MOBILE
V
V

I hope it is clear. Thanks in advance.

8
  • You can either use a tool such as csvcut or mlr (Miller) that can work with column names directly, or loop over the header line to find the indices of the desired columns. See for example How to print certain columns by name? Aug 7, 2021 at 16:51
  • 1
    We really can't help you parse data we cannot see. Please edit your question and show us a few example input files and the output you would need from that example. Try and cover all possibilities. Without this, we can't know what you need. Do you have a header? Will it always be name or can it also be Name? How are fields defined? By commas? Spaces? Tabs?
    – terdon
    Aug 7, 2021 at 16:53
  • @steeldriver thanks for your reply. I have clarified the question as instructed by you.
    – Test2
    Aug 7, 2021 at 20:19
  • 2
    Thank you, but I am afraid that is considerably more confusing :(. Please show us some of the actual contents of the file. Do not format them as a nice table, we need to know what the actual data look like. You can see this by opening a terminal and running head file.csv on the files. You are asking for a text-based solution, so we need a text file to work with. We need to know how your columns are defined, if there are IDs, how we can know if a column is present or not etc.
    – terdon
    Aug 7, 2021 at 21:12
  • 1
    4. you've given no indication of how you expect to resolve missing data - e.g. some of the input files have no fields for name or email address. One of them ("SheetB") is missing everything but the ID field, the rest are just generic "Col".
    – cas
    Aug 8, 2021 at 2:52

2 Answers 2

1

CSV, despite being having a IETF RFC is a family of formats, not just one. So even if you find somethin which claims to do what you want, it might not understand the particular variant of CSV your files are formatted in. There is no CSV parser for UnixODBC. Both Python and PHP have very robust and flexible parsers. I'm more familiar with the latter / estimate it would take me about 30 minutes to write the required code. OTOH it'd probably be as much work to import that data into Oracle as-is then normalize the structure thereafter.

1
  • Thanks, I will do it with a Python developer for now.
    – Test2
    Aug 8, 2021 at 5:18
0

Using Miller and starting in example from these 3 files

#input_01.csv
a,b,c
0,2,5

#input_02.csv
b,a,c
0,2,5

#input_03.csv
b,a
0,2

you can run

mlr --csv cut -f a,b then unsparsify input_0*.csv >output.csv

to have

a,b
0,2
2,0
2,0

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .