I need to read data from an XLSX document in my MFC application. It works fine, but now I have a requirement to read in foreign languages, and they are lost.
An example I need to read:
Face and tax
Mom and Bebe
I am currently converting xlsx to csv first using the following script I found online
if WScript.Arguments.Count < 2 Then
WScript.Echo \"Error!Please specify the source path and the destination.Usage: XlsToCsv SourcePath.xls Destination.csv
Wscript.Quit
End If
Dim numRows
Dim numCols
Dim oExcel
Set oExcel = CreateObject("Excel.Application")
oExcel.DisplayAlerts = False
Dim oBook
Set oBook = oExcel.Workbooks.Open(Wscript.Arguments.Item(0))
Set oWorksheet = oBook.Worksheets(1)
oWorksheet.Activate
oWorksheet.Cells.Replace ",", ""
Dim celltxt
celltxt = oWorksheet.Cells(1,1).Text
If InStr(1, celltxt, \"Date\") Then
For count = 13 to 17
oWorksheet.Columns(count).NumberFormat = "0"
Next
Else
For count = 1 to 4
oWorksheet.Columns(count).NumberFormat = "0"
Next
End If
numRows = oWorksheet.UsedRange.Rows.Count
numCols = oWorksheet.UsedRange.Columns.Count
Const adTypeText = 2
Const adSaveCreateOverWrite = 2
Dim BinaryStream
Set BinaryStream = CreateObject("ADODB.Stream")
BinaryStream.Charset = "UTF-8"
BinaryStream.Type = adTypeText
BinaryStream.Open
For r = 1 To numRows
s = ""
For c = 1 To numCols
s = s & oWorksheet.Cells(r, c).Value
If c < numCols Then
s = s & ","
End If
Next
BinaryStream.WriteText s, 1
Next
BinaryStream.SaveToFile WScript.Arguments.Item(1), adSaveCreateOverWrite
BinaryStream.Close
oBook.Close False
oExcel.Quit
As soon as this script runs and I open the resulting csv before any further processing, the data in csv looks great.
Once this csv is processed by the application (Loaded using CRecordSet), the above values look like this:
| S | ¬T || | ¬ TBTP | ¬ | -
||| - | - | - | ¬ | - ||| - ||
, csv " ", " " "Unicode Text".
csv, "CSV ( -)", , :
TЎ Є
|
CSV "CSV (MS-DOS)", , :
, , MS-DOS csv, , -, hte .
!
UPDATE:
, , ( script csv) .txt .csv, , , .
.txt:
From Date,To Date,Store ID,Store Name,Retailer,Retail Format,Region,Cluster,Market Attr 1 (Numeric),Market Attr 2 (Numeric),Market Attr 3 (Text),Market Attr 4 (Text),Market Attr 5 (Text),Product ID,EAN (Barcode),UPC (Product Code),Brand,Description,Size & Uom,Size,Uom,Supplier,Shrink Pack,Minimum Display Depth,KVI,Status,Product Height,Product Width,Product Depth,Supergroup A,Supergroup B,Category,Sub Category,Segment,Sub Segment,Product Attr 1 (Numeric),Product Attr 2 (Numeric),Product Attr 3 (Text),Product Attr 4 (Text),Product Attr 5 (Text),Sales (Value),Units (Volume),Sales at Cost,Ranging Indicator,Fact 1 (Numeric),Fact 2 (Numeric),Fact 3 (Numeric),Fact 4 (Text),Fact 5 (Text)
01/01/2014,31/01/2016,130,Maritza,Clicks,Pharmacy,Plovdiv,A Range,1.99,2.99,Attribute 3,Attribute 4,Attribute 5,1347,7612729000518, 80/ 33,Coca Cola, 80/ 33,330ml,330,ml,Coca Cola,6,2,1,Active,CONVERT,37,36, , ,Carbonated Soft Drinks,Diet,On The Go,Cans,1.99,2.99,Attribute 3,Attribute 4,Attribute 5,136.27,12,122.21,1,1.99,2.99,3.99,Yes,No
01/01/2014,31/01/2016,130,Maritza,,Pharmacy,Plovdiv,,,,,,,1349,5903263245933, 125,, 125,,,,,,,,,66,6,6, , ,,,,,,,,,,,,,,,,,,
01/01/2014,31/01/2016,130,Maritza,,Pharmacy,Plovdiv,,,,,,,1381,, 1 1,, 1 1,,,,,,,,,198,3,1, , ,,,,,,,,,,,,,,,,,,
01/01/2014,31/01/2016,130,Maritza,,Pharmacy,Plovdiv,,,,,,,1607,8716200646536, AC 400,, AC 400,,,,,,,,,123,10,10, , ,,,,,,,,,,,,,,,,,,