CSV data conversion

[Pitoun]

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.

 

[Special-K99]

"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]

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.

 

[Fluff]

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]

You can change the configuration temporarily to load these files

Additional settings. Change List separator by ;

 

[Pitoun]

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]

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.

​

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.