Solved

What setting should I do to see my .csv file in right format?


Userlevel 2
Badge +8

On one machine, the csv seems in right format, but another machine the csv file seems not so.

example:

what should i change for the machine which shows me the csv file as the second screenshot?

icon

Best answer by Tamil Arasu10 5 January 2023, 14:29

View original

10 replies

Userlevel 7
Badge +12

Hi @Semih 3318 ,

 

When you put the CSV file in your spreadsheet editor (Excel, for example), it shows all of the data in a single column, instead of splitting them across columns.

The reason behind this behavior is that CSV files (Comma Separated Values) are splitting data into columns using " , " as separator by default. But in certain countries, the default value used as separator is " ; " instead.

To resolve this issue, within your open CSV file, you can click on the Data tab, select Convert and choose commas.

 

To change the settings for future Excel documents:

Go to your Settings page clicking on the link in the top-right corner of your screen (You have to be an admin)

In the Localization module, change the "List separator for exports" field to  "comma ( , )"

 

To change the settings for your Windows computer: 

Go to Start > Settings > Control Panel > Regional and Language Options. 
Click “Customize”. 
Enter “,” for “List Separator”

Userlevel 3
Badge +7

Hi @Semih 3318 ,

You can delimit the CSV file.

Please check the link here 

 

 

Userlevel 5
Badge +9

Hi @Semih 3318 

In North America and some other countries, the default list separator is a comma, so you get CSV comma delimited.

In European countries, a comma is reserved for the decimal symbol, and the list separator is generally set to semicolon. That is why the result is CSV semicolon delimited.

 

Checkout below link to know more info,

 

https://www.ablebits.com/office-addins-blog/change-excel-csv-delimiter/

Userlevel 2
Badge +8

Hello @Tamil Arasu10  thanks for your reply. It will work when i do your solution but my question is,

on one machine, i didnt do any settings, and it seems perfect

but on another machine, its not. There should be some settings that need to be changed on the machine i guess

Userlevel 5
Badge +9

Hello @Tamil Arasu10  thanks for your reply. It will work when i do your solution but my question is,

on one machine, i didnt do any settings, and it seems perfect

but on another machine, its not. There should be some settings that need to be changed on the machine i guess

Yes please checkout my comment

Userlevel 2
Badge +8

Hello @Padmakumar  thank you for the reply

when i looked at customize setting(region), list seperator is same for both machines which show me the csv as i wanted, and which doesnt show.

I couldnt understand the other excel solution. This excel is not same, i mean everyday i will get different csv file. but on one machine, everyday it shows me the right format, 

but on another machine it doesnt.

Userlevel 2
Badge +8

Hello @ChanduMohammad thanks for the reply

I understand, but i looked at the setting for list seperator on both machines, and its comma.

Even though its comma, the csv which i download using sftp, i see it in nice shape.

but on other machine it is not like that. I try to understand what could be the difference between both machines

Userlevel 4
Badge +9

@Semih 3318 try this system setting https://answers.microsoft.com/en-us/msoffice/forum/all/system-settings-how-to-import-a-csv-file-that-does/3376be5b-ec44-435a-9058-8d8ae573db2c

Userlevel 2
Badge +8

@rbkadiyam thank you for the reply, i did the language setting the same but still it was same, i will just do this on excel, Data-import csv- seperator (;) then it will work on both machines

Userlevel 2
Badge +8

@Tamil Arasu10 thanks ill go with your solution. I tried to find the missing part that work for one machine and not for other. But ill start adding csv on excel(data-import) thats better way

Reply