gpt4 book ai didi

Python脚本跳过excel文件中的标题行

转载 作者:太空狗 更新时间:2023-10-30 02:32:39 26 4
gpt4 key购买 nike

我写了一个 python 脚本,它将从文件夹中提取 excel 文件并将它们写入 SQL 表。我得到了可以工作的代码,但前提是我删除了包含标题的 excel 文件的第一行。我是 Python 的新手,所以这可能很简单,但我看了很多不同的技术,但无法弄清楚如何将它插入我的代码中。任何想法将不胜感激!

# Import arcpy module
from xlrd import open_workbook ,cellname
import arcpy
import pyodbc as p

# Database Connection Info
server = "myServer"
database = "my_Tables"
connStr = ('DRIVER={SQL Server Native Client 10.0};SERVER=' + server + ';DATABASE=' + database + ';' + 'Trusted_Connection=yes')

# Assign path to Excel file
file_to_import = '\\\\Location\\Report_Test.xls'

# Assign column count
column_count=10

# Open entire workbook
book = open_workbook(file_to_import)

# Use first sheet
sheet = book.sheet_by_index(0)

# Open connection to SQL Server Table
conn = p.connect(connStr)

# Get cursor
cursor = conn.cursor()

# Assign the query string without values once, outside the loop
query = "INSERT INTO HED_EMPLOYEE_DATA (Company, Contact, Email, Name, Address, City, CentralCities, EnterpriseZones, NEZ, CDBG) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"

# Iterate through each row

for row_index in range(sheet.nrows):

row_num = row_index
Company = sheet.cell(row_index,0).value
Contact = sheet.cell(row_index,1).value
Email = sheet.cell(row_index,2).value
Name = sheet.cell(row_index,3).value
Address = sheet.cell(row_index,4).value
City = sheet.cell(row_index,5).value
CentralCities = sheet.cell(row_index,6).value
EnterpriseZones = sheet.cell(row_index,7).value
NEZ = sheet.cell(row_index,8).value
CDBG = sheet.cell(row_index,9).value

values = (Company, Contact, Email, Name, Address, City, CentralCities, EnterpriseZones, NEZ, CDBG)

cursor.execute(query, values)

# Close cursor
cursor.close()

# Commit transaction
conn.commit()

# Close SQL server connection
conn.close()

最佳答案

您可以在第二行初始化迭代。尝试以下操作:

for row_index in range(1,sheet.nrows):

编辑:如果您需要遍历 .xls 文件列表,如您在评论中所问,基本思想是对文件执行外部循环。这里有一些提示:

# You need to import the os library. At the beinning of your code
import os

...
# Part of your code here
...

# Assign path to Excel file
#file_to_import = '\\\\Location\\Report_Test.xls'
folder_to_import = '\\\\Location'
l_files_to_import = os.listdir(folder_to_import)
for file_to_import in l_files_to_import:
if file_to_import.endswith('.xls'):
# The rest of your code here. Be careful with the indentation!
column_count=10
...

关于Python脚本跳过excel文件中的标题行,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/16859954/

26 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com