1

I'm trying to create a very basic package that copies data from my SQL server into an Excel file.

The data that comes from the server comes with a decimal comma (e.g. 1234,56) - that is how I want it, that is how it's always worked, that is how I want the final data to be formatted in Excel. For some reason Visual Studio / SSIS is always transforming this data to a decimal point format (e.g. 1234.56) - that's not how I want it, I can't work with that.

I've tried every form of data conversion, doesn't matter if it's DT_NUMERIC, DT_DECIMAL, DT_CY, it's always giving me a decimal dot - even when going the Derived Column route, specifically turning the values into DT_WSTR, then replacing every "." with a "," and then converting to any numerical format still gives me a result with decimal points.

The problem is that Visual studio is previewing the SQL input in the correct formats, the numbers do have a decimal comma, the input is 100% correct - but at some point the comma is being transformed into a period without my doing. image

The Excel Connection Manager's LocalID setting is set to German, so it should produce correctly formatted numbers. I tried pre-formatting the Excel-Destination file's columns to my desired format - not working. I tried putting in correctly formatted dummy data into the Excel-File and set it to Overwrite - not working.

Kinda losing my mind here, anyone got any clue on what's causing this behavior?

Improve this question
2
  • Are you sure this is a problem with SSIS and not Excel auto-formating it incorrectly when you open the file? If you're generating a CSV, just open the file in any text editor and observe if the data is correctly formatted. Otherwise, you can try changing the format settings of that column in Excel to "Default" or possibly "Text" to try to get the original value, but I've seen Excel even still be a little wonky in some cases then.
    – J.D.
    Nov 3, 2021 at 11:08
  • I'll be damned, it is actually Excel being weird as CSV are producing the proper decimal comma values.
    – Peter Parker
    Nov 3, 2021 at 13:53

1 Answer 1

Reset to default
0

As I mentioned in my comment, always worth checking the consumer for rendering issues. In this case, when Excel's involved and it's a formatting issue, you can never be too careful to verify that Excel isn't the source of the problem.

Sometimes changing the Format option for that column in Excel to "Default" or "Text" reveals the original formatting. Though I've had my fair share of issues with Excel still being wonky with how it was formatting my data even then.

Best way to check, when it's a CSV, is to just open the file in a plain text editor and verify the raw data is formatted as expected. Excel seemed to be the issue in your case.

Improve this answer
2
  • Excel was indeed the problem. Is there any known fix as to what is causing this problem with Excel? Even when trying to convert the CSV to XLSX it's causing the same , <-> , issues. I mean, CSV's are definitely workable, but for end user firendliness a proper Excel solution would be just that extra little bit more convenient.
    – Peter Parker
    Nov 3, 2021 at 14:33
  • @PeterParker Unfortunately I don't think there's much control you have if you're initially generating the file as a CSV. Excel has its own built in logic to handle how it auto-formats data. Though you might have more luck asking this question on a more relevant StackExchange board to Excel like SuperUser, for potential solutions, since this is no longer a database related issue.
    – J.D.
    Nov 3, 2021 at 14:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.