1

I have this sql server query that I am running in my .net app.

(CONVERT(VARCHAR(8), EventDate, 112)+ substring(RequestedBy,1,1)+right( '0000000' + convert( varchar( 7 ), ContactID ), 7 )) as Contacts

It produces the following results in the following format:

20120731e0000001 20120731f0000002 20120731p0000003

This is the result and format that we want.

Problem is when we click on export icon to export these results to excel, the first one changes to the scientific format like 2.01E+08.

Any date that has e in the middle such as 20120731*e*0000001 turns into scientific data.

The rest is just fine.

Any ideas how to fix this?

I want to apologize in advance if I stick the wrong tag in the Tags section since I am not sure where the fix could come from.

Kenny
  • 1,058
  • 4
  • 20
  • 49

2 Answers2

2

The formatting is happening when Excel opens your exported file. Simply change the column to have "formatted text" of string so that it displays as the original format.

Brian Scott
  • 9,221
  • 6
  • 47
  • 68
  • @deathApril: The perils of giving answers when you're on your mobile :-) – Brian Scott Apr 27 '12 at 16:01
  • Thanks all for your kind responses. What I am getting so far is there is no programmatic way of forcing Excel to NOT translate e as exponential. – Kenny Apr 27 '12 at 16:26
  • 1
    @Kenny you're correct - Excel decides how to interpret the data and there's nothing you can do except switch to a manual import, or a more intelligent data format. I went through this same exercise years ago. – Mark Ransom Apr 27 '12 at 19:06
  • Thanks all. For now, until there is some sort of solution, I will switch from e to m. – Kenny Apr 27 '12 at 19:48
1

When you open the exported data file directly with Excel, all formats are set as General. In General format, Excel applies formatting to what it recognizes as numbers and dates. So, to be clear, the issue is not with your export, but rather with how Excel is reading your data by default. Try the following to get around this issue.

Export to CSV. Then, rather than opening the CSV in Excel, use Excel's 'Get External Data From Text' tool to import the data into an empty workbook. Here you can specify to treat this field as TEXT rather than as GENERAL.

Note that once Excel applies number (or date) format to a cell, the data in the cell has been changed. No application of a new format will bring back your desired data. For this reason you must specify the format before Excel opens the file.

Excellll
  • 5,609
  • 4
  • 38
  • 55
  • 1
    Right, I understand all that and many thanks for taking the time to explain it. I am doing this for over 30 users and they will be exporting data quite often. I am not sure they will be happy with having to manipulate it to get data right. I was just hoping that there is some vb code or something that will help manipulate excel to retain the value it receives once the open button is clicked. – Kenny Apr 27 '12 at 18:41