0

Using SQL Server Management Studio 18.3 I want to import data (semicolon separated text file) that has some columns with dot as decimal separator, and some with comma as decimal separator. I can't find an option to import both types on the fly, forcing me to choose nvarchar for either of them, and then transform it. Changing locale first would not help either, obviously. (I find it odd that the import wizard is not locale-agnostic.) How do you best resolve this problem?

2
  • Do you have permissions to create a staging table for the file?
    – user212533
    Jan 20, 2021 at 13:03
  • PowerShell to ingest the file, then search and replace in the needed fields to standardize the output? ... Could even go directly from PowerShell to the database if you want, or have it spit out a clean text file. Jan 20, 2021 at 13:41

1 Answer 1

1

Maybe you can try to edit (with notepad++) the file before import and clean (with a carefull find and replace) the separator you don't like.

2
  • Yes, thanks. I occasionally do that with smaller datasets using UltraEdit or Notepad++, but that is a bit slow or cumbersome with large files and many columns with this problem (mine has +700K rows.)
    – kgbviz
    Jan 20, 2021 at 12:42
  • A command-line tool like sed could do this find/replace quite easily. Jan 20, 2021 at 14:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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