I just browsed through the forum for a while and I am really excited, what easydatatransform can do. I use it for some time now, mainly for creating MySQL INSERT statements and clean Excel data.
But now now I have a challenge I cannot solve myself: I have a table of data which I want to get into a MySQL table. Because I the records exist already in the database, it will be an UPDATE statement. While in INSERT you can just leave empty cells in the statement, I can’t do it in UPDATE, because it will overwrite the existing value. The UPDATE statement follows the syntax:
UPDATE table SET
column_name2 = value2,
column_name3 = value3,
WHERE column_name1 = value1;
INSERT statements I created in easysdatatransform with the Substitute transformation (Substitution script):
INSERT INTO lime_survey_331974 (id, token, submitdate, 331974X61X14531, 331974X62X592VAbuss) VALUES ($(id), $(token), $(submitdate), $(331974X61X14531), $(331974X62X592VAbuss));
But If I see it right I cannot use this script for UPDATE because I see no way to delete empty values later or in the transformation.
I also tried to use the Concat plus Replace transformation I found in this forum post: Concat columns, skipping blanks. This way I only get the values in the statement and could even delete the empty values, but could not find a way to add the column names.
Anybody knows a solution where can delete the empty cells AND include the column names in the statement?
Can you give me a few example rows of the input you have and the output you want?
sure! I tried to attach a small version of the data table but could not upload it because I am a new user. So I just paste the csv here:
102113;;N;N;;;;;;Hat keine relevanten Kapazitäten mehr
The output would look like this:
Line 2: UPDATE db_table SET Aktiv = “N”, MEBaZaehlung = “N”, Bemerkung = “Hat keine relevanten Kapazitäten mehr” WHERE U_ID = 102113;
Line 5: UPDATE db_table SET Unternehmen = “Alice Rooftop”, KapazBiggest = 250, KapazGesamt = 250 WHERE U_ID = 102161;
So it should just be skipping all the blank cells and only using the cells which contain a value. Is this somehow possible?
You can do this with Replace and a regular expression.
Here is my quick and dirty attempt. Someone might have a more elegant approach.
DocRolfo.transform (3.1 KB)
Ps/ The Substitute is only set up for some columns, not all.
Here you go.
Create MySQL UPDATE .transform (4.3 KB)
Interesting way to avoid regex.
BTW You might need extra steps with either approach if the data contains commas or quotes.
This way, one does not have to type the column names which was one of the requirement.
Wow - thanks a lot!
Yeah Regex is a sore spot of mine… but I also did not get the idea to use it in this case!
Anonymous, your way was very interesting - I never thought of going this way. I took your steps and added just one step: when the values are numbers/integer, they are not allowed to be inserted in “”. So I added one Replace and now the transformation is just how I need it to be!
I attached the file again - aaaaach shoot… I cannot upload files! Sorry, would have liked to show you.
Admin - as you said, your way was quick and I learned a lot about transforming text in easydatatransform. I also tried to work the restriction in here that integers need to be without “” but it did not work. I tried to add a third Replace field where I replace
but in Update statement it stays Rooms_Max = "4
I also wanted to attached the file - as I said above: no chance. Too bad.
Anyway, thanks for the help, guys… and I really have to get into Regex!
There are some useful regex resources linked at:
VERY interesting! Thanks a lot!
Here is the updated transform that will not put numbers/integer in quotes.
Create MySQL UPDATE .transform (4.2 KB)
Here you go with the solution provided by @Admin, by modifying it your requirement of numbers/integers not to be in quotes. Also I added all the columns.
Modified Transform file.
DocRolfo.transform (3.4 KB)
Thanks a lot, guys, for your great help! Appriciate it a lot!