Import csv into Excel - FieldInfo

S

Skc

Member
Import csv into Excel 2007 - FieldInfo

I am trying first time to open Excel (Excel 2007 Trial version) from Progress (OE10.1B) and to import a csv file into Excel. The file is delimited by semi-colon rather than comma. The current codes :-

Def var ExAppl as com-handle.
CREATE "Excel.Application" ExAppl.
ExAppl:Visible=true.
ExAppl:Workbooks:OpenText ( "D:\McData\Cust92\arprsagt.dat",,,1,,,,True,,,, ,).


Everything is default except for '1' which should mean it is a delimited file and 'True' meaning it is delimited by semi-colon.

Question:
How to format the columns? Assuming I have 2 columns - 1 Text column and 1 Amount column - how do I issue commands to tell Excel that I want the 2nd-column to display upto 2 decimal places or that I want negative numbers to be displayed in ( ) . Should I use the FieldInfo (which is the 2nd last-parameter that is left blank above) and if so, what is the syntax. I am stumped by the syntax here.

Thanks for any clues.
 
assuming your 2nd column is column B of the sheet, you might want to do something like this:

Code: 
Def var ExAppl as com-handle.

CREATE "Excel.Application" ExAppl.
ExAppl:Visible=true.
ExAppl:Workbooks:OpenText ( "D:\McData\Cust92\arprsagt.dat",,,1,,,,True,,,, ,).

ExApp:range("B:B"):NumberFormat = "#,##0.00_);[Red](#,##0.00)".
Please let me know if this works. I just formated my machine, so theres noo way I can test it right now.
 
Your code should work but it does not in my case. Because there is another problem - the negative numbers I have in the csv files are with TRAILING minus sign. After import these negatiive numbers seem not be recognised as negative numbers by Excel.

Tried setting TrailingMinusNumbers which is the last parameter to true (as shown below)- but does not work.

ExAppl:Workbooks:OpenText ( "D:\McData\Cust92\arprsagt.dat",,,1,,,,True,,,, ,True).

My guess is that the Fieldinfo (a 2-dim array) - which comes just before TrialingMinusNumbers parameter field - might be a cause of the error.

Anyone has a solution ?
 
If the source file does not have trailing negative signs, then the problem might be during the import. Have you already tried using Excel function Open() instead of OpenText()? I have done this before and should work.

Much better if you can attach the file so anybody can help you figure it out..

Regards
 
My source file DOES have trailing minus sign. I have done a Google search and it is confirmed that Trailing minus sign is treated as a Text rather than numeric.

Via Progress Com Object Viewer, "Workbooks:OpenText()" shows 13 parameters available which is upto FieldInfo and excludes TrialingMinusNumbers parameter (which should be the next parameter according to Excel-VBA Record-Macro) . I don't think there is a way to set it to true from within Progress. However, interestingly, if you set the 17th parameter to TRUE, it might work to a certain extent - but I am not sure whether it is reliable (Excel 2007).

Attached is a simple SampleTest.dat if you want to give it a shot.

Regards
 

Attachments

Woow.. If this s the case, I might think that you have to check each cell in a column if it has trailing minus sign and manipulate the value of the cell by 4gl.

This will remove the trailing minus sign in column B and format the column to Decimal. Please let me know what you think.

Code: 
Def var ExAppl as com-handle.
DEFINE VARIABLE iRow AS INTEGER     NO-UNDO INIT 1.
DEFINE VARIABLE cTrail AS CHARACTER   NO-UNDO.
DEFINE VARIABLE BCellValue AS CHARACTER   NO-UNDO.


CREATE "Excel.Application" ExAppl.
ExAppl:Workbooks:OPENTEXT( "D:\sampleTest.dat",,,1,,,,True,,,, ,).

REPEAT:
    IF ExAppl:range('A' + STRING(iRow)):VALUE = '' THEN LEAVE.

    ASSIGN iRow  = iRow + 1            
           BCellValue = ExAppl:range('B' + STRING(iRow)):VALUE.
           
    ASSIGN BCellValue = SUBSTRING(BCellValue,1,LENGTH(BCellValue) - 1)  
                            WHEN SUBSTRING(BCellValue,LENGTH(BCellValue),1) = '-'
                        
                        ExAppl:range('B' + STRING(iRow)):VALUE = BCellValue.
END.
ASSIGN 
    ExAppl:range("B:B"):NumberFormat = "#,##0.00_);[Red](#,##0.00)"
    ExAppl:Visible=true.

RELEASE OBJECT exappl.
This should work.
 
Yes your code does work except that I think you missed out to put the "-" sign infront. This is definitely one option I can use - thank you.

ASSIGN BCellValue = "-" + SUBSTRING(BCellValue,1,LENGTH(BCellValue) - 1)
WHEN SUBSTRING(BCellValue,LENGTH(BCellValue),1) = '-'
ExAppl:range('B' + STRING(iRow)):VALUE.

[Below: Transferred to new Thread]
Out of curiosity, I am also exploring the 17th parameter of "Workbooks:OpenText()" . In my case, it seems to work when I set it to True and it will convert Trailing minus sign to Leading minus sign in all columns with Excel 2007. If someone can also confirm that it works especially with older versions of Excel, it would be interesting . [I have attached a bigger Test file if anyone is interested ] I am transferring this to a New Post as negative numbers does not fit the Title I gave for this thread.
 

Attachments

You must log in or register to reply here.
Back
Top