2

I have 3 .txt files and I am using COM-Handle to create an Excel sheet. Can anyone help me how to set the delimiter? My Excel sheet is getting created but the columns are not comma separated.

Please see attached screenshot of the file I got. I want it to be in different columns.

Screen shot of issue in Excel

Tom Bascom
  • 13,405
  • 2
  • 27
  • 33
Nidhi
  • 25
  • 4

3 Answers3

1

DEFINE VARIABLE chExcel AS COM-HANDLE NO-UNDO. DEFINE VARIABLE chWorksheet1 AS COM-HANDLE NO-UNDO. DEFINE VARIABLE chWorksheet2 AS COM-HANDLE NO-UNDO. DEFINE VARIABLE chWorkbook1 AS COM-HANDLE NO-UNDO. DEFINE VARIABLE chWorkbook2 AS COM-HANDLE NO-UNDO. DEFINE VARIABLE iQtArq AS INTEGER NO-UNDO. DEFINE VARIABLE iPos AS INTEGER NO-UNDO. DEFINE VARIABLE idx AS INTEGER NO-UNDO.

DEFINE TEMP-TABLE tt-arq
    FIELD nomeArq AS CHAR.
CREATE tt-arq.
tt-arq.nomeArq = "C:\damgra\excel\Test1.txt".
CREATE tt-arq.
tt-arq.nomeArq = "C:\damgra\excel\Test2.txt".
CREATE tt-arq.
tt-arq.nomeArq = "C:\damgra\excel\Test3.txt".


CREATE "excel.application" chExcel.

iQtArq = 0.

FOR EACH tt-arq.
    iQtArq = iQtArq + 1.

    IF iQtArq = 1 THEN DO:    
        chWorkbook1=chExcel:Workbooks:Open(tt-arq.nomeArq).
        chWorksheet1=chWorkbook1:Worksheets(1).
        chWorksheet1:NAME = "test" + STRING(iQtArq).
        NEXT.
    END.

    iPos = chWorkbook1:sheets:COUNT .

    chWorkbook2=chExcel:Workbooks:Open(tt-arq.nomeArq).

    DO idx = 1 TO (chWorkbook2:sheets:COUNT):
        iPos = iPos + 1.
        chWorksheet2=chWorkbook2:Worksheets(idx).
        chWorksheet2:NAME = "plan" + STRING(iPos).
    END.

    chWorksheet2=chWorkbook2:Worksheets(1).
    chWorksheet1=chWorkbook1:Worksheets(chWorkbook1:sheets:COUNT).
    chWorksheet1:Activate.
    chWorkbook2:Sheets:move(,chWorksheet1).    
END.

chWorksheet1=chWorkbook1:Worksheets(1).
chWorksheet1:Activate.

chExcel:visible=true.

IF valid-handle(chWorksheet1) THEN RELEASE OBJECT chWorksheet1.
IF valid-handle(chWorksheet2) THEN RELEASE OBJECT chWorksheet2.
IF valid-handle(chWorkbook1 ) THEN RELEASE OBJECT chWorkbook1 .
IF valid-handle(chWorkbook2 ) THEN RELEASE OBJECT chWorkbook2 .
IF valid-handle(chExcel )     THEN RELEASE OBJECT chExcel.
Nidhi
  • 25
  • 4
  • Ok, I see it. Working in this way, like I said, you're going to need to output field by field to the cells . I believe if you'd just like to export, you'd be better off doing it by exporting it to a csv (as text) and using the export command using the delimiter you prefer. Then open it in Excel at the end, the result is going to be the same. Let me know if you need any help with that. – bupereira Jan 03 '18 at 15:23
0

I am very familiar with Excel but not so much with generating Excel documents via 4GL, but I'll risk an answer anyhow:

I suspect that you are creating text-based files for Excel (ie., non-binary or non-XLS format). Simply adding commas will not create separate columns.

A couple things you could try:

  • If the generated file is indeed text (ie., you can open it with Notepad) then, make sure the extension of the filename is CSV, like c:\mypath\MyFile.csv.

When a file with a .csv extension is opened in Excel, it will separate commas into columns.

Example:

1) Open NOTEPAD.EXE

2) Copy/Paste in this text:

This is my CSV File called: myFile.csv
This is column A,and this is column B, column C,D,E,F
If I need to include a comma in the text,"I wrap it, in quotes, in the CSV file."
A,B,C,D,E,F,G

3) Save the file with name: myFile.csv

4) Close the file and then double-click† to open it.

†(If Excel isn't associated with the .CSV file type type on your system, you may need to open Excel and then open the CSV file from within Excel.)

csv1 csv2

Excel won't AutoFit the columns (or allow any other formatting) in a CSV file but double-clicking between column heads will AutoFit. csv3

Another option, instead of using commas, use Tab characters (ASCII Code 9). Excel may also recognize tabs as a column-separator.

ashleedawg
  • 20,365
  • 9
  • 72
  • 105
  • "When a file with a .csv extension is opened in Excel, it will separate commas into columns.": Not true in that general. It fails if comma is not the systems list separator given in locale settings. It fails if comma **is** the systems list separator but the `*.csv` is encoded UTF-8 but does not have a BOM. It fails if comma **is** the systems list separator but the `*.csv` is encoded in any other unicode encoding ... ;-). – Axel Richter Jan 02 '18 at 09:02
  • 1
    Ah yes, you're right. I sometimes forget that dates, times and keyboards are not the only region-specific settings. Thanks for clarifying. – ashleedawg Jan 02 '18 at 09:07
0

Ashleedawg's answer is probably correct, but let me give some Progress side context: It is most likely a problem of how you're exporting this. If you're turning your TXT files into spreadsheets, it will depend on whether you're exporting this to Excel as a spreadsheet, and then you'll have to name the cells you want data to go to, or you export it as a CSV, and Comma should be recognized as the delimiter, unless the user computer specifically uses another symbol instead as the default, in a nutshell. So if you wish to generate a CSV, the com-handle is really not required. You can use a vanilla OUTPUT TO and make it all text, or if you go with com-handle, you'll most likely have to handle it field by field (of course you could cycle the fields in a buffer using BUFFER-FIELD and name the cells with (line integer,col integer), if memory serves me well). Anyway, some code for us to analyze how you're exporting this would shed some more light on it.

bupereira
  • 1,463
  • 8
  • 13