| Renaming column [message #2242] |
Mon, 22 July 2024 14:48  |
slprakash
Messages: 4
Registered: July 2024
|
Junior Member |
|
|
|
I have loaded data from SQL Database. I want to rename multiple columns without manually renaming. Is there a way to automate the renaming process if I have a mapping document (which will have new name and old name in the format <old name> as <new name>)
|
|
|
|
|
|
| Re: Renaming column [message #2274 is a reply to message #2242] |
Mon, 12 August 2024 04:17  |
yuan
Messages: 6
Registered: May 2024
|
Junior Member |
|
|
Hi slprakash,
I used the following SQL script to Query my SQL database and renamed some columns in it. You can modify it to fit your purpose. Besides, you can use complex SQL query to join data from different tables. Hope it can give you some help.
<macro name="QuerySQLDB">
<task name="runSQLQuery">
connect=mysql://your_database/your_query_table (e.g. mysql://sql.xxxx.com/chemicaldb)
sql=SELECT<NL> smiles,<NL> CONCAT(structure_ID) AS ID,<NL> CONCAT(weight) AS MW,<NL> CONCAT(mass,' ',mass_unit) AS mass,<NL>
</task>
<task name="selectWindow">
viewName=Custom SQL Result
</task>
<task name="closeView">
viewName=2D View
</task>
<task name="closeView">
viewName=3D View
</task>
<task name="closeView">
viewName=Structure of smiles
</task>
<task name="addCalculatedValues">
columnName=ParentID
isOverwrite=false
formula=substring(ID,1,15)
</task>
<task name="sortRows">
column=ParentID
selectedFirst=false
descending=true
</task>
</macro>
comments:
1) Save it as a .dwam file and then use "Macro-->Import Macro-->Run Macro" to run the script file.
2) CONCAT(structure_ID) AS ID ---- will rename structure_ID column in your database as ID in DataWorrior
3) CONCAT(weight) AS MW ---- will rename weight column in your database as MW in DataWorrior
4) CONCAT(mass,' ',mass_unit) AS mass ---- will merge mass and mass_unit in your database as mass in DataWorrior
5) <task name="addCalculatedValues">
columnName=ParentID
isOverwrite=false
formula=substring(ID,1,15)
</task>
---- will add calculated values (ParentID column in DataWorrior) from ID column
6) <task name="sortRows">
column=ParentID
selectedFirst=false
descending=true
</task>
---- will sort table by previously calculated ParentID
[Updated on: Mon, 12 August 2024 04:18] Report message to a moderator |
|
|
|
|
|
Search
Help
Members
Register
Login
Home
