Monthly Archives: October 2017

Python Write Formula to Excel

I recently had a challenge writing formulas to Excel file using Python. I managed to create the value of the cell as the right formula but it is not being evaluated or active when you open it up in Excel.

I was using the to_excel method of pandas dataframe. And then someone pointed me to this awesome library XlsxWriter. It supports many of the features within Excel including but not restricted to formula, chart, image and even customized format.

Here is a short snippet of code of showing how it worked just out of box.

Screen Shot 2017-10-12 at 10.28.41 PM

And the output looks straightforward and satisfying.

Screen Shot 2017-10-12 at 10.29.01 PM

Also, as you can see from the code, you should really try to get yourself out of the business of working with row index and column index directly. For example, whenever I think about you are going to use things like row=row+1 or i++, it reminds me of the languages like C++, Java which we should stay away from.

Here is another example of directly working with pandas dataframe using XlsxWriter as the engine.

Screen Shot 2017-10-12 at 10.54.33 PM

And then, this is how the output file looks like, we have a new column called col2 that are active links. Clearly, it has been evaluated and active, when you click on it, it will link you to/jump directly to the A1 cell of sheet1. Problem solved.

Screen Shot 2017-10-12 at 10.56.12 PM

Oracle SQLdeveloper Reset Expired Password

Today I was trying to access an Oracle database after being provided with the credentials, which I have been waiting for a long time, typical IT, isn’t it?

However, I am using a tool called “Oracle SQL Developer” which made this a super fun experience for me that made me want to document and share with people.

When I click to create a new connection and fill in all the needed information (BTW, the SID is actually the database name: Site IDentifier). It prompted me with an error message “the password has expired”.

oracle_login

I tried a few times making sure it is not my typo but every time it gave me the same error message. Also, I randomly entered a few strings and it gave me a different error message of “invalid username/password; logon denied“. This made me believe the password our DBA gave me was definitely right but it just expired. My first question was that my credential must just got created and “brand new”, how could it expire? Clearly, there is a difference between how milk went expired and how password expired.

After a few quick Google research, I realized that some DBAs will set the default password as expired out of box so it will force the users to reset the password, like many web subscription that the confirmation email will directly link you to reset password page. However, now knowing that I need to reset my password, the frustration part is how?

Looking at the connection wizard, there is no where/no button that I can click giving me the option of resetting the password. I navigated through all the buttons, drop downs and tabs and still couldn’t find any sign of “reset password”. “Test Connection” will keep showing this same error message and you are not able to “Connect Either”.

This reminds me of a scenario where it is your first day of employment and your manager told you that your badge is at the front desk. However, the security will not even let you into the building because you do not have your badge. To get the badge, you need to get into the building though to visit the front desk…

I notified my DBA of the awkward situation and he notified me that I need to reset my password in one sentence without any further explanation. I already feel like making a fool out of myself and I think I had better figure this “reset my password” thing all by myself.

In the end, believe it or not, I need to first ignore the error message, SAVE the connection as a valid connection. Then, you need to right click to bring up the menu for the connection and the “reset password” option will be there for you to use. From there, everything will be so straightforward where you enter your old and new password and then you are IN!

Screen Shot 2017-10-09 at 4.08.12 PM

This is purely a UI/UX problem where people should have considered but I just want to share this fun experience so it can save a bit time for those like me, who happen to be new to Oracle, who happen to be given an expired password, who happen to use SQLdeveloper and who happen to think you need to connect first before you change your password.