Hello. I've recently added the ability for a web tool I'm working on for the user to upload an Excel file to the server and than have the data within be inserted into our SQL Server 2000 database. I have it working with a more bare-bones Excel file in which it uses the SqlBulkCopy statement to select all data from the Excel file into a temp. table and then insert the data from the temp table into the database. The issue is with the Excel file. The template we require has gibberish, or more importantly, text that SQL cannot interpret (nor shouldn't) and thus cannot put into the database on the first 5 rows of the file. Right now, the basic SQL query is just: SELECT * FROM blah Code (markup): Is there a way to select everything after the first 5 rows within the Excel file, or just to skip the top 5 rows? Something like (obviously non working syntax): SELECT NOT TOP 5 * FROM blah OR SELECT * FROM blah WHERE NOT TOP 5 * Code (markup): Any suggestions? Thanks.
Hi, Is there an index column in the file or a row number? If so then you could do SELECT * FROM blah WHERE rownumber > 5 Just one thought
You can do this using the LIMIT modifier.... SELECT * FROM BLAH LIMIT 5,18446744073709551615 From MySql manual : http://dev.mysql.com/doc/refman/5.0/en/select.html or you could just use a for loop to iterate through the first 5 rows and discard them ie: $sql = "SELECT * from BLAH"; $result = mysql_query( $sql ); for ($i=0; $i < 5; $i++){ $rs = mysql_fetch_object( $result ); } * then go on to your processing here *
Limit not used in MS SQL. Dont think there's a clean way of doing this like in MySQL. I'd suggest same as Timothy247, have a unique index row.
Thanks for the update. I'm not sure if it will be possible to incorporate an index column, but I'll see what I can do. Thanks again.
You could always write a macro that reads through the spreadsheet and either dumps the data into a clean sheet for importing or directly into the database.
What about simplifying the process and using a .CSV file with no header row instead of an Excel file. CSVs can be opened and editted in Excel, but since it is just a comma seperated text file there is no excess garbage. I know you can do it with Excel files too, but we have always used CSV files as the work required to put into it is much less. The other benefit is that CSV/TXT files don't have a version that you might have to worry about (like Excel does). Basic text is basic text, but Excel formatting changes every few years and older versions of SQL don't always have automatic support.
Any ideas or references on how to do that? @bluegrass special It has to be Excel because of certain information in the file that users will need to see and/or use themselves. Plus if it's a CSV, sometimes Excel or other programs can interpret those in a different way than intended and it would cause far too much confusion, and the user base that will use this will already be confused as it is, lol. Thanks though.
Use example #2 I provided and iterate through the first 5 rows using a for loop, then start processing data. Simple and easy. If you have SQL2005, you can use the ROW_NUMBER functionhave a look here... http://blogs.msdn.com/sqlserver/archive/2006/10/25/limit-in-sql-server.aspx
Will they be uploading files from an application or a web page? What do you use to program with? Depending on your coding language, you could parse the file in the code before you ever send data to SQL-Server.
We are currently using SQL Server 2000 right now and I'm coding a web tool that they'll use to upload the Excel file in ASP.NET and VB.NET using Visual Studio 2005. Thanks for the input, but I already figured it out. I basically used timothy247's post to help me. I only had to slightly modify the original Excel template to make it work perfect. Thanks all.
Excel has a record macro feature that would go a long way to helping you. If you need specific help PM me.
I know how to use that and do simple macros involving shortcut keys, but anything beyond that is something I haven't done before. Anyway, it doesn't matter as I got it working. Thanks.