0

I'm having issue retaining the number/text format when I export the data out in .csv format.

In my database table, I have a column with this design: ClaimantNRIC varchar(20)

In my classic asp page, I'm using these code to read the data and export it to excel:

do while not rs.EOF 

        ROCNo = rs("ROCNo")
        ClaimantNRIC = rs("ClaimantNRIC")
        SurgAnaesPayable10 = rs("SurgAnaesPayable10")
        Detail ="""" & ROCNo & """,""" & ClaimantNRIC & """,""" & SurgAnaesPayable10 & """"

        Response.Write(Detail & vbcrlf)
        rs.MoveNext()
loop 

MyTF.Close
Response.AddHeader "content-disposition", "attachment;filename=ABC.csv"
Response.Charset = ""
Response.CacheControl=0
Response.ContentType = "application/vnd.ms-excel"

Response.End

Expected result:

when view in excel

Currently getting (wrong result):

What I'm currently getting - which is wrong

When open using notepad, I'm still getting the wrong result. So, how can I fix this?

The Art of Squi-Fu
  • 44
  • 1
  • 11
  • It's interpreting the column as a numeric, not a string. – user692942 Aug 11 '22 at 12:03
  • Does this answer your question? [formatting numbers as text in excel](https://stackoverflow.com/a/13596406) – user692942 Aug 11 '22 at 14:17
  • After this line: ClaimantNRIC = rs("ClaimantNRIC") Try adding this line: ClaimantNRIC = cstr(ClaimantNRIC) cstr( ) converts the variable into a string/varchar type. Granted knowing Excel that might not even matter and you will still need to re-validate the data type on the Excel side, but keep us posted. – easleyfixed Aug 11 '22 at 19:08
  • x = 1 means x is equal to the value of number 1 x = "1" means x is not a value, but instead "equal" to the the word "1" – easleyfixed Aug 16 '22 at 18:42

0 Answers0