Importing CSV merge error

[wowcatfactor]

I have a script that uploads a daily CSV to Fabric, then imports it into a table. There's one column that's a doulbe data type that I consistently have issues with. Sometimes the CSV will accurately have all zero values for that column, then it won't append to the table. However, as soon as I add a dummy value with something like 0.05, etc.. it imports successfully. Should I be using a different data type?

[v-achippa]

Hi @wowcatfactor,

Thank you for reaching out to Microsoft Fabric Community.

Thank you @tayloramy and @spaceman127 for the prompt response. 

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided by the user's for the issue worked? or let us know if you need any further assistance.

Thanks and regards,

Anjan Kumar Chippa

[v-achippa]

Hi @wowcatfactor,

We wanted to kindly follow up to check if the solution provided by the user's for the issue worked? or let us know if you need any further assistance.

Thanks and regards,

Anjan Kumar Chippa

[spaceman127]

Hey @wowcatfactor ,

This is a typical CSV problem that occurs repeatedly.
Unfortunately, you have to intercept these things beforehand.

I would do exactly what @tayloramy  suggests, and it's a good approach to solving your problem.

Best regards 

[tayloramy · Accepted solution]

Hi @wowcatfactor, 

How are you importing the CSV? Here's some recommendations based on various ingestion methods into Fabric: 

• Dataflow Gen2 (Power Query): In the last steps, explicitly set the column to Fixed decimal number (currency) or Decimal number with Table.TransformColumnTypes, then load to the table. (Power Query data types, Dataflow Gen2 destinations)
• Copy Activity (Pipeline): In Mappings, set the sink type to decimal(p,s) (e.g., decimal(18,6)) for that column. (Fabric Data Factory type mapping)
• Spark/Notebook: Read the CSV with an explicit schema (DoubleType or DecimalType) and write to Delta. (Delta schema enforcement)

If you found this helpful, consider giving some Kudos. If I answered your question or solved your problem, mark this post as the solution.