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)
**OperationInput
**CommandExportExcel
**PropertiesProject=C:\Users\bh master_2.gpj
**SpecDataFile=C:\Users\\Point_BH372.xlsPointIDList=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,
This will be a long post so please be patient.
First to reiterate what I believe you are trying to accomplish.
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:
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.
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.