Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Open CSV files in Excel
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Open CSV files in Excel
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Open CSV files in Excel
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Open CSV files in Excel
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Open CSV files in Excel
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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Open CSV files in Excel
Francesco,
How are you saving your file to .csv ? NickHK "kekkonen" wrote in message om... 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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Open CSV files in Excel
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" wrote in message ... Francesco, How are you saving your file to .csv ? NickHK |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Open CSV files in Excel
Francesco,
Why not let Excel do it for you: WB.SaveAs Filename:=strFileName", FileFormat:=xlCSV NickHK "kekkonen" wrote in message om... 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" wrote in message ... Francesco, How are you saving your file to .csv ? NickHK |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Open CSV files in Excel
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 "NickHK" wrote in message ... Francesco, Why not let Excel do it for you: WB.SaveAs Filename:=strFileName", FileFormat:=xlCSV NickHK |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Open CSV files in Excel
Francesco,
You say this is CSV file. "C" stands for comma. Then you say the delimiter is ";". If so, of course Excel will not find the fields, as there is only 1 field per row. Use a comma as the delimiter, or do not call your files csv. Hope that helps NickHK "kekkonen" wrote in message om... 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 "NickHK" wrote in message ... Francesco, Why not let Excel do it for you: WB.SaveAs Filename:=strFileName", FileFormat:=xlCSV NickHK |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Open CSV files in Excel
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" wrote in message ... Francesco, You say this is CSV file. "C" stands for comma. Then you say the delimiter is ";". If so, of course Excel will not find the fields, as there is only 1 field per row. Use a comma as the delimiter, or do not call your files csv. Hope that helps NickHK |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Open CSV files in Excel
Francesco,
If you use the "Import text File" wizard, what to you get ? NickHK "kekkonen" wrote in message om... 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" wrote in message ... Francesco, You say this is CSV file. "C" stands for comma. Then you say the delimiter is ";". If so, of course Excel will not find the fields, as there is only 1 field per row. Use a comma as the delimiter, or do not call your files csv. Hope that helps NickHK |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Open CSV files in Excel
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" wrote in message ... Francesco, If you use the "Import text File" wizard, what to you get ? NickHK |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Open CSV files in Excel
kekkonen,
Well, write code to emulate this Wizard's functionality, which is basically a QueryTable.Add statement. Or do not use Unicode. NickHK "kekkonen" wrote in message m... 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" wrote in message ... Francesco, If you use the "Import text File" wizard, what to you get ? NickHK |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
Open CSV files in Excel
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 *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
Open CSV files in Excel
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" wrote in message ... 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 *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#17
Posted to microsoft.public.excel.programming
|
|||
|
|||
Open CSV files in Excel
NickHK,
Thanks for your help, is as I suspected. Will probably split the field in two, Thanks, Theo *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#18
Posted to microsoft.public.excel.programming
|
|||
|
|||
Open CSV files in Excel
Theo,
I take it 10/2023 does not really represent a fraction ? NickHk "Theo Burt" wrote in message ... NickHK, Thanks for your help, is as I suspected. Will probably split the field in two, Thanks, Theo *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#19
Posted to microsoft.public.excel.programming
|
|||
|
|||
Open CSV files in Excel
No, its a common form of key we use, so the value maps to a finite list.
Is not a fraction. Cheers, Theo *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
emailing files from excel, the files will not go until I open up . | New Users to Excel | |||
how do I toggle between 2 open excel files and leave both open | Excel Discussion (Misc queries) | |||
How to change default Open/Files of Type to "Microsoft Excel Files | Excel Discussion (Misc queries) | |||
I cant open files unless I open the Excel program first | Excel Discussion (Misc queries) | |||
Can not open excel files without open application | Excel Discussion (Misc queries) |