Python Forum
Reading specific rows from CVS to Excel
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Reading specific rows from CVS to Excel
#1
I am not completely sure that this is a Python pandas problem.

I start from a CVS file with 707,000 rows. From this (with help from yourselves) I have found out the first and last rows associated with a given ID in column 9. I have transferred these limits into an Excel workbook. For example : The col 9 ID EG47296 runs from row 3833 to row 13896.

Now I want to transfer these EG47296 rows from the CVS file into my new Excel workbook and particularly into worksheet(EG47296). It might be possible to copy paste, but I would rather do it programatically as there are so many rows.

How to set about this, please ?
Reply
#2
May be you can refer to this sample code


import pandas as pd
from pandas import ExcelWriter
from copy import deepcopy

data = pd.read_csv('c://filepath.csv')  #Set the CSV file path

# Select the required columns, rows from CSV
data1 = data.iloc[3832:13896,['column 9']].copy() # Set the required rows, columns and get a copy

# Save to excel
writer = ExcelWriter('c://filepathtoexcel.xlsx')
data1.to_excel(writer,'EG47296',index=False)
writer.save()
Reply
#3
Working from your code I have got the data out of the CSV file, because I can see it on the clipboard. But I still cannot get it to go into the Excel file. I have been fiddling with the syntax but, so far, to no avail. The error message seems to indicate that a module openpyxl is missing.

What I have is

# This is to read from row_s to row_e in Padget.cvs 
# and to put the material in Pajb.xlsm
import pandas as pd
from pandas import ExcelWriter
from copy import deepcopy
print("Hello, World!")

 
url = "F:/Carrier Bag F/NAV Padget/Padget.csv"
pacto = pd.read_csv(url) 

# Select the required columns, rows from CSV
#data1 = pacto.iloc[24120:24130,['column 1' : 'Column 9']].copy() 
data1 = pacto.iloc[24120:24130, 1:9].copy()

# A check
#data1.to_clipboard()   # THIS was OK I can see the data on the clipboard     

# Save to excel
vrl = "E:/CSVStuff/Pajb.xlsm"
writer = ExcelWriter(vrl)
#data1.to_excel(writer, sheet_name= 'EG47296',index=False, startrow=1, startcol = 1)
data1.to_excel(writer, sheet_name= 'EG47296',index=False)  # Doesn't work.
writer.save()


Reply
#4
OK Got it to work. Thank you.
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  Strange Problem when reading from excel faryad13 3 142 Nov-04-2020, 03:48 PM
Last Post: DeaD_EyE
  Reading from Excel: value not formula! faryad13 0 120 Oct-28-2020, 09:52 PM
Last Post: faryad13
  Reading SQL scripts from excel file and run it using python saravanatn 2 442 Aug-23-2020, 04:49 PM
Last Post: saravanatn
  How to sort rows based on specific order Mekala 3 262 Jul-31-2020, 01:01 AM
Last Post: bowlofred
  Excel tables and move rows jdos 6 622 Jul-02-2020, 05:52 AM
Last Post: jdos
  Merging excel worksheets with balnk rows sauravksingh85 1 283 May-19-2020, 12:46 PM
Last Post: sauravksingh85
  How can I speed up my openpyxl program reading Excel .xlsx files? deac33 0 498 May-04-2020, 08:02 PM
Last Post: deac33
  my openpyxl use is too slow, am I reading rows incorrectly? Clunk_Head 2 2,339 Apr-30-2020, 10:29 PM
Last Post: deac33
  Trouble reading Excel file. Shembeginner 2 543 Apr-07-2020, 04:55 AM
Last Post: Shembeginner
  How to extract specific rows and columns from a text file with Python Farhan 0 510 Mar-25-2020, 09:18 PM
Last Post: Farhan

Forum Jump:

User Panel Messages

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