How can I import data from Excel and keep the numbers numeric?
Submitted by Dave Rathbun. If you work with reports that include data from Excel, there is a common problem where numeric data that should be treated as character data is imported as numeric. If it comes in with the wrong type, it may be a problem for aggregation, linking, or other variables. So I wrote the following really quick macro that can be used in any Excel sheet to convert a numeric value to a text formula.
Sub Set2Text()
ActiveCell.Select
While Not (IsEmpty(ActiveCell))
ActiveCell.FormulaR1C1 = "=" & """" & ActiveCell.Value & """"
ActiveCell.Offset(1, 0).Select
Wend
End Sub
Simply enter this macro in your worksheet, highlight the first cell, and run the macro. It will go down the column until it reaches a blank cell replacing 1234 with =“1234”. This data will be recognized as character data when the Excel data is imported into BusinessObjects.
Submitted by Brie Anne Clark :
I wrote a macro much like yours a while ago, but mine just adds an apostrophe to the beginning of the cell value to convert it to text.
Sub AddSymbol()
Do Until ActiveCell.Formula = ""
ActiveCell.FormulaR1C1 = "'" & ActiveCell.FormulaR1C1
ActiveCell.Offset(1, 0).Select
Loop
End Sub
Submitted by Dwayne Hoffpauir on 3/17/04:
This is the method that I use for Excel personal data providers, and at the risk of jinxing myself, it never fails:
Instead of using a “live” Excel file to build the data provider, create a sample file. Same column headings, but use dummy data.
Put alpha characters in the columns you want to be alpha, numerics in numeric, and so forth. Maybe 20 rows, but every row the same. It’s usually columns that have ambiguous data (could be alpha, could be numeric) that confuse BusObj.
After creating the data provider, you can now put “live” data in the Excel file, and the definitions will “stick” from now on.
Anita Craig (BOB member since 2002-06-17)