|
dev
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Importing DataI'm new to SQL stuff. All I have is MS SQL Express that came with Visual
Studio 2005, and SQL Server Management Studio Express, which I downloaded and installed. I've created a new table, and I have a comma-delimited text file of data I want in that table. Can someone tell me the least painful way to import that data into the table? Thanks. The best way is to Create a table in the database that represents the data
structure. Then use the filestream in System.IO namespace and read the file. you will have to use some creativity to read until it reaches a comma then mark that as a field. Do that to create a datarow in a datatable or arraylist then write that row to the database. That is one way and in that I would do all your error checking and other logic required to ensure you are importing what you want. But this uses the basics and is pretty easy. If you look at other built in methods those can make your life easier but would take longer to explain. Tom Show quote "Jonathan Wood" <jw***@softcircuits.com> wrote in message news:%239kZoJ0HIHA.4584@TK2MSFTNGP03.phx.gbl... > I'm new to SQL stuff. All I have is MS SQL Express that came with Visual > Studio 2005, and SQL Server Management Studio Express, which I downloaded > and installed. > > I've created a new table, and I have a comma-delimited text file of data I > want in that table. > > Can someone tell me the least painful way to import that data into the > table? > > Thanks. > > -- > Jonathan Wood > SoftCircuits Programming > http://www.softcircuits.com > Jonathan,
A comma seperated file contains datafields, the best way is to set those datafields one by one in your table. However if you see the rows as objects, then you can by instance zip them and set them then as a kind of image/blog whatever you name it in the SQL server. However as least you have then to make from your CSV file a byte array. Be aware that it is not one simple instruction to do the latter, doing it field by field is much easier and as well better to use. Cor Thanks, but I didn't even understand what you mean. I was hoping there was
an import command somewhere, like there is in Access. I'll write code if I have to, if I can figure out how. Show quote "Cor Ligthert[MVP]" <notmyfirstn***@planet.nl> wrote in message news:57FE669C-2242-4512-95BA-43C26135CFF9@microsoft.com... > Jonathan, > > A comma seperated file contains datafields, the best way is to set those > datafields one by one in your table. > > However if you see the rows as objects, then you can by instance zip them > and set them then as a kind of image/blog whatever you name it in the SQL > server. However as least you have then to make from your CSV file a byte > array. > > Be aware that it is not one simple instruction to do the latter, doing it > field by field is much easier and as well better to use. > > Cor OK,
> Thanks, but I didn't even understand what you mean. I was hoping there was Make from your CSV a dataset with datatable(s) in it using OleDB,> an import command somewhere, like there is in Access. > > I'll write code if I have to, if I can figure out how. > Then create the update from that to your database. (You can use the commandbuilder for that). Be aware that there is not a method, which can make from a dataset a DataBase. Be aware that the dataset has to have the same schema as your database. In case that it is not equal you can use the FillSchema method. ----------------------------------------------------------- For Bill. If you want to do it only one time, then use by hand one of the by Bill so often mentioned methods., he knows probably more of them then me. Bill will give you the address of his book, in my opinion the most practical books about AdoNet that I know. (I don't get fees from Bill) Cor Ah, none of the above.
ADO and ADO.NET (and all of its predecessors after DB-Library) are QUERY interfaces, not designed for bulk operations. It's a waste of time and resources to try to use the System.Data classes to import more than a few rows of data. If the data is being "manufactured" it still makes sense to write it to a delimited file and stream it in via one of the bulk copy techniques discussed below. When it's time to import data into SQL Server, the fastest (by an order of magnitude or more) is to use the TDS bulk copy approach. This is exposed in a number of ways including: a.. The BCP commandline utility. b.. TSQL BulkCopy operations c.. DTS/SSIS scripts d.. ADO.NET SqlBulkCopy method. All of these techniques can import data from anything that can be read by a .NET data provider, an OLE DB data provider, and ODBC driver or your own custom-written data provider that exposes a DataReader. This means other database tables, flat files, text files, delimited files--almost anything. These routines can import millions of rows in no time. Some Oracle customers buy SQL Server just to get SSIS. My latest book details how to use SqlBulkCopy. BOL shows how to do the rest. I'll be happy to show it to you if you come to one of my sessions this week at DevConnections in Vegas or at my workshop in Vancouver BC on the 26th (see www.devweek.com for availability). hth -- Show quote____________________________________ William (Bill) Vaughn Author, Mentor, Consultant Microsoft MVP INETA Speaker www.betav.com/blog/billva www.betav.com Please reply only to the newsgroup so that others can benefit. This posting is provided "AS IS" with no warranties, and confers no rights. __________________________________ Visit www.hitchhikerguides.net to get more information on my latest book: Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition) and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook) ----------------------------------------------------------------------------------------------------------------------- "Jonathan Wood" <jw***@softcircuits.com> wrote in message news:%239kZoJ0HIHA.4584@TK2MSFTNGP03.phx.gbl... > I'm new to SQL stuff. All I have is MS SQL Express that came with Visual > Studio 2005, and SQL Server Management Studio Express, which I downloaded > and installed. > > I've created a new table, and I have a comma-delimited text file of data I > want in that table. > > Can someone tell me the least painful way to import that data into the > table? > > Thanks. > > -- > Jonathan Wood > SoftCircuits Programming > http://www.softcircuits.com > Bill,
>>>>> ADO and ADO.NET (and all of its predecessors after DB-Library) are QUERY interfaces, not designed for bulk operations. It's a waste of time and resources to try to use the System.Data classes to import more than a few rows of data. >>>>> Can you explain why it's a waste of time? Looking at what I've found so far, that approach would probably take me, personally, about hundredth of the time requires to figure out the other stuff. And that takes into consideration the fact that I've been so far unable to find a simple example showing how to add records to a database using the System.Data classes. >>>>> When it's time to import data into SQL Server, the fastest (by an order of magnitude or more) is to use the TDS bulk copy approach. This is exposed in a number of ways including: a.. The BCP commandline utility. b.. TSQL BulkCopy operations c.. DTS/SSIS scripts d.. ADO.NET SqlBulkCopy method. All of these techniques can import data from anything that can be read by a ..NET data provider, an OLE DB data provider, and ODBC driver or your own custom-written data provider that exposes a DataReader. This means other database tables, flat files, text files, delimited files--almost anything. These routines can import millions of rows in no time. Some Oracle customers buy SQL Server just to get SSIS. >>>>> Okay...I guess TDS stands for something but "tds bulk copy" brings up one a single result on Google, and that wasn't much help. Let's try this: Can you tell me which of these is available with what I have (VS2005 and SQL Server Management Studio Express)? Also, any spec of details on using any of these would be a tremendous help. Maybe even a link of some sort. And I have your book "Hitchhiker's Guide to Visual Studio and SQL Server." Am I understanding correctly that this doesn't step through what I want to do anywhere? Or how about an example that shows how to add records to a database from C# (with no controls, Access, or anything else other than C# and SQL)? >>>>> My latest book details how to use SqlBulkCopy. BOL shows how to do the rest. I'll be happy to show it to you if you come to one of my sessions this week at DevConnections in Vegas or at my workshop in Vancouver BC on the 26th (see www.devweek.com for availability). <<<<< Sounds good, but I'll be far from Vegas at that time. Thanks. And, I just wrote a whitepaper on this for Developer.Com.
http://www.developer.com/net/asp/article.php/10917_3702826_1 -- Show quote____________________________________ William (Bill) Vaughn Author, Mentor, Consultant, Dad, Grandpa Microsoft MVP INETA Speaker www.betav.com www.betav.com/blog/billva Please reply only to the newsgroup so that others can benefit. This posting is provided "AS IS" with no warranties, and confers no rights. __________________________________ Visit www.hitchhikerguides.net to get more information on my latest book: Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition) and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook) ----------------------------------------------------------------------------------------------------------------------- "Jonathan Wood" <jw***@softcircuits.com> wrote in message news:e$rVhx%23HIHA.4272@TK2MSFTNGP06.phx.gbl... > Bill, > >>>>>> > ADO and ADO.NET (and all of its predecessors after DB-Library) are QUERY > interfaces, not designed for bulk operations. It's a waste of time and > resources to try to use the System.Data classes to import more than a few > rows of data. >>>>>> > > Can you explain why it's a waste of time? Looking at what I've found so > far, that approach would probably take me, personally, about hundredth of > the time requires to figure out the other stuff. And that takes into > consideration the fact that I've been so far unable to find a simple > example showing how to add records to a database using the System.Data > classes. > >>>>>> > When it's time to import data into SQL Server, the fastest (by an order of > magnitude or more) is to use the TDS bulk copy approach. This is exposed > in a number of ways including: > a.. The BCP commandline utility. > b.. TSQL BulkCopy operations > c.. DTS/SSIS scripts > d.. ADO.NET SqlBulkCopy method. > All of these techniques can import data from anything that can be read by > a .NET data provider, an OLE DB data provider, and ODBC driver or your own > custom-written data provider that exposes a DataReader. This means other > database tables, flat files, text files, delimited files--almost anything. > These routines can import millions of rows in no time. Some Oracle > customers buy SQL Server just to get SSIS. >>>>>> > > Okay... > > I guess TDS stands for something but "tds bulk copy" brings up one a > single result on Google, and that wasn't much help. > > Let's try this: Can you tell me which of these is available with what I > have (VS2005 and SQL Server Management Studio Express)? Also, any spec of > details on using any of these would be a tremendous help. Maybe even a > link of some sort. > > And I have your book "Hitchhiker's Guide to Visual Studio and SQL Server." > Am I understanding correctly that this doesn't step through what I want to > do anywhere? Or how about an example that shows how to add records to a > database from C# (with no controls, Access, or anything else other than C# > and SQL)? > >>>>>> > My latest book details how to use SqlBulkCopy. BOL shows how to do the > rest. I'll be happy to show it to you if you come to one of my sessions > this week at DevConnections in Vegas or at my workshop in Vancouver BC on > the 26th (see www.devweek.com for availability). > <<<<< > > Sounds good, but I'll be far from Vegas at that time. > > Thanks. > > -- > Jonathan Wood > SoftCircuits Programming > http://www.softcircuits.com > You could write code as many of the others suggested. Or you could do it
the easy way. Download the 180 day trial of full SQL Server 2005 and install just the client access & developer tools. It will include include SQL Server Management Studio. From that you can do a simple import/export. http://www.microsoft.com/sql/downloads/trial-software.mspx#EXC Show quote "Jonathan Wood" <jw***@softcircuits.com> wrote in message news:%239kZoJ0HIHA.4584@TK2MSFTNGP03.phx.gbl... > I'm new to SQL stuff. All I have is MS SQL Express that came with Visual > Studio 2005, and SQL Server Management Studio Express, which I downloaded > and installed. > > I've created a new table, and I have a comma-delimited text file of data > I want in that table. > > Can someone tell me the least painful way to import that data into the > table? > > Thanks. > > -- > Jonathan Wood > SoftCircuits Programming > http://www.softcircuits.com >  |
|||||||||||||||||||||||
Other interesting topics