This might be asking too much, but I am hoping to easily manipulate .xlsm or .xlsx files within a specific directory.

Is there a way to?:

1. make a list of files in a directory (a la os.listdir or something similar)
2. choose a file from that list according to index (i.e., typing in '2' to retrieve xyz.xlsm from the list below)

tyc.xlsm
abc.xlsm
xyz.xlsm
gyf.xlsm
txz.xlsm

3. and then execute pandas.read_excel to convert to .csv for easy import into JMP

Pieces of the puzzle I am stuck on:

A. generating that list in Step 2 above with specific index positions

B. defining the io for pandas.read_excel as the output from that file name selection in Step 2 above.

Below is the code I have so far; I am able to list the .xlsm file and create the .csv from the specific sheet, but not sure how to do it in a folder of multiple Excel files.

import pandas as pd
import numpy as np
import os

for f_name in os.listdir('.'):
    if f_name.endswith('.xlsm'):
        print(f_name)

data_xls = pd.read_excel('example.xlsm', 'Sheet2', dtype=str, index_col=None)
data_xls.to_csv('csvfile.csv', encoding='utf-8', index=False)

Many thanks in advance!

CodePudding user response：

You're on the right track;

import pandas as pd
import numpy as np
import os

# Makes a list of files in directory
files = []
directory = '.'
for f_name in os.listdir(directory):
   if f_name.endswith(".xlsm"):
      files.append(os.path.join(directory, f_name))

# Lists possible files
for i, file in enumerate(files):
   print(i, file)

# Prompts user to pick a file
while True:
   index = input('Pick a file by index: ')
   try:
      index = int(index)
      if index in range(len(files)):
         break
   finally:
      print('Incorrect Input, Try Again.')

# Converts chosen file to csv
df = pd.read_excel(files[index])
df.to_csv(files[index].split('.')[0]   '.csv')

CodePudding user response：

I would suggest you add the names to a list. Put the "excel to csv" process into a function with two arguments. Put your list of names as one argument and index you want to use for the list item into the other. Call the function with those two arguments via CLI with sys arguments if you wish.

CodePudding user response：

Could create a prompt to choose a directory (then choose the one containing the files you want to change) using tkinter