Automatic Import of File Definition?

patonp · Post by **patonp** » Mon Feb 20, 2006 11:14 am

Unfortunately, I think I already know the answer to this question, but I thought I'd ask anyway...

I've been provided with a file definition in spreadsheet format. The spreadsheet includes the following columns:

- Column Name
- SQL Data Type
- Field Length
- Precision (if applicable)

Is there any way to import this definition into DataStage? As there are roughly 500 column definitions listed in the file, it would save me a lot of manual effort!

Thanks!

Peter

kcbland · Post by **kcbland** » Mon Feb 20, 2006 11:18 am

Turn it into DDL and create a table. All that really matters for a Sequential file stage is column name, position, length (if fixed width then it's really the DISPLAY column that's used, and scale. The data type can be all VARCHAR if it's a delimited file.

ArndW · Post by **ArndW** » Mon Feb 20, 2006 11:32 am

Does your metadata import in your manager contain a tab to import a px schema? If so, you could reformat your spreadsheet in a macro to make a PX schema file and load that into your DS installation. I think it will show up even on non-px installs.

patonp · Post by **patonp** » Mon Feb 20, 2006 12:19 pm

Thanks for the responses.

Arnd - can you outline the required format for the Excel file? As would be expected, I'm receiving the following error when I simply try to import the csv file "as is".

"No schemas found in file H:\tmp\file1.csv. View contents?"

Is there a way for me to create a shema file?

Thanks again for your help.

Peter[/img][/list]

ArndW · Post by **ArndW** » Mon Feb 20, 2006 12:25 pm

You can't just import the Excel meta-metadata. But if you can import a PX Schema it would save you writing a macro to create the DDL to load into the database of your choice and from there to load into the DataStage repository. So, does your manager show capability for loading schemas?

patonp · Post by **patonp** » Mon Feb 20, 2006 12:34 pm

I have an option to "Import Orchestrate Schema". Does that count?

ArndW · Post by **ArndW** » Mon Feb 20, 2006 12:47 pm

Yes, it does

I'm not at a PC with a DataStage installation, or even the documentation, right now so any statements I make should be cross checked for syntax and other errors. The format is quite simple, something like:

Code: Select all

record (
{columnName}:{DataType};
...
)

The DataTypes are named a bit differently from SQL datatypes, but not significantly. If you have 7.5 then you can get the exact format in the Parallel Developer's Guide.

So all you need to do is make a formula in your .XLS sheet to make the appropriate schema format line and export that to a text file.

Now that I've written it, I'm thinking that if you have complex datatypes then it might be best to use Ken's suggestion of creating the DDL directly, since you don't have to worry about re-mapping SQL datatypes, then running that in some database and loading the metadata into DataStage.
It would work the same, a new column in Excel with a formula creating the DDL line; then copying that new column and pasting it (as values) to a text file that can be executed by SQL.

kcbland · Post by **kcbland** » Mon Feb 20, 2006 12:51 pm

On Server jobs the data types in a Sequential file are irrelevant for reading the data. All that matters is column names, order, and size (if fixed width). Therefore, thru some simple Excel spreadsheet manipulation, you could simply take your column names and create a DDL statement using VARCHAR and the size for the column. Then, create a table and you'll be 99% of the way to your definitions.

patonp · Post by **patonp** » Mon Feb 20, 2006 1:08 pm

Thanks folks - that's been very helpful.

BTW, I should mention that the Import-->Orchestrate Schema Definitions option is only available (as far as I can see) in the Enterprise Edition of the product, so other users viewing this thread should consider this when selecting the best solution.