How can I make sure that a table and csv file can merge in an azure databricks query?

Question

I am currently trying to copy data from a csv file into my table in azure databricks and am getting the following error.

[DELTA FAILED TO MERGE FIELDS] Failed to merge fields ‘transfer_month’ and ‘transfer_month’

I am unable to solve the error. I can confirm that the number, order and name of columns are matching so I’m guessing it’s something to do with the data type.

my query is formatted as shown below

CREATE TABLE catalogB.schemaB.tableB(
  transfer_month    STRING,
  value             DOUBLE
);



COPY INTO catalogB.schemaB.tables
FROM '/Volumes/catalogA/schemaA/data.csv'
FILEFORMAT = CSV
FORMAT_OPTIONS(
  ‘header’ = ‘true’,
  ‘ignoreCorruptFiles’ = ‘true’
);

Answer 1

Hi ,

Thanks for reaching out to Microsoft Q&A.

Error suggests a data type mismatch between the table schema and the CSV file.

• Even though column names match, the data types inferred from the CSV file might be different from the table schema.
  • You have transfer_month STRING in the table.
  • The CSV file might be inferred as another type (e.g., DATE or INT).

Try explicitly casting the transfer_month column in the COPY INTO query. If the issue persists, use the following inferSchema workaround to check how Databricks reads the CSV

df = spark.read.option("header", "true").csv("dbfs:/Volumes/catalogA/schemaA/data.csv")

• Modify your query to force all columns into the correct data type before merging.
• Databricks might interpret hidden special characters (e.g., non-breaking spaces). Run:

DESCRIBE DETAIL catalogB.schemaB.tableB;

If there’s a mismatch, rename the column:

Please feel free to click the 'Upvote' (Thumbs-up) button and 'Accept as Answer'. This helps the community by allowing others with similar queries to easily find the solution.