Date format not correct.. Ways to hide the password?? Excel, Python, Mysql

python write to existing excel file xlsxwriter
pandas to excel no formatting
python excel functions
python loop through excel files
find and replace in excel using python
pandas to_excel date format
python excel data analysis
python excel file

I am working on a script to load information from excel to SQL. I cannot figure out how to fix my loops to remove the extra comma and extra space I get at the end of my sql code..

This is python code meant to read an excel file, and create INSERT INTO querys...

This is a python script meant to pull data from an excel file

import xlrd
with open('Phase3initial.sql', 'w') as file:
    data = xlrd.open_workbook('Phase3 Initial Data.xlsx', on_demand = True)
    sheets = data.sheet_names()
    tables = ['User', 'UserEmail', 'Employee', 'Site', 'Event', 'Transit', 'Connect', 'TakeTransit', 'AssignTo', 'VisitEvent', 'VisitSite']
    for i in range(data.nsheets):
        sheets[i] = data.sheet_by_index(i)
        for j in range (1, sheets[i].nrows):
            file.write('INSERT INTO ' + str(tables[i]) + '(')
            row = sheets[i].row_values(j)
            for k in range(len(row)):
                cell = sheets[i].cell(j,k)
                if tables[i] == 'User':
                    if k ==1:
                        file.write(str(cell.value) +', ')
                        continue
                file.write(str(cell.value) + ', ')
            file.write(');\n')

image of excel file https://imagizer.imageshack.com/v2/1024x768q90/924/EOTu41.png

I get an extra space and comma at the end.. after my code runs..Not sure how to fix that..

INSERT INTO User(james.smith, jsmith123, Approved, James, Smith, Employee, );
INSERT INTO User(michael.smith, msmith456, Approved, Michael, Smith, Employee, Visitor, );
INSERT INTO UserEmail(james.smith, jsmith@gatech.edu, );
INSERT INTO UserEmail(james.smith, jsmith@outlook.com, );

Edit After some trial an error I am still stuck with the Date format.. Also, any ideas on how to hide the password? It's not supposed to be visible..

import xlrd

with open('Phase3initial.sql', 'w') as file:
    data = xlrd.open_workbook('Phase3 Initial Data.xlsx', on_demand = True)
    sheets = data.sheet_names()
    tables = ['User', 'UserEmail', 'Employee', 'Site', 'Event', 'Transit', 'Connect', 'TakeTransit', 'AssignTo', 'VisitEvent', 'VisitSite']
    for i in range(data.nsheets):
        sheets[i] = data.sheet_by_index(i)
        for j in range (1, sheets[i].nrows):
            file.write('INSERT INTO ' + str(tables[i]) + '(')
            row = sheets[i].row_values(j)

            # first len(row)-1 columns
            for k in range(len(row)-1):
                cell = sheets[i].cell(j,k)
                if tables[i] == 'User':
                    if k ==1:
                        file.write(str(cell.value) +',')
                        continue
                file.write(str(cell.value) + ',')

            # last column
            k = len(row) - 1
            cell = sheets[i].cell(j, k)
            if tables[i] == 'User':
                if k == 1:
                    file.write(str(cell.value))
                    continue
            file.write(str(cell.value))

            file.write(');\n')



For some reason my date is changed to 435000.0... is not formated.. check StartDate and EndDate on link https://imagizer.imageshack.com/v2/1024x768q90/921/uODMTH.png after james.smith and michael.smith the password are showing.. any ideas on how to hide the passwords?

My output

INSERT INTO User(james.smith,jsmith123,Approved,James,Smith,Employee);
INSERT INTO User(michael.smith,msmith456,Approved,Michael,Smith,Employee, Visitor);
INSERT INTO Event(Eastside Trail,43500.0,Piedmont Park,43501.0,0.0,99999.0,1.0,A combination of multi-use trail and linear greenspace, the Eastside Trail was the first finished section of the Atlanta BeltLine trail in the old rail corridor. The Eastside Trail, which was funded by a combination of public and private philanthropic sources, runs from the tip of Piedmont Park to Reynoldstown. More details at https://beltline.org/explore-atlanta-beltline-trails/eastside-trail/,);
INSERT INTO Event(Eastside Trail,43500.0,Inman Park,43501.0,0.0,99999.0,1.0,A combination of multi-use trail and linear greenspace, the Eastside Trail was the first finished section of the Atlanta BeltLine trail in the old rail corridor. The Eastside Trail, which was funded by a combination of public and private philanthropic sources, runs from the tip of Piedmont Park to Reynoldstown. More details at https://beltline.org/explore-atlanta-beltline-trails/eastside-trail/,);


You can solve this two ways: - Check which line is last in loop, and not add a ', ' to it, - Change write(str(cell.value) + ', ') to write(', ' +str(cell.value)) and check for first line and skip adding ', ' to it.

How To Read Password Protected Excel File In Python, Lock, hide and password protects files and folder on your computer. XlsxWriter is a Python module for writing files in the Excel 2007+ XLSX file format. If not, two ways introduced in this article are available for PDF users to open encrypted -f Filename of the key file. sql: Download Python MySQL Sample Database. How do I hide default dates when creating a formula and no value is entered? I created a formula adding 30 days to any dates entered into a row, which appear in the adjacent row. A default date appears when no date is entered for the calculation all the way down the row on my blank formatted spreadsheet.

Get a list of cell values i.e.: cellvalues = ['user2','user1','user3','user4','user5']

And then use join on them like so: result = cellvalues.join(',') The result will be: "user2,user1,user3,user4,user5"

You can further do:

values = f"({cellvalues.join(',')})"

And just add it to the beginning of the query.

Just one thing don't you need single quotes on those values? Or hey, her's another idea just take the cellvalues and do:

values = str(tuple(cellvalues))

date format not working php mysql Code Example, Get code examples like "date format not working php mysql" instantly right You may not remember next time either, so install Grepper. All Python Answers. string functions ascii char_length character_length concat concat_ws field find_in_set format insert instr lcase left length locate lower lpad ltrim mid position repeat replace reverse right rpad rtrim space strcmp substr substring substring_index trim ucase upper numeric functions abs acos asin atan atan2 avg ceil ceiling cos cot count degrees

It will be easy if use pandas to read excel file as data frame and you can export it to mysql table.Use default mysql connector. You can write data back to table using to_sql or execute or executemany command. By using environment variable you can hide your password. For an example if u set pass='xyz' in your computer you can assign that password to variable by passwd=os.getenv('pass').Ref https://dev.mysql.com/doc/connector-python/en/connector-python-example-connecting.html

How To Read Password Protected Excel File In Python, CSV files are simply formatted text files and have to be compressed to be secured. Cracking a password protected excel doc with python - excel. when I tried to open uncompress the file and copy it to a folder such as C:\mysql\ python_mysql. In Excel, when you click the Print button, Excel will not print hidden sheets,� datetime.strptime(string, format) The string parameter is the value in string format that we want to convert into date format. The format parameter is the directive specifying the format to be taken by the date after the conversion. For example, let's say we need to convert the string "9/15/18" into a datetime object.

Python scripts to format data in Microsoft Excel, This article makes use of Python Scripts for data formatting in Xlrd: It is also a Python library and useful to read data from the excel files. In the code above, we added the column width 60 so that it does not conflict with text in column C: </ p> It changed the column width to the appropriate size. Convert Python Classes to Excel Spreadsheet. You already saw how to convert an Excel spreadsheet’s data into Python classes, but now let’s do the opposite. Let’s imagine you have a database and are using some Object-Relational Mapping (ORM) to map DB objects into Python classes. Now, you want to export those same objects into a spreadsheet.

How To Read Password Protected Excel File In Python, Obviously, the file was password protected, and I had to find a way to read it. Now the default save format option for Excel documents will be as a xls file. or some other piece of software opens your file does not mean it is a valid xls file. hide or protect sensitive data, so it's different from file. python linux excel xlsx xlrd � ExcelWriter ("pandas_datetime.xlsx", engine = 'xlsxwriter', datetime_format = 'mmm d yyyy hh:mm:ss', date_format = 'mmmm dd yyyy') # Convert the dataframe to an XlsxWriter Excel object. df. to_excel (writer, sheet_name = 'Sheet1') # Get the xlsxwriter workbook and worksheet objects in order to set the column # widths, to make the dates clearer

How to Resolve the "Not a Valid Month" Error with Oracle TO_DATE , The "ORA-01843: not a valid month" error is quite common for Oracle SQL developers, and can be fixed in a few ways. Sign in. Email. Password. Forgot your password? the date value. We'll cover both of these below. Sessions on your database are created in a certain language format or date format. now, protect your spreadsheet. You don't need to hide columns A, C . note. the spreadsheet is unprotected, except from columns A, B if you want to protect, seperate cells (e.g formulas) then before Step3 (protect sheet) select the cells and.. Format > Cells > Protection .> tick Locked and/or Hidden . Comment

Comments
  • You may want to change your logic and append your ', ' before the cell value. You will need to suppress it for the first write.This is easier since you can always determine the first write but not the last.
  • @PM77-1 That's a hack. The proper approach is to use the appropriate MySQL string escaping method. Any field with '' in it will break this horribly.
  • You may find it's easier to write out a CSV file using a Python CSV library and then import that directly into MySQL using LOAD DATA INFILE. Writing raw SQL is not as easy since you must get the syntax and escaping correct.
  • This needs to call the Python MySQL string escaping function.