Sql Stored Procedure to csv file with Timestamp

Question

The application stores results from a SQL stored procedure into a given csv. It is necessary for the file to have a timestamp within the filename. I haven't been successful finding the solution through any of my research. Here's the code, keep in mind that the timestamp needs to have the date and most importantly the time 'hh:ss'

string db = "databasename";
        string startTime = "2018-04-17 00:00:00.000";
        string endTime = "2018-04-17 23:59:59.997";
        string LiquorFile = "LiquorFile.csv";

        using (SqlConnection con = new SqlConnection(GlobalConfig.CnnString(db)))
        {
            var tableName = "liqTemp";
            var fileName = tableName + ".csv";
            var recordCount = 0;
            var fileCount = 0;

            SqlCommand scCmd = new SqlCommand("dbo.spGetInventory_Liquor", con);
            scCmd.CommandType = CommandType.StoredProcedure;
            SqlDataReader reader;

            con.Open();

            scCmd.Parameters.Add("@StartDate", SqlDbType.DateTime).Value = startTime;
            scCmd.Parameters.Add("@EndDate", SqlDbType.DateTime).Value = endTime;

            reader = scCmd.ExecuteReader();

            StreamWriter writer = null;

            try
            {
                while (reader.Read())
                {
                    if (writer == null || recordCount == 50000)
                    {
                        if (writer != null)
                        {
                            writer.Close();
                            writer.Dispose();
                        }
                        fileName = tableName + "_" + (++fileCount).ToString() + ".csv";

                        writer = new StreamWriter(fileName);
                    }
                    recordCount++;
                    writer.WriteLine("\t{0}\t{1}", reader.GetDecimal(0), reader.GetString(1));
                }
                reader.NextResult();
            }
            finally
            {
                if (writer != null)
                {
                    writer.Dispose();
                }
            }
        }

Brainstorming through this implementation I believe this can be incorporated somehow through the start and end time string.

I'm still thinking of a proper title for this question.

So are you saying that this line `fileName = tableName + "_" + (++fileCount).ToString() + ".csv";` should contain the timestamp instead of filecount, or something like that? — TomC, Aug 10 '18 at 06:06
That's correct. Once the timestamp is implemented in the filename I won't have to worry about large individual files. However, I'm going to have account for many files being stored over time. — Gonzo Nick, Aug 10 '18 at 06:59

score 0 · Answer 1 · answered Aug 10 '18 at 06:17

Appears that you want to store some metadata along with the raw CSV data. Some file types, depending on the file type, have loads of metadata properties like authorship and company name etc. So, in this situation, I might elect to store my CSV data in XSLX format using the amazing ClosedXML library. The XSLX file type has lots of metadata properties to store your timestamps and many more.

Below is an example adding properties to a Docx file. This just shows that Microsoft office formats have lots of available metadata properties you can access and use.
How to set extended file properties?

using Microsoft.WindowsAPICodePack.Shell;
using Microsoft.WindowsAPICodePack.Shell.PropertySystem;

string filePath = @"C:\temp\example.docx";
var file = ShellFile.FromFilePath(filePath);

// Read and Write:

string[] oldAuthors = file.Properties.System.Author.Value;
string oldTitle = file.Properties.System.Title.Value;

file.Properties.System.Author.Value = new string[] { "Author #1", "Author #2" };
file.Properties.System.Title.Value = "Example Title";

// Alternate way to Write:

ShellPropertyWriter propertyWriter =  file.Properties.GetPropertyWriter();
propertyWriter.WriteProperty(SystemProperties.System.Author, new string[] { "Author" });
propertyWriter.Close();

Thank you for the feedback, I'll post my solution as soon as it meets the criteria — Gonzo Nick, Aug 10 '18 at 07:07

score 0 · Answer 2 · answered Aug 10 '18 at 08:40

0

Think its just a simple use of current date formatting.

fileName = tableName + "_" + DateTime.Today.ToString("yyyyMMddHHmmss") + ".csv";

where the format is whatever you need - date, time, ticks, etc - whatever you need to get the granularity you desire. You can go to a formt string of "o" to get it down to decimal sub-seconds if you need to.

answered Aug 10 '18 at 08:40

TomC

2,759
1
7
16

Thanks for the feedback Tom. Just tried this implementation but no luck. I'll post my solution as soon as I find one – Gonzo Nick Aug 10 '18 at 09:01
@GonzoNick I must misunderstand what you are trying - perhaps if you give a sample of what you require it will be easier. The code I posted I've used many times before. – TomC Aug 10 '18 at 09:46
You understood my objective, for some reason this implementation is not working. I've tried the following fileName = tableName + "_" + DateTime.Today.ToString("yyyy:MM:dd:HH:mm:ss") + ".csv"; and fileName = tableName + "_" + DateTime.Now.ToString("yyyy:MM:dd:HH:mm:ss") + ".csv"; – Gonzo Nick Aug 11 '18 at 05:38
Also referenced https://stackoverflow.com/questions/12500091/datetime-tostring-format-that-can-be-used-in-a-filename-or-extension however, the result still remains liqTemp.csv – Gonzo Nick Aug 11 '18 at 05:47
Are you sure it’s actually running that line of code, or using that variable when creating the writer? As far as I can see both your examples would fail as you have embedded a quote character in 5e file name. I’d check your build sequence. Either that or a logic error I can’t see. Try moving the stream create outside of the loop as it no longer has to be there. – TomC Aug 11 '18 at 23:03

Sql Stored Procedure to csv file with Timestamp

2 Answers2