I have a Excel name "CCP_MARGIN_2017-09-11_risk_cpm_stg.xlsx" in C drive. I am reading it and using

[Amrendra]

I have a Excel name "CCP_MARGIN_2017-09-11_risk_cpm_stg.xlsx" in C drive. I am successfully  reading it through

excellib.excelRW('C:\temp\CCP_MARGIN_2017-09-11_risk_cpm_stg.xlsx').

The Problem is that if (2017-09-11) date will change if I run it on different date,Example 😞CCP_MARGIN_2017-09-12_risk_cpm_stg.xlsx)  . I am using PYTHON. Please help

[Amrendra]

Please help how can I read the that excel file

[Ravik]

Hi,

are u sure about your file name, I had this type of problem when my file name or sheetname cross length (~36 char) .

I am using "openpyxl " module to work with Excel file and I found its really helpful and compatible with excel-

here are the sample, may help you

#---------------------------------------------------------------------------

import openpyxl

def openmyExcel():

workbookObj = openpyxl.load_workbook("C:\TC_Python\Test1.xlsx")

worksheetobj = workbookObj.get_sheet_by_name('Sheet1')

rowcout = worksheetobj.max_row

colCount = worksheetobj.max_column

Log.Message(rowcout)

Log.Message(colCount)

#-----------------------------------------------------------------------------

def readCellData():

workbookobj = openpyxl.load_workbook("C:\TC_Python\Test1.xlsx")

worksheetObj = workbookobj.get_sheet_by_name("Sheet1")

rowcount = worksheetObj.max_row

colcount = worksheetObj.max_column

for introw in range(1,rowcount):

celldata = worksheetObj.cell(row = introw ,column =1).value

Log.Message(celldata)

#-----------------------------------------------------------------------------

def readAlldatainExcel():

wbo = openpyxl.load_workbook("C:\TC_Python\Test1.xlsx")

wso = wbo.get_sheet_by_name("Sheet1")

intAllRow = wso.max_row

intAllCol = wso.max_column

for introw in range(1, intAllRow):

for intcol in range(1,intAllCol):

mydata= wso.cell(row= introw, column = intcol).value

#Log.Message(mydata)

if (mydata == None😞

break

Log.Message(mydata)

#----------------------------------------------------------------------------------

def writeinExcel():

wbo = openpyxl.load_workbook("C:\TC_Python\Test1.xlsx")

wso = wbo.get_sheet_by_name("Sheet1")

intAllRow = wso.max_row

intAllCol = wso.max_column

for introw in range(1, intAllRow):

for intcol in range(1,intAllCol):

wso['A1'] = introw

mydata= wso.cell(row= introw, column = intcol).value

Log.Message(mydata)

if (mydata == None😞

break

Log.Message(mydata)

#----------------------------------------------------------------------------------

def getSheetName():

wbo = openpyxl.load_workbook("C:\TC_Python\Test1.xlsx")

wso = wbo.get_sheet_names()

for shname in wso:

Log.Message(shname)

[AlexKaras]

Hi,

http://support.smartbear.com/testcomplete/docs/testing-with/advanced/working-with-external-data-sour... and https://support.smartbear.com/viewarticle/9046/ should help.

[Amrendra]

hi , in my case the excel file name is changing everyday (CCP_MARGIN_2017-09-11_risk_cpm_stg.xlsx) ,the date. Tomorrow it will be (CCP_MARGIN_2017-09-12_risk_cpm_stg.xlsx). That's problem. Please help!

[baxatob · Accepted solution]

CCP_MARGIN_2017-09-12_risk_cpm_stg.xlsx

You have a string and one part of this string changes dynamically, right? 

One way is to create a method which will return appropriate date:

def get_file_name(date_):
    return "CCP_MARGIN_{}_risk_cpm_stg.xlsx".format(date_)

Now if you call get_file_name("2017-09-13"), it returns: CCP_MARGIN_2017-09-13_risk_cpm_stg.xlsx

You can hardcode the date argument or make it more flexible using Python's datetime library.

For example you can set today's date:

from datetime import date

get_file_name(date.today())

[Ravik]

You mean to say that on the basis of Date you want open sheet and wants to perform the operation !!!

If it is then we can parameterize the date string.

Please give the more input on it.