-2

Using VB.Net application

Creating a CSV file from SQL output in the application

When i open the csv into xls or xlsx file, i am getting a column as 1.02141E+15 instead of 123456789

In SQL, When i add the below line in front of number

Select ''''  + 123456789  from table

Then I am getting output with single quote as '123456789 in xls file instead of 1.02141E+15. I have tried with '' + 123456789 and ' ' + 123456789 but result is same as 1.02141E+15

User don't want to format the excel sheet manually

What is the best input character pass to the query column to view the number properly in xls file like 123456789 with out any single quote or character

Gopal
  • 11,712
  • 52
  • 154
  • 229
  • Format the cell in Excel to use a number format rather than "General". – Gordon Linoff Jan 05 '15 at 14:51
  • Client dont want to format the excel sheet manually – Gopal Jan 05 '15 at 14:51
  • 1
    Then output it as a string, with double quotes around the value. Note: I think it will be imported into Excel as text rather than a number. – Gordon Linoff Jan 05 '15 at 14:52
  • read : https://support.office.com/en-au/article/Create-or-delete-a-custom-number-format-78f2a361-936b-4c03-8772-09fab54be7f4 – CristiC777 Jan 05 '15 at 15:04
  • 1
    What does the CSV look like when you open it in notepad? – the_lotus Jan 05 '15 at 15:08
  • 1
    The reason that Excel is displaying your number that way is that the number can't fit into the column at Excel's default width of 8.43. Even if you could the text would be truncated by the next cell's boundary and you would still need to format the cell anyway. However, if that is acceptable, I would do as @GordonLinoff said and format it as a string [(try this, too)](http://stackoverflow.com/a/6023847/231316). Or, if your client can tolerate 3 clicks, click the upper left corner "select all cells" button and double-click any column's separator to automatically adjust widths. – Chris Haas Jan 05 '15 at 15:24
  • Even if it is converted to string, it won't work. Only way to get around is to add a `'` before the number in excel. This way it doesn't bundle up into the exponential notation. – SouravA Jan 05 '15 at 15:41
  • @Gopal - why do you `open the csv into xls or xlsx file`? why don't you define a new data connection and provide the SQL query? It will be well formatted that way. – SouravA Jan 05 '15 at 15:55

1 Answers1

0

Or you must create programtatic the excell sheet in VB like that

First (1) column will be a text, second (2) as General, third (3) as Number

Sub formatColumns()
 Columns(1).NumberFormat = "@"
 Columns(2).NumberFormat = "General"
 Columns(3).NumberFormat = "0"
End Sub

you need like column (3)..
and so on ..
Happy codding ! :)

CristiC777
  • 481
  • 11
  • 20