Tip of the day: Save columns with long numbers in CSV in proper format using C# and SQLServer

I know this blog is not about Microsoft Azure which I usually do. There are a lot of Tips that are in my mind and I want to just share them to free my mind because I haven’t shared them with anyone.

This Tip is a basic tip but it doesn’t have any straight forward accepted answer in google. So I thought I should share this Tip how to resolve this if you get stuck with this basic problem.

Problem Statement: We have to generate a Report to the CSV file but there are some columns in Db table which has records with leading zero and are long as shown below:

So when I generate the CSV report, I get the records in CSV in below format.

You can see that my BarcodeNo Column is displaying in E(scientific notation) and leading 0s are missing in the CSV file.

Solution:
In order to solve this problem, we will use the char function SQL server, this function converts an int ASCII code to a character value.
Char(9) denotes to Tab.

Control character Value
Tab char(9)

Let us try to implement the same in our SQL query or Stored procedure in your application.

I am just using simple SQL query to get the result in this demo to simplify things and be focused toward a solution rather than implementation:

using LeadingZeros.Helper;
using System.Data;
using System.Data.SqlClient;

namespace LeadingZeros.Persistence
{
    public class BarcodeRepository
    {
        private QueryInfo _query;
        public BarcodeRepository()
        {
            _query = new QueryInfo();
        }
        public DataTable GetBarcodeData()
        {
            DataTable table = new DataTable();
            SqlConnection connection = new SqlConnection(_query.GetSourceConnectionString());
            connection.Open();
            SqlCommand cmd = new SqlCommand("Select SeqID,UsageCode,char(9)+BarcodeNo as BarcodeNo from Garments", connection);
            SqlDataAdapter adapt = new SqlDataAdapter(cmd);
            adapt.Fill(table);
            connection.Close();
            return table;
        }
    }
}

Now if I try to regenerate the report again I will get the records in a proper format and will get the leading Zero no more scientific notation in CSV file.

If this tip was helpful for you. Please like, subscribe and comment on the blog post.

Happy Learning!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.