DSXchange

Hi!

I have XLS file as my data source. What sequential file format should I specify?

Thanks.

Rochelyn

That depends how you want to get it out of Excel. (Im assuming that is what you mean by XLS)

You wont be able to read the file directly as a sequential file.

Either you will need to export (save as) the data in a sequential format (for example, CSV) or if you are running DataStage on an NT server and the file is on the server, you might be able to use ODBC to read it.

Hope this helps

David

-----Original Message-----
From: Rochelyn Gamao [mailto:rochelyn@summit.com.sg]
Sent: Saturday, 24 February 2001 7:47
To: informix-datastage@oliver.com
Subject: XLS file data source


Hi!

I have XLS file as my data source. What sequential file format should I specify?

Thanks.

Rochelyn

Thank you very much David!

----- Original Message -----
From: "David Barham"
To:
Sent: Saturday, February 24, 2001 10:07 AM
Subject: RE: XLS file data source


> That depends how you want to get it out of Excel. (Im assuming that
> is what you mean by XLS)
>
> You wont be able to read the file directly as a sequential file.
>
> Either you will need to export (save as) the data in a sequential
> format (for example, CSV) or if you are running DataStage on an NT
> server and the file is on the server, you might be able to use ODBC to
> read it.
>
> Hope this helps
>
> David
>
> -----Original Message-----
> From: Rochelyn Gamao [mailto:rochelyn@summit.com.sg]
> Sent: Saturday, 24 February 2001 7:47
> To: informix-datastage@oliver.com
> Subject: XLS file data source
>
>
> Hi!
>
> I have XLS file as my data source. What sequential file format should
> I specify?
>
> Thanks.
>
> Rochelyn
>

this is a repost as my original post did not make it to the list...


You could save as a csv, or like david said if you are on NT you can use odbc. To access it via odbc set up a system dsn to point at your xls file. Then in the manager select odbc, and use the dsn, but select show system tables In the list of tables you will have Sheet1, Sheet2, Sheet3 and any ranges you have specified. Select the worksheet you need, and the metadata will be brought in.

There is one other way on NT to do this, you can use the OLE/db stage to bring in the xls information. Its something Im working on at the moment, but you might want to try it yourself. you can use the OLE/db stage to connect to the OLE/DB provider for Jet, and have a custom connection string that gets Jet to attach the xls sheet. Im currently looking at the sql command then needed to bring in the rows, but it should work providing you get the sql right. It also should be quicker than odbc...

Either of these have pros and cons, it depends what you want to do which is the best.

I hope that helps

Andrew Webb
Principal Presales Consultant
Ascential Software
www.ascentialsoftware.com
Mobile : +44 (0)777 5762347
Direct : +44 (0)20 8818 1025
Switchboard : +44 (0)20 8818 1000
Fax: +44 (0)20 8818 1064

Email: andrew.webb@ascentialsoftware.com




-----Original Message-----
From: Rochelyn Gamao [mailto:rochelyn@summit.com.sg]
Sent: 23 February 2001 09:47 PM
To: informix-datastage@oliver.com
Subject: XLS file data source



Hi!

I have XLS file as my data source. What sequential file format should I specify?

Thanks.

Rochelyn

Thanks a lot Clif!

----- Original Message -----
From: "Moderator"
To:
Sent: Saturday, March 03, 2001 1:48 AM
Subject: Re: XLS file data source


> A word of caution if you use the Save As csv method. Using Excel 97
> and 2000, I have discovered that if you have empty columns at the
> beginning or end of some rows, Excel is inconsistant with whether or
> not it will include them. In other words, it sometimes stores fewer
> fields on the line than the number of columns you have selected. When
> DataStage hits this, it aborts the job.
>
> The only solution I have found so far is to make sure you select and
> save column headings as the first row. Then Excel seems to store ,,
> correctly.
>
> Also watch out for and within a cell. These get saved as is
> and cause the csv line to break into multiple lines (records). Again,
> your DS job will abort with invalid number of columns.
>
> Regards,
>
> Clif
>
>
> Friday, March 02, 2001, 1:36:30 AM, you wrote:
>
> > You could save as a csv, or like david said if you are on NT you can
> > use
>
>

A word of caution if you use the Save As csv method. Using Excel 97 and 2000, I have discovered that if you have empty columns at the beginning or end of some rows, Excel is inconsistant with whether or not it will include them. In other words, it sometimes stores fewer fields on the line than the number of columns you have selected. When DataStage hits this, it aborts the job.

The only solution I have found so far is to make sure you select and save column headings as the first row. Then Excel seems to store ,, correctly.

Also watch out for and within a cell. These get saved as is and cause the csv line to break into multiple lines (records). Again, your DS job will abort with invalid number of columns.

Regards,

Clif


Friday, March 02, 2001, 1:36:30 AM, you wrote:

> You could save as a csv, or like david said if you are on NT you can
> use