Write and save a semicolon delimeted csv file

Hi,

I have this data that I would like to save to a semicolon seperated csv/text file.

The data result from the sql database looks like this:

EmployeeID   aDay  aMonth  aYear      WHours
1                    1        12          2013       8
1                    2        12          2013       6,5
2                    1        12          2013       7
2                    2        12          2013       4,75

I would like this to be written to the following format:
1;20131201;800
1;20131202;650
2;20131201;700
2;20131202;475

And then possible to save as a csv/text file to the harddrive.

Thanks for help!

Peter
peternordbergAsked:
Who is Participating?
 
Fernando SotoConnect With a Mentor RetiredCommented:
Hi peternordberg;

I am assuming that you place the data from the database into a DataTable control and that all columns are integer except the WHours which I assume is Decimal type. The following Linq to Object query should work the way you need it to.

Dim query = From data In dt.AsEnumerable() _
            Select data.Field(Of Integer)("EmployeeID").ToString() & ";" & _
                   data.Field(Of Integer)("aYear").ToString("D4") & _
                   data.Field(Of Integer)("aMonth").ToString("D2") & _
                   data.Field(Of Integer)("aDay").ToString("D2") & ";" & _
                   (data.Field(Of Decimal)("WHours") * 100).ToString("G3")

For Each line In query
    Console.WriteLine(line)
Next

Open in new window

0
 
Monica PConnect With a Mentor Software DeveloperCommented:
This is my rough idea ., do make testing with code when u apply.,

Code to Write the Text in to TEXT File :

Dim FILE_OUT_PATH As String =HttpContext.Current.Server.MapPath("~") & "\SaveFile\"
            If Not Directory.Exists(FILE_OUT_PATH) Then
                Directory.CreateDirectory(FILE_OUT_PATH)
            End If
            Dim outFileName As String = "Filename1_" & Today.ToString("ddMMyyyy") & "_" & Now.ToString("HHmmss") & ".csv"
            Dim outFilePath As String = FILE_OUT_PATH & "\" & outFileName

            Dim oWrite As StreamWriter
            oWrite = File.CreateText(outFilePath)
            dim line as string
               
         'Format the String as u mentioned in loop and assign the formatted value to variable "Line"
          '********************************************************
            oWrite.WriteLine(Line)

            Dim file As System.IO.FileInfo = New System.IO.FileInfo(outFilePath)
                If file.Exists Then 'set appropriate headers
                    Response.Clear()
                    HttpContext.Current.Response.Clear()
                    HttpContext.Current.Response.AddHeader("content-disposition", String.Format("attachment; filename={0}", file.Name))
                    HttpContext.Current.Response.ContentType = "plain/text"

                    Dim MyStreamReader As New StreamReader(file.FullName)
                    Dim str As String = MyStreamReader.ReadToEnd()
                    HttpContext.Current.Response.Write(str)
                    MyStreamReader.Close()
                    HttpContext.Current.Response.End()
                Else
                    Response.Write("This file does not exist.")
                End If
0
 
peternordbergAuthor Commented:
thanks for good help!
0
All Courses

From novice to tech pro — start learning today.