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.