CSV data conversion

Pitoun

New Member
Joined
Jun 23, 2015
Messages
17
Hello there,

Since I installed office 2019 Excel I got data conversion problem when opening a CSV file.

problem :
when opening a genuine csv file form bank or postal account some lines show data on conol B or C
So if a run data conversion I got a message telling that this action will overwrite cells containig data.

In my mind when a CSV file is opened in Excel all data should be shown only on colon A ! Isn't it so ???

What I tried :
- Office repair short and complete
- Office uninstall, MS office uninstall tool, reboot, reinstall

....and I still have some data in other colons than A when opening a CSV file.
 
Last edited by a moderator:

Some videos you may like

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,350
"In my mind when a CSV file is opened in Excel all data should be shown only on colon A ! Isn't it so ???"

Nope. A CSV file is a comma separated vraiable file. Each comma in the CSV file defines where data ends.
Only if there are no commas will the data be shown in column A.
Or if the file is .TXT file it will show in column A only.

Depending on your location in the world the comma may be replaced by a semi-colon ";"
This may explain why your data is showing in the next column, each semi-colon is defining where the data ends.
 

Pitoun

New Member
Joined
Jun 23, 2015
Messages
17
CSV file arrives in following format with semicolon separator :

Date;Heure;Appelant;Vers;Service;Durée/Quantité/KB;Montant
31.01.2019;19:49:58;="0000000000";="0000000000";SMS tous les réseaux, inclus;1;0.00
31.01.2019;16:48:00;="0000000000";="0000000000";0800 Numéros gratuits nationaux;00:28:38;0.00
31.01.2019;16:47:18;="0000000000";="0000000000";0800 Numéros gratuits nationaux;00:00:30;0.00
31.01.2019;16:42:34;="0000000000";="0000000000";Réseau fixe Swisscom, inclus;00:02:45;0.00
31.01.2019;16:25:01;="0000000000";="0000000000";Appels déviés, COMBOX®, inclus;00:00:22;0.00
31.01.2019;14:10:24;="0000000000";="0000000000";Réseau fixe Swisscom, inclus;00:05:53;0.00
31.01.2019;11:01:13;="0000000000";="0000000000";Réseau mobile Swisscom, inclus;00:00:43;0.00
31.01.2019;10:55:16;="0000000000";="0000000000";Réseau mobile Swisscom, inclus;00:03:54;0.00
31.01.2019;08:55:35;="0000000000";="";Données au volume, inclus;2;0.00
31.01.2019;00:00:00;="0000000000";="";Données au volume, inclus;38884;0.00
30.01.2019;18:37:37;="0000000000";="";Données au volume, inclus;12300;0.00
30.01.2019;18:03:29;="0000000000";="";Données au volume, inclus;27;0.00
30.01.2019;17:15:06;="0000000000";="0000000000";Réseau mobile Swisscom, inclus;00:00:05;0.00
30.01.2019;17:15:01;="0000000000";="0000000000";Appels déviés, COMBOX®, inclus;00:00:05;0.00
30.01.2019;13:49:26;="0000000000";="0000000000";Réseau mobile Swisscom, inclus;00:00:57;0.00
30.01.2019;13:09:52;="0000000000";="0000000000";Appels déviés, COMBOX®, inclus;00:00:03;0.00
30.01.2019;09:35:06;="0000000000";="";Données au volume, inclus;74018;0.00
30.01.2019;09:35:00;="0000000000";="0000000000";Appels déviés, COMBOX®, inclus;00:00:49;0.00
30.01.2019;09:22:02;="0000000000";="";Données au volume inclus;462;0.00
30.01.2019;09:11:18;="0000000000";="0000000000";Réseau mobile d'autres opérateurs inclus;00:00:11;0.00
30.01.2019;09:08:47;="0000000000";="0000000000";Appels déviés COMBOX® inclus;00:00:02;0.00
30.01.2019;00:00:00;="0000000000";="";Données au volume inclus;98405;0.00
29.01.2019;17:42:31;="0000000000";="0000000000";SMS MT: Ticket SMS TPG TPG1;1;3.00
29.01.2019;17:24:40;="0000000000";="";Données au volume inclus;379304;0.00
29.01.2019;15:02:35;="0000000000";="";Données au volume inclus;4;0.00
29.01.2019;15:01:18;="0000000000";="0000000000";Danemark inclus;00:01:16;0.00
29.01.2019;13:44:48;="0000000000";="0000000000";084x Tarifs variables (Réseau mobile);00:05:18;0.50
29.01.2019;13:44:36;="0000000000";="0000000000";084x Tarifs variables (Réseau mobile);00:00:04;0.10
29.01.2019;10:09:18;="0000000000";="0000000000";Réseau fixe Swisscom inclus;00:03:03;0.00
29.01.2019;09:28:43;="0000000000";="0000000000";Réseau mobile Swisscom inclus;00:00:47;0.00
29.01.2019;09:04:58;="0000000000";="0000000000";Réseau fixe Swisscom inclus;00:22:16;0.00
29.01.2019;00:00:00;="0000000000";="";Données au volume inclus;40;0.00
28.01.2019;16:02:06;="0000000000";="0000000000";Réseau mobile Swisscom inclus;00:09:02;0.00
28.01.2019;15:58:12;="0000000000";="0000000000";Appels déviés COMBOX® inclus;00:00:06;0.00
28.01.2019;13:58:24;="0000000000";="0000000000";Réseau fixe Swisscom inclus;00:10:06;0.00
28.01.2019;12:22:52;="0000000000";="0000000000";Réseau mobile d'autres opérateurs inclus;00:01:04;0.00

when I open this file with Exel 2019
line 2, 5, 7-14, 18 and 18 are splited in colon B
and line 6, 15, 17 and 19 are splited in colon B and C

The problem comes from wrong interpretation of comma.
This is happening before applying data conversion with only semicolon separator selected.

Does anyone have a clue how to avoid this "natural" comma splitting ?


My regional seeting are Swiss-French.
 
Last edited:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,264
Office Version
  1. 365
Platform
  1. Windows
Rather than opening the file "normally", on the Data tab select "From Text" & import the file. That way you get to select the delimiter & what format the columns should be.
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows

ADVERTISEMENT

You can change the configuration temporarily to load these files





Additional settings. Change List separator by ;
 

Pitoun

New Member
Joined
Jun 23, 2015
Messages
17
Okay, your methode gives the correct result. But it takes a bit more clicks to open it.
Excel 2016 was not setup with this automatic comma splitting feature when double clicking on a CSV file.

I look for a way to avoid Excel 2019 to split CSV files contents when a comma is encountered ? No idea ?
 

Pitoun

New Member
Joined
Jun 23, 2015
Messages
17
You're right !

My regional list separator was set to comma.
Ichanged it to semicolon and now values are displayed in colons just by double clicking CSV file.
No need anymore to do a data conversion.

Thank you.
:rolleyes:
(y)
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows
I'm glad to help you. Thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,910
Messages
5,525,587
Members
409,652
Latest member
strangelyangely

This Week's Hot Topics

Top