All Forums Database
Malleshkr 9 posts Joined 08/12
12 Sep 2012
Fastload File read: 35, Text: EOF encountered before end of record

I have a scenario here.

I get the an error when my source CSV contains data in below format & hence my fastload is unable to process it.

"February 29, 2012","","","","","15/3/2012","22/3/2012","Packaging :: Departure Point :: LON","","15",1,1,0,0.00

"February 29, 2012","","","","","16/5/2012","20/5/2012","Packaging :: Departure Point :: LON","","77",1,1,0,0.00

"February 29, 2012","","","","","19/4/2012","22/4/2012","Packaging :: Departure Point :: LON","","50",0,1,0,0.00

I feel its cause my source data has some quotes. Can you please help me with this?

Error:

I/O Error on File Read: 35, Text: EOF encountered before end of record Highest return code encountered = '12'

_Mallesh

 

_Mallesh
13 Sep 2012

What is the record type you are using? I think you should use SET RECORD VARTEXT

Is there any line break after your last record?

Malleshkr 9 posts Joined 08/12
14 Sep 2012

I have used SET RECORD VARTEXT "," & also checked for line break. But there is no issue with it.
But still am seeing the error.

_Mallesh

CarlosAL 321 posts Joined 04/08
14 Sep 2012

Mallesh:
Please, share the fastload script if you want us to help you...
Two points:
The comma separator in your (presumed) first field will split the first date into two (February 29 + 2012) as fastload will not take in account the double quotes as 'field content' delimitator.
HTH.
Cheers.
Carlos.

Malleshkr 9 posts Joined 08/12
14 Sep 2012

Carlos,
here is the script

CREATE SET TABLE DEV_TCY_STG_VOL.STG_LAND_SHOP_OMNI_DTL,
NO FALLBACK,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT,
DEFAULT MERGEBLOCKRATIO
(
SEARCH_DATE VARCHAR(50),
SEARCH_YEAR VARCHAR(30),
REG_USER_ID VARCHAR(35),
PROD_CATEGORY VARCHAR(255),
MARKET CHAR(10),
BRAND CHAR(3),
INTERNET_SEARCH_WORD VARCHAR(255),
PRODUCTS VARCHAR(255),
START_DATE DATE,
END_DATE DATE,
DEP_PT_NO_OF_GUESTS VARCHAR(255),
SEARCH_PASSENGERS VARCHAR(255),
LEAD_TIME DECIMAL(38,0),
VISITS INTEGER,
UNIQUE_VISITORS INTEGER,
ORDERS INTEGER,
REVENUE DECIMAL(38,0),
LOAD_DATE TIMESTAMP,
MAPPING_NAME VARCHAR(50)
);

SET RECORD VARTEXT "," DISPLAY_ERRORS;
SET SESSION CHARTEXT "UTF8" ;

DEFINE
SEARCH_DATE (VARCHAR(50)),
SEARCH_YEAR (VARCHAR(30)),
REG_USER_ID (VARCHAR(35)),
PROD_CATEGORY (VARCHAR(255)),
INTERNET_SEARCH_WORD (VARCHAR(255)),
PRODUCTS (VARCHAR(255)),
START_DATE (VARCHAR(100)),
END_DATE (VARCHAR(100)),
DEP_PT_NO_OF_GUESTS (VARCHAR(255)),
SEARCH_PASSENGERS (VARCHAR(255)),
LEAD_TIME (VARCHAR(50)),
VISITS (VARCHAR(20)),
UNIQUE_VISITORS (VARCHAR(20)),
ORDERS (VARCHAR(20)),
REVENUE (VARCHAR(20))

File=SHOP_OMNI_DTL_LMN_UK.csv;
SHOW;

BEGIN LOADING STG_LAND_SHOP_OMNI_DTL
ERRORFILES STG_LAND_SHOP_OMNI_DTL_E1, STG_LAND_SHOP_OMNI_DTL_E2
CHECKPOINT 1000;

INSERT INTO STG_LAND_SHOP_OMNI_DTL
(SEARCH_DATE,SEARCH_YEAR,REG_USER_ID,MARKET,BRAND,INTERNET_SEARCH_WORD, PRODUCTS, START_DATE, END_DATE, DEP_PT_NO_OF_GUESTS,SEARCH_PASSENGERS,LEAD_TIME,VISITS,UNIQUE_VISITORS,ORDERS,REVENUE,LOAD_DATE,MAPPING_NAME)
VALUES
(:search_date
,:search_year
,:REG_USER_ID
,'en_GB'
,'LMN'
,:INTERNET_SEARCH_WORD
,:PRODUCTS
,:START_DATE
,:END_DATE
,:DEP_PT_NO_OF_GUESTS
,:SEARCH_PASSENGERS
,:LEAD_TIME
,:VISITS
,:UNIQUE_VISITORS
,:ORDERS
,:REVENUE
,CURRENT_TIMESTAMP
,'m_bb_flat_land_omni_dtl'
);

END LOADING;
QUIT;

Mallesh

_Mallesh

CarlosAL 321 posts Joined 04/08
14 Sep 2012

As I said, fastload will not take in account the double quotes as 'field content' delimitator, so each time it encounters a comma assumes a new field:
"February 29, 2012","","","","","15/3/2012","22/3/2012","Packaging :: Departure Point :: LON","","15",1,1,0,0.00
will give:
FIELD01: '"February 29'
FIELD02: '2012"'
FIELD03: '""'
FIELD04: '""'
FIELD05: '""'
FIELD06: '""'
FIELD07: '"15/3/2012"'
FIELD08: '"22/3/2012"'
FIELD09: '"Packaging :: Departure Point :: LON"'
FIELD10: '""'
FIELD11: '"15"'
FIELD12: '1'
FIELD13: '1'
FIELD14: '0'
FIELD15: '0.00'
You should get rid of the double quotes.
Cheers.
Carlos.

You must sign in to leave a comment.