Infomantra

import openpyxl import warnings warnings.filterwarnings('ignore', category=UserWarning, module='openpyxl')

sheets = pd.ExcelFile('./pdfconv/Test_NEW.xlsx').sheet_names

# # sheets = ['Sheet1', 'Sheet3']

# df = pd.concat([pd.read_excel('./pdfconv/Test.xlsx', sheet_name = sheet) for sheet in sheets], ignore_index = True)

df = pd.concat([pd.read_excel('./pdfconv/Test.xlsx', sheet_name = sheet) for sheet in sheets], ignore_index = True)

print(df)

print(sheets)

for sh in sheets: # if sh == 'Sheet1': # for variation=1 month Jan 2022 if sh == 'Sheet1' and 'Sheet3': # for variation1 month April 2022 df = pd.concat([pd.read_excel('./pdfconv/Test_NEW.xlsx', sheet_name = sheet, header=None) for sheet in sheets], ignore_index = True, axis=0) #print(df) df.to_csv('./pdfconv/ddtest.csv') else: # df = pd.concat([pd.read_excel('./pdfconv/Test.xlsx', sheet_name = sheet) for sheet in sheets], ignore_index = True) print('Sorry!! not to print')

from PIL import Image import os,sys

def resize_rename_rotate(srcdir,srcfile, targetdir="", size=(128,128)):

def resize_rename_rotate(srcdir,srcfile, targetdir="", size=(4000,2000)):

##srcfile_path : the complete path to source file src_path = srcdir+srcfile

targetfile = os.path.splitext(srcfile) extension = os.path.splitext(srcfile)[1]

if srcfile != targetfile: try : im = Image.open(src_path) ## open file ## im = im.rotate(90) ## degrees counter-clockwise im = im.transpose(Image.ROTATE_90) ## im = im.resize(size) ## resize the file ## im.save(targetdir+targetfile[0]+".jpeg") im.save(targetdir+targetfile[0]+"_Output"+".jpeg") except IOError: print ("cannot change image for ", srcfile)

if name=="main": targetdir = "./RotatedImages/" srcdir = "./pdfconv/"

targetdir = "./target_pics/"

srcdir = "./src_pics/"

for file in os.listdir(srcdir): if file.endswith(".jpg"): ## if file == 'image0.jpg' and 'image2.jpg': if file == 'image1.jpg': resize_rename_rotate(srcdir,file,targetdir) continue

import os import glob

source_path = 'C:\Users\DD\Desktop\TOI\TASK\For PDF Extraction Task\Documents\'

path = 'C:\Users\DD\Desktop\TOI\TASK\For PDF Extraction Task\Documents\*.pdf'

print(source_path)

Source Path:

path = source_path+'*pdf' print (path) file_list= [] for file in glob.glob(path): data_file_list = os.path.basename(file) file_list.append(data_file_list) source_file = ''.join([str(elem) for elem in file_list]) # list to string print('source_file+: '+source_file)

Source File:

#filename=source_file.split(' ')[len(source_file.split(' '))-1].split('.')[0]

filename=source_file print("File Name is Here: "+ filename)

using Positive indexing

Str = filename[:len(filename)-4] print(Str)

# Str = filename

# # using negative indexing

# Str = Str[:-4]

# print(Str)

destination_path = 'C:\Users\DD\Desktop\TOI\TASK\For PDF Extraction Task\Documents\pdfconv\AlliedIPA' print(destination_path+'\'+Str+' '+'Output'+'.txt')

For More Details Follow:

######## Delete last line from file

with open("C:\\Users\\Desktop\\TASK\\\Output_Dec2022.txt", "r+") as f:

    current_position = previous_position = f.tell()

    while f.readline():

        previous_position = current_position

        current_position = f.tell()        

    f.truncate(previous_position)

################# Delete from Start: To delete the first N lines use list slicing.

with open("C:\\Users\\Desktop\\TASK\\\Output_Dec2022.txt", "r+") as fp:

    # read an store all lines into list

    lines = fp.readlines()

    # move file pointer to the beginning of a file

    fp.seek(0)

    # truncate the file

    fp.truncate()

    # start writing lines except the first line

    # lines[1:] from line 2 to last line

################## Date time in python using strftime() ################

import datetime

#import date

today = datetime.datetime.now()

#accessing the year attribute

year1 = today.month

print(year1)

#using the strftime() method

year2 = today.strftime("%Y")

print(year2)

#using the strftime() method

mo = today.strftime("%m")

print(mo)

date_formatted = today.strftime('%m/%d/%Y')

import os

import shutil

src_dir = "C:/Users/DD/Desktop/TASK/Source"

dst_dir = "C:/Users/DD/Desktop/TASK/Destination"

for root, dirs, files in os.walk(src_dir):

    for f in files:

        if f.endswith('.csv'):

CREATE TABLE Employee ( [ID] INT identity(1,1), [FirstName] Varchar(100), [LastName] Varchar(100), [Country] Varchar(100), ) GO

Insert into Employee ([FirstName],[LastName],[Country] ) values('Raj','Gupta','India'),

('Raj','Gupta','India'),

('Mohan','Kumar','USA'),

('James','Barry','UK'),

('James','Barry','UK'),

('James','Barry','UK')

Select * from Employee

/*** duplicate Records ***/

Select FirstName, LastName, Country, count() as cnt from Employee group by FirstName, LastName, Country having count(*)>1

How to replace a special character or punctuation in a string in SQL Server:

SELECT C.name 'COLUMNNAME',T.name 'TABLENAME' FROM SYS.[columns] c JOIN SYS.tables t ON C.[object_id]=T.[object_id] WHERE T.NAME LIKE '%service%' or C.NAME LIKE '%service%' ORDER BY ColumnName, TableName

/ *Details View*/ SELECT DISTINCT TABLE_CATALOG , TABLE_SCHEMA , TABLE_NAME , COLUMN_NAME , ORDINAL_POSITION , DATA_TYPE , IS_NULLABLE , CHARACTER_MAXIMUM_LENGTH -- SELECT TOP 1000 * FROM INFORMATION_SCHEMA.COLUMNS --WHERE TABLE_NAME = 'risk_scores' WHERE COLUMN_NAME LIKE '%Service%' ORDER BY TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, ORDINAL_POSITION, COLUMN_NAME /*Details of All Procedures*/ select procedures.object_id ,procedures.[name] as [SP Name],parameters.name [Parameter] from sys.parameters inner join sys.procedures on parameters.object_id = procedures.object_id inner join sys.types on parameters.system_type_id = types.system_type_id AND parameters.user_type_id = types.user_type_id where procedures.name IN (select distinct name from sys.objects where type = 'P' )

SELECT T.name 'TABLE NAME',C.name 'COLUMN NAME' FROM SYS.[columns] c JOIN SYS.tables t ON C.[object_id]=T.[object_id] WHERE C.NAME LIKE '%POD%'

In Teradata system tables are stored under DBC schema.

1- For columns, it is dbc.columns

select * from dbc.columns

eg: For views,

it is dbc.tables with a filter on a column something named table_type 'V' (where V stands for Views)

select * from dbc.tables

In Teradata DBC.Tables contains many of the objects that exist on the system. (e.g. Stored Procedures, UDF, Triggers, Macros, Views, Tables, Hash Index, Join Index, etc.) The column Table Kind is used to identify the type of object.

SELECT * FROM DBC.TABLES WHERE TABLEKIND = '<see below>' A = Aggregate Function

B = Combined Aggregate Function and ordered analytical function

D = JAR E = External Stored Procedure

F = Standard Function

G = Trigger

H = Instance or Constructor Method

I = Join Index

J = Journal

M = Macro

N = Hash Index

O = No Primary Index (Table)

P = Stored Procedure

Q = Queue Table

R = Table Function

S = Ordered Analytical Function

T = Table

U = User-defined data type

V = View

X = Authorization

Y = GLOP Set

Realtime Example:-

SELECT  a.TBL_REC_ID ,b.DOMAIN_NM ,b.TGT_DB_NM ,b.SRC_TBL_NM ,b.TGT_TBL_NM ,a.COL_REC_ID ,a.SRC_TBL_COL_NM ,a.TGT_TBL_COL_NM ,a.PUB_VW_COL_NM ,a.SRC_COL_DATA_TYP ,a.TGT_COL_DATA_TYP ,a.COL_LEN ,a.COL_SCALE ,a.COL_FORMAT ,a.COL_NOT_NULL_IND ,a.FILT_CRI ,a.AS_OF_TMS ,a.UPD_TMS ,a.AS_AT_TMS ,a.SRC_DEL_IND ,a.JN_IND

Generally, it’s best practice to put unique constraints on a table to prevent duplicate rows.

However, you may find yourself working with a database where duplicate rows have been created through human error, a bug in your application, or uncleaned data from external sources. This tutorial will teach you how to find these duplicate rows.

To follow along, you’ll need read access to your database and a tool to query your database.

Identify Duplicate Criteria: The first step is to define your criteria for a duplicate row. Do you need a combination of two columns to be unique together, or are you simply searching for duplicates in a single column? In this example, we are searching for duplicates across two columns in our Users table: username and email.

Write Query to Verify Duplicates Exist: The first query we’re going to write is a simple query to verify whether duplicates do indeed exist in the table. For our example, my query looks like this:

SELECT username, email, COUNT(*) FROM users GROUP BY username, email HAVING COUNT(*) > 1

HAVING is important here because unlike WHERE, HAVING filters on aggregate functions.

If any rows are returned, that means we have duplicates. In this example, our results look like this:

USERNAME EMAIL COUNT Pete [email protected] 2 Jessica [email protected] 2 Miles [email protected] 2 List All Rows Containing Duplicates In the previous step, our query returned a list of duplicates. Now, we want to return the entire record for each duplicate row.

To accomplish this, we’ll need to select the entire table and join that to our duplicate rows. Our query looks like this:

SELECT a.* FROM users a JOIN (SELECT username, email, COUNT(*) FROM users GROUP BY username, email HAVING count(*) > 1 ) b ON a.username = b.username AND a.email = b.email ORDER BY a.email

If you look closely, you’ll see that this query is not so complicated. The initial SELECT simply selects every column in the users table, and then inner joins it with the duplicated data table from our initial query. Because we’re joining the table to itself, it’s necessary to use aliases (here, we’re using a and b) to label the two versions.

Here is what our results look like for this query:

FIRST. and LAST. variables: Data step processing within by groups

If you use a by statement along with a set statement in a data step then SAS creates two automatic variables, FIRST.variable and LAST.variable, where variable is the name of the by variable.  FIRST.variable has a value 1 for the first observation in the by group and 0 for all other observations in the by group. LAST.variable has a value 1 for the last observation in the by group and 0 for all other observations in the by group.

The code shown below is available here.

/************************************************** The automatic variables first.group and last.group are not saved with the data set. Here we write them to data set variables to show their contents. **************************************************/ data new; set temp; by group; first=first.group; last=last.group; run;

proc print; title 'Raw data along with first.group and last.group'; run;

/************************************************** A common task in data cleaning is to identify observations with a duplicate ID number. If we set the data set by ID, then the observations which are not duplicated will be both the first and the last with that ID number. We can therefore write any observations which are not both first.id and last.id to a separate data set and examine them. **************************************************/ data single dup; set temp; by group; if first.group and last.group then output single; else output dup; run;

/************************************************** We may also want to do data set processing within each by group. In this example we construct the cumulative sum of the variable X within each group. **************************************************/ data cusum(keep=group sum); set temp; by group; if first.group then sum=0; sum+x; if last.group then output; run;

proc print data=cusum noobs; title 'Sum of X within each group'; run;

1.  What is SAS?

SAS (Statistical Analysis System) is a software suite developed by SAS Institute for advanced analytics, business intelligence, data                             management, and predictive analytics. It is the largest market-share holder for advanced analytics. SAS was developed at North Carolina State University from 1966 until 1976, when SAS Institute was incorporated. SAS was further developed in the 1980s and 1990s with the addition of new statistical procedures, additional components and the introduction of JMP. A point-and-click interface was added in version 9 in 2004. A social media analytics product was added in 2010.

2.  What are the special input delimiters?

The input delimiters are DLM and DSD.

3.  What is the difference between a format and an informat?

Format: A format is to write data i.e.  WORDIATE18 and WEEKDATEW

Informat: An informat is to read data i.e. comma, dollar and date (MMDDYYw, DATEw, TIMEw, PERCENTw)

4.  Describe any one SAS function?

RIM: removes trailing blanks from a character expression

Str1 = ‘my’;

Str2 = ‘dog’;

Result = TRIM (Str1)(Str2);

Result = ‘mydog’

5.  What is Program Data Vector (PDV) and what are its functions ?

PDV is a logical area in the memory

SAS creates a dataset one observation at a time

Input buffer is created at the time of compilation, for holding a record from external file

PDV is created followed by the creation of input buffer

SAS builds dataset in the PDV area of memory

6.  Compare SAS STATA & SPSS?

Each package offers its own unique strengths and weaknesses.  As a whole, SAS, Stata and SPSS form a set of tools that can be used for a wide variety of statistical analysis.  With Stat/Transfer it is very easy to convert data files from one package to another in just a matter of seconds or minutes.  Therefore, there can be quite an advantage to switching from one analysis package to another depending on the nature of your problem.  For example, if you were performing analysis using mixed models you might choose SAS, but if you were doing logistic regression you might choose Stata, and if you were doing analysis of variance you might choose SPSS. If you are frequently performing statistical analysis, we would strongly urge you to consider making each one of these packages part of your toolkit for data analysis.

7.  What are the uses of SAS?

SAS/ETS software provides tools for a wide variety of applications in business, government, and academia. Major uses of SAS/ETS procedures are economic analysis, forecasting, economic and financial modeling, time series analysis, financial reporting, and manipulation of time series data.

The common theme relating the many applications of the software is time series data: SAS/ETS software is useful whenever it is necessary to analyze or predict processes that take place over time or to analyze models that involve simultaneous relationships.

Although SAS/ETS software is most closely associated with business, finance and economics, time series data also arise in many other fields. SAS/ETS software is useful whenever time dependencies, simultaneous relationships, or dynamic processes complicate data analysis.For example, an environmental quality study might use SAS/ETS software’s time series analysis tools to analyze pollution emissions data. A pharmacokinetic study might use SAS/ETS software’s features for nonlinear systems to model the dynamics of drug metabolism in different tissues.

8.  What are the features of SAS?

SAS provides a powerful programming language with components called procedures that allow you to perform many different kinds of analysis and data management functions, as well as produce many different types of text-based and graphical presentation output.Combined with other features, the SAS language and its procedures make an immense variety of applications possible, including the following examples:

       Access raw data files and data in external databases and database management systems.

       Manage data using tools for data entry, editing, retrieval, formatting, and conversion.

       Analyze data using descriptive statistics, multivariate techniques, forecasting and modeling, and linear programming.

        Present data using reports and business and statistical graphics.

SAS is also portable across computing environments. SAS applications function the same and produce the same results regardless of the operating environment on which you are running SAS to process your data. However, some features, such as interactive windows, are not supported on all platforms.

9.  How do I Create a SAS Data Set with Compressed Observations?

To create a compressed SAS data set, use the COMPRESS=YES option as an output DATA set option or in an OPTIONS     statement.Compressing a data set reduces its size by reducing repeated consecutive characters or numbers to 2-bye or 3-byte representations.To uncompress observations, you must use a DATA step to copy the data set and use option COMPRESS=NO for the new data set.

The advantages of using a SAS compressed data set are reduced storage requirements for the data set and fewer input/output operations necessary to read from and write to the data set during processing. The disadvantages include not being able to use SAS observation number to access an observation. The CPU time required to prepare compressed observations for input/output observations is increased because of the overhead of compressing and expanding the observations. (Note: If there are few repeated characters, a data set can occupy more space            in compressed form than in uncompressed form, due to the higher overhead per observation.) For more details on SAS compression see “SAS Language: Reference, Version 6, First Edition, Cary, NC: SAS Institute Inc., 1990”.

10.  How can we minimize space requirement for the huge data set in SAS for window?

When you are working with large data sets, you can do the following steps to reduce space requirements.

Split huge data set into smaller data sets.

Clean up your working space as much as possible at each step.

Use data set options (keep= , drop=) or statement (keep, drop) to limit to only the variables needed.

Use IF statement or OBS = to limit the number of observations.

Use WHERE= or WHERE or index to optimize the WHERE expression to limit the number of observations in a Proc Step and Data Step.

Use length to limit the bytes of variables.

Use _null_ data set name when you don’t need to create a data set.

Compress data set using system options or data set options (COMPRESS=yes or COMPRESS=binary).

1. Demonstrate or whiteboard how you would suggest using configuration files in packages.  Would you consider it a best practice to create a configuration file for each connection manager or one for the entire package? There should be a single configuration file for each connection manager in your packages that stores their connection string information.  So if you have 6 connection managers then you have 6 config files.  You can use the same config file across all your packages that use the same connections.   If you have a single config file that stores all your connection managers then all your packages must have contain the connection managers that are stored in that config file.  This means you may have to put connection managers in your package that you don’t even need.

2. Demonstrate or whiteboard how checkpoints work in a package. When checkpoints are enabled on a package if the package fails it will save the point at which the package fails.  This way you can correct the problem then rerun from the point that it failed instead of rerunning the entire package.  The obvious benefit to this is if you load a million record file just before the package fails you don’t have to load it again.

3. Demonstrate or whiteboard using a loop in a package so each file in a directory with the .txt extension is loaded into a table.  Before demonstrating this tell which task/container accomplishes this and which enumerator will be used.  (Big hint on which task/container to use is that it requires and enumerator) This would require a Foreach Loop using the Foreach File Enumerator.  Inside the Foreach Loop Editor you need to set a variable to store the directory of the files that will be looped through.  Next select the connection manager used to load the files and add an expression to the connection string property that uses the variable created in the Foreach Loop.

4. Demonstrate or whiteboard how transactions work in a package. If transactions are enabled on your package and tasks then when the package fails it will rollback everything that occurred during the package. First make sure MSDTC (Microsoft Distributed Transaction Coordinator) is enabled in the Control Panel -> Administrative Tools -> Component Services. Transactions must be enabled not only on the package level but also on each task you want included as part of the transaction. To have the entire package in a transaction set TransactionOption at the package level to Required and each task to Supported.

5. If you have a package that runs fine in Business Intelligence Development Studio (BIDS) but fails when running from a SQL Agent Job what would be your first guess on what the problem is? The account that runs SQL Agent Jobs likely doesn’t have the needed permissions for one of the connections in your package. Either elevate the account permissions or create a proxy account. To create a proxy account you need to first create new credentials with the appropriate permissions. Next assign those credentials to a proxy account. When you run the job now you will select Run As the newly created proxy account.

6. What techniques would you consider to add auditing to your packages?  You’re required to log when a package fails and how many rows were extracted and loaded in your sources and destinations. I like to create a database that is designated for package auditing. Track row counts coming from a source and which actually make it to a destination. Row counts and package execution should be all in one location and then optionally report off that database. There are also third party tools that can accomplish this for you (Pragmatic Works BI xPress).

7. What techniques would you consider to add notification to your packages?  You’re required to send emails to essential staff members immediately after a package fails. This could either be set in the SQL Agent when the package runs or actually inside the package you could add a Send Mail Task in the Event Handlers to notify when a package fails. There are also third party tools that can accomplish this for you (Pragmatic Works BI xPress).

8. Demonstrate or whiteboard techniques you would use to for CDC (Change Data Capture)?  Tell how you would write a package that loads data but first detects if the data already exists, exists but has changes, or is brand new data for a destination. If for some reason you’ve avoided using a whiteboard to show your ideas to this point then make sure you start on this question! For small amounts of data I may use the Slowly Changing Dimension. More often than not the data is too large to use in such a slow transform. I prefer to do a lookup on the key of the target table and rows that don’t match are obviously new rows that can be inserted. If they do match it’s possible they are updates or duplicates. Determine this by using a conditional split comparing rows from the target to incoming rows. Send updates to a staging table that can then be updated in an Execute SQL Task. Explain that putting updates in a staging table instead of updating using the OLE DB Command is much better for performance because the Execute SQL Task performs a bulk operation.

9. Explain what breakpoints are and how you would use them. Breakpoints put pauses in your package. It’s a great tool for debugging a package because you can place a breakpoint on a task and it will pause the package based on execution events. A reason in which I have used breakpoints is when I have a looping container and I want to see how my variables are changed by the loop. I would place a watch window on the package and type the variable name in. Set a break point on the container the stop after each iteration of the loop.

We may be required to log such errors once upon the failure of the complete package or we may want it for each & every task failed during the package execution. So depending upon these requirements we have two types of event handlers in SSIS: 1.      OnError 2.      OnTaskFailed

OnError: This event is raised when an executable gets some errors due to any reason. This may be due to failure of any of the tasks included in the package.

OnTaskFailed: This event is raised when a task is failed irrespective of the complete package failure.

The important difference between these two is explained below:

Let’s suppose we have a package (Package 1) which has many tasks; one of them is ExecutePackage task. Now this ExecutePackage task executes another package (Package 2) which in turn contains some tasks.

Pacakge 1:  Data Flow Task -> Script Task -> ExecutePacakge Task (Package 2) Package 2:   Script Task

Now suppose Script task of the package 2 gets failed due to some errors (like with some wrong code inside). Then OnError Event of the package 1 will be raised only once. But OnTaskFailed event of the package 1 will be raised twice; once due to the failure of Script Task of the package 2 and second time due to the failure of the ExecutePackage task of the package 1 (which gets failed due to failure of package 2).

1. Can we use SQL database in power BI and Power Query together?

Ans)-Using SQL and Power BI together takes the data analysis to the next level. We can easily connect the SQL Server to Power BI and extract the data directly into it. Power BI enables the users to toggle connections with a click to apply in-memory queries to a larger dataset.

2. What are the updates in Power BI in September or October Release? Ans)- Power BI can translate Excel Vlookups, macros, and other data sources, and tables into Power BI. Then translate them into Power BI solutions and demonstrate the benefits.

3. How many Types of filters are there in Power BI? Ans)-There are four types of filters. A)-page filter applies to all the visuals on the report page B)-visual filter applies to a single visual on a report page. You only see visual level filters if you've selected a visual on the report canvas. C)-report filter applies to all pages in the report D)-drillthrough filter applies to a single entity in a report

4. Can we put visual level filter on Slicer in power BI? Ans)- Often when working with a Power BI report you will add a slicer that has a "Blank" item in the selection criteria. This may not be the desired behavior for a slicer.

5. How Matrix is different from Normal table visual in Power BI? Ans)-The matrix visual is Power BI's version of the pivot table. The ordinary table in Power BI is only two-dimensional. The data shown are flat and duplicate values are displayed, not aggregated. A matrix table supports multiple dimensions, a stepped layout, aggregates data, and has a drill-down functionality

6. How RLS is implemented in Power BI? Ans)- Row-level security (RLS) with Power BI can be used to restrict data access for given users. Filters restrict data access at the row level, and you can define filters within roles. In the Power BI service, members of a workspace have access to datasets in the workspace. RLS does n't restrict this data access.

7. Can we apply RLS rules in Power BI services? Ans)- In the Power BI service, members of a workspace have access to datasets in the workspace. RLS does n't restrict this data access. You can configure RLS for data models imported into Power BI with Power BI Desktop. You can also configure RLS on datasets that are using Direct Query, such as SQL Server.

8. Why on Premises gateway is used? Ans)- This gateway is well-suited to complex scenarios with multiple people accessing multiple data sources. On-premises data gateway (personal mode) allows one user to connect to sources, and can't be shared with others. An on-premises data gateway (personal mode) can be used only with Power BI.

9. What is the procedure to setup Gateway? URL: https://docs.microsoft.com/en-us/data-integration/gateway/service-gateway-install

10. Can we use Power BI as on premises? And how we connect it? Ans)- Yes

11. How we can put Colors on condition according to values in matrix? Ans)- conditional formatting in a Power BI table or matrix. There are 4 different types of conditional formatting. These include conditionally formatting the background color or font color, creating data bars, or formatting by rules.

12. Which DAX operation should be used if calculation needs to be done not on the basis of Visual dimension? Ans)-  Calculated Columns

13. How FILTER DAX function is used and what it returns? Ans)- You can use DAX FILTER function to reduce the number of rows in the table that you are working with, and use only specific data in calculations. DAX FILTER function is not used independently, but as a function that is embedded in other functions that require a table as an argument.

14. What is the use of SUMMARIZE DAX operation? Ans)- Returns a summary table for the requested totals over a set of groups. Syntax:- SUMMARIZE (<table>, <groupBy_columnName>, [<groupBy_columnName>] …,   [<name>, <expression>] …) = SUMMARIZE(SalesTarget,SalesTarget[SalesTarget],"MaxTarget",MAX (SalesTarget[SalesTarget]))