remove symbol & convert comma separated values to rows

before start looking up script you can run this script to see the result in advance:

after that enter text like that: 12as3,@aa@12d34,1a2345, a12226 (without quotes)


save excel sheets as csv files, using python (jupyter notebook)

import os
import pandas as pd
from datetime import date

f_name = ‘Samle Data.xlsx’ # Enter excel file name (from the same directory)
prfx = os.path.splitext(f_name)[0] # you can change prefix
sffx = datetime.today().strftime(‘%Y-%m-%d’) # or suffix

WS = pd.ExcelFile(f_name)

for i in range(len(WS.sheet_names)):
df = pd.read_excel(f_name, sheet_name=WS.sheet_names[i], dtype=str)
df.to_csv(prfx + ‘_’ + WS.sheet_names[i] + ‘_’ + sffx + ‘.csv’, sep=’,’, header=True, index=False, encoding=’utf-8')

WS.close()

thats all

script on gist: https://gist.github.com/iraklidd/1d17aa351608eb69118888aed2260269#file-export-worksheets-as-csv-files-using-python


Copy excel sheets and save as another file with VBA

in this VBA Module I used simple functions for save as sheets in another excel template files

  1. Save as Macro-Enabled Workbook

2. Enable Content


In this case, every next row record equals random number between of the last two rows values.

just type the parameters
link: https://github.com/iraklidd/1Mcellsimulation
random number iteration
fill data in excel file, C7-C12 cells
save simulation data into excel template (another sheet)

Thank you. Now you can simulate everything..

https://github.com/iraklidd/1Mcellsimulation


Data > Get Data > From Databas > From SQL Server Database

If you already know Power Query in MS Excel — you ofc can easily work with SQL statements and retrieve data from a database.

But if you need to extract data dynamically, with WHERE clause, or with using any parameter, or to retrieve data using the stored procedure, you can…


Create virtual “dummy” Table, for testing something in stydying process, you can make like this:

Dummy table name is earthquackes and columns is cDate and Magnitude.
Significant Earthquakes, 1965–2016

For using one table many times use temporary table function

;WITH - CTE common table expression

CTE Table

;WITH tablename as ( 0any select script select * from -) select * from tablename

u can also use convert function in last select convert(DATE, CTEearthquakes.cDate) as fDate

also, you can use CTE temporary tables many times by this ;WITH statement:

and in last select you can use all temporary tables, wich was created above, for example

from openpyxl import *
# https://openpyxl.readthedocs.io/en/stable/styles.html

then add this column to the dataframe

# insert column “Subregion” after position — 1
data.insert(1,’Subregion’, subregion)
data

Irakli DD

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store