使用pyodbc从SAP HANA拉取数据转CSV并导入SQL Server时，如何批量保留原始列数据类型？

I’ve dealt with identical problems—leading zeros getting stripped, decimal values getting cast to integers—when moving large datasets between databases without manual type mapping. Here are four scalable solutions tailored to your workflow:

1. Stop Pandas from Guessing Types When Pulling from SAP HANA #

Pandas’ automatic type inference is the culprit here—it sees columns like KUNNR (with leading zeros) as integers and strips the leading zeros, or converts decimals with .0 to integers. Fix this at the data pull step:

• Add coerce_float=False to read_sql_query to prevent pandas from converting string-based numeric values to numeric types:

  dfs = psql.read_sql_query(
      "SELECT * FROM SAPABAP1.VBAK ORDER BY VBELN LIMIT %d OFFSET %d" % (chunk, offset),
      conn,
      coerce_float=False
  )
  

• For pandas 2.0+, use dtype_backend='pyarrow' to leverage Arrow’s type system, which better preserves the native types returned by SAP HANA:

  dfs = psql.read_sql_query(
      "SELECT * FROM SAPABAP1.VBAK ORDER BY VBELN LIMIT %d OFFSET %d" % (chunk, offset),
      conn,
      dtype_backend='pyarrow'
  )
  

This keeps string columns as strings and preserves decimal precision without any per-column configuration.

2. Export CSV with Type-Safe Quoting #

CSV is a type-agnostic format, so we need to tell SQL Server how to interpret each column. Use csv.QUOTE_NONNUMERIC to wrap all non-numeric values in quotes—this ensures BULK INSERT treats them as string types instead of trying to parse them as numbers:

First import the csv module, then update your export code:

import csv

final_df.to_csv(
    creds.path+'output.csv',
    sep=',',
    na_rep='',
    encoding='utf-8-sig',
    index=False,
    quoting=csv.QUOTE_NONNUMERIC
)

Now columns with leading zeros will be quoted, and BULK INSERT will respect their string type. Decimal values that were preserved by the first step will export correctly too.

3. Skip CSV Entirely: Direct Bulk Insert from Pandas to SQL Server #

The most reliable way to preserve types is to cut out the CSV middleman. Use pandas’ to_sql with SQLAlchemy for fast, type-safe bulk inserts:

from sqlalchemy import create_engine

# Create a SQLAlchemy engine using your existing SQL Server credentials
engine = create_engine(
    f"mssql+pyodbc://@{creds.svr}/{creds.db}?driver=ODBC+Driver+17+for+SQL+Server&Trusted_Connection=yes"
)

# Truncate the table first (matching your existing workflow)
with engine.connect() as conn:
    conn.execute("TRUNCATE TABLE LKP_VBAK")
    conn.commit()

# Insert the DataFrame directly
final_df.to_sql(
    'LKP_VBAK',
    engine,
    if_exists='append',
    index=False,
    method='multi',  # Faster bulk insert mode
    chunksize=chunk  # Reuse your existing chunk size to avoid memory issues
)

This sends the DataFrame’s native types directly to SQL Server, so leading zeros, decimals, and all other types are preserved perfectly—no manual mapping needed. It’s also often faster than writing to CSV and then doing BULK INSERT.

4. Auto-Generate a Format File for BULK INSERT #

If you need to stick with CSV, use SQL Server’s bcp utility to auto-generate a format file that matches your target table’s schema. This tells BULK INSERT exactly how to parse each column:

1. Run this command in Command Prompt (replace placeholders with your server/database/table name):

   bcp YourDatabaseName.dbo.LKP_VBAK format nul -c -f LKP_VBAK.fmt -T -S YourServerName
   

   • -c: Uses character format (matches your CSV)
   • -f: Outputs the format file to LKP_VBAK.fmt
   • -T: Uses your trusted Windows authentication

2. Update your BULK INSERT query to use this format file:

   BULK INSERT LKP_VBAK 
   FROM 'path.csv' 
   WITH (
       FIRSTROW = 2,
       FORMATFILE = 'path\LKP_VBAK.fmt',
       CHECK_CONSTRAINTS,
       MAXERRORS = 999,
       ERRORFILE = 'path\\VBAK_log'
   );
   

The format file automatically maps each CSV column to the target table’s data type, so you don’t have to manually define anything. This works for all your tables—just generate a format file for each one once.

Critical Pre-Requisite #

Make sure your SQL Server target tables have data types that match the SAP HANA source. For example, if KUNNR is a string in HANA, it should be nvarchar (not int) in SQL Server. If the target table type is wrong, even the best CSV parsing will cause issues.

内容的提问来源于stack exchange，提问作者Rahul