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
  trouble reading string/module from excel as a list popular_dog 0 384 Oct-04-2023, 01:07 PM
Last Post: popular_dog
  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
  Reading Specific Rows In a CSV File finndude 3 940 Dec-13-2022, 03:19 PM
Last Post: finndude
  How to properly format rows and columns in excel data from parsed .txt blocks jh67 7 1,801 Dec-12-2022, 08:22 PM
Last Post: jh67
  Excel file reading problem max70990 1 865 Dec-11-2022, 07:00 PM
Last Post: deanhystad
  reading content between a specific xml tag saisankalpj 1 808 Oct-31-2022, 01:37 PM
Last Post: wavic
  Deleting rows based on cell value in Excel azizrasul 11 2,475 Oct-19-2022, 02:38 AM
Last Post: azizrasul
  Pymysql delete specific rows in tableview stsxbel 2 1,049 Aug-18-2022, 09:50 AM
Last Post: ibreeden
  Trying to delete rows above a specific datetime value cubangt 19 11,007 May-09-2022, 08:57 PM
Last Post: deanhystad
  Reading Excel file and use a wildcard in file name and sheet name randolphoralph 6 6,857 Jan-13-2022, 10:20 PM
Last Post: randolphoralph

Forum Jump:

User Panel Messages

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