General format not determining correctly the data type

[loslor]

Hi there,

I have an excel sheet in which I export data from a SSIS package. This data has several columns, mainly text and integers. My problem is that, despite the format I put into the cells, Excel is showing all the data as text, even the numeric columns. When I take a look into the format of those cells, they appear as 'General'.

This wouldn't be a serious problem (I can change the type manually) if it weren't because I'm supposed to generate these sheets and send them by mail automatically, and also i have a pivot table within the document that of course is not displaying the data correctly.

Any idea on why could be this happening?

Thx in advance

[Bryan Hessey]

Hi there,

I have an excel sheet in which I export data from a SSIS package.

as import into Excel?

This data has several columns, mainly text and integers. My problem is that, despite the format I put into the cells, Excel is showing all the data as text,

Q. if you select one of these cells and look at the formula bar, is there a leading ' (tick) that does not reflect in the cell?

even the numeric columns. When I take a look into the format of those cells, they appear as 'General'.

This wouldn't be a serious problem (I can change the type manually) if it weren't because I'm supposed to generate these sheets and send them by mail automatically, and also i have a pivot table within the document that of course is not displaying the data correctly.

Any idea on why could be this happening?

Thx in advance

---

[loslor]

Hi Bryan,

The data is exported into excel (not imported) from an external program (Business intelligence studio). I've verified with several people that the data is exported correctly and in the adequate format, so that only leaves me with the excel.

Regarding your question, no, there is no apostrophe in the formula bar in any of the numeric cells.

I've been diving a bit through inet and read that this behavior could be something common when loading data from a database into the excel worksheet.

Any possible workaround for this?


Thx

[Bryan Hessey]

Hi Bryan,

The data is exported into excel (not imported) from an external program (Business intelligence studio). I've verified with several people that the data is exported correctly and in the adequate format, so that only leaves me with the excel.

Regarding your question, no, there is no apostrophe in the formula bar in any of the numeric cells.

I've been diving a bit through inet and read that this behavior could be something common when loading data from a database into the excel worksheet.

Any possible workaround for this?


Thx

There are many articles on this, and I was trying to find some VBA code I spotted the other day to cure the sheet, but so far can only find a reference to setting the format (in VB) to
Selection.Numberformat = xlNumber

I will keep looking.
---

[loslor]

Thx a lot bryan.

[Bryan Hessey]

Thx a lot bryan.

Relating to another post, if you do a =Code(A1) on your data fields, does the code 160 appear?

Found!
the following removes the 160 (no break space) character.

Please Login or Register  to view this content.

---

[annep]

The same problem occurs with data exported from Quickbooks. There is no indication that the column is text, no apostophe or anything, but I do have to convert the numbers to real numbers using copy, pasted special, values, multiply, to convert it.
I just created a macro which seems to work. My data starts a C3 and with macro I enter a 1 into cell C2, than copy and paste the format through the end of the data in column C.

Range("C2").Select
ActiveCell.FormulaR1C1 = "1"
Range("C3").Select
Range(Selection, Selection.End(xlDown)).Select
Range("C2").Select
Selection.Copy
Range("C3").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlMultiply, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False

[Bryan Hessey]

The same problem occurs with data exported from Quickbooks. There is no indication that the column is text, no apostophe or anything, but I do have to convert the numbers to real numbers using copy, pasted special, values, multiply, to convert it.
I just created a macro which seems to work. My data starts a C3 and with macro I enter a 1 into cell C2, than copy and paste the format through the end of the data in column C.

Range("C2").Select
ActiveCell.FormulaR1C1 = "1"
Range("C3").Select
Range(Selection, Selection.End(xlDown)).Select
Range("C2").Select
Selection.Copy
Range("C3").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlMultiply, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False

Good that you have it working, but it shouldn't be necessary to multyiply by 1, and could be dangerous.
Can you attach a small sample of data which is 'corrupt' before you fix it? you can remove or alter any other sensitive fields and excess rows.

--

[loslor]

Relating to another post, if you do a =Code(A1) on your data fields, does the code 160 appear?
---

Negative, I get the code of the first digit in the field.

[loslor]

However you may be pointing in the right direction. I've found this article:

http://support.microsoft.com/kb/291047/en-us

Using method 5, that is, applying the formula that is there, i get the data formatted correctly as number, so it appears theres some more chars besides the digits that makes the Excel believe they are text.

I'll try to see if i could be adding those extra chars when exporting the data. Wish me luck.


Regards

[loslor]

Well, I haven't been able to find any weird character that shouldn't be there when exporting data. So I'll keep working like I'm doing now, that is, with a macro executing on start forcing the Excel to reformat the cells. I don't think it's the best solution but I can't figure out something else.

The code in the macro is as follows:

Please Login or Register  to view this content.

As you can see I use a Name within the workbook that has this code:

Please Login or Register  to view this content.

This allow me to dynamically change the range of cells i need to be reformatted.



Thx and regards.

Edit: sorry for the spam...

[Bryan Hessey]

However you may be pointing in the right direction. I've found this article:

http://support.microsoft.com/kb/291047/en-us

Using method 5, that is, applying the formula that is there, i get the data formatted correctly as number, so it appears theres some more chars besides the digits that makes the Excel believe they are text.

I'll try to see if i could be adding those extra chars when exporting the data. Wish me luck.


Regards

Luck you will need.

To test for such, you can compare
=Len(A1)<>Len(trim(clean(A1)) which, if different, signifies some cleaning has occurred. Pity that 160 is not included.

Try =Find("^",A1)
where ^ is made by holding the Alt key and typing 0160 on the numeric keypad.
(for practice, Alt 0162 is the cents sign)

Don't type Alt 0160 here in a browser.

You can clean a column of 0160 by:
=IF(FIND(" ",A1)=0,A1,LEFT(A1,FIND(" ",A1)-1)&MID(A1,FIND(" ",A1)+1,9999))

or by the VB code shown earlier.

Let us know how you go


Added

Looks like you snuck in a post whilst I was typing (I type slowly, and usually test all code so that I don't feed you too much bull) - looks like time to isolate a couple of your error cells, remove the rest of the sheet, and post them here.
---

---

[loslor]

=Len(A1)<>Len(trim(clean(A1))
Test result negative, it seems the length is the correct one.

I've attached a sample, take a look at it if you can.

Thx a lot!

Edit: I forgot to delete the macros, just disable them.

[Bryan Hessey]

=Len(A1)<>Len(trim(clean(A1))
Test result negative, it seems the length is the correct one.

I've attached a sample, take a look at it if you can.

Thx a lot!

Edit: I forgot to delete the macros, just disable them.

No macros from you found.

Either of my two macros cleans the data

note: sorry about the name, I already had a Sample.

Recommend Sheet4 = Sheet2(3) version, the other macro appears to overwrite cell C2



---

[Bryan Hessey]

Either of the two macros cleans the data

note: sorry about the name, I already had a Sample.
---

Also noted, that if you save the file as '.csv' - close and re-open the data appears clean.

--

[loslor]

Also noted, that if you save the file as '.csv' - close and re-open the data appears clean.

--

Ha, could it be so simple?

Thx a lot for your help Bryan