Delimitted text file Issues

[former_member206388]

Hi team.

I'm trying to load a text file taken from SAP system and load into table using BODS.

I downloaded a text file from SAP BW with the following format with header and data like below:

/1B/XBACAB1  /2B/XBACAB2 /3C/XBACAB3 /4D/XBACAB4 /5E/XBACAB5  /6F/XBACAB6 /7G/XBACAB7 /8H/XBACAB8

12345| ABCDE | 34 | SALE | B | CFGUI |1200.00 | X

There are actually 81 fields and its values available in the text file including the header data.

When I try to configure a file format for this input. I have chosed

Delimetters

Column = |

Rows = {new line}

Row with text string = character

Text = None

Input/Output

Style = Headers

When I preview the output for the above settings is strange.

1. It is taking only 71 header fields and remaining fields it is skipping

2. There are 100 records in the file

3. The record values for each row is taken upto 71 fields and the remaining values(71-81 fields) are taken as a second row in the table rather than updating in the remaining 10 fields.

I'm finding it hard to transform the 81 field output data into a text processing file. Each record values are rolling up to the next row rather than updating the corresponding fields.

Has anyone received such issue?

Thanks

Bala

[former_member187605]

Carefully check the format of your files. There's got to be something wrong with it.I can process Excel and csv files with more than 100 columns without any problems.

[former_member206388]

Hi all,

Thanks for the suggestions.
I have got the file corrected and received in csv format and now I can able to load in data services.

Thanks

Bala

[former_member198401]

Hi Bala,

Why dont you use the SAP transport file format.

The Transport_Format is a Data Services predefined file format object for reading flat files from SAP

applications. It is automatically used to define data transport objects added to SAP application data

flows.

The Transport_Format appears in the Format tab of the object library under Flat Files.

The limitation for delimiters is 127 for SAP Transport file format.

For more details refer SAP Supplement Guide

http://help.sap.com/businessobject/product_guides/sbods42/en/ds_42_sap_en.pdf

Regards

Arun Sasi

[Former Member]

Hi Bala,

I think the issue is with the flat file, for troubleshooting you can first paste the data into excel sheet and see how do you see it there.

Regards..

[former_member206388]

Hi Sandeep,

I tried using excel and the result is also same.

The problem is the output of the sap tables with piped delimeters is writing 71 columns and remaining 10 columns are truncated to the next line in the text file.

So excel is taking the first 71 columns for a given record as first row and remaining10 columns as a second row for the same record.

Similarly all the remaining records are coming as 2 rows for single record.

How to avoid this?

Thanks

Bala

[Former Member]

Hi Bala,

There could some setting in SAP to do it, try pulling the data choosing spreadsheet as an option or try fetching data in two parts with 1-40 columns in one and then the remaining ones in the next.

I'll suggest if you are pulling data from sap tables, rather loading it into flat file and then using that flatfile as source in BODS, simply you can use the SAP table as a source in BODS directly.

Regards,

Sandeep

[former_member206388]

Hi Sandeep,

Yes, that would be my last option.

This is a temporary solution and I'm seeing if any feasibility available with BODS to fix this issue.

I'm looking if we can customize the schema in any way.

Thanks

Bala

[Former Member]

Hi Bala,

The only problem here is the source you are using itself has a problem, bods will take the schema of the file and based on the delimiters and next row setting it will flow the data.

If you still want to proceed with the same file then some logic you will have to build in the dataflow to split the 1st 70 columns data into one file and next 10 column data into another and them joining the two, to get the final dataset. You split the data based on odd and even rows.

Regards,

Sandeep