cancel
Showing results for 
Search instead for 
Did you mean: 

BODS: extra empty record getting loaded from flat file to target table

former_member186160
Contributor
0 Kudos

hi all,

i am trying to load data from a csv file to a target table from BODS.

i have 500 records in teh file, but 501 records are loaded to target.

one extra empty row is gettign added to the target.

when i check the file in text editor, i dont find any extra record.

in the ignore row market settign in the flat file format i have given setting as in below snapshot.

[my column delimiter is '|' character and row delimiter is new line character]

but this dint help.

please suggest.

Accepted Solutions (1)

Accepted Solutions (1)

venkataramana_paidi
Contributor
0 Kudos

Hi Swetha,

It is definitely from source file only . Data services never add any extra records in  1 to 1 load.  Try to copy your source data into excel file and load into the table using DS? 

Thanks & Regards,

Ramana.

former_member186160
Contributor
0 Kudos

hi,

we are currently unable to load excel files from bods due to excel adapter issues and will be resolved only with the upgrade which is planned after 3 months.

till then the only option i have is to load flat files.

when i view the csv file in wordpad, there is no emtpy record.

is there anything that i can do to avoid this record from getting loaded into target ?

Former Member
0 Kudos

Hi Swetha,

Just try multiple things

1) drop and recreate your flat file format.

2) add one more column in flat file manually and give the sequence number in excel.

3) now load the file with the sequence number ,you will get to know which is the extra row easily.

Thanks,

Swapnil

venkataramana_paidi
Contributor
0 Kudos

Hi Swetha,

Is blank record is adding after data set ? I mean after loading the all records inserting into table blank record is inserting right?

1. Open your flat file in note pad ( if it is small ) or notepad++( if it is large.

2. Go to the last record in the file.

3. After the last record select down and delete it.

4. Save the file and tried to load.

It is common thing blank space available after the last record in the flat files. We need to delete manually blank space after the last record.

I hope it will help.

Thanks & Regards,

Ramana.

former_member186160
Contributor
0 Kudos

hi,

yes i tried adding an extra sequence row to the file, this helped me to identify the empty record loaded to the table.

but still unable to find the empty record in the source file.

former_member186160
Contributor
0 Kudos

hi,

the problem is that the files will be automatically sent by a third party and developers wont have access to those files in production environment. i need to deal with the job design , but cannot modify the files.

please suggest.

former_member187605
Active Contributor
0 Kudos

Add a filter to your Query transform:

     ColX <> ''

with ColX any mandatory column in your files.

venkataramana_paidi
Contributor
0 Kudos

Hi Swetha ,

If you dont have access to the file you need to handle in the job design .

1. Take key field or  some non blank fields.

2. Use the decode function in the where  length(field) <> 0

or

3. use any extra query with extra field with name flag.

4. flag mapping with me like decode(length(field)<>0 or field is not null,'non blank' ,'blank')

5. Use flag <> 'blank' in the where clause.

I hope your data set might contain at least one field with whole data else combination of few fields.

Thanks &Regards,

Ramana.

Answers (1)

Answers (1)

former_member186160
Contributor
0 Kudos

hi all,

thanks for your replies, issue has been resolved.

solution1: filter the data where data is supplied for atleast one of the input columns [since i dont have any primary key or non-null column as per the requirement]

solution2: can mention to ignore row with a continuous delimiter characters.