[Python知识库]python批量导入excel/csv文件 并选择特定列（字段）

python批量导入excel/csv文件 并选择特定列（字段）

用到的模块

• import pandas as pd
  import os 
  

路径设置

• 查看当前工作路径

  os.getcwd()#查看当前工作路径get the current working directory
  

  ‘D:\文档’

• 路径设置

  查看工作路径是否是excel文件保存的路径，不是则可以更改一下工作路径。

  os.chdir(r"D:/桌面/viet")#设置工作路径
  

• 查看路径已经变成文件保存路径

  os.getcwd()
  

  ‘D:\桌面\viet’

  os.path.splitext(file)[1]## 分离文件名和扩展名
  

  ‘.xlsx’

  path = os.getcwd()
  filelist = []#创建空列表
  for root,dirs,files in os.walk(path):
      for file in files:
          if os.path.splitext(file)[1] == '.xlsx':#后缀为.xlsx的文件
              filelist.append(file)
              
  filelist
  
  
  

  [‘outbound daily report - for agent 06.18.xlsx’,
  ‘outbound daily report - for agent 06.21.xlsx’,
  ‘outbound daily report - for agent 06.22.xlsx’,
  ‘outbound daily report - for agent 06.23.xlsx’,
  ‘outbound daily report - for agent 06.24.xlsx’,
  ‘outbound daily report - for agent 06.25.xlsx’,
  ‘outbound daily report - for agent 06.28.xlsx’,
  ‘outbound daily report - for agent 06.29.xlsx’,
  ‘outbound daily report - for agent 07.01.xlsx’,
  ‘outbound daily report - for agent 07.02.xlsx’,
  ‘outbound daily report - for agent 07.05.xlsx’,
  ‘outbound daily report - for agent 07.06.xlsx’,
  ‘outbound daily report - for agent 07.07.xlsx’,
  ‘outbound daily report - for agent 07.08.xlsx’,
  ‘outbound daily report - for agent 07.09.xlsx’,
  ‘outbound daily report - for agent 07.12.xlsx’,
  ‘outbound task 05.27.xlsx’,
  ‘outbound task 05.28.xlsx’,
  ‘outbound task 05.31.xlsx’,
  ‘outbound task 06.01.xlsx’,
  ‘outbound task 06.02.xlsx’,
  ‘outbound task 06.03.xlsx’,
  ‘outbound task 06.04.xlsx’,
  ‘outbound task 06.07.xlsx’,
  ‘outbound task 06.08.xlsx’,
  ‘outbound task 06.09.xlsx’,
  ‘outbound task 06.10.xlsx’,
  ‘outbound task 06.11.xlsx’,
  ‘outbound task 06.14.xlsx’,
  ‘outbound task 06.15.xlsx’,
  ‘outbound task 06.16.xlsx’,
  ‘outbound task 06.17.xlsx’]

函数实例

• os.walk()

  这个函数需要传入一个路径作为top参数，函数的作用是在以top为根节点的目录树中游走，对树中的每个目录生成一个由(dirpath, dirnames, filenames)三项组成的三元组。

  其中，dirpath即'D:/桌面/viet'是一个指示这个目录路径的字符串，dirnames即[测试]是一个dirpath下子目录名组成的列表，filenames即目录下文件名，是由dirpath下所有非目录的文件名组成的列表。要注意的是，这些名称并不包含所在路径本身，要获取dirpath下某个文件或路径从top目录开始的完整路径，需要使用os.path.join(dirpath, name)。

  for item in os.walk(r"D:/桌面/viet"):
      print(item)
  

  (‘D:/桌面/viet’, [‘测试’], [‘webinfo101.csv’, ‘webinfo1500-1700.csv’, ‘webinfo1500-2000.csv’, ‘webinfo1700-1900.csv’, ‘webinfo200 -500.csv’, ‘webinfo200.csv’, ‘webinfo500-1500.csv’, ‘web_info 2001-2500.csv’])
  (‘D:/桌面/viet\测试’, [], [‘web_info2000.csv’])

  a=0
  for (root, dirs, files) in os.walk(r"D:/桌面/viet"):
      print(root)
      print(dirs)
      print(files)
      a=a+1
      print("循环次数：",a)
  

  D:/桌面/viet
  [‘测试’]
  [‘webinfo101.csv’, ‘webinfo1500-1700.csv’, ‘webinfo1500-2000.csv’, ‘webinfo1700-1900.csv’, ‘webinfo200 -500.csv’, ‘webinfo200.csv’, ‘webinfo500-1500.csv’, ‘web_info 2001-2500.csv’]
  循环次数： 1
  D:/桌面/viet\测试
  []
  [‘web_info2000.csv’]
  循环次数： 2

• os.path.splitext() 介绍

  os.path.splitext(“文件路径”) 分离文件名与扩展名；默认返回(fname,fextension)元组，可做分片操作

  os.path.splitext(r"D:/桌面/mypython/outbound task 06.17.xlsx")
  

  (‘D:/桌面/mypython/outbound task 06.17’, ‘.xlsx’)

  i = 0
  for root,dirs,files in os.walk(path):
      for file_name in files:
          if os.path.splitext(file)[1] == '.xlsx':
              i += 1
              print(file_name,'循环次数',i)
              
  

  outbound daily report - for agent 06.18.xlsx 循环次数: 1
  outbound daily report - for agent 06.21.xlsx 循环次数: 2
  outbound daily report - for agent 06.22.xlsx 循环次数: 3
  outbound daily report - for agent 06.23.xlsx 循环次数: 4
  outbound daily report - for agent 06.24.xlsx 循环次数: 5
  outbound daily report - for agent 06.25.xlsx 循环次数: 6
  outbound daily report - for agent 06.28.xlsx 循环次数: 7
  outbound daily report - for agent 06.29.xlsx 循环次数: 8
  outbound daily report - for agent 07.01.xlsx 循环次数: 9
  outbound daily report - for agent 07.02.xlsx 循环次数: 10
  outbound daily report - for agent 07.05.xlsx 循环次数: 11
  outbound daily report - for agent 07.06.xlsx 循环次数: 12
  outbound daily report - for agent 07.07.xlsx 循环次数: 13
  outbound daily report - for agent 07.08.xlsx 循环次数: 14
  outbound daily report - for agent 07.09.xlsx 循环次数: 15
  outbound daily report - for agent 07.12.xlsx 循环次数: 16
  outbound task 05.27.xlsx 循环次数: 17
  outbound task 05.28.xlsx 循环次数: 18
  outbound task 05.31.xlsx 循环次数: 19
  outbound task 06.01.xlsx 循环次数: 20
  outbound task 06.02.xlsx 循环次数: 21
  outbound task 06.03.xlsx 循环次数: 22
  outbound task 06.04.xlsx 循环次数: 23
  outbound task 06.07.xlsx 循环次数: 24
  outbound task 06.08.xlsx 循环次数: 25
  outbound task 06.09.xlsx 循环次数: 26
  outbound task 06.10.xlsx 循环次数: 27
  outbound task 06.11.xlsx 循环次数: 28
  outbound task 06.14.xlsx 循环次数: 29
  outbound task 06.15.xlsx 循环次数: 30
  outbound task 06.16.xlsx 循环次数: 31
  outbound task 06.17.xlsx 循环次数: 32

  dflist = []
  for i in range(len(filelist)) :
      dflist.append(pd.read_xlsx(filelist[i],usecols = ['Last call date','meeting','Reach KP','KP Complete','Last Outbound Result','Company Name','Company Name','Country','Contact Name','Import batch']))#默认第一行为索引 ，并提取相应字段
  

• pd.contact()

  pd.concat(objs, axis=0, join=‘outer’, join_axes=None, ignore_index=False,
  keys=None, levels=None, names=None, verify_integrity=False,
  copy=True)
  常用参数：
  axis = {0,1，…}，默认为0，也就是打竖,上下拼接。
  join = {‘inner’，‘outer’}，默认为“outer”,outer为并集,inner为交集。
  join_axes = Index对象列表。

  data = pd.concat(dflist)#讲dflist竖向拼接  默认outer并集（行列索引）
  data
  

文件导出

• data.to_excel(r"D:/桌面/mypython/web_vietnam.xlsx",index = False)#不显示新索引