viewpoint-particle

Author Topic: importing csv while setting column data types.  (Read 2371 times)

jerwah

  • Newbie
  • *
  • Posts: 16
importing csv while setting column data types.
« on: November 11, 2014, 02:26:10 pm »
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]
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

  • Tech Support
  • *****
  • Posts: 2586
    • WinBatch
Re: importing csv while setting column data types.
« Reply #1 on: November 11, 2014, 02:52:26 pm »
Wild guess but assuming you have a relatively new version of WinBatch, you might give this a try
Code: Winbatch
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
TFCDT = ArrDimension(6)
ArrInitialize(TFCDT, 2)
oQ.TextFileColumnDataTypes = TFCDT
 


"Success is a lousy teacher. It seduces smart people into thinking they can't lose."
  - Bill Gates


jerwah

  • Newbie
  • *
  • Posts: 16
Re: importing csv while setting column data types.
« Reply #2 on: November 12, 2014, 03:10:39 pm »
THANK YOU! Worked perfectly.