Crossing the bar (delimited)

[spl]

From some earlier threads I pointed out challenges importing csv/text files into SQL Server/SQLite as typed columns. Quoted columns would import as varchar/nvarchar even if the data was numeric or date. The solution was to take steps to convert files to bar-delimited and ADO would cast them accordingly.

When working with converting multi-tabbed Excel into csv files the standard Excel Saveas() with file format 6 (csv) would have issues with foreign chars when converted to bar-delimited. The file format 62 for csv UTF-8 would solved that issue but it appears it is not recognized through Office 2016 [can't try it with 365].

My solution, at this point, is to use format 42, [unicode] which creates a tab-delimited output with foreign characters intact but, unfortunately quotes columns with spaces in column name.

I have attached a spreadsheet within the zip file which illustrates.  Code to process it [quite basic and ugly]

Code (WINBATCH) Select Expand

IntControl (40,3, 0, 0, 0)
dir = "C:\samples\"  ;change as needed
XLFile = dir:"suppliers.xlsx"
oXL = CreateObject("Excel.Application")
oXL.Visible = @false
oXL.DisplayAlerts = @false
WK = oXL.Workbooks.Open(XLFILE)
list = ""
ForEach ws In oXL.ActiveWorkbook.Worksheets
  ws.Activate()
  ws.Copy()
  n = ws.Name
  csv = dir:n:".csv"
  If fileexist(csv) Then filedelete(csv)
  list = list:csv:","
  oXL.ActiveWorkbook.SaveAs(csv,42)
Next

oXL.ActiveWorkbook.Close()
oXL.Quit
oXL=0

list = strsub(list,1,strlen(list)-1)
For i = 1 to ItemCount(list,",")
    var = ItemExtract(i,list,",")
    file = FileGetW(var,"")
    file = strreplace(file,@TAB,"|")
    file = strreplace(file,'"',"")
    fileput(var,file)
    If fileexist(var) Then Display(3,"Bar-Delimited file Created",var)
Next
Exit