Export SQL Server data to EXCEL cause all column become TEXT

  • Thread starter Thread starter ong
  • Start date Start date
O

ong

Hi all,

I want to export some data from the SQL Server to Excel
worksheet using ADO and ASP.
I have already read the MS KB acticle and use the
recommend method to get data, open connection to excel and
then write the data to the worksheet using ADO in my ASP.
However, I find that after the program complete, the data
type of every column become TEXT, ie, have a preceding
single quote, even though the original data type is
Numeric.
The reason of the export function is allowing the user to
check the data in the database in a worksheet format, and
then import back the worksheet to the database if any
changes have been made. The advantage of this method is
many of the user find themselves more familiar with excel
worksheet rather than any file maintanence program.
However, when I export the data, all column become TEXT
but the user will input numeric data into certain column,
as a result, the import function (also using ADO) doesnot
recognize the mixed data coluumn and caused some data is
missing. It is hard to explain or forces the user to input
all column in TEXT format, even I do so, there is also a
risk that the user forgot to do so and casuse data loss.

Anyone have workaround this problem ?
Any help is highly appreciated

Thank in advance

Regards
ong
 
Ong,
I cannot help you with your problem, but maybe you could help me. I
have to do the same exact thing as you and I am just getting started.
You mentioned an article and I would be interested in reading it. Do
you have the link or can you tell me where you read it.

Brian
 
I also came across this problem. While importing the data from excel,
if a column has a mixed data types, SQL ingnores the numeric data and
shows null value. To sort out this problem, I have converted all the
data in text with a trim formula in excel and then imported in SQL
which is now working. Just try it. GOOD LUCK.
 
Back
Top