Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 550
  • Last Modified:

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
0
peternordberg
Asked:
peternordberg
3 Solutions
 
Fernando SotoCommented:
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 PCommented:
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

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now