Problem in reading the Excel data with sequential file

Sridhar Sivakoti · Post by **Sridhar Sivakoti** » Tue Sep 01, 2009 5:33 am

Hi,

When I am reading the data from Excel sheet by using sequential file stage I am getting the below error
Delimiter for field DB_Office not found

Please let me know what should be the Record level final delimiter, Field Delimiter and Quote values.

Regrds
Sri

ArndW · Post by **ArndW** » Tue Sep 01, 2009 5:35 am

You are probably reading a .csv file, not an excel spreadsheet. could you post a sample line or three?

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Tue Sep 01, 2009 5:35 am

Where is the source file?

Can you view it using vi ?

chulett · Post by **chulett** » Tue Sep 01, 2009 5:40 am

ArndW wrote:You are probably reading a .csv file, not an excel spreadsheet. could you post a sample line or three?

I wager 400 Quatloos on it being a .xls file.

ArndW · Post by **ArndW** » Tue Sep 01, 2009 5:43 am

That's a lot of Quatloos, back in the day I could get a thrall for a mere 2000Qt's . I think I'll not wager on this one...

Sridhar Sivakoti · Post by **Sridhar Sivakoti** » Tue Sep 01, 2009 6:01 am

I am reading the Excel file which is in Datastage server(Unix box), at present I am not having the this Unix access, but the same excel sheet I am having on my desktop.

Please let me know if you need any other information.

Regards
Sri

stefanfrost1 · Post by **stefanfrost1** » Tue Sep 01, 2009 6:11 am

If it is indeed a csv or skv or any -separated file, then your problem probably is on the last or second last column if the last column is empty. Some versions of Excel seems to export data skipping empty fields at the end. This means that datastage will find an early end marker and skip or reject that record due to error in import. This can be fixed by having space at empty columns in excel or having a better export utility for excel or using column importer in datastage and reading all columns from the file as one big column, later parsing it and allowing nulls in your new imported column.

chulett · Post by **chulett** » Tue Sep 01, 2009 7:29 am

You need to clarify if this is a native xls file or one you've saved as a csv or prn or some other delimited format first.

ray.wurlod · Post by **ray.wurlod** » Tue Sep 01, 2009 4:20 pm

Try setting Final Delimiter = End

ArndW · Post by **ArndW** » Wed Sep 02, 2009 1:04 am

Sridhar Sivakoti wrote:I am reading the Excel file which is in Datastage server(Unix box)...

As has been asked several times before - what format is this file in? If native Excel format then you cannot read it directly. You might be able to find a free ODBC UNIX driver (or buy ConnectDirect) and then use the ODBC connectivity inside DataStage to read the file.

Sreenivasulu · Post by **Sreenivasulu** » Wed Sep 02, 2009 10:31 am

Hi,

You can use free jars (java executables) like jxl.jar to read excel from unix

Regards
Sreeni

DSXchange