Join 150,053 VB.NET Programmers for FREE! Get instant access to thousands of VB.NET experts, tutorials, code snippets, and more! There are 1,728 people online right now. Registration is fast and FREE... Join Now!
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!
CODE
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!
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
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.
CODE
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
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 .
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.
| 