Export Postgres Table to csv File using C#

I tried to export table values from Postgres to csv file using c#. Below is my code;

private void ExportFileFromLocalDb1()
{
// Export path and file.
string exportPath = "D:\\JESAP files\\LTS Project\\Test\\"; //D:\JESAP files\LTS Project\Test
string exportCsv = "transaction.csv";
// Stream writer for CSV file.
StreamWriter csvFile = null;
// Check to see if the file path exists.
if (Directory.Exists(exportPath))
{
try
{
using (NpgsqlConnection con = ClassConnection.GetDbCon())
{
NpgsqlCommand cmd = new NpgsqlCommand("SELECT id, date, bankbooknumber, transaction_code_id, amount, debit_acc_number, credit_acc_number, user_id, vbcode, synchronized, description FROM public.transactions;", con);
cmd.CommandType = CommandType.Text;
con.Open();
NpgsqlDataReader rdr = cmd.ExecuteReader();
// Stream writer for CSV file.
csvFile = new StreamWriter(@exportPath + exportCsv);
// Add the headers to the CSV file.
csvFile.WriteLine(string.Format("\"{0}\",\"{1}\",\"{2}\",\"{4}\",\"{5}\",\"{6}\",\"{7}\",\"{8}\",\"{9}\",\"{10}\"",
rdr.GetName(0),
rdr.GetName(1),
rdr.GetName(2),
rdr.GetName(3),
rdr.GetName(4),
rdr.GetName(5),
rdr.GetName(6),
rdr.GetName(7),
rdr.GetName(8),
rdr.GetName(9),
rdr.GetName(10)
));
// Construct CSV file data rows.
while (rdr.Read())
{
// Add line from reader object to new CSV file.
csvFile.WriteLine(string.Format("\"{0}\",\"{1}\",\"{2}\",\"{4}\",\"{5}\",\"{6}\",\"{7}\",\"{8}\",\"{9}\",\"{10}\"",
rdr.GetName(0),
rdr.GetName(1),
rdr.GetName(2),
rdr.GetName(3),
rdr.GetName(4),
rdr.GetName(5),
rdr.GetName(6),
rdr.GetName(7),
rdr.GetName(8),
rdr.GetName(9),
rdr.GetName(10)
));
}
// Message stating export successful.
MessageBox.Show("Data export successful.");
}
}
catch (Exception e)
{
throw e;
}
finally
{
csvFile.Close();
}
}
else
{
// Display a message stating file path does not exist.
MessageBox.Show("File path does not exist.");
}
}

the export is successful, but, I only get the header for all rows. See image below:

Please help.

Add this methods in your code and pass filename and connection sting in that

private const string QUOTE = "\"";

private const string ESCAPED_QUOTE = "\"\"";

private static char[] CHARACTERS_THAT_MUST_BE_QUOTED = { ',', '"', '\n', '\r' };

private static string Escape(string s)

{

if (string.IsNullOrEmpty(s))

return string.Empty;

if (s.Contains(QUOTE))

s = s.Replace(QUOTE, ESCAPED_QUOTE);

if (s.IndexOfAny(CHARACTERS_THAT_MUST_BE_QUOTED) > -1)

s = QUOTE + s + QUOTE;

return s;

}

private void SqlToFile(string Sql, string FileName)

{

NpgsqlCommand cmd = new NpgsqlCommand(Sql, conn);

NpgsqlDataReader reader = cmd.ExecuteReader();

string[] outline = new string[reader.FieldCount];

StreamWriter sw = new StreamWriter(FileName);

while (reader.Read())

{

while (reader.Read())

{

for (int i = 0; i < reader.FieldCount; i++)

outline[i] = Escape(reader.GetValue(i).ToString());

sw.WriteLine(String.Join(",", outline));

}

reader.Close();

sw.Close();

}

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Jes Sie6y ago
Thank you Rajanikant and Jenith. I will explore all your suggested links and code. Thank you so much
0
Rajanikant Hawaldar6y ago
Hey Hold On, refer below articles.
https://www.c-sharpcorner.com/UploadFile/deveshomar/export-datatable-to-csv-using-extension-method/
https://www.postgresqltutorial.com/export-postgresql-table-to-csv-file/
https://phoenixnap.com/kb/export-postgresql-table-to-csv
0
Jenith Thakkar6y ago
Add this methods in your code and pass filename and connection sting in that

private const string QUOTE = "\"";
private const string ESCAPED_QUOTE = "\"\"";
private static char[] CHARACTERS_THAT_MUST_BE_QUOTED = { ',', '"', '\n', '\r' };
private static string Escape(string s)
{
if (string.IsNullOrEmpty(s))
return string.Empty;
if (s.Contains(QUOTE))
s = s.Replace(QUOTE, ESCAPED_QUOTE);
if (s.IndexOfAny(CHARACTERS_THAT_MUST_BE_QUOTED) > -1)
s = QUOTE + s + QUOTE;
return s;
}
private void SqlToFile(string Sql, string FileName)
{
NpgsqlCommand cmd = new NpgsqlCommand(Sql, conn);
NpgsqlDataReader reader = cmd.ExecuteReader();
string[] outline = new string[reader.FieldCount];
StreamWriter sw = new StreamWriter(FileName);
while (reader.Read())
{
while (reader.Read())
{
for (int i = 0; i < reader.FieldCount; i++)
outline[i] = Escape(reader.GetValue(i).ToString());
sw.WriteLine(String.Join(",", outline));
}
}
reader.Close();
sw.Close();
}
0