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
Automatic Import of File Definition?
Moderators: chulett, rschirm, roy
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.
Kenneth Bland
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
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.
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
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]
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]
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?
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
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:
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.
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};
...
)
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.
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
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.
Kenneth Bland
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle