Python Forum
How to ignore formulas when reading excel file
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
How to ignore formulas when reading excel file
#1
Hi,
I am reading an excel file which has formulas in a few columns names. When read using pd.read_ecel, the column names are becoming 0, 0.1, 0.2 etc. This may be because the cell contain formulas, how to just read only the value and ignore formula.

import pandas as pd
import numpy as np
df = pd.read_excel('D:\input.xlsx',sheetname='Sheet2')
Reply
#2
it looks like your file/data has no header row and it defaults to first line. I guess you don't want to skip the first row of data. you need to explicitly pass header=None when call pandas.read_excel()
https://pandas.pydata.org/pandas-docs/st...excel.html
If you can't explain it to a six year old, you don't understand it yourself, Albert Einstein
How to Ask Questions The Smart Way: link and another link
Create MCV example
Debug small programs

Reply
#3
My input data is as below:

Caegory    Model			        TM		
	       Date	       WAT	 Actual	Date	    Standard	Cal
N22HN	   2019-07-07	7	 Fail	2019-08-01	Noraml	    5/9
	       2019-07-22	12	 1.36	2019-08-02	Struck	    2/1.3
	       2019-07-30	62/9 6/1	2019-08-03	Miss	    4
TM--> "=HYPERLINK("#Sheet2!A1","TM")"

I still want to keep all the columns.
Reply
#4
IMHO the problem is that single header value e.g. Model Date is split in multiple rows, not that you have formulas.
If you can't explain it to a six year old, you don't understand it yourself, Albert Einstein
How to Ask Questions The Smart Way: link and another link
Create MCV example
Debug small programs

Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  Updating formulas in Excel - best solution MasterOfDestr 4 560 Mar-11-2024, 01:02 PM
Last Post: MasterOfDestr
  Python openyxl not updating Excel file MrBean12 1 250 Mar-03-2024, 12:16 AM
Last Post: MrBean12
  plotting based on the results of mathematical formulas Timur 1 295 Feb-08-2024, 07:22 PM
Last Post: Gribouillis
  Copy Paste excel files based on the first letters of the file name Viento 2 348 Feb-07-2024, 12:24 PM
Last Post: Viento
Sad problems with reading csv file. MassiJames 3 559 Nov-16-2023, 03:41 PM
Last Post: snippsat
  Search Excel File with a list of values huzzug 4 1,147 Nov-03-2023, 05:35 PM
Last Post: huzzug
  Updating sharepoint excel file odd results cubangt 1 754 Nov-03-2023, 05:13 PM
Last Post: noisefloor
  trouble reading string/module from excel as a list popular_dog 0 384 Oct-04-2023, 01:07 PM
Last Post: popular_dog
  Reading a file name fron a folder on my desktop Fiona 4 851 Aug-23-2023, 11:11 AM
Last Post: Axel_Erfurt
  Reading data from excel file –> process it >>then write to another excel output file Jennifer_Jone 0 1,046 Mar-14-2023, 07:59 PM
Last Post: Jennifer_Jone

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020