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!!
read_fileis adictof 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 notread_excelon the same file inside a loop... read the entire file, once.