[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Get Column headers into a CSV export file

Posted on 2009-04-27
5
Medium Priority
?
823 Views
Last Modified: 2013-11-26
Hi,

I'm using Visual Studio 2008  VB.Net    (   .net 3.5   )

The following code exports a CSV file of data contained in the locations table. At the moment the data export works fine.

What I would like is to include the column headers in the export as well as the data.

Thanks,

Dave
'Get a .csv of the locations table
            Dim fs As New IO.FileStream(strDesktopFolder & "\" & "locations.csv", IO.FileMode.CreateNew, IO.FileAccess.Write)
            Dim sw As New IO.StreamWriter(fs)
            Dim conn As New Data.SqlClient.SqlConnection(My.Settings.internalConnectionString)
            Dim cmd As New Data.SqlClient.SqlCommand("SELECT locationID, name, secondName, " & _
            "description, long, lat, datum, stamp, userID FROM locations", conn)
            conn.Open()
            Dim dr As Data.SqlClient.SqlDataReader = cmd.ExecuteReader()
            While dr.Read
                sw.WriteLine("{0},{1},{2},{3},{4},{5},{6},{7},{8}" _
                             , dr(0), dr(1), dr(2), dr(3), dr(4), dr(5), dr(6), dr(7), dr(8))
            End While
            sw.Flush()
            sw.Close()
            conn.Close()
            fs.Close()
        Catch ex As Exception
            MsgBox(ex.Message & ". locations")
        End Try

Open in new window

0
Comment
Question by:davecocks
5 Comments
 
LVL 9

Expert Comment

by:Sreedhar Vengala
ID: 24240094
Check this:  this is in C# (may help you)
private void loadTables()
{
         // Connects to database, and selects the table names.
         SqlConnection cn = new SqlConnection(prop.sqlConnString);
SqlDataAdapter da = new SqlDataAdapter
("select name from dbo.sysobjects where xtype = 'U'
and name <> 'dtproperties' order by name", cn);
DataTable dt = new DataTable();

         // Fills the list to an DataTable.
da.Fill(dt);

         // Clears the ListBox
this.lbxTables.Items.Clear();

         // Fills the table names to the ListBox.
         // Notifies user if there is no user table in the database yet.
if (dt.Rows.Count == 0)
{
MessageBox.Show("There is no user table in the specified database.
Import a CSV file first.", "Warning",
MessageBoxButtons.OK, MessageBoxIcon.Warning);
this.lbxTables.Items.Add("< no user table in database >");
this.btnExportToCSV.Enabled = false;
}
else
{
this.btnExportToCSV.Enabled = true;

for (int i = 0; i < dt.Rows.Count; i++)
{
    this.lbxTables.Items.Add(dt.Rows[i][0].ToString());
}
this.lbxTables.SelectedIndex = 0;
}
}
 
originally from : http://www.codeproject.com/KB/database/Cs_CSV_import_export.aspx
0
 
LVL 9

Expert Comment

by:Sreedhar Vengala
ID: 24240099

private void exportToCSVfile(string fileOut)
{
    // Connects to the database, and makes the select command.
    SqlConnection conn = new SqlConnection(prop.sqlConnString);
    string sqlQuery = "select * from " + this.lbxTables.SelectedItem.ToString();
    SqlCommand command = new SqlCommand(sqlQuery, conn);
    conn.Open();
			
    // Creates a SqlDataReader instance to read data from the table.
    SqlDataReader dr = command.ExecuteReader();
 
    // Retrieves the schema of the table.
    DataTable dtSchema = dr.GetSchemaTable();
 
    // Creates the CSV file as a stream, using the given encoding.
    StreamWriter sw = new StreamWriter(fileOut, false, this.encodingCSV);
			
    string strRow; // represents a full row
 
    // Writes the column headers if the user previously asked that.
    if (this.chkFirstRowColumnNames.Checked)
    {
	sw.WriteLine(columnNames(dtSchema, this.separator));
    }
 
    // Reads the rows one by one from the SqlDataReader
    // transfers them to a string with the given separator character and
    // writes it to the file.
    while (dr.Read())
    {
	strRow = "";
	for (int i = 0; i < dr.FieldCount; i++)
	{
	    strRow += dr.GetString(i);
	    if (i < dr.FieldCount - 1)
	    {
		strRow += this.separator;
	    }
	}
	sw.WriteLine(strRow);
    }
 
    // Closes the text stream and the database connection.
    sw.Close();
    conn.Close();
 
    // Notifies the user.
    MessageBox.Show("ready");
}

Open in new window

0
 
LVL 53

Assisted Solution

by:Dhaest
Dhaest earned 300 total points
ID: 24240140

Dim bAddHeader as boolean = true
 
While dr.Read
     if bAddHeader = true then
sw.WriteLine("{0},{1},{2},{3},{4},{5},{6},{7},{8}" _
dr.Table.Columns(0).ColumnName, dr.Table.Columns(1).ColumnName, ...
          bAddHeader = false
     end if
                sw.WriteLine("{0},{1},{2},{3},{4},{5},{6},{7},{8}" _
                             , dr(0), dr(1), dr(2), dr(3), dr(4), dr(5), dr(6), dr(7), dr(8))
            End While

Open in new window

0
 
LVL 48

Accepted Solution

by:
Wayne Taylor (webtubbs) earned 1700 total points
ID: 24240146
Try something like the below example. You'll also note that I've used a StringBuilder and am only writing to the file at the end of the procedure, which will greatly improve the execution time.

Wayne
        Dim sb As New System.Text.StringBuilder
        Dim conn As New Data.SqlClient.SqlConnection(My.Settings.internalConnectionString)
        Dim cmd As New Data.SqlClient.SqlCommand("SELECT locationID, name, secondName, " & _
                                                 "description, long, lat, datum, stamp, userID FROM locations", conn)
        conn.Open()
        Dim dr As Data.SqlClient.SqlDataReader = cmd.ExecuteReader()
        For c As Integer = 0 To 8
            sb.Append(dr.GetName(c))
            If c < 8 Then sb.Append(",")
        Next
        sb.AppendLine()
        While dr.Read
            sb.AppendLine(String.Format("{0},{1},{2},{3},{4},{5},{6},{7},{8}", dr(0), dr(1), dr(2), dr(3), dr(4), dr(5), dr(6), dr(7), dr(8)))
        End While
        conn.Close()
        My.Computer.FileSystem.WriteAllText(strDesktopFolder & "\" & "locations.csv", sb.ToString, False)

Open in new window

0
 
LVL 1

Author Closing Comment

by:davecocks
ID: 31574853
Thanks guys, I split the points a bit cos it looked like Dhaest's solution would have worked, but Wayne's was just too good.

Thanks again!
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
Simulator games are perfect for generating sample realistic data streams, especially for learning data analysis. It is even useful for demoing offerings such as Azure stream analytics, PowerBI etc.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses
Course of the Month20 days, 2 hours left to enroll

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question