logo
down
shadow

To ignore Pipe in pipe delimited file


To ignore Pipe in pipe delimited file

By : yochi
Date : November 25 2020, 07:06 PM
it should still fix some issue With GNU awk you would need to use FPAT to describe what a field is instead of using FS to describe what the field separator is. For example:
code :


Share : facebook icon twitter icon
Oracle SQL Pipe Delimited File w/One Comma Delimited Column to Split and Load Using SQLLDR

Oracle SQL Pipe Delimited File w/One Comma Delimited Column to Split and Load Using SQLLDR


By : Jojo
Date : March 29 2020, 07:55 AM
Hope this helps You could do a transformation on the data on the way in. It is specified in the control file. See this recent post for an example: SQLLDR CTL: Load date field received in DDMonYYYY to db fields with formats of YYYYMM or MM/DD/YYYY
I would use REGEXP_SUBSTR to extract the various codes from their positions in the comma separated list. Give it a try and let us know what happens.
code :
LOAD DATA
INFILE 'data.txt'
APPEND
INTO TABLE X_TEST
FIELDS TERMINATED BY '|'
TRAILING NULLCOLS
(
 STYLE       CHAR
,SIZE        CHAR
,CODESTRING  BOUNDFILLER
,CODE1      "trim(regexp_substr(:CODESTRING, '([^,]*)(,|$)', 1, 1, NULL, 1))"
,CODE2      "trim(regexp_substr(:CODESTRING, '([^,]*)(,|$)', 1, 2, NULL, 1))"
,CODE3      "trim(regexp_substr(:CODESTRING, '([^,]*)(,|$)', 1, 3, NULL, 1))"
)
SQL import pipe delimited with some data fields that have have pipe in record

SQL import pipe delimited with some data fields that have have pipe in record


By : user3105284
Date : March 29 2020, 07:55 AM
To fix this issue I've had the same problem before and from my experience there's not much you can do during the import. Obviously if you have any control during the export process from the source you can handle the cleansing of the data at that point, buy most likely is not your case. One thing you can do at least to prevent failures during the import is to validate your input file before the batch insert like I did with a simple code like this:
code :
    public class ValidateMigrationFile {

    private static final String REGEX = "^([^|]*\\|){50}[^|]*$";

    public static void testFile(String fileName) {

        int lineCounter = 1;
        int totalErrors = 0;

        try {

            BufferedReader br = new BufferedReader(new FileReader(fileName));

            String line = null;

            while ((line=br.readLine())!=null) {

                // Validate the line is formatted correctly based on regular expressions                
                if (!line.matches(REGEX)){
                    System.out.println("Invalid format on line " + lineCounter + " (" + line + ")");
                    totalErrors++;
                }

                lineCounter++;
            }
            br.close();
            System.out.println("Total rows processed: " + --lineCounter);
            System.out.println("Total errors found: " + totalErrors);


        } catch (Exception ex) {
            System.out.println("Exception occurred: " + ex.toString());
        }
    }
}
Find and Replace Pipe delimiter from field in a pipe delimited file

Find and Replace Pipe delimiter from field in a pipe delimited file


By : oneway
Date : March 29 2020, 07:55 AM
may help you . I have had a similar question like this earlier later i've to add more scope to that question but had no idea how to edit it and make it live again. that's why i'm posting as a new Question.
code :
$ cat tst.awk
BEGIN { FS=OFS="|" }
NR==1 { outNf=NF; print; next }
{
    end = beg + (NF - outNf) - 1
    for (i=1; i<=NF; i++) {
        sep = (i>=beg && i<=end ? "#" : OFS)
        printf "%s%s", $i, (i<NF ? sep : ORS)
    }
}

$ awk -v beg=3 -f tst.awk file
 NAME | NUM | WEB | LOCATION | CURRENCY | PLACE
 ABCD | 04  | GO#OGLE | EUROPE | EURO   | PARIS
 XYZE | 12  | Y#A#HOO | USA    | DOLLAR | SEATTLE
 LMNO | 17  | #FACE#B#O#O#K | ASIA | ASIAN DOLLAR | HONGKONG
 EDDE | 98  | A##M#AZ#ON# | AFRICA | AF DOLLAR | CAPETOWN
Remove everything in a pipe delimited file after second-to-last pipe

Remove everything in a pipe delimited file after second-to-last pipe


By : Christopher Chafin
Date : March 29 2020, 07:55 AM
I wish this helpful for you Replace |(string without pipe)|(string without pipe) at the end of each line:
How to deal with pipe as data in pipe delimited file

How to deal with pipe as data in pipe delimited file


By : Jeff Knecht
Date : March 29 2020, 07:55 AM
it fixes the issue You have to change the Column Delimiter property to | (vertical bar) and the Text Qualifier property to " within the Flat File Connection Manager
shadow
Privacy Policy - Terms - Contact Us © ourworld-yourmove.org