importing csv while setting column data types.

[jerwah]

So the following code works correctly with the exception of these two lines:
   TFCDT=Arrayize("2,2,2,2,2,2",",")
   oQ.TextFileColumnDataTypes = TFCDT

I am trying to import a csv and force the columns to be text so as to not lose the formatting. 

In a macro the line comes up:
         .TextFileColumnDataTypes = Array(2,2,2,2,2,2)

Can't quite seem to figure out how to do that in wbt.

TIA!
jerway

Code Select Expand

xlInsertDeleteCells = 1
	xlDelimited = 1
	xlTextQualifierDoubleQuote=1

	cCSV               = WorkFile
	cXLS               = StrReplace( StrUpper(cCSV), ".CSV",".XLS")
	cText              = StrCat("Transferring %cCSV%",@CRLF,"TO %cXLS%")
	IF FileExist(cXLS) Then FileDelete(cXLS)
	cURL = StrCat("TEXT;",Workfile)
	
	oXL                  = ObjectOpen("Excel.Application")
	oXL.Visible          = @TRUE   ;if testing have this set to @TRUE until you are sure 
	oXL.UserControl      = @TRUE	 ;if testing have this set to @TRUE until you are sure 
	oXL.DisplayAlerts    = @TRUE   ;if testing have this set to @TRUE until you are sure 
	oXL.ScreenUpdating   = @TRUE   ;if testing have this set to @TRUE until you are sure 
	oXL.Workbooks.Add()
	oWS = oXL.ActiveWorkbook.Worksheets("Sheet1")
	oWS.Activate
	oQ = oWS.QueryTables.Add(:: Connection=cURL,Destination=oWS.Range("A1"))

        oQ.Name = "test"
        oQ.FieldNames = @True
        oQ.RowNumbers = @False
        oQ.FillAdjacentFormulas = @False
        oQ.PreserveFormatting = @True
        oQ.RefreshOnFileOpen = @False
        oQ.RefreshStyle = xlInsertDeleteCells
        oQ.SavePassword = @False
        oQ.SaveData = @True
        oQ.AdjustColumnWidth = @True
     	oQ.RefreshPeriod = 0
	oQ.TextFilePromptOnRefresh = @False
	oQ.TextFilePlatform = 437
	oQ.TextFileStartRow = 1
	oQ.TextFileParseType = xlDelimited
	oQ.TextFileTextQualifier = xlTextQualifierDoubleQuote
	oQ.TextFileConsecutiveDelimiter = @False
	oQ.TextFileTabDelimiter = @True
	oQ.TextFileSemicolonDelimiter = @False
	oQ.TextFileCommaDelimiter = @True
	oQ.TextFileSpaceDelimiter = @False
	TFCDT=Arrayize("2,2,2,2,2,2",",")
	oQ.TextFileColumnDataTypes = TFCDT
	oQ.TextFileTrailingMinusNumbers = @True
	oQ.Refresh()

	oXL.Visibile=@TRUE
	oXL.ScreenUpdating=@TRUE

[td]

Wild guess but assuming you have a relatively new version of WinBatch, you might give this a try

Code  winbatch Select Expand

TFCDT=Arrayize("2,2,2,2,2,2",",")
oQ.TextFileColumnDataTypes = ObjectType("array|i4", TFCDT)

Or if you have an older version of WinBatch maybe

Code  winbatch Select Expand

TFCDT = ArrDimension(6)
ArrInitialize(TFCDT, 2)
oQ.TextFileColumnDataTypes = TFCDT

[jerwah]

THANK YOU! Worked perfectly.