openmolecules.org Forum: Functionality

Home » DataWarrior » Functionality » Renaming column

Show: Today's Messages :: Polls :: Message Navigator

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>)

Report message to a moderator

Re: Renaming column [message #2248 is a reply to message #2242]

Mon, 22 July 2024 17:15

thomas
Messages: 715
Registered: June 2014

Senior Member

Columns cannot be renamed, because the original column name is used in many places to refer to a column. You can add an alias, though. This causes to display the alias instead of the original column name.

Currently, you cannot set column alias defined by a mapping file. But maybe you can do one of these as a workaround:
- define target column names in the SQL statement as: SELECT user_id ID, user_name Name FROM ...
- run a macro (dwam) or apply a template (dwat) to define aliases for you dwar document

Report message to a moderator

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

Re: Renaming column [message #2286 is a reply to message #2248]

Thu, 22 August 2024 13:07

slprakash
Messages: 4
Registered: July 2024

Junior Member

Thanks for the prompt response !

Report message to a moderator

Previous Topic:	Use label in Form Table
Next Topic:	chemical complexity calculation

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

] [

]

Current Time: Thu Nov 21 18:48:54 CET 2024

Total time taken to generate the page: 0.03358 seconds