flat file extraction

[Former Member]

what are the different types of flat files that can be used for data load in BW.

what are the corresponding Escape characters and Data separators for thos types of

flat files?

[former_member188972]

You can import also BIN file but in a more complicated manner:

/people/sergio.locatelli2/blog/2006/10/27/binary-file-in-bw-what-i-can-do-with-control-file

Regards,

Sergio

[Former Member]

Hi Karthik,

I have a PDF here are some extracts from it for your reference.

Excel files use separators to separate fields. In the European version, a semi-colon (

is used as a separator. In the American version, a comma (,) is used. You can also use

other separators. You must specify the delimiter used in the Scheduler.

● Fields that are not filled in a CSV file are filled with a blank space if they are character

fields and with a zero (0) if they are numerical fields.

● If separators are used inconsistently in a CSV file, the “wrong” separator is read as a

character, and both fields are merged into one field and possibly shortened.

Subsequent fields are then no longer in the correct order.

Note the following with regard to CSV files and ASCII files:

● If your file contains headers that you do not want to be loaded, on the External Data

tab page in the Scheduler, specify the number of headers that you want the system to

ignore during the data load. This gives you the option of keeping the column headers in

your file.

● A conversion routine determines whether or not you have to specify leading zeros.

● For dates, you usually use the format YYYYMMDD, without internal separators.

Depending on the conversion routine, you can also use other formats.

● If you use IDocs to upload data, note the 1000 byte limit for each data record length.

This limit does not apply to data that is uploaded using the PSA.

Notes on Uploading

● When you upload external data, you are able to load the data from any workstation into

the BI system. However, from a performance point of view, you should store the data

on an application server and load it from there into the BI system. This also means that

you can load the data in the background.

● If you want to upload a large amount of transaction data from a flat file, and you are

able to specify the file type of the flat file, you should create the flat file as an ASCII file.

From a performance point of view, uploading the data from an ASCII file is the most

cost-effective method. In certain circumstances, generating an ASCII file might involve

a larger workload.

[Former Member]

But, what is the purpose of Escape sign..???

[Former Member]

Hi,

Escape sign is used to differentiate the records. once teh escapesign is reached means a new record is started. If you have any .CSV file with sample data, just open it in the notepad and you can find the difference between the separator and escape sign.

Hope this helps you.....

[Former Member]

If you load data from an Excel CSV file, determine the Data Separator and Escape

Character.

Specify the separator that your file uses to divide the fields in the Data

Separator field.

A separator is part of a value when this value is enclosed within particular start

and end characters in the file. Enter these start and end characters in the

Escape Characters field.

You chose the ; character as the data separator. However, the value 12;45

exists for a field. If you selected “ as the escape character, the value in the

file must be “12;45”, in order for 12;45 to be loaded into the BW system.

[Former Member]

Hi,

I have to extract data from a Notepad that is .TXT file.How this can be done. Please help me out...

Regards

Arpita

[Former Member]

hai

if u r extracting from .txt (text file) u have to maintain the data in comma separated format,

segregate each field with comma and in extraction tab in datasource use deliminator as ","

[Former Member]

Karthik,

You can load data using flat file in two formats.

Comma Separated Values(CSV), field separating characters (such as and sentence ending characters (CR, Carriage return).

Secondly in ASCII format.In this format fixed field lenghts are used to differentiate feilds for the individual data record.

[Former Member]

Hi,

it's either .csv (comma seperated) or ascii.

I'd recommend to use either Pipe | or else semicolon ;.

Anyway, you must be sure that the data transfered doesn't contain any seperators as data.

For ASCII, seperation is done by the field-lenth.

So you must make sure that the data transfered corresponds always with the expected length for the fields.

For administrative purposes (searching for erreors etc.) it's better to have csv.

The document-overhead for ASCII is less, due to the additional seperators that are not needed.

Escape, as far as I know is ".

hth

cheers

sven