 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/YYYYI 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
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
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
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
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
|
» 
code :
Related Posts :
