Bolding cells in CSV import 1

[nagornyi]

I create CSV file and open it in Excel. Works fine. Now I need to get some items bold in Excel. Is there a way to mark those items in CSV file, like using tags <b> </b> or something like that?

 

[SkipVought]

Hi,

Your .cvs file is ONLY a text file! It has NONE of the features of Excel.

Skip,

_{[red]Be advised:[/red] Researchers have found another Descartes trueism, "Cogito ergo spud."
"I think; therefore, I YAM!}

 

[nagornyi]

Exactly. But I need to impart it with some of the features. How to do this?

 

[SkipVought]

Please explain the entire process from the extract of data to the .cvs to the import to Excel. How do you know that a column should be bolded, for instance?



Skip,

_{[red]Be advised:[/red] Researchers have found another Descartes trueism, "Cogito ergo spud."
"I think; therefore, I YAM!}

 

[nagornyi]

I am exporting data fromo Oracle Reports to Excel. Oracle Reports is known for not providing any standard interface for the export, unlike, for example, Crystal Reports.
What I do is a special report that just dumps the data on screen as CSV. Actually I am using not comma to separate the data, but backslash symbol, because the comma is used by the data. This on-screen dump is not pleasant for an eye, however it can be captured to the text file and then read by Excel. This works fine. Now I want more features for this export, for example I want totals to be bold. In the report, I know where the totals are and can mark them in any special way for the dump. In this manner I can produce a full-fledged HTML file for the on-screen dump, even with javascript. That file if captured and then read by browser shows nicely formatted data... But I don't need HTML (for this assignment), I need Excel...

 

[SkipVought]

Why not query Oracle DIRECTLY with the same query elements that the report uses? Then you don't have all these extra steps.

Data/Get External Data/New Query -- Oracle Files....

After the query format the columns as you want. All you need do is refresh the query.

Skip,

_{[red]Be advised:[/red] Researchers have found another Descartes trueism, "Cogito ergo spud."
"I think; therefore, I YAM!}

 

[nagornyi]

The report data is not just one query. It's a mixture of multiple queries and PL/SQL units bundled togeter with the tools available only inside Oracle Reports.

 

[SkipVought]

Hmmmmm, maybe and maybe not. I'd wager that I could do it in Excel with one or more queries, and then do totals, probably using spreadhseet functionality.

But...

so how do you get a BOLD total in the Oracle report to do something special to the .csv file?

Skip,

_{[red]Be advised:[/red] Researchers have found another Descartes trueism, "Cogito ergo spud."
"I think; therefore, I YAM!}

 

[nagornyi]

Oracle does not export to CSV either. CSV is what I capture from screen. I am in total posession of each character on screen. So, for example, where I need bold in HTML, I replace the mumber with corresponding character string adding <b> </b> tags to it. So I was thinking (I understand it may not be that simple though) of somehow marking the file for Excel also, so that Excel would know that such and such data I need in bold, while those other data in italic, and so on..

 

[SkipVought]

So what difference does it make if you...

change something on the screen and save it in your .csv file

or...

save the .csv file, import it into Excel and format what you want?

Seems to me...

six of one and half dozen of the other...

except that...

option 2 requires a WHOLE LOT LESS WORK!!!

Skip,

_{[red]Be advised:[/red] Researchers have found another Descartes trueism, "Cogito ergo spud."
"I think; therefore, I YAM!}

 

[nagornyi]

Sorry for being unclear. I do not change anything on screen. The report does the markup for me. I just program the report the way I need. Also, the end user agrees to do steps on bringing the data to Excel via the screen dump. However, to do any further work with the spreadsheet is not acceptable. There are thousands of rows in the report. So the user can not, for example, manually bold the line each time the value in column A is changing (againg, this is just for example).

 

[SkipVought]

I am thoroughly confused!!!

You previously stated...

I replace the mumber with corresponding character string adding <b> </b> tags to it.

Is this a global replace, or line by line?


Skip,

_{[red]Be advised:[/red] Researchers have found another Descartes trueism, "Cogito ergo spud."
"I think; therefore, I YAM!}

 

[nagornyi]

That doesn't mean I am replacing anything manually. I do not interfere with the text file captured from screen. "I replace" means I program the report to do the replacement where necessary.

 

[SkipVought]

Then why not do the same thing in Excel?

What is the logic of your program?

Let's get it all out on the table so that I don't have to continue guessing at what's going on!

Skip,

_{[red]Be advised:[/red] Researchers have found another Descartes trueism, "Cogito ergo spud."
"I think; therefore, I YAM!}

 

[nagornyi]

This time I don't understad which "same" thing you mean.
The logic of report is pretty complicated and can not be reproduced in Excel. The logic of that part that makes certain field bold is not that complicated, of cource, but it can not be done outside the report engine. So, once the screen dump is generated, the information on which fields need to be bold is lost, unless we mark the fields somehow (like adding tags) while the report is being generated.

 

[SkipVought]

Sorry, I missed the place whery you stated, "I program the report..." to put these codes in, I guess.

So is it just <b>..</b>?

Skip,

_{[red]Be advised:[/red] Researchers have found another Descartes trueism, "Cogito ergo spud."
"I think; therefore, I YAM!}

 

[SkipVought]

Something like this might work once you got the data into excel

Sub FormatCells()
   For Each s In ActiveSheet.UsedRange
      p1 = 0
      p2 = 0
      For i = 1 To Len(s.Value)
         Select Case Mid(s.Value, i, 3)
            Case "<b>"
               p1 = i
            Case "</b"
               p2 = i
         End Select
      Next
      If p1 > 0 And p2 > p1 Then
         s.Value = Mid(s.Value, p1 + 3, Len(s.Value) - p2 - 2)
      End If
   Next
End Sub

Skip,

_{[red]Be advised:[/red] Researchers have found another Descartes trueism, "Cogito ergo spud."
"I think; therefore, I YAM!}

 

[nagornyi]

Thanks a lot Skip. I appreciate your continuous attention to this problem. As I understand, we can program the Excel somehow to recognize the tags. First, I never used Excel to the extent of programming it, so, if possible, I would appreciate some guidance on how to use this routine. Second, what I see in the module looks for me like it is just stripping the tags off. Where is the place where a cell gets bold? Thanks.

 

[SkipVought]

oops. I forgot to BOLD the cell

Sub FormatCells()
   For Each s In ActiveSheet.UsedRange
      p1 = 0
      p2 = 0
      With s
         For i = 1 To Len(.Value)
            Select Case Mid(.Value, i, 3)
               Case "<b>"
                  p1 = i
               Case "</b"
                  p2 = i
            End Select
         Next
         If p1 > 0 And p2 > p1 Then
            .Value = Mid(.Value, p1 + 3, Len(.Value) - 7)
            .Font.Bold = True
         End If
      End With
   Next
End Sub

I don't know how your process will work. I'd put this code in a module in the workbook that the report will reside in. The event that will run the code will occur after the import. It could be a button click.

Skip,

_{[red]Be advised:[/red] Researchers have found another Descartes trueism, "Cogito ergo spud."
"I think; therefore, I YAM!}

 

[nagornyi]

Thanks a lot! Even without trying yet, I understand this is the way to go for the problem. Very likely I'll be back with more questions, but good enough to start.