Hi guys,
I need to add spaces before column name.
I used column rename node but the space didn’t appear in the generated column … can u please help me how can I do that.
Hi @Mai_Nashaat , I’m not sure that’s possible. It appears that Knime removes the space, and this is most probably because it can create problems.
Is there any reason you want to add start a column name with a space? This can create confusion and drive users crazy when they can’t see the space and think that there is no space there.
1 Like
unfortunately I have a folder has more that 60 reports and all of them start with space.
I use this folder in power bi report, recently I’m using knime to add these reports to the folder
Hi @Mai_Nashaat , I’m not sure what folder names or report names have to do with column names. Can you explain the relationship?
1 Like
Using spaces in column names is not a good practice and using space(s) at the beginning or at the end of a column name is the worst case I can imagine.
Still, some things cannot be fixed that easily and maybe you need to be able to handle your column names with those spaces at the beginning. Your question made me try to create a few columns in a mySQL table and see what happens if we have spaces at the beginning. I could create multiple columns with the same name starting with different number of spaces at the beginning but when I read them in KNIME, the column names are changed like when you try to add columns with duplicate names (duplicateColumn (#1), duplicateColumn (#2), …). Still, I think this is not the problem; the real problem is inserting or updating records for those columns for which I couldn’t find any solution in KNIME.
I would never name a column like this (starting with spaces) but I asked myself what if I was asked to work with a client’s database and I encountered such columns. Should I ask the client to change the column names in all of their tables and then change every piece of work that reads or writes to those tables?
Although this is very improbable I like to be able to handle these cases as well in KNIME.
4 Likes
Hi @armingrudd , yes Knime eliminates the spaces at the beginning.
@Mai_Nashaat This is what I have in mind. If you are going to export the result as a CSV or Excel, then the column header can just be the first line of the data 
Besides, that’s what the export probably does in the background: Add header as first line, then add the data.
So, based on that, just move the header to the first line, make the modification on the targeted headers, and simply export by making sure we do not export the headers.
Something like this:
Input:
I extract the headers, and add the space for the columns “Amount in LC” and “Arrears”, which are column 7 and 8, so I use the String Manipulation (Multi Column) since I’m doing the same operation on both columns:
join(" ", $$CURRENTCOLUMN$$)
Concatenate back with the data:
You can see the space was added at the beginning for the 2 columns.
When exporting to CSV, just make sure that this box is unchecked:
And similarly for Excel:
This is what the CSV file looks like:
You can see the space in both column names
And this is what the Excel file looks like:
If you look at the top for this column, you can see a space between the cursor and “Amount”.
Note: After the Concatenate, these 2 columns got converted to “dataValue” type, which the CSV Writer and Excel Writer complained about. I had to change their type before writing. I did this via a Column Rename:
Here’s the workflow: Add space at the beginning of column name.knwf (20.2 KB)
EDIT: @armingrudd in terms of DB writing, this would not necessarily work, since a table column name would rely on the Knime table header. I tried replacing back the header with the first line, Knime just removes the space (same with Column Rename or Column Rename (Regex))
3 Likes
Hi @armingrudd , I pushed some tests further. In this case, I’m using H2 (virtual) for testing.
I execute this in the DB SQL Executor:
CREATE TABLE PUBLIC.test(
"COL1" varchar(100)
, "COL2" varchar(100)
, " COL2" varchar(100)
);
INSERT INTO PUBLIC.test VALUES ('abc', 'xyz', '123');
After executing it, Knime has problem fetching the structure of the table:
It executed properly though.
And from the DB Query Reader, this is what I get:
The column is in fact stored as " COL2", you can see it here:
Pushing this a bit further, I tried reading from the CSV that I created for this thread, and write this to H2. The idea is to stream, hoping that it will write the data as is, including the headers as column names.
Unfortunately, that is not the case. The table is still interpreted in Knime first, therefore it removes the space:
That being said, you could use the DB SQL Executor to write to your DB tables with the column names that you want with the use of aliases, as I did in my test.
EDIT: Just to clarify my last comment, the idea would be to write your data from the Knime table to an H2 table for example, and then using the DB SQL Executor, you would do an INSERT SELECT where you insert into your db table while reading from the H2 table, and you can then use alias when you read from your H2 table (SELECT “Amount in LC” AS " Amount in LC" etc) - that is if your system allows you to do an INSERT SELECT directly without having to create the table first.
Or if it’s writing to an existing table, then you can just reference to the destination table name.
Or if it’s a new table, you can create the table beforehand like I did
1 Like
So your suggesting to insert the new records into a fake table first and then copy them to the main table?
Although that sounds doable, I doubt that this is a practical solution. what if the workflow is used as a service and several jobs are running at the same time. All inserting to the fake table and all copying the records to the main. Maybe I have not understood your idea perfectly.
It’s not the most practical solution of course, but I don’t see how this can be done currently.
The idea of loading into a virtual DB is to take advantage of the DB SQL Executor where you can write your final data to columns whose name start with a space in your DB table.
If you have a better idea, please do share 
EDIT: Actually I just realized that this would not work either. I was trying to create a demo for you, but I can’t use 2 different systems in the DB SQL Executor - the original idea was to read from H2 and write to let’s say MSSQL. The DB SQL Executor can only use 1 connection at a time, so we can’t do an INSERT SELECT.
The only thing that I can think of then is to load the data into your DB system as is, but to a temporary (physical) table, and then do the INSERT SELECT from there using the DB SQL Executor. This would work.
1 Like
My idea would be asking KNIME to allow leading and trailing whitespaces in column names.
Also some UI changes may be needed as well to make sure users don’t get confused when there are leading or trailing spaces. Like applying some contrast to column name background color in data table views.
Thank you @bruno29a for taking your time to provide a workaround. Of course when there is no other solutions, one must do whatever possible. It’s great to have you in the KNIME community. 
1 Like
The same goes on my side, and that’s why I’m not too keen about requesting that Knime supports it.
However, to your point, DB tables support it, and you can get files with such headers, and as you mentioned, you could be in a situation where:
So, for these reasons, I’m gonna request a +1 for me too for Knime to support leading and trailing space(s) in column names. It would just require discipline to use it and be responsible about it.
1 Like
Using blanks at the start of a column name is a very, very, very bad idea and I would strongly encourage no one to go there. It will set them up for desaster in a lot of ways and I would not like KNIME to support that
2 Likes
Hi @mlauber71 , I think there is no denying that this is not a good practice. However, as @armingrudd pointed out (and as I eventually tested too), some DB systems do support this. And of course, any text or Excel file you read, this can happen since they don’t really have a column header, but rather the headers are in the first line, as text.
I think the challenge here is more about when it happens with db tables. There are of course workarounds, and I explained one of them.
But yes, if this is not used responsibly, it can turn into a very, very, very, very bad idea (I added another “very” )
1 Like
This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.