5 Replies - 18283 Views - Last Post: 01 February 2008 - 08:26 PM Rate Topic: -----

#1 gymratz  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 129
  • Joined: 18-October 07

Export SQL Query Results to file.

Posted 01 February 2008 - 01:22 PM

Sooo...
I have a tab delimited txt file with 6 million records.
There are 290 unique identifiers in this file so what I did was import the entire thing into SQL.
I am now trying to write a program in vb.net that will first run a query to gain all 290 unique identifiers, and then for each identifier it will query for all rows. I would like to save this data to a file named after the unique identifier.

Below is the code that I have so far, but I am unsure what code I need to use to save the information - any examples would be great!



	Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
		Dim db_connection As SqlConnection
		Dim db_command, db_command2 As SqlCommand
		Dim datareader As SqlDataReader
		Dim var1, var2, filename As String
		'Begin populate dropdownlist

		db_connection = New SqlConnection(ConfigurationSettings.AppSettings("ConnectionStringMerrillDataImport"))
		db_connection.Open()
		db_command = New SqlCommand("select DISTINCT [Column 2], [Column 3] from PIA_Holdings_Records_data_JanToJune order by [Column 2], [Column 3]", db_connection)
		datareader = db_command.ExecuteReader()
		While datareader.Read
			var1 = datareader.GetString(0)
			var2 = datareader.GetString(1)
			filename = var1 & var2
			db_command2 = New SqlCommand("select * from PIA_Holdings_Records_data_JanToJune WHERE [Column 2] = '" & var1 & "'[Column 3] = '" & var2 & "'", db_connection)


		End While
		datareader.Close()



Sooo... It appears I have all the code needed to grab the information - what I need to do is take all the information in db_command2 and save it to a tab delimited text file.... Please help! :)

Is This A Good Question/Topic? 0
  • +

Replies To: Export SQL Query Results to file.

#2 baavgai  Icon User is offline

  • Dreaming Coder
  • member icon

Reputation: 5777
  • View blog
  • Posts: 12,591
  • Joined: 16-October 07

Re: Export SQL Query Results to file.

Posted 01 February 2008 - 03:00 PM

To create a file, use a filestream: http://www.ondotnet....14/streams.html

Why arey you looping, looping? A simple select should get the same data in total:

select *
	from PIA_Holdings_Records_data_JanToJune 
	order by [Column 2], [Column 3]



Hope this helps.
Was This Post Helpful? 0
  • +
  • -

#3 gymratz  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 129
  • Joined: 18-October 07

Re: Export SQL Query Results to file.

Posted 01 February 2008 - 03:57 PM

The table I will be selecting from has 6 million records.
When combining Column 2 and 3 there are a total of 290 unique identifiers.
My goal is to first find all 290 of those (as I will have to do this with other tables over the next couple of weeks) and for each unique identifier pull only the information that contains it - and export all that information to a file with the same name.
I couldn't think of a way to do that by pulling all the information at once. I am very.. VERY noobish at this whole thing :)
Was This Post Helpful? 0
  • +
  • -

#4 baavgai  Icon User is offline

  • Dreaming Coder
  • member icon

Reputation: 5777
  • View blog
  • Posts: 12,591
  • Joined: 16-October 07

Re: Export SQL Query Results to file.

Posted 01 February 2008 - 04:42 PM

I follow, so a different file for each [Column 2],[Column 3] pair?

All things considered, it might still be more efficient to do one giant pull.

Here's something to get you started.

Dim db_command As SqlCommand
Dim datareader As SqlDataReader
Dim filename, lastFilename As String
Dim writer As StreamWriter

db_connection = New SqlConnection(ConfigurationSettings.AppSettings("ConnectionStringMerrillDataImport"))
db_command = New SqlCommand("select * from PIA_Holdings_Records_data_JanToJune order by [Column 2], [Column 3]", db_connection)
Try
	db_connection.Open()
	datareader = db_command.ExecuteReader()
	While datareader.Read
		' initially, lastFilename and writer should be Null, so this should work.
		filename = datareader.GetString(0) & datareader.GetString(1) & ".txt"
		' Same filename? If yes, ignore and just write to the currently open file
		If filename<>lastFilename Then
			' if we have an open file, close it.
			' this will always be true except for the first item
			If writer<>Null Then
				writer.Close()
			End If
			' open a file
			writer = New StreamWriter(filename, True, System.Text.Encoding.ASCII)
			' reset our name check
			lastFilename = filename
		End
		' Write the output; your code here.
		' I'd loop through columns property for the
		' also, a tab is a good delimiter, not usually in data
		' beware of nulls
		writer.Write( ...
	End While
Finally
	' must close open connection
	db_connection.Close()
	
	' must close open file; if it is
	If writer<>Null Then
		writer.Close()
	End If
End Try


Was This Post Helpful? 0
  • +
  • -

#5 gymratz  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 129
  • Joined: 18-October 07

Re: Export SQL Query Results to file.

Posted 01 February 2008 - 05:02 PM

Just saw your code so I will give that a try.
To let you know, I was continuing to work on my idea of using loops and thought I was progressing well until I learned that you can only have one datareader open at a time. So looping through a datareader and trying to create a second one will never work...
I will see what I can do with the code you provided - thank you much :).
Was This Post Helpful? 0
  • +
  • -

#6 baavgai  Icon User is offline

  • Dreaming Coder
  • member icon

Reputation: 5777
  • View blog
  • Posts: 12,591
  • Joined: 16-October 07

Re: Export SQL Query Results to file.

Posted 01 February 2008 - 08:26 PM

View Postgymratz, on 1 Feb, 2008 - 07:02 PM, said:

until I learned that you can only have one datareader open at a time. So looping through a datareader and trying to create a second one will never work...


You should be able to have as many open as you like??? Well, within reason...

If you're working a solution that makes sense to you, that's ideal You'll learn more that way and understand the results much better than someone else's. If you want to do a two loop method, go for it.

I'd suggest, creating a Sub that takes those two columns as parameters. That way you can make both bits, the file write and the outer loop, easier to follow. You'll also isolate the various connection objects, making sure you're one loop isn't stepping on the other with. Which is what I suspect is happening.

Hope this helps.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1