Data, separation marks change

%3CLINGO-SUB%20id%3D%22lingo-sub-3276217%22%20slang%3D%22en-US%22%3EData%2C%20separation%20marks%20change%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3276217%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%3EI%20would%20like%20to%20ask%20%3C%2FSPAN%3E%3CSPAN%3Efor%20%3C%2FSPAN%3E%3CSPAN%3Eyour%20help%20with%20%3C%2FSPAN%3E%3CSPAN%3Ethe%20%3C%2FSPAN%3E%3CSPAN%3Etransfor%3C%2FSPAN%3E%3CSPAN%3Em%3C%2FSPAN%3E%3CSPAN%3Ea%3C%2FSPAN%3E%3CSPAN%3Etion%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3Eof%20the%20CSV%20f%3C%2FSPAN%3E%3CSPAN%3Eile%20into%20an%20excel%20file.%20%3C%2FSPAN%3E%3CSPAN%3EAfter%20I%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3Bdownload%20the%20CS%3C%2FSPAN%3E%3CSPAN%3EV%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3Eand%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3Eimport%20it%3C%2FSPAN%3E%3CSPAN%3E%2C%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3Ether%3C%2FSPAN%3E%3CSPAN%3Ee%3C%2FSPAN%3E%3CSPAN%3E%E2%80%99%3C%2FSPAN%3E%3CSPAN%3Es%20a%20problem%20with%20the%20separation%20marks%3C%2FSPAN%3E%3CSPAN%3E.%20%3C%2FSPAN%3E%3CSPAN%3E(Ex%3C%2FSPAN%3E%3CSPAN%3Ecel%20%3C%2FSPAN%3E%3CSPAN%3Eversion%3C%2FSPAN%3E%3CSPAN%3E%3A%2021%3C%2FSPAN%3E%3CSPAN%3E02%3C%2FSPAN%3E%3CSPAN%3E)%20I%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3Buse%20UTF%3C%2FSPAN%3E%3CSPAN%3E-%3C%2FSPAN%3E%3CSPAN%3E8%20code%20for%20c%3C%2FSPAN%3E%3CSPAN%3Eh%3C%2FSPAN%3E%3CSPAN%3Ear%3C%2FSPAN%3E%3CSPAN%3Eacter%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3Ebut%20%3C%2FSPAN%3E%3CSPAN%3E-%3C%2FSPAN%3E%3CSPAN%3Eif%20%3C%2FSPAN%3E%3CSPAN%3EI%3C%2FSPAN%3E%3CSPAN%3E%E2%80%99%3C%2FSPAN%3E%3CSPAN%3Em%20right%3C%2FSPAN%3E%3CSPAN%3E-%3C%2FSPAN%3E%3CSPAN%3Ethe%20numbers%20are%20not%20%3C%2FSPAN%3E%3CSPAN%3Eaffected%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3Eof%20it.%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3EFurther%3C%2FSPAN%3E%3CSPAN%3Emore%3C%2FSPAN%3E%3CSPAN%3E%2C%20during%20the%20import%20I%20use%20comma%20as%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3Efield%20separation%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3E(%3C%2FSPAN%3E%3CSPAN%3EDelimiter%3C%2FSPAN%3E%3CSPAN%3E)%3C%2FSPAN%3E%3CSPAN%3E.%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3EI%20%3C%2FSPAN%3E%3CSPAN%3Emust%20hig%3C%2FSPAN%3E%3CSPAN%3Ehlight%20that%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3Et%3C%2FSPAN%3E%3CSPAN%3Ehe%20%3C%2FSPAN%3E%3CSPAN%3Eimport%20%3C%2FSPAN%3E%3CSPAN%3Eof%20%3C%2FSPAN%3E%3CSPAN%3Ethe%20%3C%2FSPAN%3E%3CSPAN%3Edownloaded%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3ECSV%20%3C%2FSPAN%3E%3CSPAN%3Efile%20%3C%2FSPAN%3E%3CSPAN%3Eperfectly%20%3C%2FSPAN%3E%3CSPAN%3Eworks%20%3C%2FSPAN%3E%3CSPAN%3Ein%20the%20earlier%20version%20of%20excel%3C%2FSPAN%3E%3CSPAN%3E%2C%20%3C%2FSPAN%3E%3CSPAN%3Etherefore%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3EI%20assume%20%3C%2FSPAN%3E%3CSPAN%3Ethat%20the%20problem%20will%20be%20with%20the%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3Eupdated%20version%20of%20%3C%2FSPAN%3E%3CSPAN%3Eit%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3E(%3C%2FSPAN%3E%3CSPAN%3Eversion%3A%202102).%20%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EThe%20problem%20is%3A%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3EThe%20numbers%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3Eappear%20with%20%22%3C%2FSPAN%3E%3CSPAN%3Ewrong%22%20separation%20marks%3C%2FSPAN%3E%3CSPAN%3E.%20%3C%2FSPAN%3E%3CSPAN%3EW%3C%2FSPAN%3E%3CSPAN%3Ehen%20we%20load%20the%20data%3C%2FSPAN%3E%3CSPAN%3E%2C%20it%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3Euses%20%3C%2FSPAN%3E%3CSPAN%3E%E2%80%9C%3C%2FSPAN%3E%3CSPAN%3Edot%3C%2FSPAN%3E%3CSPAN%3E%E2%80%9D%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3Efor%20%3C%2FSPAN%3E%3CSPAN%3Edecimal%20separator%20and%20%3C%2FSPAN%3E%3CSPAN%3E%E2%80%9C%3C%2FSPAN%3E%3CSPAN%3Ecomma%3C%2FSPAN%3E%3CSPAN%3E%E2%80%9D%3C%2FSPAN%3E%3CSPAN%3Efor%20thousand%20%3C%2FSPAN%3E%3CSPAN%3Eseparator%3C%2FSPAN%3E%3CSPAN%3E.%20Nevertheless%3C%2FSPAN%3E%3CSPAN%3E%2C%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3Ewe%20would%20li%3C%2FSPAN%3E%3CSPAN%3Eke%20to%20use%20%3C%2FSPAN%3E%3CSPAN%3E%E2%80%9C%3C%2FSPAN%3E%3CSPAN%3Ecomma%3C%2FSPAN%3E%3CSPAN%3E%E2%80%9D%3C%2FSPAN%3E%3CSPAN%3Efor%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3Edecimal%20separator%20%3C%2FSPAN%3E%3CSPAN%3Eand%20%3C%2FSPAN%3E%3CSPAN%3E%E2%80%9C%3C%2FSPAN%3E%3CSPAN%3Espace%3C%2FSPAN%3E%3CSPAN%3E%E2%80%9D%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3Eor%20%3C%2FSPAN%3E%3CSPAN%3E%E2%80%9C%3C%2FSPAN%3E%3CSPAN%3Enothing%3C%2FSPAN%3E%3CSPAN%3E%E2%80%9D%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3Efor%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3Bthousand%20separ%3C%2FSPAN%3E%3CSPAN%3Eator%3C%2FSPAN%3E%3CSPAN%3E.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EI%20tried%20to%20change%20them%20with%20%E2%80%9Cctrl%2Bf%20and%20replace%E2%80%9D%20but%20obviously%20this%20is%20not%20a%20solution%20for%20a%20long%20term.%20I%26nbsp%3Bhave%20more%20than%2030%20columns%20to%20change.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3E(The%20settings%20of%20excel%20and%20windows%20use%20the%20right%20separator)%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EThis%20is%20the%20exact%20format%20what%20I%20want.%20If%20you%20have%20any%20idea%20that%20would%20lead%20me%20to%20solve%20this%20insane%2C%20irritating%20problem%2C%20I%20would%20be%20the%20happiest%20person%20in%20the%20world.%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22A_Me77_1-1649147571367.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F361360i8277EF77020E571B%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22A_Me77_1-1649147571367.png%22%20alt%3D%22A_Me77_1-1649147571367.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3276217%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3276338%22%20slang%3D%22en-US%22%3ERe%3A%20Data%2C%20separation%20marks%20change%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3276338%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1353532%22%20target%3D%22_blank%22%3E%40A_Me77%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIt%20shall%20work%20with%20both.%20Just%20with%20legacy%20wizard%20you%20may%20define%20separators%20on%20third%20step%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20463px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F361397iF24F6718536C937D%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Ewith%20Power%20Query%20is%20through%20encoding%20and%20delimiter%20with%20converting%20texts%20to%20numbers%20using%20locale.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3276311%22%20slang%3D%22en-US%22%3ERe%3A%20Data%2C%20separation%20marks%20change%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3276311%22%20slang%3D%22en-US%22%3EDear%20Sergei!%3CBR%20%2F%3EI%20want%20to%20say%20a%20huge%20thank%20you!%20It%20is%20workin%20with%20legacy%20wizard!%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3276303%22%20slang%3D%22en-US%22%3ERe%3A%20Data%2C%20separation%20marks%20change%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3276303%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1353532%22%20target%3D%22_blank%22%3E%40A_Me77%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHow%20do%20you%20import%20csv%20file%2C%20using%20Power%20Query%20or%203-steps%20legacy%20wizard%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

I would like to ask for your help with the transformation of the CSV file into an excel file. After I download the CSand import ittheres a problem with the separation marks. (Excel version: 2102) I use UTF-8 code for character but -if Im right-the numbers are not affected of it. Furthermore, during the import I use comma as field separation (Delimiter)I must highlight that the import of the downloaded CSV file perfectly works in the earlier version of excel, therefore I assume that the problem will be with the updated version of it (version: 2102).

The problem is: The numbers appear with "wrong" separation marks. When we load the data, it uses dot” for decimal separator and commafor thousand separator. Neverthelesswe would like to use commafor decimal separator and space” or nothing” for thousand separator.

I tried to change them with “ctrl+f and replace” but obviously this is not a solution for a long term. I have more than 30 columns to change.

 

(The settings of excel and windows use the right separator)

This is the exact format what I want. If you have any idea that would lead me to solve this insane, irritating problem, I would be the happiest person in the world. 

 

A_Me77_1-1649147571367.png

 

 

3 Replies

@A_Me77 

How do you import csv file, using Power Query or 3-steps legacy wizard?

Dear Sergei!
I want to say a huge thank you! It is workin with legacy wizard!

@A_Me77 

It shall work with both. Just with legacy wizard you may define separators on third step

image.png

with Power Query is through encoding and delimiter with converting texts to numbers using locale.