Help with Excel CSV import

Posted by Rob Fitzpatrick on 05-Apr-2014 22:15

I'm writing a program to create a new Excel workbook from an existing template (.xltm).  I can do this easily enough manually in Excel.  When I capture that activity with the macro recorder I get VBA code like the following:

    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;C:\temp\file.csv", Destination:=Range("$A$1"))
        '.Name = "file"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 437
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = False
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = True
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 2, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With

Translating that to 4GL I have this (variable declarations etc. omitted):

assign
    v-xl-QT                              = v-excel:ActiveSheet:QueryTables:Add( "TEXT;c:\temp\file.csv",
                                                                                  v-worksheet:range( "$A$1" ) )
    v-xl-QT:Name                         = "file"
    v-xl-QT:FieldNames                   = True
    v-xl-QT:RowNumbers                   = False
    v-xl-QT:FillAdjacentFormulas         = False
    v-xl-QT:PreserveFormatting           = True
    v-xl-QT:RefreshOnFileOpen            = False
    v-xl-QT:RefreshStyle                 = xlInsertDeleteCells
    v-xl-QT:SavePassword                 = False
    v-xl-QT:SaveData                     = True
    v-xl-QT:AdjustColumnWidth            = True
    v-xl-QT:RefreshPeriod                = 0
    v-xl-QT:TextFilePromptOnRefresh      = False
    v-xl-QT:TextFilePlatform             = 437    /* "OEM United States" */
    v-xl-QT:TextFileStartRow             = 1
    v-xl-QT:TextFileParseType            = xlDelimited
    v-xl-QT:TextFileTextQualifier        = xlTextQualifierDoubleQuote
    v-xl-QT:TextFileConsecutiveDelimiter = False
    v-xl-QT:TextFileTabDelimiter         = False
    v-xl-QT:TextFileSemicolonDelimiter   = False
    v-xl-QT:TextFileCommaDelimiter       = True
    v-xl-QT:TextFileSpaceDelimiter       = False
    v-xl-QT:TextFileColumnDataTypes      = ???         
    v-xl-QT:TextFileTrailingMinusNumbers = True
    v-xl-QT:Refresh                      = False
.

My issue is that I don't know how to translate the QueryTable TextFileColumnDataTypes property assignment into 4GL syntax.

MSDN defines this property as an ordered array of constants.  The Progress COM object viewer tells me the setter syntax is: <com-handle>:TextFileColumnDataTypes [ = <anytype>-Var ].  I don't know what to make of that.

Can anyone tell me what the 4GL version of "Array( 1, 2, 1)" should be?

Posted by jmls on 06-Apr-2014 01:24

this article may be of some help : unfortunately I can't get access to
the KB as it is down for maintenance ;)

knowledgebase.progress.com/articles/Article/000028089

Julian

On 6 April 2014 04:15, Rob Fitzpatrick
wrote:
> Help with Excel CSV import
> Thread created by Rob Fitzpatrick
>
> I'm writing a program to create a new Excel workbook from an existing
> template (.xltm). I can do this easily enough manually in Excel. When I
> capture that activity with the macro recorder I get VBA code like the
> following:
>
> With ActiveSheet.QueryTables.Add(Connection:= _
> "TEXT;C:\temp\file.csv", Destination:=Range("$A$1"))
> '.Name = "file"
> .FieldNames = True
> .RowNumbers = False
> .FillAdjacentFormulas = False
> .PreserveFormatting = True
> .RefreshOnFileOpen = False
> .RefreshStyle = xlInsertDeleteCells
> .SavePassword = False
> .SaveData = True
> .AdjustColumnWidth = True
> .RefreshPeriod = 0
> .TextFilePromptOnRefresh = False
> .TextFilePlatform = 437
> .TextFileStartRow = 1
> .TextFileParseType = xlDelimited
> .TextFileTextQualifier = xlTextQualifierDoubleQuote
> .TextFileConsecutiveDelimiter = False
> .TextFileTabDelimiter = False
> .TextFileSemicolonDelimiter = False
> .TextFileCommaDelimiter = True
> .TextFileSpaceDelimiter = False
> .TextFileColumnDataTypes = Array(1, 2, 1)
> .TextFileTrailingMinusNumbers = True
> .Refresh BackgroundQuery:=False
> End With
>
> Translating that to 4GL I have this (variable declarations etc. omitted):
>
> assign
> v-xl-QT =
> v-excel:ActiveSheet:QueryTables:Add( "TEXT;c:\temp\file.csv",
>
> v-worksheet:range( "$A$1" ) )
> v-xl-QT:Name = "file"
> v-xl-QT:FieldNames = True
> v-xl-QT:RowNumbers = False
> v-xl-QT:FillAdjacentFormulas = False
> v-xl-QT:PreserveFormatting = True
> v-xl-QT:RefreshOnFileOpen = False
> v-xl-QT:RefreshStyle = xlInsertDeleteCells
> v-xl-QT:SavePassword = False
> v-xl-QT:SaveData = True
> v-xl-QT:AdjustColumnWidth = True
> v-xl-QT:RefreshPeriod = 0
> v-xl-QT:TextFilePromptOnRefresh = False
> v-xl-QT:TextFilePlatform = 437 /* "OEM United States" */
> v-xl-QT:TextFileStartRow = 1
> v-xl-QT:TextFileParseType = xlDelimited
> v-xl-QT:TextFileTextQualifier = xlTextQualifierDoubleQuote
> v-xl-QT:TextFileConsecutiveDelimiter = False
> v-xl-QT:TextFileTabDelimiter = False
> v-xl-QT:TextFileSemicolonDelimiter = False
> v-xl-QT:TextFileCommaDelimiter = True
> v-xl-QT:TextFileSpaceDelimiter = False
> v-xl-QT:TextFileColumnDataTypes = ???
> v-xl-QT:TextFileTrailingMinusNumbers = True
> v-xl-QT:Refresh = False
> .
>
> My issue is that I don't know how to translate the QueryTable
> TextFileColumnDataTypes property assignment into 4GL syntax.
>
> MSDN defines this property as an ordered array of constants. The Progress
> COM object viewer tells me the setter syntax is:
> :TextFileColumnDataTypes [ = -Var ]. I don't know what
> to make of that.
>
> Can anyone tell me what the 4GL version of "Array( 1, 2, 1)" should be?
>
> Stop receiving emails on this subject.
>
> Flag this post as spam/abuse.



--
Julian Lyndon-Smith
IT Director,
dot.r
http://www.dotr.com

"The bitterness of poor quality remains long after the sweetness of
low price is forgotten"

Follow dot.r on http://twitter.com/DotRlimited

All Replies

Posted by jmls on 06-Apr-2014 01:24

this article may be of some help : unfortunately I can't get access to
the KB as it is down for maintenance ;)

knowledgebase.progress.com/articles/Article/000028089

Julian

On 6 April 2014 04:15, Rob Fitzpatrick
wrote:
> Help with Excel CSV import
> Thread created by Rob Fitzpatrick
>
> I'm writing a program to create a new Excel workbook from an existing
> template (.xltm). I can do this easily enough manually in Excel. When I
> capture that activity with the macro recorder I get VBA code like the
> following:
>
> With ActiveSheet.QueryTables.Add(Connection:= _
> "TEXT;C:\temp\file.csv", Destination:=Range("$A$1"))
> '.Name = "file"
> .FieldNames = True
> .RowNumbers = False
> .FillAdjacentFormulas = False
> .PreserveFormatting = True
> .RefreshOnFileOpen = False
> .RefreshStyle = xlInsertDeleteCells
> .SavePassword = False
> .SaveData = True
> .AdjustColumnWidth = True
> .RefreshPeriod = 0
> .TextFilePromptOnRefresh = False
> .TextFilePlatform = 437
> .TextFileStartRow = 1
> .TextFileParseType = xlDelimited
> .TextFileTextQualifier = xlTextQualifierDoubleQuote
> .TextFileConsecutiveDelimiter = False
> .TextFileTabDelimiter = False
> .TextFileSemicolonDelimiter = False
> .TextFileCommaDelimiter = True
> .TextFileSpaceDelimiter = False
> .TextFileColumnDataTypes = Array(1, 2, 1)
> .TextFileTrailingMinusNumbers = True
> .Refresh BackgroundQuery:=False
> End With
>
> Translating that to 4GL I have this (variable declarations etc. omitted):
>
> assign
> v-xl-QT =
> v-excel:ActiveSheet:QueryTables:Add( "TEXT;c:\temp\file.csv",
>
> v-worksheet:range( "$A$1" ) )
> v-xl-QT:Name = "file"
> v-xl-QT:FieldNames = True
> v-xl-QT:RowNumbers = False
> v-xl-QT:FillAdjacentFormulas = False
> v-xl-QT:PreserveFormatting = True
> v-xl-QT:RefreshOnFileOpen = False
> v-xl-QT:RefreshStyle = xlInsertDeleteCells
> v-xl-QT:SavePassword = False
> v-xl-QT:SaveData = True
> v-xl-QT:AdjustColumnWidth = True
> v-xl-QT:RefreshPeriod = 0
> v-xl-QT:TextFilePromptOnRefresh = False
> v-xl-QT:TextFilePlatform = 437 /* "OEM United States" */
> v-xl-QT:TextFileStartRow = 1
> v-xl-QT:TextFileParseType = xlDelimited
> v-xl-QT:TextFileTextQualifier = xlTextQualifierDoubleQuote
> v-xl-QT:TextFileConsecutiveDelimiter = False
> v-xl-QT:TextFileTabDelimiter = False
> v-xl-QT:TextFileSemicolonDelimiter = False
> v-xl-QT:TextFileCommaDelimiter = True
> v-xl-QT:TextFileSpaceDelimiter = False
> v-xl-QT:TextFileColumnDataTypes = ???
> v-xl-QT:TextFileTrailingMinusNumbers = True
> v-xl-QT:Refresh = False
> .
>
> My issue is that I don't know how to translate the QueryTable
> TextFileColumnDataTypes property assignment into 4GL syntax.
>
> MSDN defines this property as an ordered array of constants. The Progress
> COM object viewer tells me the setter syntax is:
> :TextFileColumnDataTypes [ = -Var ]. I don't know what
> to make of that.
>
> Can anyone tell me what the 4GL version of "Array( 1, 2, 1)" should be?
>
> Stop receiving emails on this subject.
>
> Flag this post as spam/abuse.



--
Julian Lyndon-Smith
IT Director,
dot.r
http://www.dotr.com

"The bitterness of poor quality remains long after the sweetness of
low price is forgotten"

Follow dot.r on http://twitter.com/DotRlimited

Posted by Rob Fitzpatrick on 06-Apr-2014 12:37

Looks like that KB article is what I needed.  Still not quite there yet but you've removed my roadblock.

Thanks Julian!  

This thread is closed