0

I need help in transforming an EXCEL file (which may have 1 or many sheets) into a CSV file. I was asked to read a Python file (first time I use Python and Pandas) which is in another server to do this request. Searching in the web I found and modified this code:

import pandas as pd
read_file = pd.read_excel (r"/data/IRIS/mgr/PCICODE/ExcelReading/ExcelFiles/Probanding.xls")
read_file.to_csv (r"/data/IRIS/mgr/PCICODE/ExcelReading/CSVFiles2/testFile.csv", index = None, header=True, encoding="utf-8-sig" , sep = ';')

This code does the job, but it does not work properly when the EXCEL file has 2 or more sheets, since I need that if the EXCEL has sheets, every sheet must be in a different EXCEL file, and the code I am using right now just takes the first sheet and discard the rest. I looked for other options and found this code:

import pandas as pd

str_InputFilePath =  input('Name and Path of Excel: ')
str_path = str_InputFilePath.split('/')
int_name = len(str_path)
str_file = str_path[int_ame-1].split('.')
str_shortName = str_file[0]
str_OutputFilePath =  input('Output Path: ')

la  = pd.read_excel(str_InputFilePath, sheet_name=None) 
counter = 0    
for name, sheet  in la.items():
    FileReading  = pd.read_excel(str_InputFilePath, sheet_name=(name)) 
    
    FileReading.to_csv(str_OutputFilePath + str(counter+1) + str_shortName + ".csv", encoding='UTF-32BE', index = None, header=False, sep = ';')
    counter = counter + 1

I tested this last code locally and worked. It takes every sheet from Excel file and creates a CSV file for every single sheet, but the Python file I need to use is in another "server" and I can't send it any Input. So I would really appreciate your help trying to merge these codes. Thank you for your time and guidance!!

3
  • on the server are the excel files all in a folder that you are going to extract from? Where would you be outputting the files to? Commented Feb 4, 2022 at 17:15
  • Does this answer your question? How to convert multiple excel sheets to csv python Commented Feb 4, 2022 at 17:18
  • read_file is a dict of dataframes corresponding to each sheet for a multi-sheet Excel file. The linked thread shows how to write each to an individual csv. You should not read_excel on the same file inside a loop... read the entire file, once. Commented Feb 4, 2022 at 17:19

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.