All Forums Database
snaren777 9 posts Joined 08/11
02 Feb 2012
Adding CR/LF to huge datafiles

Hello,

I am trying to load data from Pipe delimited flat files (size from 1.5Gb to 6GB) to a table in our database. I tried MLOAD. After reading all the records in the smallest file (1.5GB), it threw an error 'EOD encountered before end of record'. I was able to fix it by opening the file in Textpad and adding a CR at the very end of the file (i went to the last line in the file and pressed an enter).  I am not able to do the same with the other files as they are too huge to open in my computer. Is there a way in which we can handle it at the script level?  Any suggestions will be much appreciated.

Thank you
Naren

Sample Script:

.LOGTABLE ud122.data_q4_2012_log;
.LOGON tdpid/112,pwd;
.begin import mload
tables UD122.data_Q4_1
Errortables
ud122.data_Q4_ERROR1
ud122.data_Q4_ERROR2
CHECKPOINT 500000
ERRLIMIT 50      
SESSIONS 25
AMPCHECK ALL;
.layout olblayout;
.FIELD IDFIELD * varchar(10);
.FIELD CODEFIELD * varchar(10);
.dml label olbodml;
INSERT INTO UD122.data_Q4_1(
:IDFIELD,
:CODEFIELD
);
import infile "c:\uploading\abc.txt"
format VARTEXT '|'
layout olblayout
apply olbodml;
.end mload;
.logoff;
 

 

hans-peter 7 posts Joined 11/04
02 Feb 2012

an awk-command, which is simply printing every line adds the LF after last line.  But I'm not sure if the size of the file might  cause problems with awk..

> awk '{print $0}' < infile.txt >outfile.txt

ulrich 816 posts Joined 09/09
03 Feb 2012

you might try to use a perl script with write append.

Didn't test it but the script would be something like

 

 #!/usr/local/bin/perl

 open (MYFILE, '>>yourFileName.dat') || die("This file will not open!");

 print MYFILE "\n";

 close (MYFILE); 

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

snaren777 9 posts Joined 08/11
03 Feb 2012

Hi,

Thank you for the suggestions. I used a File Splitter and split the files in to 1GB sizes. Now, I am able to open the files in Textpad and add a CR at the end. I did have to merge the truncated row (last record in a file and first record in the next file). I will update on my progress soon. 

 

Regards

Naren

You must sign in to leave a comment.