Topic
  • 10 replies
  • Latest Post - ‏2012-02-10T16:53:01Z by bsosale
bsosale
bsosale
22 Posts

Pinned topic Extract data from SA in CSV format using batch job

‏2012-01-10T15:34:49Z |
Hello Everyone,

Happy New Year.

is there anyway to extract data from SA Ency's using batch job? I want the data in CSV format.

Any help is appreciated.

Thanks
Bala
Updated on 2012-02-10T16:53:01Z at 2012-02-10T16:53:01Z by bsosale
  • PeterCW
    PeterCW
    205 Posts

    Re: Extract data from SA in CSV format using batch job

    ‏2012-01-11T14:40:00Z  
    Bala

    Two issues here:
    1 Batch - by that I assume that you mean that you want the job to run on the server without manual intervention and without the need for a client PC to be running and logged in. I am not aware of any integral functionality that will do that although there may be third party software available dependent on server operating system and database.
    2. CSV - why? The integral reporting functions will produce html which is just as portable and a lot more flexible, do you have a real need for csv or is it just a case of your end users not knowing how to use their spreadsheet software?
  • bsosale
    bsosale
    22 Posts

    Re: Extract data from SA in CSV format using batch job

    ‏2012-01-11T16:40:01Z  
    • PeterCW
    • ‏2012-01-11T14:40:00Z
    Bala

    Two issues here:
    1 Batch - by that I assume that you mean that you want the job to run on the server without manual intervention and without the need for a client PC to be running and logged in. I am not aware of any integral functionality that will do that although there may be third party software available dependent on server operating system and database.
    2. CSV - why? The integral reporting functions will produce html which is just as portable and a lot more flexible, do you have a real need for csv or is it just a case of your end users not knowing how to use their spreadsheet software?
    Peter,

    Yes. Periodically I want to extract some of the definitions from SA as csv and use it to load a database. We are using this option as our SA Encyclopedia data is one of the trusted source.

    I am trying to save my time by using automation.

    I know we can use batch job to do publishing.

    Thanks
    Bala
  • RobertoFdez
    RobertoFdez
    102 Posts

    Re: Extract data from SA in CSV format using batch job

    ‏2012-01-12T08:21:33Z  
    • bsosale
    • ‏2012-01-11T16:40:01Z
    Peter,

    Yes. Periodically I want to extract some of the definitions from SA as csv and use it to load a database. We are using this option as our SA Encyclopedia data is one of the trusted source.

    I am trying to save my time by using automation.

    I know we can use batch job to do publishing.

    Thanks
    Bala
    To export data to a comma separated file (csv), use the bcp command line utility:

    bcp [<database_name.>http://<owner>].<table_name> out <csv-file> [/U<username>] [/P<password>] [/S<server_name>] /c /t,

    where:

    <database_name> is an optional parameter specifying the name of the database. It can only be omitted if the desired database is the default database.
    <owner> the name of the table owner.
    <table_name> the name of the table.
    <csv-file> The name of the comma separated file to generate.
    <username> The SQL Server username to use. This can be left blank if NT authentication is used.
    <password> The password to use for the account.
    <server_name> The name of the server on which the database resides. This can be omitted if the database is on the local machine (i.e. the same machine. as the bcp command is issued from).

    for example:

    bcp mydb.dbo.sales out sales.csv /U /P /Ssales_svr /c /t,

    or:

    bcp mydb..resellers out resellers.csv /U /P /Ssales_svr /c /t,



    Extract from: http://www.cryer.co.uk/brian/sqlserver/howtoexportcsv.htm
  • PeterCW
    PeterCW
    205 Posts

    Re: Extract data from SA in CSV format using batch job

    ‏2012-01-12T09:32:27Z  
    • bsosale
    • ‏2012-01-11T16:40:01Z
    Peter,

    Yes. Periodically I want to extract some of the definitions from SA as csv and use it to load a database. We are using this option as our SA Encyclopedia data is one of the trusted source.

    I am trying to save my time by using automation.

    I know we can use batch job to do publishing.

    Thanks
    Bala
    Batch - this is a term that is sometimes used loosely. My personal definition of batch is in my previous post. If you just want to manually start a job on your desktop and let it run in the background then I would use VBA. If your target database is access then it would be possible to input direct using VBA without an intermediate file.
  • bsosale
    bsosale
    22 Posts

    Re: Extract data from SA in CSV format using batch job

    ‏2012-01-12T16:56:33Z  
    • PeterCW
    • ‏2012-01-12T09:32:27Z
    Batch - this is a term that is sometimes used loosely. My personal definition of batch is in my previous post. If you just want to manually start a job on your desktop and let it run in the background then I would use VBA. If your target database is access then it would be possible to input direct using VBA without an intermediate file.
    Peter,

    Thanks. It is not access.

    we are a db2 shop.

    Do you have an example of the VBA?

    Regards
    Bala
  • bsosale
    bsosale
    22 Posts

    Re: Extract data from SA in CSV format using batch job

    ‏2012-01-12T17:00:09Z  
    To export data to a comma separated file (csv), use the bcp command line utility:

    bcp [<database_name.>http://<owner>].<table_name> out <csv-file> [/U<username>] [/P<password>] [/S<server_name>] /c /t,

    where:

    <database_name> is an optional parameter specifying the name of the database. It can only be omitted if the desired database is the default database.
    <owner> the name of the table owner.
    <table_name> the name of the table.
    <csv-file> The name of the comma separated file to generate.
    <username> The SQL Server username to use. This can be left blank if NT authentication is used.
    <password> The password to use for the account.
    <server_name> The name of the server on which the database resides. This can be omitted if the database is on the local machine (i.e. the same machine. as the bcp command is issued from).

    for example:

    bcp mydb.dbo.sales out sales.csv /U /P /Ssales_svr /c /t,

    or:

    bcp mydb..resellers out resellers.csv /U /P /Ssales_svr /c /t,



    Extract from: http://www.cryer.co.uk/brian/sqlserver/howtoexportcsv.htm
    Roberto,

    Thanks. How do I know which fields to pick and what table has those fields?

    is there any documentation on this?

    Regards
    Bala
  • RobertoFdez
    RobertoFdez
    102 Posts

    Re: Extract data from SA in CSV format using batch job

    ‏2012-01-13T08:23:33Z  
    • bsosale
    • ‏2012-01-12T17:00:09Z
    Roberto,

    Thanks. How do I know which fields to pick and what table has those fields?

    is there any documentation on this?

    Regards
    Bala
    It depends on what information you want to back up. If you want to back-up all the encyclopedia any day, I recommend to you create a .bak file every night because you don't need to understand the schema of the db. If you want to extract, for example, all the definitions and symbols, you have to extract table Entity and Relationship. But, as I have told to you, it depends on your needs.
  • bsosale
    bsosale
    22 Posts

    Re: Extract data from SA in CSV format using batch job

    ‏2012-02-10T14:26:03Z  
    It depends on what information you want to back up. If you want to back-up all the encyclopedia any day, I recommend to you create a .bak file every night because you don't need to understand the schema of the db. If you want to extract, for example, all the definitions and symbols, you have to extract table Entity and Relationship. But, as I have told to you, it depends on your needs.
    Hi Roberto,

    Greetings.

    I looked at Entity and Relationship tables and it has all the data I need.

    Only problem is one of the field has 1000 Chars where all the data resides for a particular type.
    is there any way to split this into various fields in csv format ?

    My goal is to create a reporting solution for auditing the changes.

    I am getting closer.

    Thanks
    Bala
  • SystemAdmin
    SystemAdmin
    1061 Posts

    Re: Extract data from SA in CSV format using batch job

    ‏2012-02-10T16:04:14Z  
    • bsosale
    • ‏2012-02-10T14:26:03Z
    Hi Roberto,

    Greetings.

    I looked at Entity and Relationship tables and it has all the data I need.

    Only problem is one of the field has 1000 Chars where all the data resides for a particular type.
    is there any way to split this into various fields in csv format ?

    My goal is to create a reporting solution for auditing the changes.

    I am getting closer.

    Thanks
    Bala
    Bala,

    You mention that your purpose is to "create a reporting solution for auditing the changes".

    Have you considered using the Object History option? This is enabled in SAEM. You can use the Object History report (in the View menu in SA) to then see the history of selected objects.

    Another option is to write to an Excel file using the System Architect API. For each definition of interest, you can output the Last Change Date and Last Change Time. If you use these values to check for changes in successive reports, you can infer what has changed.

    Hope that helps.

    Scott
  • bsosale
    bsosale
    22 Posts

    Re: Extract data from SA in CSV format using batch job

    ‏2012-02-10T16:53:01Z  
    Bala,

    You mention that your purpose is to "create a reporting solution for auditing the changes".

    Have you considered using the Object History option? This is enabled in SAEM. You can use the Object History report (in the View menu in SA) to then see the history of selected objects.

    Another option is to write to an Excel file using the System Architect API. For each definition of interest, you can output the Last Change Date and Last Change Time. If you use these values to check for changes in successive reports, you can infer what has changed.

    Hope that helps.

    Scott
    Thanks Scott,

    I looked at View -> Object History. It is helpful.

    can you send me some examples of Excel with API or link to the documentation?

    Thanks again
    Bala