How to export point tab data to excel using a script

Hi,

I have gINT files with multiple point IDs and want to export each POINT data to separate excel files with different names. How can I do it writing a script ? 

Below is what I tried but need a bit of guidance to improve it. I am not sure what needs to be included in script to select a specific table (i.e. POINT in this case)

**Operation
Input

**Command
ExportExcel

**Properties
Project=C:\Users\bh master_2.gpj

**Spec
DataFile=C:\Users\\Point_BH372.xls
PointIDList=BH372

Thank you

Regards

Aruna

  • One way to export only the point table is to create a correspondence file that omits all data that you do not want exported, then specify that corspondence file in the script. There may be other ways. 

    The hard part if your request is to export each point to a separate file. There are ways to do this using a text document report to create the script after selecting which borings wou want to export. It involves a couple of steps to export but can save time when you want to export many borings or do this for several projects

    I am traveling now but I will post an example next week when I return. It might help to know more about what your goal is in exporting multiple files. There may be better ways to acomplish your desired result. 

  • Thanks for your reply. Please see below, which summaries what I am planning to do. I might have to repeat this process down the track. I thought having a script will save bit of time.

    Look forward to hearing from you.

    Thank you,

    Regards,

    Aruna

  • This will be a long post so please be patient.

    First to reiterate what I believe you are trying to accomplish.

    1. You have 50 individual gINT files.  For talking purposes let us assume that each has 3 borings in it.
    2. You have merged all 50 individual files into one master file.  This has all 150 borings in it.
    3. You have edited the point table of the master file by adding some data and changing some data.
    4. You want the changes that you made to be reflected in the original 50 gINT project files.  To do this you want to:
      1. Export just the point table for each boring to an individually named excel file that will have the point table data for only that boring in it
      2. Open each of the original 50 gINT project files and import the relevant excel files created in 4a for the borings contained in that file.  With the proper overwrite options this will update the point table for each boring imported.

    While this procedure should work, there are a few of potential issues that I will discuss latter in the post.  Further there are other approaches that may be better suited that I will present at the end of the post.

    The first step to accomplishing 4a above is to create a correspondence file that will allow you to export only the point table from the master file.  A correspondence file is a text file that relates the table names and field names of the source and target file. The easy way to do this is to use the correspondence file editor in the data design tab. Set the source and target files to your current data template file (.gdt).  This will prepopulate the correspondence file target with all the table and field names in your data structure.  See image below.

    Note that I have selected the Point table and then specified the POINT table as the default table for the point table.  Doing this means I do not have to complete all the source expressions in the grid below as I want them all to be the same as the target. The next step is to get rid of all the other tables in the correspondence file because we want gINT to ignore these during our export process.  You could do this by selecting each target table in your data structure and selecting <Omit> as the default table.  The other way to do this is to select view as text file from the menu and then simply delete all sections except the point table section.  I saved this file as exportpoint.gcx in gINT's data template directory.  You can open it and edit it outside of gINT in any text editor such as notepad. A portion of my final correspondence file is shown below.

    Note that the first line is the table name  for the target followed by the default table name for the source table separated by a comma.  Below that are all the field names in the point table followed by a comma (ie the corresponding source is left blank).  You can test this correspondence file by doing a manual export to excel operation and specifying this file as the correspondence file.

    The second step is to create a script file that will export each boring to a separate excel file.  Scripts are linear in nature, ie there is no way they can branch or loop that I am aware of.  Thus you have to create a script that contains individual instructions for each of the borings that you want to export. To do this, I use a gINT text report that will create the script based on the borings I select in the output tab. Start a new text report in the report design tab. It should have a key set of Point. then enter the desired script text using expressions to customize it for each boring.  See the image below

    Note that under **properties I have used the data item <<ProjectFileSpec>> to insert the current project file and path into the output. Under **Spec, for datafile I have used an expression that creates an excel file name using the boring number and places it in the same path as the project.  for correspondence file it is simply the path and name of the correspondence file created in step 1. The PointIdList is simply the current pointID. I named it EXPORT_EXCEL

    Once this report is created, I can simply export the report to a text file that will serve as my script. An example of what this report outputs if I select 4 of the borings in my master file is shown in the code window below.

    **Operation
    Input
    
    
    **Command
    ExportExcel
    
    
    **Properties
    Project=C:\USERS\PUBLIC\DOCUMENTS\BENTLEY\GINTCL\PROJECTS\HOMEWORK2018.GPJ
    
    
    **Spec
    DataFile=C:\USERS\PUBLIC\DOCUMENTS\BENTLEY\GINTCL\PROJECTS\ALPHA-1.xlsx
    CorrespondenceFile=C:\Users\Public\Documents\Bentley\gINTCL\datatmpl\exportpoint.gcx
    PointIdList=ALPHA-1
    
    
    **Operation
    Input
    
    
    **Command
    ExportExcel
    
    
    **Properties
    Project=C:\USERS\PUBLIC\DOCUMENTS\BENTLEY\GINTCL\PROJECTS\HOMEWORK2018.GPJ
    
    
    **Spec
    DataFile=C:\USERS\PUBLIC\DOCUMENTS\BENTLEY\GINTCL\PROJECTS\ALPHA-2.xlsx
    CorrespondenceFile=C:\Users\Public\Documents\Bentley\gINTCL\datatmpl\exportpoint.gcx
    PointIdList=ALPHA-2
    
    
    **Operation
    Input
    
    
    **Command
    ExportExcel
    
    
    **Properties
    Project=C:\USERS\PUBLIC\DOCUMENTS\BENTLEY\GINTCL\PROJECTS\HOMEWORK2018.GPJ
    
    
    **Spec
    DataFile=C:\USERS\PUBLIC\DOCUMENTS\BENTLEY\GINTCL\PROJECTS\B-1.xlsx
    CorrespondenceFile=C:\Users\Public\Documents\Bentley\gINTCL\datatmpl\exportpoint.gcx
    PointIdList=B-1
    
    
    **Operation
    Input
    
    
    **Command
    ExportExcel
    
    
    **Properties
    Project=C:\USERS\PUBLIC\DOCUMENTS\BENTLEY\GINTCL\PROJECTS\HOMEWORK2018.GPJ
    
    
    **Spec
    DataFile=C:\USERS\PUBLIC\DOCUMENTS\BENTLEY\GINTCL\PROJECTS\B-10.xlsx
    CorrespondenceFile=C:\Users\Public\Documents\Bentley\gINTCL\datatmpl\exportpoint.gcx
    PointIdList=B-10
    
    
    

    Note that it just repeats the same export operation over and over but changes the datafile and PointIdList each time.  It can be 150 or 1000 points long if you want.  I tested this script and got the desired excel files containing only the point table for each boring.

    Note that you can use a similar approach to create a script file to import multiple excel files based on the borings selected in each of your original 50 files.  You will not have to deal with a corespondence file but you will have to include the data structure and an overwrite option. I can help with this if you want.

    Some cautions with this approach:

    1. You are transferring data using an intermediate file. Although the above script will overwrite existing excel files, you will generate a large number of files that is easy to lose track of.
    2. If you have changed a boring name in the master file (for example changing B-5 to B-005 for consistency), when you go to import it into the original file, it will create a new boring rather than change the exiting boring.  This is because all the daugther tables to the point table will be keyed to the original point ID.  There is a way around this that I will describe below

    There are two other approaches to accomplishing your desired goal

    Approach A - Splitting corrected master file into new individual files

    Once you have corrected the master file as desired, you can split it into new files that contain the same borings as the original 50 gINT files. This is a simple manual export to database.  The set up of the export to database window is shown below

    The template to create new database is you current gdt file.  The database is the name and path where your new project will be created.  Do not select the name and path of your original project as it will be overwritten.  You can use the never overwrite option in this case because the file is new and empty. You must select the borings to be exported to the new project which means you have to have a record of which borings were in which project. This approach solves the problem of changing the boring name discussed above as all the daugheter tables will have been automatically updated when you changed it in the master file.  It has the following drawbacks.

    1. The project table in each of the newly created projects will be the same as in the master file.  If each of the original project files had different entries in the project table you will need to recreate them in the new files (or import them)
    2. It will need to be done 50 times but that is the same as importing the excel files 50 times.
    3. If you (or others) have made changes in the original 50 project files (such as adding lab test data or correcting descriptions).  These changes will be lost.

    Approach B - Direct merge of point table

    It is not necessary to use excel as an intermediate file to transfer the corrected point table data back to the original files.  Simply open each of the original files and import the point table from the master project for the borings contained in that file.  The Import window would look like the image below.

    In the above, the data base would be your master file, I have selected the point table as the import, and I have set the overwrite option to records which will replace the whole point record for each boring in the current file.  When I go to select which borings to import from the master file, it shows which borings in the current file match the borings in the master file.  If you have changed the boring name in the master file this will not work.  This has the advantage of not needing any intermediate excel files or a correspondence file.  it can sometimes be challenging to select the correct overwrite option.  You will have to do this 50 times in each original file but this is similar to what you would be doing using excel and script.

    Hope this helps.  Note that all examples are shown using my testing project and my data structure.  Yours will likely be different and you will have to modify accordingly.  The data shown may not make sense, it is for testing purposes only.