Ask a Question

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

SOLVED
Amrendra
Occasional Contributor

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

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

 

6 REPLIES 6
Amrendra
Occasional Contributor

Please help how can I read the that excel file

Ravik
Super Contributor

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
Champion Level 2

Hi,

 

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

Regards,
  /Alex [Community Champion]
____
[Community Champions] are not employed by SmartBear Software but
are just volunteers who have some experience with the tools by SmartBear Software
and a desire to help others. Posts made by [Community Champions]
may differ from the official policies of SmartBear Software and should be treated
as the own private opinion of their authors and under no circumstances as an
official answer from SmartBear Software.
The [Community Champion] signature is assigned on quarterly basis and is used with permission by SmartBear Software.
https://community.smartbear.com/t5/Community-Champions/About-the-Community-Champions-Program/gpm-p/252662
================================
Amrendra
Occasional Contributor

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!

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
Super Contributor

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.

cancel
Showing results forΒ 
Search instead forΒ 
Did you mean:Β