SQLTeam.com | Weblogs | Forums

Extract items from a table to .txt files


#1

Hi I'm completely new to SQL and need some help with a very specific problem.

I have a .db file that contains a table with two columns. One column is for IDs, and the other column is a text field for each ID.

Using DB Browser for SQLite, I can extract the data from a text field and output it to a .txt file individually, but I need to do this for every item in the text field column. Can someone help me with an SQL script to select every item in the text column, and output the data to a separate .txt file for each individual item?

Your help is much appreciated :slight_smile:


#2

This is a SQL Server forum and you may get better help from a forum that is specific to SQLite. However, I have something laying around that will do that using PowerShell so I will share.

I created a SQLite table with two columns: Id and Description. The PowerShell script will open the database, select the data from the table, and output to file with a name based on a value in the column. Probably not the best example of PowerShell but it works.

This assumes a lot of paths so change to meet your needs.

Add-Type -Path "C:\Program Files (x86)\System.Data.SQLite\2010\bin\System.Data.SQLite.dll";

$con = New-Object -TypeName System.Data.SQLite.SQLiteConnection;
$con.ConnectionString = "Data Source=C:\Temp\ListOfItems.sqlite";
$con.Open();
$con.Close();
$sql = $con.CreateCommand();
$sql.CommandText =
"
SELECT *
FROM ListOfItems As li;
";
$adapter = New-Object -TypeName System.Data.Sqlite.SQLiteDataAdapter $sql;
$data = New-Object System.Data.DataSet;
[void]$adapter.Fill($data);
ForEach($row in $data.tables[0].Rows) {
$Id = $row.Id;
$Description = $row.Description;
$FileName = "C:\Temp$Id.txt";
write-output $FileName;
Out-File -filepath $FileName -append -Inputobject $Description;
}


#3

Thanks for the fast reply, but I'm lost on a few things.

Firstly, I don't have anything at C:\Program Files (x86)\System.Data.SQLite\2010\bin\System.Data.SQLite.dll.

Secondly, I'm not really sure what I need to change in the script to suit my .db file.

Sorry I'm such a noob at this :sweat_smile:


#4

Yeah, you will have to download the SQLite libraries from their site. I should have mentioned that. I based a lot of that off an excellent article you may be interested in.

SQLite and PowerShell

Another option is creating an SSIS package but you may not have that option available to you.


#5

Sorry to be so bothersome, but do you have a link to somewhere I can download the libraries? I'm having trouble finding them.


#6

No problem, glad I can help.

You can find all different flavors of those at this site:

SQLite Downloads

I downloaded the Precompiled Binaries for 64-bit Windows (.Net Framework 3.5 SP1). You will have to get the one for your OS and what is supported on your system.