Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Rhinorhino on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

How to create CSV file report?

Status
Not open for further replies.

passs

Programmer
Joined
Dec 29, 2003
Messages
170
Location
RU
Hello everybody!

Does someone know how to create a new csv file which will get the information from SQL request?

I understand that I can use something like this:
SqlCommand cmdGet = new SqlCommand("SELECT * FROM Table", sqlConnection);
SqlDataReader readDat;
readDat = cmdGet.ExecuteReader();
while (readDat.read())
{
somevar = readDat.getStr(0);
somevar2 = readDat.getStr(1);
somevar3 = readDat.getStr(2);
//here to write this vars to a file with ";" between them
//but how can I do it?
}

Thank you,
with best wishes,
Alex
 
you could use this a a boiler plate
Code:
string sep = ";";
StringBuilder sb = new StringBuilder();            
while (dr.Read()) 
{
    for (int i = 0; i < dr.FieldCount; ++i)
    {
        sb = sb.Append(dr.GetValue(i));
        sb = sb.Append(sep);
    }
}
sb.Remove(sb.Length - 1,1); //remove the trailing comma
string str = sb.ToString();
Marty
 
this is a class I created to create a csv string. It accepts a data table and resturns a csv string. You can then write the string to a file.
Code:
Public Class WriteFile
  Public Sub New() 'initialize new write file
  End Sub

'write datatable to a CSV with double quote text qualifiers
  Public Function WriteToFile(ByVal DataTbl As DataTable) As String
    Try
      Dim Record As New System.Text.StringBuilder
      Dim Row As DataRow
      Dim Column As DataColumn

      'dynamically create a csv file with text qualifiers
      For Each Column In DataTbl.Columns
        If Column.Ordinal > 0 Then Record.Append(",")
        Record.Append("""" & Column.ColumnName & """")
      Next
      Record.Append(Chr(13))
      For Each Row In DataTbl.Rows
        For Each Column In DataTbl.Columns
          If Column.Ordinal > 0 Then Record.Append(",") 'add field seperator if not 1st item
          'encapsulate value in quotes if it is a non-null string
          If Column.DataType Is System.Type.GetType("System.String") And Not IsDBNull(Row.Item(Column)) Then
            Record.Append("""" & ChkStr(Row.Item(Column)) & """")
              'ElseIf Column.DataType Is System.Type.GetType("System.DateTime") And Not IsDBNull(Row.Item(Column)) Then
              'Record.Append("#" & ChkStr(Row.Item(Column)) & "#")
          Else
            Record.Append(Row.Item(Column))
          End If
        Next
        Record.Append(Chr(13))
      Next
      Return Record.ToString
    Catch ex As Exception
      Return ("Error: " & ex.Message)
    End Try
  End Function

  Private Function ChkStr(ByVal Str As String) As String
    Str = Replace(Str, Chr(10), " ")
    Str = Replace(Str, Chr(13), " ")
    Str = Replace(Str, """", "'")
    Return Str
  End Function

Jason Meckley
Database Analyst
WITF
 
Hey Guys, thanks for your help!

But I found a little bit different solution, maybe not so optimal and beautiful but it works:
StreamWriter objW;
objW = File.CreateText(Server.MapPath("Reports/"+lblPrID.Text.ToString()+".csv"));//"C:\\Inetpub\\ objW.WriteLine("ProjectID;JobID;PlannedCost;Confrimed by Client;Actual Cost;USD or Rubles;Invoice to Date;Payment from Client;Payment to Partner;Start Date;End Date;Total Spots;GRP;GRP30;Target Audience;Reach 1+;Reach 3+;Reach 5+;Job Description;Media;Detail Media;SubDetail Media;Job Status;ManagerID;Printed;CPP/CPR;CPT;");
sqlConnection.Open();
SqlCommand cmdGetJobs = new SqlCommand("SELECT * FROM Job WHERE ProjectID='"+lblPrID.Text.ToString()+"'",sqlConnection);
SqlDataReader readJobs;
readJobs = cmdGetJobs.ExecuteReader();
string strJobID,strJDescription,strPlannedCost,strConfirmedByClient,strActualCost,strMonitoring;
string strDolOrRub,strInvoiceToDate,strPaymentFClient,strPaymentTPartner,strJobDateB,strJobDateE;
string strTotalSpots,strGRP,strGRP30,strTargetAudience,strR1,strR3,strR5,strJobType,strSubJobType;
string strSubSubJobType,strJobStatus,strManagerID,strPrinted,strCPPCPR,strCPT;
while (readJobs.Read())
{
strJobID = readJobs.GetString(1);
strJDescription = readJobs.GetString(3);
strPlannedCost = readJobs.GetInt32(4).ToString();
strConfirmedByClient = readJobs.GetInt32(5).ToString();
strActualCost = readJobs.GetInt32(6).ToString();
strMonitoring = readJobs.GetInt32(7).ToString();
strDolOrRub = readJobs.GetInt32(8).ToString();
strInvoiceToDate = readJobs.GetString(9);
strPaymentFClient = readJobs.GetString(10);
strPaymentTPartner = readJobs.GetString(11);
strJobDateB = readJobs.GetString(12);
strJobDateE = readJobs.GetString(13);
strTotalSpots = readJobs.GetInt32(14).ToString();
strGRP = readJobs.GetString(15);
strGRP30 = readJobs.GetString(16);
strTargetAudience = readJobs.GetString(17);
strR1 = readJobs.GetString(18);
strR3 = readJobs.GetString(19);
strR5 = readJobs.GetString(20);
strJobType = readJobs.GetString(22);
strSubJobType = readJobs.GetString(23);
strSubSubJobType = readJobs.GetString(24);
strJobStatus = readJobs.GetInt32(25).ToString();
strManagerID = readJobs.GetString(26);
strPrinted = readJobs.GetInt32(27).ToString();
strCPPCPR = readJobs.GetInt32(28).ToString();
strCPT = readJobs.GetInt32(29).ToString();
objW.WriteLine(lblPrID.Text.ToString()+";"+strJobID+";"+strPlannedCost+";"+strConfirmedByClient+";"+strActualCost+";"+strDolOrRub+";"+strInvoiceToDate+";"+strPaymentFClient+";"+strPaymentTPartner+";"+strJobDateB+";"+strJobDateE+";"+strTotalSpots+";"+strGRP+";"+strGRP30+";"+strTargetAudience+";"+strR1+";"+strR3+";"+strR5+";"+strJDescription+";"+strJobType+";"+strSubJobType+";"+strSubSubJobType+";"+strJobStatus+";"+strManagerID+";"+strPrinted+";"+strCPPCPR+";"+strCPT+";");
}
readJobs.Close();
sqlConnection.Close();
objW.Close();

But thank you very much anyway!

best regards,
Alex
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top