Changing data types

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Rick - 23 Aug 2007 22:42 GMT

I am trying to use ODBC to query an Excel spreadsheet. The ODBC driver
looks at the first 8 rows in each column to determine the datatype. I
have mixed numbers and text, but the driver thinks the column is
numeric. As a result, text entries are not returned.

My solution is to change the format of the cells to Text. This works,
but I have to reenter each numeric value in order for Excel to see
them as Text. Is there a quick shortcut for reentering text in
selected cells? Thank you very much.

Rick Quatro

Reply to this Message

Pete_UK - 23 Aug 2007 22:55 GMT

Once you have imported the data, check that the format in these cells
is not set to Text - if it is, highlight the column and change the
format to General (it will not take effect yet). Then select an empty
cell beyond your data (so the format is also set to General) and click
<copy>. Then highlight the column with mixed numbers and text and Edit

| Paste Special | Values (check) | Add (check - in the Operation part)
| OK then <Esc>. This should convert your "numbers" back to numbers.

You might also try Data | Text-to-columns, after first changing the
format to General.

Hope this helps.

Pete

> I am trying to use ODBC to query an Excel spreadsheet. The ODBC driver
> looks at the first 8 rows in each column to determine the datatype. I
[quoted text clipped - 7 lines]
>
> Rick Quatro

Reply to this Message

Rick - 23 Aug 2007 23:08 GMT

Hi Pete,

Actually, I want to convert everything to text. I will give it a try.
Thanks.

Rick

> Once you have imported the data, check that the format in these cells
> is not set to Text - if it is, highlight the column and change the
[quoted text clipped - 22 lines]
>
> > Rick Quatro

Reply to this Message

Pete_UK - 23 Aug 2007 23:19 GMT

Sorry, I'd got it the wrong way round. You could insert a new column
next to the one you want to convert and enter this formula:

=""&A1

and copy this down by double-clicking the fill handle. Then with the
data already highlighted, click <copy> then Edit | Paste Special |
Values (check) | OK then <Enter>. Then you can delete your original
column.

Hope this helps.

Pete

> Hi Pete,
>
[quoted text clipped - 31 lines]
>
> - Show quoted text -

Reply to this Message