Windows Application date format output

Shane Nicholson

Good afternoon all,

I have made a windows form application that allows my user to insert some data and when "insert" is selected, the data is saved to my drive as a CSV file.

This all works fine. However, I need to date format to save in the CSV file as yyyy-MM-dd, as Tableau is reading it.

I am very much a beginner and this is my first project, so please be gentle. I am using a dateTimepicker for my user to select the date.

Attached is my code to export to csv. I have tried Tostring("yyyy-MM-dd") and this did not work.

Thank you so much for your help.

Best regards,

"Excel.Workbook xlworkbook;

Excel.Worksheet xlworksheet;

object misValue = System.Reflection.Missing.Value;

xlworkbook = xlap.Workbooks.Add(misValue);

xlworksheet = (Excel.Worksheet)xlworkbook.Worksheets.get_Item(1);

Excel.Sheets worksheets = xlworkbook.Worksheets;

worksheets[3].Delete();

worksheets[2].Delete();

xlworksheet.Cells[1, 1] = textBox1.Text;

xlworksheet.Cells[1, 2] = ((DateTime)dateTimePicker1.Value).ToString("yyyy-MM-dd");

xlworksheet.Cells[1, 3] = textBox2.Text;

xlworksheet.Cells[1, 4] = textBox3.Text;

xlworksheet.Cells[1, 5] = textBox4.Text;

xlworksheet.Cells[1, 6] = textBox5.Text;

xlworksheet.Cells[1, 7] = textBox6.Text;

xlworksheet.Cells[1, 8] = textBox7.Text;

xlworksheet.Cells[1, 9] = textBox8.Text;

xlworksheet.Cells[1, 10] = textBox9.Text;

xlworksheet.Cells[1, 13] = textBox10.Text;

xlworksheet.Cells[1, 11] = comboBox1.Text;

xlworksheet.Cells[1, 12] = comboBox2.Text;

xlworkbook.SaveAs(@"\\172.21.4.14\BusinessLanding\BusinessLanding_Sync\operations_sync\FlightPlanning\savings_" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".csv", Excel.XlFileFormat.xlCSVWindows);

xlworkbook.Close(true);

xlap.Quit();"

5 Replies

Know the answer? Post it — somebody with the same question will find it here.

Nitin Sontakke5y ago
Accepted answer
Few thoughts and notes. This is NOT an answer.

Be very precise in your communication. When you are storing it in MS-Excel file, please do not say, you are storing it in .csv file. These are poles apart.

While I have ABSOLUTELY NO knowledge of Tabeau, most applications will read string as date, if they are expecting date there ONLY IF it is in correct format. Which preferably should be yyyy-mm-dd / yyyymmdd.

Date is a man made virtual construct and computers natively do not support it. Depending on application you are using you will need to be bit more creative. For example, have you tried giving an Excel cell the number format as yyyy-mm-dd? That could work.
Shane Nicholson5y ago
Gentlemen,

I have finally fixed the problem after taking what Nitin said on-board.
I have formatted the cell in excel that the date is assigned to and it works..

Excel.Workbook xlworkbook;
Excel.Worksheet xlworksheet;
object misValue = System.Reflection.Missing.Value;
xlworkbook = xlap.Workbooks.Add(misValue);
xlworksheet = (Excel.Worksheet)xlworkbook.Worksheets.get_Item(1);
Excel.Sheets worksheets = xlworkbook.Worksheets;
Range rg = (Excel.Range)xlworksheet.Cells[1, 2];
rg.EntireColumn.NumberFormat = "YYYY-MM-DD";
worksheets[3].Delete();
worksheets[2].Delete();
xlworksheet.Cells[1, 1] = textBox1.Text;
xlworksheet.Cells[1, 2] = dateTimePicker1.Value;
xlworksheet.Cells[1, 3] = textBox2.Text;
xlworksheet.Cells[1, 4] = textBox3.Text;
xlworksheet.Cells[1, 5] = textBox4.Text;
xlworksheet.Cells[1, 6] = textBox5.Text;
xlworksheet.Cells[1, 7] = textBox6.Text;
xlworksheet.Cells[1, 8] = textBox7.Text;
xlworksheet.Cells[1, 9] = textBox8.Text;
xlworksheet.Cells[1, 10] = textBox9.Text;
xlworksheet.Cells[1, 13] = textBox10.Text;
xlworksheet.Cells[1, 11] = comboBox1.Text;
xlworksheet.Cells[1, 12] = comboBox2.Text;

Thank you both for contributing..
Shane Nicholson5y ago
Good mornnig Nitin,

Thank you for joining the conversation. This is why I enjoy these forums, you have already taught me something! I thought CSV and excel were almost like for like.
I am going to try and export the data directly as a CSV and avoid excel completely.
Secondly, alluding to your comment about formatting a cell to a date format, I have not yet tried that, but I am guessing I would put that part of the code after instructing VS to create a new xlworkbook?

Thanks for the reply.
Shane Nicholson5y ago
Hey Sachni,

Thanks for the reply. I have tried this before, and it didnt work. I need the date to remain as a date and not a string because Tableau will not recognise it.
Surely there is a simple way I can format date as its exported?
Thanks for helping..
Sachin Singh5y ago
you don't need to cast DateTimePicker value as dateTime , it is already a dateTime
use below code
xlworksheet.Cells[1, 2] = dateTimePicker1.Value.ToString("yyyyMMdd");