Converting program generated pseudo .xls to UTF-8

%3CLINGO-SUB%20id%3D%22lingo-sub-3176683%22%20slang%3D%22en-US%22%3EConverting%20program%20generated%20pseudo%20.xls%20to%20UTF-8%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3176683%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20program%20that%20writes%20a%20file%20in%20the%20following%20format%2C%20and%20gives%20the%20file%20an%20.xls%20file%20extension.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%22Header%20line%201%20...%22%3C%2FP%3E%3CP%3E%22Header%20line%202%20...%22%3C%2FP%3E%3CP%3E%22%22%3C%2FP%3E%3CP%3EHead1%20%26nbsp%3B%20%3CTAB%3E%26nbsp%3B%26nbsp%3B%20Head2%26nbsp%3B%26nbsp%3B%20%3CTAB%3E%26nbsp%3B%26nbsp%3B%20Head3%3C%2FTAB%3E%3C%2FTAB%3E%3C%2FP%3E%3CP%3EData1_1%26nbsp%3B%20%3CTAB%3E%26nbsp%3B%20Data1_2%26nbsp%3B%20%3CTAB%3E%26nbsp%3B%20Data1_3%3C%2FTAB%3E%3C%2FTAB%3E%3C%2FP%3E%3CP%3EData2_1%26nbsp%3B%20%3CTAB%3E%26nbsp%3B%20Data2_2%26nbsp%3B%20%3CTAB%3E%26nbsp%3B%20Data2_3%3C%2FTAB%3E%3C%2FTAB%3E%3C%2FP%3E%3CP%3EData3_1%26nbsp%3B%20%3CTAB%3E%26nbsp%3B%20Data3_2%26nbsp%3B%20%3CTAB%3E%26nbsp%3B%20Data3_3%3CBR%20%2F%3E%3CBR%20%2F%3E(There%20is%20only%20a%20single%20tab%20character%20separating%20the%20Head%23%20fields%20and%20also%20for%20the%20Data%23%20fields%2C%3CBR%20%2F%3EI%20just%20put%20spaces%20around%20%3CTAB%3E%20to%20make%20it%20more%20readable.)%3C%2FTAB%3E%3C%2FTAB%3E%3C%2FTAB%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhen%20the%20file%20is%20opened%20with%20Excel%2C%20it%20gives%20a%20warning%20message%3A%3C%2FP%3E%3CP%3E%22The%20file%20format%20and%20extension%20of%20%3CFILE%3E%20don't%20match.%20...%20Do%20you%20want%20to%20open%20it%20anyway%3F%22%3C%2FFILE%3E%3C%2FP%3E%3CP%3EThe%20user%20clicks%20yes%2C%20and%20the%20file%20opens%20in%20Excel.%26nbsp%3B%20The%20Text%20Import%20Wizard%20is%20not%20invoked.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20code%20was%20changed%20to%20contain%20Chinese%20in%20UTF-8.%26nbsp%3B%20The%20file%20would%20open%2C%20but%20the%20characters%3CBR%20%2F%3Ewere%20garbled%20because%20Excel%20didn't%20know%20it%20was%20UTF-8.%26nbsp%3B%20We%20added%20the%203-byte%20%22byte%20order%20mark%22%3CBR%20%2F%3Efor%20UTF-8%20as%20the%20first%203%20characters%20of%20the%20file.%26nbsp%3B%20The%20Chinese%20characters%20are%20now%20displayed%20correctly.%3CBR%20%2F%3EBut%20now%2C%20when%20the%20file%20is%20opened%2C%20the%20data%20fields%20in%20the%20table%20are%20concatenated%20together%20into%20the%3CBR%20%2F%3Efirst%20cell%20of%20each%20row%2C%20they%20aren't%20put%20into%20sequential%20cells%20in%20the%20row.%3CBR%20%2F%3EData1_1Data1_2Data1_3%3CBR%20%2F%3Erather%20than%20one%20item%20per%20cell%20of%20the%20row.%3CBR%20%2F%3E%3CBR%20%2F%3EIf%20I%20change%20the%20filename%20to%20.xls%2C%20or%20if%20I%20change%20the%20tabs%20to%20commas%2C%20Excel%20will%20open%20the%20file%2C%3CBR%20%2F%3Ebut%20puts%20the%20user%20into%20the%20Text%20Import%20Wizard%2C%20where%20they%20have%20to%20specify%20if%20there%20are%20headers%2C%3CBR%20%2F%3Ewhether%20the%20data%20is%20fixed-width%20or%20delimited%2C%20what%20the%20delimiter%20is%2C%20etc.%3CBR%20%2F%3E%3CBR%20%2F%3EI%20would%20like%20the%20users%20to%20be%20able%20to%20open%20the%20Chinese%20files%20the%20same%20as%20the%20English%20ones%2C%3CBR%20%2F%3Ewhere%20they%20just%20click%20%22Yes%22%20and%20the%20file%20opens%2C%20and%20they%20don't%20have%20to%20go%20through%20the%20hassle%3CBR%20%2F%3Eof%20the%20import%20wizard.%3CBR%20%2F%3E%3CBR%20%2F%3EDo%20you%20have%20any%20suggestions%20for%20a%20small%20fix%3F%3C%2FP%3E%3CP%3EThanks%20in%20advance!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3176683%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EDeveloper%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Visitor

I have a program that writes a file in the following format, and gives the file an .xls file extension.

 

"Header line 1 ..."

"Header line 2 ..."

""

Head1   <TAB>   Head2   <TAB>   Head3

Data1_1  <TAB>  Data1_2  <TAB>  Data1_3

Data2_1  <TAB>  Data2_2  <TAB>  Data2_3

Data3_1  <TAB>  Data3_2  <TAB>  Data3_3

(There is only a single tab character separating the Head# fields and also for the Data# fields,
I just put spaces around <TAB> to make it more readable.)

 

When the file is opened with Excel, it gives a warning message:

"The file format and extension of <file> don't match. ... Do you want to open it anyway?"

The user clicks yes, and the file opens in Excel.  The Text Import Wizard is not invoked.

 

The code was changed to contain Chinese in UTF-8.  The file would open, but the characters
were garbled because Excel didn't know it was UTF-8.  We added the 3-byte "byte order mark"
for UTF-8 as the first 3 characters of the file.  The Chinese characters are now displayed correctly.
But now, when the file is opened, the data fields in the table are concatenated together into the
first cell of each row, they aren't put into sequential cells in the row.
Data1_1Data1_2Data1_3
rather than one item per cell of the row.

If I change the filename to .xls, or if I change the tabs to commas, Excel will open the file,
but puts the user into the Text Import Wizard, where they have to specify if there are headers,
whether the data is fixed-width or delimited, what the delimiter is, etc.

I would like the users to be able to open the Chinese files the same as the English ones,
where they just click "Yes" and the file opens, and they don't have to go through the hassle
of the import wizard.

Do you have any suggestions for a small fix?

Thanks in advance!

0 Replies