-2

I am very new to Unix; looking for a solution to my problem here. Do not have any code to start with :) just the problem and required solution.

I am looking to automate the following process (i.e. replicate the Vlookup function in Excel):

  • System generates a number of CSV files, different numbers of columns, delimiters.

All generated files contain a key (transaction #) - can be located in different columns between different documents.

edit: assume the extracts are not sorted by the transaction#.

example, Table1:

field1,field2,field3,Transaction#,field4
ABC,ABC,ABC,1,CFG
ABC,ABC,ABC,2,CFG
ABC,ABC,ABC,3,CFG

example, Table2:

field1;Transaction#;field3;field4;field5
ABC;1;ABC;ABC;CFG
ABC;2;ABC;ABC;CFG
ABC;3;ABC;ABC;CFG
  • I have a lookup table which looks like the following:

    Transaction#    New#
        1            122
        2            123
        3            124
    
  • I need to add the column with header New# at the end of each csv table:

edit: assume not all the Transaction#s from lookup table are present in the input table; and not all Transaction#s from input table are present in lookup table.

example, Table1:

field1,field2,field3,Transaction#,field4,new#
ABC,ABC,ABC,1,CFG,122 
ABC,ABC,ABC,2,CFG,123
ABC,ABC,ABC,3,CFG,124

example, Table2:

field1;Transaction#;field3;field4;field5;new#
ABC;1;ABC;ABC;CFG;122
ABC;2;ABC;ABC;CFG;123
ABC;3;ABC;ABC;CFG;124
  • issue here is the tables will not necessarily be sorted by transaction #s... so a Vlookup type of procedure is required. – user3934230 Mar 28 '17 at 21:44
  • additionally, not all transaction #s from lookup file might be present in the input. – user3934230 Mar 28 '17 at 21:45
  • 1
    Can you please improve your sample data? With every line exactly equivalent (ABC ABC ABC ABC ABC), it's really hard to tell what you want. Show example input and example desired output. If you say some transaction numbers are missing from one or the other table, show that in your sample data. The key to a good answerable text processing question is good representative example input and output. – Wildcard Mar 29 '17 at 5:48
1

I'll assume both your tables and the lookup file are CSVs, they have the same delimiter, and they use the same quoting conventions. If they don't you need to normalize them first, by some other means.

I'll also assume the lookup file is small enough to be read in memory. If it isn't then you should probably convert your data to SQL.

With these assumptions you can use awk:

awk -F , -v OFS=, -v col=4 '
    NR == 1 { next }
    NR == FNR {
        n[$1] = $2
    }
    NR != FNR {
        NF++
        $NF = FNR == 1 ? "new" : n[$col]
        print
    }' lookup.csv Table1.csv

You can adjust -F, OFS, and col above to match the CSV delimiter and the relevant column in the table.

1

I don't think text processing tools are up to the task. Instead, I recommend using a language properly equipped to deal with CSV files.

Here is a proposal in R (http://r-project.org, it's quite difficult to google efficiently if you don't know it).

#!/usr/bin/Rscript
args <- commandArgs(TRUE)

# Here, we read each table passed as argument on the commandline
tablenames <- list()
for (tablename in args) {
    header <- readLines(tablename, n=1)
    # we try to detect the separator (the character that surrounds "Transaction#")
    # That doesn't work if you use multi-characters separators
    sep <- sub(".*(.)Transaction#.*","\\1",header)
    if (nchar(sep[1]) != 1) {
        sep <- sub(".*Transaction#(.).*","\\1",header)
    }
    if (nchar(sep[1]) != 1) {
        print(paste0("Could not detect separator around column 'Transaction#' in file ",tablename))
    } else {
        # each table where the separator is succesfully detected
        # is added to a list of tablenames
        tablenames[[tablename]] <- list(name=tablename,sep=sep)
    }
}

# we parse each table in the list of tablenames
tables <- lapply(tablenames, function(tab) { read.csv(tab$name, check.names=FALSE, sep=tab$sep) })

# we also parse the lookup table, which has a different format
lookup <- read.table("lookup",header=TRUE,check.names=FALSE,comment.char="")

# then for each table, we add the new column
for (i in 1:length(tablenames)) {
  # This line magic:
  # - it adds a new column called "New#" to the table
  # - this column is populated from table lookup
  # - lines in lookup are filtered and ordered so that column "Transaction#" matches columns "Transaction#" in the table
  # - we add only column "New#" from lookup to the table
  tables[[i]][,"New#"] <- lookup[match(tables[[i]][,"Transaction#"],lookup[,"Transaction#"]),"New#"]

  # we write back the table under the name "new <original name>"
  write.table(tables[[i]], file=paste("new",tablenames[[i]]$name), sep=tablenames[[i]]$sep, quote=FALSE, row.names=FALSE)
}

You must call this script from the directory where your tables are:

./script table1 table2 ...

where table1,table2,... are the filenames of your tables. As the script is written, the lookup table must be in file lookup but this can be changed easily.

For example :

table1

field1,field2,ffield1,field2,field3,Transaction#,field4
 ABC,ABC,ABC,1,CFG
 ABC,ABC,ABC,3,CFG

table2

field1;Transaction#;field3;field4;field5
ABC;2;ABC;ABC;CFG
ABC;1;ABC;ABC;CFG
ABC;3;ABC;ABC;CFG

We run ./script.R table1 table2.

lookup

Transaction#   New#
    1            122
    2            123
    3            124

The results are:

new table1

field1,field2,field3,Transaction#,field4,New#
 ABC,ABC,ABC,1,CFG,122
 ABC,ABC,ABC,3,CFG,124

new table2

field1;Transaction#;field3;field4;field5;New#
ABC;2;ABC;ABC;CFG;123
ABC;1;ABC;ABC;CFG;122
ABC;3;ABC;ABC;CFG;124

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.