locked
How to convert string to datetime by importing csv using dataset/datareader RRS feed

  • Question

  • Hi,

    We're using visual studio 2010 + sql server 2008 and recently we want the application to read from csv into db, and this csv have datetime column such that the application can load this csv into db and convert from string to datetime automatically, e.g. 1-31-2001 23:59:59, in which we want .net framework can detect it's m-dd-yyyy hh:mm:ss format. Any suggestions?

    Thx a lot

    Wallace

    Here is the code:

            /* Schema.ini File (Text File Driver)

            When the Text driver is used, the format of the text file is determined by using a
            schema information file. The schema information file, which is always named Schema.ini
            and always kept in the same directory as the text data source, provides the IISAM
            with information about the general format of the file, the column name and data type
            information, and a number of other data characteristics*/

            private void writeSchema()
            {
                try
                {
                    FileStream fsOutput = new FileStream(this.dirCSV + "\\schema.ini", FileMode.Create, FileAccess.Write);
                    StreamWriter srOutput = new StreamWriter(fsOutput);
                    string s1, s2, s3, s4, s5;

                    s1 = "[" + this.FileNevCSV + "]";
                    s2 = "ColNameHeader=" + chkFirstRowColumnNames.Checked.ToString();
                    s3 = "Format=" + this.strFormat;
                    s4 = "MaxScanRows=25";
                    s5 = "CharacterSet=" + this.strEncoding;

                    srOutput.WriteLine(s1.ToString() + "\r\n" + s2.ToString() + "\r\n" + s3.ToString() + "\r\n" + s4.ToString() + "\r\n" + s5.ToString());
                    srOutput.Close();
                    fsOutput.Close();
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message, "writeSchema");
                }
                finally
                { }
            }

            /*
             * Loads the csv file into a DataSet.
             *
             * If the numberOfRows parameter is -1, it loads oll rows, otherwise it
             * loads the first specified number of rows (for preview)
             */

            public DataSet LoadCSV(int numberOfRows)
            {
                DataSet ds = new DataSet();
                try
                {
                    // Creates and opens an ODBC connection
                    string strConnString = "Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=" + this.dirCSV.Trim() + ";Extensions=asc,csv,tab,txt;Persist Security Info=False";
                    string sql_select;
                    OdbcConnection conn;
                    conn = new OdbcConnection(strConnString.Trim());
                    conn.Open();

                    //Creates the select command text
                    if (numberOfRows == -1)
                    {
                        sql_select = "select * from [" + this.FileNevCSV.Trim() + "]";
                    }
                    else
                    {
                        sql_select = "select top " + numberOfRows + " * from [" + this.FileNevCSV.Trim() + "]";
                    }

                    //Creates the data adapter
                    OdbcDataAdapter obj_oledb_da = new OdbcDataAdapter(sql_select, conn);
                    
                    //Fills dataset with the records from CSV file
                    obj_oledb_da.Fill(ds, "csv");
                    
                    //closes the connection
                    conn.Close();
                }
                catch (Exception e) //Error
                {
                    MessageBox.Show(e.Message, "Error - LoadCSV",MessageBoxButtons.OK,MessageBoxIcon.Error);
                }
                return ds;
            }

            private void SaveToDatabase_withDataSet()
            {
                try
                {
                    if (fileCheck())
                    {
                        // select format, encoding, and write the schema file
                        Format();
                        Encoding();
                        writeSchema();

                        // loads all rows from from csv file
                        DataSet ds = LoadCSV(-1);

                        // gets the number of rows
                        this.rowCount = ds.Tables[0].Rows.Count;

                        // Makes a DataTableReader, which reads data from data set.
                        // It is nececery for bulk copy operation.
                        DataTableReader dtr = ds.Tables[0].CreateDataReader();

                        // Creates schema table. It gives column names for create table command.
                        DataTable dt;
                        dt = dtr.GetSchemaTable();

                        // You can view that schema table if you want:
                        //this.dataGridView_preView.DataSource = dt;

                        // Creates a new and empty table in the sql database
    //                    CreateTableInDatabase(dt, this.txtOwner.Text, this.txtTableName.Text, prop.sqlConnString);

                        // Copies all rows to the database from the dataset.
                        using (SqlBulkCopy bc = new SqlBulkCopy(prop.sqlConnString))
                        {
                            // Destination table with owner - this example doesn't
                            // check the owner and table names!
                            bc.DestinationTableName = "[" + this.txtOwner.Text + "].[" + this.txtTableName.Text + "]";

                            // User notification with the SqlRowsCopied event
                            bc.NotifyAfter = 100;
                            bc.SqlRowsCopied += new SqlRowsCopiedEventHandler(OnSqlRowsCopied);
                            
                            // Starts the bulk copy.
                            bc.WriteToServer(ds.Tables[0]);

                            // Closes the SqlBulkCopy instance
                            bc.Close();
                        }

                        // Writes the number of imported rows to the form
                        this.lblProgress.Text = "Imported: " + this.rowCount.ToString() + "/" + this.rowCount.ToString() + " row(s)";
                        this.lblProgress.Refresh();

                        // Notifies user
                        MessageBox.Show("ready");
                    }
                }
                catch (Exception e)
                {
                    MessageBox.Show(e.Message, "Error - SaveToDatabase_withDataSet", MessageBoxButtons.OK, MessageBoxIcon.Error);
                }
            }



            /*
             *  shows the progress of import operation
             */

            private void OnSqlRowsCopied(object sender, SqlRowsCopiedEventArgs e)
            {
                this.lblProgress.Text = "Imported: " + e.RowsCopied.ToString() + "/" + this.rowCount.ToString() + " row(s)";
                this.lblProgress.Refresh();
            }


            private void btnSaveDirect_Click(object sender, EventArgs e)
            {
                SaveToDatabaseDirectly();
            }

            /*
             * Imports data to the database with SqlBulkCopy.
             * This method doesn't use a temporary dataset, it loads
             * data immediately from the ODBC connection
             */

            private void SaveToDatabaseDirectly()
            {
                try
                {
                    if (fileCheck())
                    {
                        // select format, encoding, and write the schema file
                        Format();
                        Encoding();
                        writeSchema();

                        // Creates and opens an ODBC connection
                        string strConnString = "Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=" + this.dirCSV.Trim() + ";Extensions=asc,csv,tab,txt;Persist Security Info=False";
                        string sql_select;
                        OdbcConnection conn;
                        conn = new OdbcConnection(strConnString.Trim());
                        conn.Open();

                        //Counts the row number in csv file - with an sql query
                        OdbcCommand commandRowCount = new OdbcCommand("SELECT COUNT(*) FROM [" + this.FileNevCSV.Trim() + "]", conn);
                        this.rowCount = System.Convert.ToInt32(commandRowCount.ExecuteScalar());

                        // Creates the ODBC command
                        sql_select = "select * from [" + this.FileNevCSV.Trim() + "]";
                        OdbcCommand commandSourceData = new OdbcCommand(sql_select, conn);

                        // Makes on OdbcDataReader for reading data from CSV
                        OdbcDataReader dataReader = commandSourceData.ExecuteReader();

                        // Creates schema table. It gives column names for create table command.
                        DataTable dt;
                        dt = dataReader.GetSchemaTable();

                        // You can view that schema table if you want:
                        //this.dataGridView_preView.DataSource = dt;

                        // Creates a new and empty table in the sql database
    // Wallace comment
    //                    CreateTableInDatabase(dt, this.txtOwner.Text, this.txtTableName.Text, prop.sqlConnString);

                        // Copies all rows to the database from the data reader.
    //                    using (SqlBulkCopy bc = new SqlBulkCopy("server=(local);database=Test_CSV_impex;Trusted_Connection=True"))
                        using (SqlBulkCopy bc = new SqlBulkCopy("server=(local);database=Test_CSV_impex;Integrated Security=SSPI;Trusted_Connection=True"))
                        {
                            // Destination table with owner - this example doesn't
                            // check the owner and table names!
                            bc.DestinationTableName = "[" + this.txtOwner.Text + "].[" + this.txtTableName.Text + "]";

                            // User notification with the SqlRowsCopied event
                            bc.NotifyAfter = 100;
                            bc.SqlRowsCopied += new SqlRowsCopiedEventHandler(OnSqlRowsCopied);

                            // Starts the bulk copy.
                            bc.WriteToServer(dataReader);

                            // Closes the SqlBulkCopy instance
                            bc.Close();
                        }

                        // Writes the number of imported rows to the form
                        this.lblProgress.Text = "Imported: " + this.rowCount.ToString() + "/" + this.rowCount.ToString() + " row(s)";
                        this.lblProgress.Refresh();
                        
                        // Notifies user
                        MessageBox.Show("ready");
                    }
                }
                catch (Exception e)
                {
                    MessageBox.Show(e.Message, "Error - SaveToDatabaseDirectly", MessageBoxButtons.OK, MessageBoxIcon.Error);
                }
            }


    Monday, September 17, 2012 11:42 AM

Answers

  • public class DateTimeParser
    {
       public static void Main()
       {
          // Assume the current culture is en-US. 
          // The date is February 16, 2008, 12 hours, 15 minutes and 12 seconds.
    
          // Use standard en-US date and time value
          DateTime dateValue;
          string dateString = "2/16/2008 12:15:12 PM";
          try {
             dateValue = DateTime.Parse(dateString);
             Console.WriteLine("'{0}' converted to {1}.", dateString, dateValue);
          }   
          catch (FormatException) {
             Console.WriteLine("Unable to convert '{0}'.", dateString);
          }
    
          // Reverse month and day to conform to the fr-FR culture.
          // The date is February 16, 2008, 12 hours, 15 minutes and 12 seconds.
          dateString = "16/02/2008 12:15:12";
          try {
             dateValue = DateTime.Parse(dateString);
             Console.WriteLine("'{0}' converted to {1}.", dateString, dateValue);
          }   
          catch (FormatException) {
             Console.WriteLine("Unable to convert '{0}'.", dateString);
          }
    
          // Call another overload of Parse to successfully convert string
          // formatted according to conventions of fr-FR culture.      
          try {
             dateValue = DateTime.Parse(dateString, new CultureInfo("fr-FR", false));
             Console.WriteLine("'{0}' converted to {1}.", dateString, dateValue);
          }   
          catch (FormatException) {
             Console.WriteLine("Unable to convert '{0}'.", dateString);
          }
    
          // Parse string with date but no time component.
          dateString = "2/16/2008";
          try {
             dateValue = DateTime.Parse(dateString);
             Console.WriteLine("'{0}' converted to {1}.", dateString, dateValue);
          }   
          catch (FormatException) {
             Console.WriteLine("Unable to convert '{0}'.", dateString);
          }   
       }
    }


    Mark Answered, if it solves your question and Vote if you found it helpful.
    Rohit Arora

    • Proposed as answer by Mike Feng Wednesday, September 19, 2012 10:13 AM
    • Marked as answer by Mike Feng Monday, September 24, 2012 1:41 PM
    Monday, September 17, 2012 1:15 PM

All replies

  • public class DateTimeParser
    {
       public static void Main()
       {
          // Assume the current culture is en-US. 
          // The date is February 16, 2008, 12 hours, 15 minutes and 12 seconds.
    
          // Use standard en-US date and time value
          DateTime dateValue;
          string dateString = "2/16/2008 12:15:12 PM";
          try {
             dateValue = DateTime.Parse(dateString);
             Console.WriteLine("'{0}' converted to {1}.", dateString, dateValue);
          }   
          catch (FormatException) {
             Console.WriteLine("Unable to convert '{0}'.", dateString);
          }
    
          // Reverse month and day to conform to the fr-FR culture.
          // The date is February 16, 2008, 12 hours, 15 minutes and 12 seconds.
          dateString = "16/02/2008 12:15:12";
          try {
             dateValue = DateTime.Parse(dateString);
             Console.WriteLine("'{0}' converted to {1}.", dateString, dateValue);
          }   
          catch (FormatException) {
             Console.WriteLine("Unable to convert '{0}'.", dateString);
          }
    
          // Call another overload of Parse to successfully convert string
          // formatted according to conventions of fr-FR culture.      
          try {
             dateValue = DateTime.Parse(dateString, new CultureInfo("fr-FR", false));
             Console.WriteLine("'{0}' converted to {1}.", dateString, dateValue);
          }   
          catch (FormatException) {
             Console.WriteLine("Unable to convert '{0}'.", dateString);
          }
    
          // Parse string with date but no time component.
          dateString = "2/16/2008";
          try {
             dateValue = DateTime.Parse(dateString);
             Console.WriteLine("'{0}' converted to {1}.", dateString, dateValue);
          }   
          catch (FormatException) {
             Console.WriteLine("Unable to convert '{0}'.", dateString);
          }   
       }
    }


    Mark Answered, if it solves your question and Vote if you found it helpful.
    Rohit Arora

    • Proposed as answer by Mike Feng Wednesday, September 19, 2012 10:13 AM
    • Marked as answer by Mike Feng Monday, September 24, 2012 1:41 PM
    Monday, September 17, 2012 1:15 PM
  • hi Wallace,

    please try this:

      string dateString = "9/22/1988";
      DateTime dateValue = DateTime.Parse(dateString);


    Regards, http://www.shwetalodha.blogspot.in/

    Monday, September 17, 2012 1:33 PM
  • public class DateTimeParser
    {
       public static void Main()
       {
          // Assume the current culture is en-US. 
          // The date is February 16, 2008, 12 hours, 15 minutes and 12 seconds.
    
          // Use standard en-US date and time value
          DateTime dateValue;
          string dateString = "2/16/2008 12:15:12 PM";
          try {
             dateValue = DateTime.Parse(dateString);
             Console.WriteLine("'{0}' converted to {1}.", dateString, dateValue);
          }   
          catch (FormatException) {
             Console.WriteLine("Unable to convert '{0}'.", dateString);
          }
    
          // Reverse month and day to conform to the fr-FR culture.
          // The date is February 16, 2008, 12 hours, 15 minutes and 12 seconds.
          dateString = "16/02/2008 12:15:12";
          try {
             dateValue = DateTime.Parse(dateString);
             Console.WriteLine("'{0}' converted to {1}.", dateString, dateValue);
          }   
          catch (FormatException) {
             Console.WriteLine("Unable to convert '{0}'.", dateString);
          }
    
          // Call another overload of Parse to successfully convert string
          // formatted according to conventions of fr-FR culture.      
          try {
             dateValue = DateTime.Parse(dateString, new CultureInfo("fr-FR", false));
             Console.WriteLine("'{0}' converted to {1}.", dateString, dateValue);
          }   
          catch (FormatException) {
             Console.WriteLine("Unable to convert '{0}'.", dateString);
          }
    
          // Parse string with date but no time component.
          dateString = "2/16/2008";
          try {
             dateValue = DateTime.Parse(dateString);
             Console.WriteLine("'{0}' converted to {1}.", dateString, dateValue);
          }   
          catch (FormatException) {
             Console.WriteLine("Unable to convert '{0}'.", dateString);
          }   
       }
    }


    Mark Answered, if it solves your question and Vote if you found it helpful.
    Rohit Arora

    thx. But I still dunno how to auto convert the string to datetime field when using odbc connection to read csv and then sqlbulkcopy with dataset and convert to datetime and write to db. Do I need to use DataColumn or DataColumnMapping class to indicate that the source has datetime format in csv which is in 'm/dd/yyyy hh:mm:ss'? How to use Datareader or other class that can indicate the source field has specific datetime format? Any suggestion? thx a lot.


    Monday, September 17, 2012 1:50 PM
  • you need to provide the format you are receiving with a string from reading the file in parse method.

    Need to look at Standard DateTimeFormats and Custom DateTimeFormats


    Mark Answered, if it solves your question and Vote if you found it helpful.
    Rohit Arora

    Monday, September 17, 2012 4:27 PM
  • you need to provide the format you are receiving with a string from reading the file in parse method.

    Need to look at Standard DateTimeFormats and Custom DateTimeFormats


    Mark Answered, if it solves your question and Vote if you found it helpful.
    Rohit Arora

    so in which class to specify the datetime format when parsing the file? We used odbcconnection class to open the connection for reading csv and schema.ini to specify the source format. Then we used odbcdataadapter class to fill in csv into dataset, and datatablereader class to fetch the row. Finally we used sqlbulkcopy class to copy the previous dataset and datatablereader into database. So can you list out the function call in the above class that can specify the datetime format(e.g. 'm/dd/yyyy hh:mm:ss' when importing csv? thx a lot


    Tuesday, September 18, 2012 2:20 PM
  • Hi Wallace,

    Welcome to the MSND Forum.

    >>so in which class to specify the datetime format when parsing the file? 

    No class, you specify it:http://msdn.microsoft.com/en-us/library/az4se3k1(v=vs.110).aspx 

    Best regards,


    Mike Feng
    MSDN Community Support | Feedback to us
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Wednesday, September 19, 2012 10:13 AM