Open CSV files in Excel

[Guest]

When I open CSV files (in ANSI format) in Excel programmatically, it works.
However, once I change my CSV file to UTF-16 Little Endian, Excel does not
parse the commas and quotes correctly. The data all show up in the first
column.

I found that if I use tabs instead of commas, Excel recognizes the columns
again.

Is this a known issue?

Thanks!

 

[Peter Huang [MSFT]]

Hi

In a normal situation, Excel is going to use its default code page to open
a UTF CSV files from Windows Explorer. The file needs to be opened from
Excel to have it go through the Text Import Wizard so the proper encoding
can be set.

Some additional information on encoding: When you save the file as Unicode,
the comma in the CSV file is double byte and is not being detected by the
default code page. This is why all of the fields are consolidated when the
file is opened from Explorer, no delimiters are not detected.

Basically, the behavior is by design and Excel is acting normally.

If you still have any concern, please feel free to post here.

Best regards,

Perter Huang
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Guest]

Hello Peter,

First, thanks for your answer.

I have a quick question, related to this topic:
Is there a way to specify to Excel that the CSV is in UTF-16, so that it
takes the specified encoding instead of the default code-page ?

Thanks,
Ingrid.

 

[Peter Huang [MSFT]]

Hi

So far we have no such feature in Excel.
Anyway if you want, you may try to submit a mswish in the link below.
Visit the following Microsoft Product Feedback Web site:
"http://register.microsoft.com/mswish/suggestion.asp" and then complete and
submit the form.

Also I think so far as a workaround you may try to convert the csv into
ansi coding. e.g. you can use notepad to do the job.


Best regards,

Perter Huang
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[kekkonen]

Hi Peter,

I developed an application that exports data (UNICODE coded) to a csv
file.
I need to document that Excel consolidates all of the fields because
it doesn't detect the separator and not for a bug in my application.
Is there some official Microsoft documents or articles that explain
that problem?

Thanks in advance.
Best regards,

Francesco.

 

[NickHK]

Francesco,
How are you saving your file to .csv ?

NickHK

 

[kekkonen]

Hi NickHK,

I have a C++ application that uses MFC classes. So I format my data
in a text string using CString::Format(), then I write this string to
file using CFile::Write().

Do you need more specific info?

Regards.

 

[NickHK]

Francesco,
Why not let Excel do it for you:
WB.SaveAs Filename:=strFileName", FileFormat:=xlCSV

NickHK

 

[kekkonen]

NickHK,

Excel recognizes my file as CSV file but since it was UNICODE encoded,
Excel failes to find the column separator (semicolon in my case) while
it's parsing the file.

P.S.: it seems to me that the code you wrote in your post is written
in VB (or VBScript?) and it seems that the instruction specifies
directly the file format.
In C++ I cannot do that, I can only write my data values formatting
them to be csv compliant.

Thanks and regards,

Francesco

 

[kekkonen]

NickHK,

if I write my data in ANSI and not in UNICODE, Excel recognizes the
semicolon as delimiter (I set semicolon as list delimiter in Control
Panel -> Regional options -> Customize...), so the problem is not the
delimiter that I use but the UNICODE encoding.

 

[NickHK]

Francesco,
If you use the "Import text File" wizard, what to you get ?

NickHK

 

[kekkonen]

NickHK,

if I use the "Import text file" wizard, everything works, because in
the wizard I specify manually what separator is used in my file.

 

[NickHK]

kekkonen,
Well, write code to emulate this Wizard's functionality, which is basically
a QueryTable.Add statement.
Or do not use Unicode.

NickHK

 

[Theo Burt]

NickHK

Hi, I have a mainframe application which generates CSV files. The users
of the files just want to be able to double click to open them. However,
the CSV includes fields such as

...,10/2023,...

which excel converts to a date field (oct-2023). However, these values
are not dates and should not be converted to dates. So far I have tried
using quotes, and using a single apostrophe in front (which still loads
and displays in excel).

Is there anyway of forcing excel to read the field and retain it as
text?

Thanks a lot,
Theo

 

[NickHK]

Theo,
If you just double-click the file, I do not think you can override Excel
"intelligent" interprepretation of you data.
If you use the "Import Text File" wizard, you get the opportunity to set the
data type of each column. Record a macro whilst you do this to see the code.
You could attach such code to a button, with a GetOpenFileName dialog and
format each column on import, assuming you know which is which.

Or can you split your 1 field "10/2023" into 2 fields "10" and "2023" and
combine in Excel ?

NickHK

 

[Theo Burt]

NickHK,

Thanks for your help, is as I suspected. Will probably split the field
in two,
Thanks,
Theo

 

[NickHK]

Theo,
I take it 10/2023 does not really represent a fraction ?

NickHk

 

[Theo Burt]

No, its a common form of key we use, so the value maps to a finite list.
Is not a fraction.

Cheers,
Theo