Saving the result of a query as a CSV file

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for SQLite
Post Reply
bairog
Posts: 120
Joined: Mon 29 Apr 2013 09:05

Saving the result of a query as a CSV file

Post by bairog » Mon 23 Apr 2018 05:45

Hello.
I need to save the result of SELECT into CSV file. For now do it using StreamWriter and SQLiteDataReader:

Code: Select all

var headers = new string[] { "Post Name", "", "User Name", "User Age", "User Role Name"};            
var sb = new StringBuilder(100);
var separator = CultureInfo.CurrentCulture.TextInfo.ListSeparator;
using (var sw = new StreamWriter( "d:\\123.csv", false, Encoding.GetEncoding("windows-1251")))
{
	//write headers to file
 	sb.Clear();
	for (i = 0; i < headers.Length; i++)
	if (i != headers.Length - 1)
	{
		sb.Append(headers[i]);
		sb.Append(separator);
	}
	else
		sb.Append(headers[i]);
	//write string to file
	sw.WriteLine(sb.ToString());

	//write recods to file
	var sqlBuilder = new SQLiteConnectionStringBuilder() { DataSource="d:\\123.db", FailIfMissing = true };               
	using (var command = new SQLiteCommand("SELECT Post.Name, User.Name, User.Age, UserRole.Name FROM Post 		INNER JOIN (User INNER JOIN UserRole ON UserRole.Id=User.UserRoleId) ON User.Id=Post.UserId", connection))                
	{
		connection.Open();
		var reader = command.ExecuteReader();
		while (reader.Read())
		{
			sb.Clear();
			sb.Append(reader.GetString(0));
			sb.Append(separator);
			sb.Append(reader.GetString(1));
			sb.Append(separator);
			sb.Append(reader.GetInt16(2));
			sb.Append(separator);
			sb.Append(reader.GetString(3));
			sb.Append(separator);                        
			//write string to file
			sw.WriteLine(sb.ToString());
		}

		reader.Close();
		connection.Close();
	}  
}
Is there some built-in API to do the same? Is It faster than my approach?

Pinturiccio
Devart Team
Posts: 2420
Joined: Wed 02 Nov 2011 09:44

Re: Saving the result of a query as a CSV file

Post by Pinturiccio » Wed 25 Apr 2018 14:02

dotConnect for SQLite does not have a tool for saving data in CSV format. So you have to implement it yourself.

bairog
Posts: 120
Joined: Mon 29 Apr 2013 09:05

Re: Saving the result of a query as a CSV file

Post by bairog » Thu 26 Apr 2018 04:54

Pinturiccio wrote: Wed 25 Apr 2018 14:02 dotConnect for SQLite does not have a tool for saving data in CSV format. So you have to implement it yourself.
So there is no exporting API at all? XLS or maybe some other format?

Pinturiccio
Devart Team
Posts: 2420
Joined: Wed 02 Nov 2011 09:44

Re: Saving the result of a query as a CSV file

Post by Pinturiccio » Thu 26 Apr 2018 10:07

dotConnect for SQLite does not have a specific tool or API to save the results of an SQLite query as a CSV file in a .NET application.
bairog wrote:XLS or maybe some other format?
You can use Devart Excel Add-in for SQLite. It allow you to select SQLite data to Excel change it and commit changes back to database. In it, you can enter your own SQL query and save the result as a CSV or XLS file via Excel.

You can download the Excel Add-ins installation file from our site: https://www.devart.com/excel-addins/download.html

After the first installation of Excel Add-ins, 30-day trial period starts for all installed sources. If you select SQLite (all sources are selected by default), the trial period for Excel Add-in for SQLite starts.


Besides dotConnect for SQLite supports SQL Server Business Intelligence Solutions. You can create a SSIS package that loads data from an SQLite database to a CSV file. For more information, please refer to https://social.msdn.microsoft.com/Forum ... onservices

Post Reply