can't read all records of .csv file

h4harry1 · Post by **h4harry1** » Sun Mar 20, 2011 5:56 pm

Hi All,
I am new to datastage. I am using an excel sheet which is saved as .csv as a source file, containing 30 records. Reading this file using sequential file stage. My job is reading only 15 records from this file and then it throw some warnings.
I physically checked .csv file and can't find any problem after record 15.
Can't understand why this is happening. Any help is highly appreciated.

Following is log file of job:-
DataStage Report - Summary Log for job: first
Produced on: 3/21/2011 10:52:17 AM
Project: test Host system: MAC01
Items: 1 - 29
Sorted on: Date Sorter
Entries are filtered

Occurred: 10:49:22 AM On date: 3/21/2011 Type: Reset
Event: Log cleared by user

Occurred: 10:49:44 AM On date: 3/21/2011 Type: Control
Event: Starting Job first.

Occurred: 10:49:50 AM On date: 3/21/2011 Type: Info
Event: Environment variable settings: (...)

Occurred: 10:49:50 AM On date: 3/21/2011 Type: Info
Event: Parallel job initiated

Occurred: 10:49:51 AM On date: 3/21/2011 Type: Info
Event: main_program: IBM WebSphere DataStage Enterprise Edition 8.0.1.4458 (...)

Occurred: 10:49:52 AM On date: 3/21/2011 Type: Info
Event: main_program: orchgeneral: loaded (...)

Occurred: 10:49:57 AM On date: 3/21/2011 Type: Info
Event: main_program: APT configuration file: C:/IBM/InformationServer/Server/Configurations/default.apt (...)

Occurred: 10:49:57 AM On date: 3/21/2011 Type: Warning
Event: Input,0: Delimiter for field "IMPACTED_USERS___FTA" not found; input: {0}, at offset: 422

Occurred: 10:49:57 AM On date: 3/21/2011 Type: Warning
Event: Input,0: Import warning at record 15.

Occurred: 10:49:57 AM On date: 3/21/2011 Type: Warning
Event: Input,0: Import unsuccessful at record 15.

Occurred: 10:49:57 AM On date: 3/21/2011 Type: Warning
Event: Input,0: Delimiter for field "IMPACTED_USERS___FTA" not found; input: {0}, at offset: 396

Occurred: 10:49:57 AM On date: 3/21/2011 Type: Warning
Event: Input,0: Import warning at record 16.

Occurred: 10:49:57 AM On date: 3/21/2011 Type: Warning
Event: Input,0: Import unsuccessful at record 16.

Occurred: 10:49:57 AM On date: 3/21/2011 Type: Warning
Event: Input,0: Delimiter for field "IMPACTED_USERS___FTA" not found; input: {0}, at offset: 406

Occurred: 10:49:57 AM On date: 3/21/2011 Type: Warning
Event: Input,0: Import warning at record 17.

Occurred: 10:49:57 AM On date: 3/21/2011 Type: Warning
Event: Input,0: Import unsuccessful at record 17.

Occurred: 10:49:57 AM On date: 3/21/2011 Type: Warning
Event: Input,0: Delimiter for field "IMPACTED_USERS___FTA" not found; input: {0}, at offset: 346

Occurred: 10:49:57 AM On date: 3/21/2011 Type: Warning
Event: Input,0: Import warning at record 18.

Occurred: 10:49:57 AM On date: 3/21/2011 Type: Warning
Event: Input,0: Import unsuccessful at record 18.

Occurred: 10:49:57 AM On date: 3/21/2011 Type: Warning
Event: Input,0: Delimiter for field "IMPACTED_USERS___FTA" not found; input: {0}, at offset: 350

Occurred: 10:49:57 AM On date: 3/21/2011 Type: Warning
Event: Input,0: Import warning at record 19.

Occurred: 10:49:57 AM On date: 3/21/2011 Type: Warning
Event: Input,0: Import unsuccessful at record 19.

Occurred: 10:49:57 AM On date: 3/21/2011 Type: Info
Event: Input,0: No further reports will be generated from this partition until a successful import.

Occurred: 10:49:57 AM On date: 3/21/2011 Type: Info
Event: Input,0: Import complete; 15 records imported successfully, 19 rejected.

Occurred: 10:49:57 AM On date: 3/21/2011 Type: Info
Event: Sequential_File_35,0: Export complete; 15 records exported successfully, 0 rejected.

Occurred: 10:49:57 AM On date: 3/21/2011 Type: Info
Event: main_program: Step execution finished with status = OK.

Occurred: 10:49:57 AM On date: 3/21/2011 Type: Info
Event: main_program: Startup time, 0:05; production run time, 0:00.

Occurred: 10:49:57 AM On date: 3/21/2011 Type: Info
Event: Parallel job reports successful completion

Occurred: 10:49:58 AM On date: 3/21/2011 Type: Control
Event: Finished Job first.

End of report.

ray.wurlod · Post by **ray.wurlod** » Sun Mar 20, 2011 6:26 pm

In short, not enough fields (= not enough delimiters) in some records.

DataStage parallel jobs expect/require consistent format.

Use server Sequential File stage if you need to handle missing columns. Or read the line as a single string and parse it within your job.

h4harry1 · Post by **h4harry1** » Wed Mar 23, 2011 3:38 pm

Thanks for the reply......
It is excel sheet, data is entered in cells, some of the cell values are empty...but can't see any issue with the delimiters.

Moreover it reads first fifteen records perfectly and ignore rest of the data with warning.
The excel file format looks same from the first record till last. Can't understand why this is happening ?

ray.wurlod · Post by **ray.wurlod** » Thu Mar 24, 2011 12:53 am

Any comma in the data in row #16? Are the last fields in row #16 empty?

PhilHibbs · Post by **PhilHibbs** » Thu Mar 24, 2011 4:49 am

h4harry1 wrote:Thanks for the reply......
It is excel sheet, data is entered in cells, some of the cell values are empty...but can't see any issue with the delimiters.

If you are looking in Excel, then you won't see it. Open the CSV file in a text editor and count the commas. I have found that it is generally a bad idea to save an Excel sheet as a CSV and expect it to work in DataStage. I have written an Excel VBA macro that processes Excel sheets and writes them out as CSV files, with optional formatting e.g. date formats, zero padding, etc. that I use to generate CSV files for DataStage job inputs.