Why the correct sintaxe for COM OpenText() Command with FieldInfo

Osmar Morais

New Member
I´m trying to import data from text files to Excel and I need to define some columns as "text" others as "date", and son on, but, for one of the parameters (the 11º, FiledInfo) I´m not managing to adjust the syntax for OpenEdge COM Object.

I listed below the command generated for Excel macro:

Workbooks.OpenText Filename:="C:\TEMP\es1203a090810-042150.sdf", Origin:= _
xlMSDOS, StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote _
, ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=True, Comma:= _
False, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 2), Array(2, 2) _
, Array(3, 1), Array(4, 1), Array(5, 2), Array(6, 1), Array(7, 2), Array(8, 4), Array(9, 2), _
Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 4), Array(14, 2), Array(15, 1), Array( _
16, 1), Array(17, 1), Array(18, 1), Array(19, 1), Array(20, 1)), TrailingMinusNumbers _
:=True

I tryed:

ASSIGN chWB2 = chXL:Workbooks:OPENTEXT(cFile1,,1,1,2,False,False,True,False,False,False,iFormat,True).

Where "iFormat" is a extent integer variable with the related field type, I used this variable because I don´t know hou to write this information (Array(1,2), Array(2,2), and so on) in OpenEdge COM Sintaxe, I also tryed to use a temp-table and to to enclose in quotas, without success.

If someone had faced this problem and could help me I thank.
 

mosfin

Member
AFAIK you can omit these arguments, just end with: Other="" (i think its character argument, not logical)
also, there is another way to load delimited text into Excel worksheet, i think : hWorksheet:QueryTables:Add(..)
dont remember exactly the arguments, lookup in Google, i'm sure you will find
 

Osmar Morais

New Member
Thanks for your help. I found some examples and I changed to "QueryTables" but then I had the same problem: Excel is cutting some zeroes to the left of character field (for example "0008105" is imported as "8105"). I should use a parameter "TextFileColumnDataTypes" to define the fields types bu I´m not managing to adjust the syntax.

I list below the command I used after had changed to "QueryTables":

ASSIGN chWS1:NAME = "Invoices".
ASSIGN chQT = chWS1:QueryTables:ADD("TEXT;" + cFile1, chWS1:Range("A1")).
ASSIGN chQT:NAME = "Test"
chQT:FieldNames = True
chQT:RowNumbers = False
chQT:FillAdjacentFormulas = False
chQT:preserveFormatting = True
chQT:RefreshOnFileOpen = False
chQT:RefreshStyle = 1 /*xlInsertDeleteCells*/
chQT:SavePassword = False
chQT:SaveData = True
chQT:AdjustColumnWidth = True
chQT:RefreshPeriod = 0
chQT:TextFilePromptOnRefresh = False
chQT:TextFilePlatform = 2 /*xlWindows*/
chQT:TextFileStartRow = 1
chQT:TextFileParseType = 1 /*xlDelimited*/
chQT:TextFileTextQualifier = 1
chQT:TextFileConsecutiveDelimiter = False
chQT:TextFileTabDelimiter = FALSE
chQT:TextFileSemicolonDelimiter = TRUE
chQT:TextFileCommaDelimiter = False
chQT:TextFileSpaceDelimiter = FALSE
chQT:TextFileColumnDataTypes = Array(2, 2, 1, 1, 2, 1, 1, 4, 2, 1, 1, 1, 4, 1, 1, 1, 1, 1, 1, 1).

Except for the last line the rest fo command is executing correctly, but, imports characters fields cutting zeroes, keeping the last line I had sintax error.

I know I could populates the worksheet cell by cell (and line by line) but the performance isn´t acceptable.

In fact, my problem is to import (or to fill) character fields without lose information (in this case the invoice number).
 

mosfin

Member
if you want to preserve the leading zeroes in invoice numbers, try to export them to text file as string (delmited by double quote)
maybe then Excel will recognize it as Text and not 'cut' the leading zeroes
 

Osmar Morais

New Member
Thank you again but as I´m using "EXPORT" command text fields already being exported with double quote (example "0000835"); I tried concatening the field value with CHR(160) too, but this character (CHR(160)) affect the cell value in the worksheet avoiding operations like "PROCV" or others analysis functions; putting a single quote (') doesn´t help because affect the cell value too.

When I try to do manually the importation, the Excel starts the Text Importation Assistant and there I, in 3d phase, I can choose the type of each field, and when I choose "text" the importation is correctly done, I would like to do the same using COM commands. I have browsed several sites, including OpenEdge Knowbase, and I dont find anything.
 

mosfin

Member
hmm.. another idea, although didn't try myself : create Excel template, with the columns pre-formatted accordingly,
then try to load the text into this worksheet using the OpenText method
 

Osmar Morais

New Member
You cannot believe but even I try to set the field type as TEXT even so Excel cuts the left zeroes. I used the command ASSIGN chWS:Columns("B:B"):NumberFormat = "@" before the execution of the importation.
Sorry, but I think this should be a very specific difficult. Thanks a lot.
 

SUDDU23

New Member
You cannot believe but even I try to set the field type as TEXT even so Excel cuts the left zeroes. I used the command ASSIGN chWS:Columns("B:B"):NumberFormat = "@" before the execution of the importation.
Sorry, but I think this should be a very specific difficult. Thanks a lot.
I know its 2017 and Question is from 2010 but still for others to Know..

Here is the solution to all your problem..

DEFINE VARIABLE chExcel AS COM-HANDLE NO-UNDO.
DEFINE VARIABLE chWorkbook AS COM-HANDLE NO-UNDO.
DEFINE VARIABLE chWorksheet AS COM-HANDLE NO-UNDO.
DEFINE VARIABLE chQueryTable AS COM-HANDLE NO-UNDO.
DEFINE VARIABLE lResult AS LOGICAL NO-UNDO.
DEFINE VARIABLE cFullname AS CHARACTER NO-UNDO INIT "C:\wrk\HMEImport.xlsx".
DEFINE VARIABLE ccsvfile AS CHARACTER NO-UNDO INIT "C:\wrk\20170215112431\tt-phy.csv".
DEFINE VARIABLE Array AS INTEGER EXTENT 28 NO-UNDO.
DEFINE VARIABLE i AS INTEGER NO-UNDO.
DEFINE VARIABLE arrayval AS CHARACTER NO-UNDO.
/* DEFINE VARIABLE cConnection AS CHARACTER NO-UNDO. */
CREATE "excel.application" chExcel NO-ERROR.
arrayval = "1, 1, 1, 1, 1, 1, 1, 2, 1, 1, 1, 2, 2, 1, 1, 2, 1, 1, 2, 1, 1, 1, 1, 1, 1, 2, 1, 1".
DO i = 1 TO NUM-ENTRIES(arrayval):
ASSIGN
Array = INTEGER(ENTRY(i, arrayval)) NO-ERROR.
END.



/* cConnection = "TEXT;" + ccsvfile. */
chWorkbook = chExcel:Workbooks:OPEN(cFullname).
chWorksheet = chWorkbook:Worksheets(7).
chQueryTable = chWorkSheet:QueryTables:ADD("TEXT;" + ccsvfile, chWorksheet:Range("$A$2")).


chExcel:DisplayAlerts = FALSE.
ASSIGN
/* chQueryTable:CommandType = 0 */
chQueryTable:Name = "ttPhy"
chQueryTable:FieldNames = True
chQueryTable:RowNumbers = False
chQueryTable:FillAdjacentFormulas = False
chQueryTable:preserveFormatting = True
chQueryTable:RefreshOnFileOpen = False
chQueryTable:RefreshStyle = 1 /* xlInsertDeleteCells */
chQueryTable:SavePassword = False
chQueryTable:SaveData = True
chQueryTable:AdjustColumnWidth = True
chQueryTable:RefreshPeriod = 0
chQueryTable:TextFilePromptOnRefresh = False
chQueryTable:TextFilePlatform = 437
chQueryTable:TextFileStartRow = 1
chQueryTable:TextFileParseType = 1 /* xlDelimited */
chQueryTable:TextFileTextQualifier = 1 /* xlTextQualifierDoubleQuote */
chQueryTable:TextFileConsecutiveDelimiter = False
chQueryTable:TextFileTabDelimiter = False
chQueryTable:TextFileSemicolonDelimiter = False
chQueryTable:TextFileCommaDelimiter = True
chQueryTable:TextFileSpaceDelimiter = False
chQueryTable:TextFileColumnDataTypes = Array
chQueryTable:TextFileTrailingMinusNumbers = TRUE
lresult = chQueryTable:REFRESH
chQueryTable:BackgroundQuery = False
NO-ERROR.

chExcel:VISIBLE = TRUE.

/* MESSAGE chQueryTable:NAME */
/* VIEW-AS ALERT-BOX INFO BUTTONS OK. */

chWorkbook:SaveAs(cFullname,51,,,,,,,,TRUE) NO-ERROR.

chExcel:workbooks:CLOSE(). /* chWorkbook:CLOSE(). */
chExcel:QUIT().

/* Release Com-handle */
IF VALID-HANDLE(chQueryTable) THEN RELEASE OBJECT chQueryTable NO-ERROR.
IF VALID-HANDLE(chWorksheet) THEN RELEASE OBJECT chWorksheet NO-ERROR.
IF VALID-HANDLE(chWorkbook) THEN RELEASE OBJECT chWorkbook NO-ERROR.
IF VALID-HANDLE(chExcel) THEN RELEASE OBJECT chExcel NO-ERROR.
 

Osmar Morais

New Member
I know its 2017 and Question is from 2010 but still for others to Know..

Here is the solution to all your problem..

DEFINE VARIABLE chExcel AS COM-HANDLE NO-UNDO.
DEFINE VARIABLE chWorkbook AS COM-HANDLE NO-UNDO.
DEFINE VARIABLE chWorksheet AS COM-HANDLE NO-UNDO.
DEFINE VARIABLE chQueryTable AS COM-HANDLE NO-UNDO.
DEFINE VARIABLE lResult AS LOGICAL NO-UNDO.
DEFINE VARIABLE cFullname AS CHARACTER NO-UNDO INIT "C:\wrk\HMEImport.xlsx".
DEFINE VARIABLE ccsvfile AS CHARACTER NO-UNDO INIT "C:\wrk\20170215112431\tt-phy.csv".
DEFINE VARIABLE Array AS INTEGER EXTENT 28 NO-UNDO.
DEFINE VARIABLE i AS INTEGER NO-UNDO.
DEFINE VARIABLE arrayval AS CHARACTER NO-UNDO.
/* DEFINE VARIABLE cConnection AS CHARACTER NO-UNDO. */
CREATE "excel.application" chExcel NO-ERROR.
arrayval = "1, 1, 1, 1, 1, 1, 1, 2, 1, 1, 1, 2, 2, 1, 1, 2, 1, 1, 2, 1, 1, 1, 1, 1, 1, 2, 1, 1".
DO i = 1 TO NUM-ENTRIES(arrayval):
ASSIGN
Array = INTEGER(ENTRY(i, arrayval)) NO-ERROR.
END.



/* cConnection = "TEXT;" + ccsvfile. */
chWorkbook = chExcel:Workbooks:OPEN(cFullname).
chWorksheet = chWorkbook:Worksheets(7).
chQueryTable = chWorkSheet:QueryTables:ADD("TEXT;" + ccsvfile, chWorksheet:Range("$A$2")).


chExcel:DisplayAlerts = FALSE.
ASSIGN
/* chQueryTable:CommandType = 0 */
chQueryTable:Name = "ttPhy"
chQueryTable:FieldNames = True
chQueryTable:RowNumbers = False
chQueryTable:FillAdjacentFormulas = False
chQueryTable:preserveFormatting = True
chQueryTable:RefreshOnFileOpen = False
chQueryTable:RefreshStyle = 1 /* xlInsertDeleteCells */
chQueryTable:SavePassword = False
chQueryTable:SaveData = True
chQueryTable:AdjustColumnWidth = True
chQueryTable:RefreshPeriod = 0
chQueryTable:TextFilePromptOnRefresh = False
chQueryTable:TextFilePlatform = 437
chQueryTable:TextFileStartRow = 1
chQueryTable:TextFileParseType = 1 /* xlDelimited */
chQueryTable:TextFileTextQualifier = 1 /* xlTextQualifierDoubleQuote */
chQueryTable:TextFileConsecutiveDelimiter = False
chQueryTable:TextFileTabDelimiter = False
chQueryTable:TextFileSemicolonDelimiter = False
chQueryTable:TextFileCommaDelimiter = True
chQueryTable:TextFileSpaceDelimiter = False
chQueryTable:TextFileColumnDataTypes = Array
chQueryTable:TextFileTrailingMinusNumbers = TRUE
lresult = chQueryTable:REFRESH
chQueryTable:BackgroundQuery = False
NO-ERROR.

chExcel:VISIBLE = TRUE.

/* MESSAGE chQueryTable:NAME */
/* VIEW-AS ALERT-BOX INFO BUTTONS OK. */

chWorkbook:SaveAs(cFullname,51,,,,,,,,TRUE) NO-ERROR.

chExcel:workbooks:CLOSE(). /* chWorkbook:CLOSE(). */
chExcel:QUIT().

/* Release Com-handle */
IF VALID-HANDLE(chQueryTable) THEN RELEASE OBJECT chQueryTable NO-ERROR.
IF VALID-HANDLE(chWorksheet) THEN RELEASE OBJECT chWorksheet NO-ERROR.
IF VALID-HANDLE(chWorkbook) THEN RELEASE OBJECT chWorkbook NO-ERROR.
IF VALID-HANDLE(chExcel) THEN RELEASE OBJECT chExcel NO-ERROR.
Thank you very much for your answer. I already had solved but just today I saw your reply.
 
Top