0

I have an XLS file. Let's assume that it has 1000 rows in all, with 10 columns. I would like to import rows 5 through 995 (say), and columns 1 though 7 and 10. How can I do this using an SQL query, either though OPENROWSET or BULK INSERT? I am using SQL Server 2008.

CodingInCircles
  • 2,565
  • 11
  • 59
  • 84

3 Answers3

0
SELECT *
FROM OPENROWSET(
    'Microsoft.ACE.OLEDB.12.0',
    'Excel 12.0;HDR=No;Database=c:\path\to\file.xls',
    'SELECT * FROM [Sheet1$A5:J995]'
);
Anon
  • 10,660
  • 1
  • 29
  • 31
0

I would approach this in a slightly different way.

I would import all the data into the database, into a temporary table. Then I would query that table, extracting the information that's needed.

Alternatively, you could look at a SSIS process to do this.

Bravax
  • 10,453
  • 7
  • 40
  • 68
  • Thanks for the reply! Thing is, I would still need the row 5 to row 995 to work, as the last row is a summary and does not follow the format of the rows above it. Is there a way I can do this? Also, I would have to automate this process. I get a file everyday and it has to automatically upload it to the DB. Any tips there? How can I make it accept the new file daily and import it to the DB? – CodingInCircles Mar 11 '11 at 09:41
  • Also, where is the SSIS? How to use it? – CodingInCircles Mar 11 '11 at 09:43
  • 1
    SQL Server Integration Services, it's probably what you need. So it can look at a spreadsheet, suck it's data into SQL, and modify it. Then you can schedule the process to run daily. – Bravax Mar 11 '11 at 09:46
0

I had another idea.

You could also use a Linked Server which connects to the Excel document.
(This is probably an easier approach for you.)

See: http://support.microsoft.com/kb/306397

Bravax
  • 10,453
  • 7
  • 40
  • 68
  • @Bravax: I've used the OPENROWSET statement, but have/had no idea of how I can customise it to suit my requirements. Just to make my question clearer, I will be receiving a file on a daily basis. It may have anywhere between 450-800 rows. All I know is that I would like to extract only a select few columns, and take rows from the 4th or 5th one, right up till the row which ends about 5 rows from the end of the spreadsheet. And I need to automate the whole process. Any ideas? – CodingInCircles Mar 11 '11 at 11:23
  • Look at this article: http://support.microsoft.com/kb/321686, scroll down to the Use ADO and SQLOLEDB. Talk about comprehensive. – Bravax Mar 11 '11 at 11:31
  • Look at this article for how to skip certain rows: http://stackoverflow.com/questions/4929217/openrowset-for-excel-can-we-skip-several-rows – Bravax Mar 11 '11 at 11:33
  • Specify the columns you want, based on their column names. – Bravax Mar 11 '11 at 11:35
  • @Bravax: I tried that too! :( It didn't work! I put the column names as given in the XLS, and it didn't work.. Should this be done in code? Can what I want be done in code, rather than query? – CodingInCircles Mar 11 '11 at 11:44
  • This should go in your query, what are your column names? possibly use []'s round them. – Bravax Mar 11 '11 at 11:49
  • All done.. Still no luck.. :( It said column not found or some such error message. This despite the fact that I had "copy-pasted" from the XLS directly. – CodingInCircles Mar 11 '11 at 12:11