Click here to Skip to main content
15,867,870 members
Please Sign up or sign in to vote.
2.00/5 (1 vote)
See more:
I'm trying to import from a csv file into datagridview a number that is longer than 15 characters.In the csv file it looks 8940012004412026012 and after import in datagridview show 8.9400120044120259E + 18


What I have tried:

C#
 private void button60_Click(object sender, EventArgs e)
        {
            OpenFileDialog fdlg = new OpenFileDialog();
            fdlg.Title = "Select file";
            fdlg.InitialDirectory = @"c:\";
            fdlg.FileName = textBox4.Text;
            fdlg.Filter = "Text and CSV Files(*.txt, *.csv)|*.txt;*.csv|Text Files(*.txt)|*.txt|CSV Files(*.csv)|*.csv|All Files(*.*)|*.*";
            fdlg.FilterIndex = 1;
            fdlg.RestoreDirectory = true;
            if (fdlg.ShowDialog() == DialogResult.OK)
            {
                textBox4.Text = fdlg.FileName;
                Import();
                Application.DoEvents();
            }
        }
        public static DataTable GetDataTable(string strFileName)
        {
            ADODB.Connection oConn = new ADODB.Connection();
            oConn.Open("Provider=Microsoft.Jet.OleDb.4.0; Data Source = " + System.IO.Path.GetDirectoryName(strFileName) + "; Extended Properties = \"Text;HDR=YES;FMT=Delimited\";", "", "", 0);
            string strQuery = "SELECT * FROM [" + System.IO.Path.GetFileName(strFileName) + "]";
            ADODB.Recordset rs = new ADODB.Recordset();
            System.Data.OleDb.OleDbDataAdapter adapter = new System.Data.OleDb.OleDbDataAdapter();
            DataTable dt = new DataTable();
            rs.Open(strQuery, "Provider=Microsoft.Jet.OleDb.4.0; Data Source = " + System.IO.Path.GetDirectoryName(strFileName) + "; Extended Properties = \"Text;HDR=YES;FMT=Delimited\";",
                ADODB.CursorTypeEnum.adOpenForwardOnly, ADODB.LockTypeEnum.adLockReadOnly, 1);
            adapter.Fill(dt, rs);
            return dt;
        }

private void Import()
        {
            if (textBox4.Text.Trim() != string.Empty)
            {
                try
                 {


                    DataTable ct = GetDataTable(textBox4.Text);
                    dataGridView2.AllowUserToAddRows = false;
                    dataGridView2.DataSource = ct;
                    dataGridView2.Columns[0].HeaderCell.Value = "1";
                    dataGridView2.Columns[1].HeaderCell.Value = "2";
                    dataGridView2.Columns[2].HeaderCell.Value = "3";
                    dataGridView2.Columns[3].HeaderCell.Value = "4";
                    dataGridView2.Columns[4].HeaderCell.Value = "5";
                    dataGridView2.Columns[5].HeaderCell.Value = "6";
                    dataGridView2.Columns[6].HeaderCell.Value = "7";
                    dataGridView2.Columns[6].DefaultCellStyle.Format = "yyyy-mm-dd";
                    dataGridView2.Columns[7].HeaderCell.Value = "8";
                    dataGridView2.Columns[8].HeaderCell.Value = "9";
                    dataGridView2.Columns[8].DefaultCellStyle.Format = "yyyy-mm-dd";
                    dataGridView2.Columns[9].HeaderCell.Value = "10";
                    dataGridView2.Columns[10].HeaderCell.Value = "11";
                    dataGridView2.Columns[11].HeaderCell.Value = "12";
                    dataGridView2.Columns[12].HeaderCell.Value = "13";
                    dataGridView2.Columns[13].HeaderCell.Value = "14";
                    dataGridView2.Columns[14].HeaderCell.Value = "15";
                    dataGridView2.Columns[15].HeaderCell.Value = "16";
                    dataGridView2.Columns[15].DefaultCellStyle.Format = "yyyy-mm-dd";
                    dataGridView2.Columns[16].HeaderCell.Value = "17";
                    
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message.ToString());
                }
            }
        }


dataGridView2.Columns[5].HeaderCell.Value = "6";
need to be
8940012004412026012
Posted
Updated 24-Aug-21 1:35am

Quote:
In the csv file it looks 8940012004412026012 and after import in datagridview show 8.9400120044120259E + 18
That is just two ways of representing the same numeric value. Since the number is big, the output is being shown as an exponentiated form.

One "poor" hack to solve this would be to convert the number to string type and show it. ;-)

A good approach would be to control the numeric formatting using style formats, for example:
dataGridView.Columns["name-of-column"].DefaultCellStyle.Format = "D";
Read the DefaultCellStyle[^] for more details on this.

Edit: Oh, and your queries are prone to SQL Injection.
string strQuery = "SELECT * FROM [" + System.IO.Path.GetFileName(strFileName) + "]";
You can argue that a direct input is not being provided here, but, it is still best approach to always avoid concatenation of the string to build up a query. Always use parameters to prevent SQL Injection.

It takes a few minutes, and in a few weeks, your brain will automatically start writing parameterized queries.
 
Share this answer
 
v2
Comments
Moldoveanu Marius 21-Aug-21 22:17pm    
Pls ca you help me with converting the number to string type and show it
BillWoodruff 22-Aug-21 9:50am    
Read Afzaal's solution, and start experimenting with DefaultCellStyle.Format,

We are not going to write your code for you.
BillWoodruff 22-Aug-21 9:47am    
+5 I was going to suggest changing the Cell Column width, or showing a ToolTip; but, on second thought ... I think your answer is the useful one :)
my solution was to format before insert to database

for (int i = 0; i < dataGridView2.RowCount; i++)
           {
               DateTime d1 = Convert.ToDateTime(dataGridView2.Rows[i].Cells[6].Value.ToString());
               string format = "s";
               DateTime d2 = Convert.ToDateTime(dataGridView2.Rows[i].Cells[8].Value.ToString());
               DateTime d3 = Convert.ToDateTime(dataGridView2.Rows[i].Cells[15].Value.ToString());

               try
               {

                   SqlCommand sqlCmd = new SqlCommand("insert into abonament (nr, serie, cui, client, sim, data_inst, activare, data_exp, telefon, nr_activat, nr_zile, ob, tip_client, email, datacurenta1, semnatura) values ( @nr, @serie, @cui, @client, @sim, @data_inst, @activare, @data_exp, @telefon, @nr_activat, @nr_zile, @ob, @tip_client, @email, @datacurenta1, @semnatura)", con);

                   sqlCmd.Parameters.AddWithValue("@mode", "Add");
                   sqlCmd.Parameters.AddWithValue("@id", 0);
                   sqlCmd.Parameters.AddWithValue("@nr   ", dataGridView2.Rows[i].Cells[1].Value);
                   sqlCmd.Parameters.AddWithValue("@serie   ", dataGridView2.Rows[i].Cells[2].Value);
                   sqlCmd.Parameters.AddWithValue("@cui   ", dataGridView2.Rows[i].Cells[3].Value);
                   sqlCmd.Parameters.AddWithValue("@client   ", dataGridView2.Rows[i].Cells[4].Value);
                   sqlCmd.Parameters.AddWithValue("@sim   ", dataGridView2.Rows[i].Cells[5].Value);
                   sqlCmd.Parameters.AddWithValue("@data_inst   ", d1.Date.ToString(format));
                   sqlCmd.Parameters.AddWithValue("@activare   ", dataGridView2.Rows[i].Cells[7].Value);
                   sqlCmd.Parameters.AddWithValue("@data_exp   ", d2);
                   sqlCmd.Parameters.AddWithValue("@telefon   ", dataGridView2.Rows[i].Cells[9].Value);
                   sqlCmd.Parameters.AddWithValue("@nr_activat   ", dataGridView2.Rows[i].Cells[10].Value);
                   sqlCmd.Parameters.AddWithValue("@nr_zile   ", dataGridView2.Rows[i].Cells[11].Value);
                   sqlCmd.Parameters.AddWithValue("@ob   ", dataGridView2.Rows[i].Cells[12].Value);
                   sqlCmd.Parameters.AddWithValue("@tip_client   ", dataGridView2.Rows[i].Cells[13].Value);
                   sqlCmd.Parameters.AddWithValue("@email   ", dataGridView2.Rows[i].Cells[14].Value);
                   sqlCmd.Parameters.AddWithValue("@datacurenta1   ", d3);
                   sqlCmd.Parameters.AddWithValue("@semnatura   ", dataGridView2.Rows[i].Cells[16].Value);

                   sqlCmd.ExecuteNonQuery();

               }
 
Share this answer
 
Comments
Richard MacCutchan 24-Aug-21 10:09am    
That is the wrong way to do it. You should always store dates as Date or DateTime types. The only time you need to format them into a string is when you want to display their value. And the format of the display iwill not always be the same as you are storing it in the database.
I found a solution using csvhelper but now I discovered another problem with formatting the date I can't format it yyyy-mm-dd to add it to the database. If I change in Windouws the date format to yyyy-mm-dd the import works but I can't change it must be dd / mm / yyyy

private void button60_Click(object sender, EventArgs e)
    {
        OpenFileDialog fdlg = new OpenFileDialog();
        fdlg.Title = "Select file";
        fdlg.InitialDirectory = @"c:\";
        fdlg.FileName = textBox4.Text;
        fdlg.Filter = "Text and CSV Files(*.txt, *.csv)|*.txt;*.csv|Text Files(*.txt)|*.txt|CSV Files(*.csv)|*.csv|All Files(*.*)|*.*";
        fdlg.FilterIndex = 1;
        fdlg.RestoreDirectory = true;
        if (fdlg.ShowDialog() == DialogResult.OK)
        {
            using (var reader = new StreamReader(fdlg.FileName))
            using (var csv = new CsvReader(reader, CultureInfo.InvariantCulture))

            {
                using (var dr = new CsvDataReader(csv))
                {
                    var dt = new DataTable();
                    dt.Load(dr);
                    dataGridView2.DataSource = dt;
                    dataGridView2.Columns[0].HeaderCell.Value = "ID";
                    dataGridView2.Columns[1].HeaderCell.Value = "Nr.";
                    dataGridView2.Columns[2].HeaderCell.Value = "Serie";
                    dataGridView2.Columns[3].HeaderCell.Value = "CUI";
                    dataGridView2.Columns[4].HeaderCell.Value = "Client";
                    dataGridView2.Columns[5].HeaderCell.Value = "Serie SIM";
                    dataGridView2.Columns[6].DefaultCellStyle.Format = "yyyy-mm-dd";
                    dataGridView2.Columns[6].HeaderCell.Value = "Data activarii";
                    dataGridView2.Columns[7].HeaderCell.Value = "Status";
                    dataGridView2.Columns[8].HeaderCell.Value = "Data expirarii";
                    dataGridView2.Columns[8].DefaultCellStyle.Format = "yyyy-mm-dd";
                    dataGridView2.Columns[9].HeaderCell.Value = "Telefon";
                    dataGridView2.Columns[10].HeaderCell.Value = "Nr. activat";
                    dataGridView2.Columns[11].HeaderCell.Value = "Nr_zile";
                    dataGridView2.Columns[12].HeaderCell.Value = "Ob.";
                    dataGridView2.Columns[13].HeaderCell.Value = "Tip client";
                    dataGridView2.Columns[14].HeaderCell.Value = "Email";
                    dataGridView2.Columns[15].HeaderCell.Value = "Data inregistrarii";
                    dataGridView2.Columns[15].DefaultCellStyle.Format = "yyyy-mm-dd";
                    dataGridView2.Columns[16].HeaderCell.Value = "Semnatura";

                    dataGridView2.AlternatingRowsDefaultCellStyle.BackColor = Color.FromArgb(238, 239, 249);
                    dataGridView2.CellBorderStyle = DataGridViewCellBorderStyle.SingleHorizontal;
                    dataGridView2.DefaultCellStyle.SelectionBackColor = Color.DarkTurquoise;
                    dataGridView2.DefaultCellStyle.SelectionForeColor = Color.WhiteSmoke;
                    dataGridView2.BackgroundColor = Color.White;
                    dataGridView2.EnableHeadersVisualStyles = false;
                    dataGridView2.ColumnHeadersBorderStyle = DataGridViewHeaderBorderStyle.None;
                    dataGridView2.ColumnHeadersDefaultCellStyle.BackColor = Color.FromArgb(20, 25, 72);
                    dataGridView2.ColumnHeadersDefaultCellStyle.ForeColor = Color.White;
                }
            }
        }
    }
private void button58_Click(object sender, EventArgs e)
    {
        string constring = ConfigurationManager.ConnectionStrings["db"].ConnectionString;
        SqlConnection con = new SqlConnection(constring);
        if (con.State == ConnectionState.Closed)
            con.Open();
        for (int i = 0; i < dataGridView2.RowCount; i++)
        {
            try
            {
                SqlCommand sqlCmd = new SqlCommand("insert into abonament (nr, serie, cui, client, sim, data_inst, activare, data_exp, telefon, nr_activat, nr_zile, ob, tip_client, email, datacurenta1, semnatura) values ( @nr, @serie, @cui, @client, @sim, @data_inst, @activare, @data_exp, @telefon, @nr_activat, @nr_zile, @ob, @tip_client, @email, @datacurenta1, @semnatura)", con);

                sqlCmd.Parameters.AddWithValue("@mode", "Add");
                sqlCmd.Parameters.AddWithValue("@id", 0);
                sqlCmd.Parameters.AddWithValue("@nr   ", dataGridView2.Rows[i].Cells[1].Value);
                sqlCmd.Parameters.AddWithValue("@serie   ", dataGridView2.Rows[i].Cells[2].Value);
                sqlCmd.Parameters.AddWithValue("@cui   ", dataGridView2.Rows[i].Cells[3].Value);
                sqlCmd.Parameters.AddWithValue("@client   ", dataGridView2.Rows[i].Cells[4].Value);
                sqlCmd.Parameters.AddWithValue("@sim   ", dataGridView2.Rows[i].Cells[5].Value);
                sqlCmd.Parameters.AddWithValue("@data_inst   ", dataGridView2.Rows[i].Cells[6].Value);
                sqlCmd.Parameters.AddWithValue("@activare   ", dataGridView2.Rows[i].Cells[7].Value);
                sqlCmd.Parameters.AddWithValue("@data_exp   ", dataGridView2.Rows[i].Cells[8].Value);
                sqlCmd.Parameters.AddWithValue("@telefon   ", dataGridView2.Rows[i].Cells[9].Value);
                sqlCmd.Parameters.AddWithValue("@nr_activat   ", dataGridView2.Rows[i].Cells[10].Value);
                sqlCmd.Parameters.AddWithValue("@nr_zile   ", dataGridView2.Rows[i].Cells[11].Value);
                sqlCmd.Parameters.AddWithValue("@ob   ", dataGridView2.Rows[i].Cells[12].Value);
                sqlCmd.Parameters.AddWithValue("@tip_client   ", dataGridView2.Rows[i].Cells[13].Value);
                sqlCmd.Parameters.AddWithValue("@email   ", dataGridView2.Rows[i].Cells[14].Value);
                sqlCmd.Parameters.AddWithValue("@datacurenta1   ", dataGridView2.Rows[i].Cells[15].Value);
                sqlCmd.Parameters.AddWithValue("@semnatura   ", dataGridView2.Rows[i].Cells[16].Value);

                sqlCmd.ExecuteNonQuery();

            }

            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Datele nu au fost salvate");
            }

           

        }
        MessageBox.Show("Datele au fost salvate");
    }
 
Share this answer
 
Comments
Richard Deeming 23-Aug-21 8:27am    
Don't store dates as strings. Use the correct data type to store it instead.
card_maruis 23-Aug-21 9:26am    
Pls can i have an example
Patrice T 23-Aug-21 16:07pm    
This is not an answer. Make it a new question to get attention from helpers.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900