0

I have an excel file that I need to take a data from and insert into database table.

This file contains not only data that I need. It also contains logo, some information about the file and total amount as a subtotal of some numbers in a column.

I need to have a stored procedure that only reads a data and ignores any other extra stuff including logos, total and etc.

Also, I need to insert only the data that does not exist yet. Table where I do insert has composite primary key (CardMember,YYYYMM).

We are going to have one file every month for one card member.

This is an example of an image

enter image description here

I read some posts but they do not explain my scenario.

How can I do this?

gene
  • 2,098
  • 7
  • 40
  • 98

1 Answers1

0

Have you tried using OpenRowset? You can select the range of cells that you want to query.

This previous thread may be of help: openrowset for excel: can we skip several rows?

SELECT *
FROM OPENROWSET(
'Microsoft.ACE.OLEDB.12.0',
'Excel 12.0;HDR=YES;Database=c:\YourFilePath',
'SELECT * FROM [sheet1$A5:F]')
WHERE AccountNumber is not null

You can use a where clause on a column that will always have data to exclude the totals

Community
  • 1
  • 1
DaveFoyf
  • 1
  • 2
  • The file is going to be created manually by a user. And I do not know where tabular data will start from. Also, the amount field has a total, that I will need to ignore. – gene Jul 07 '15 at 16:23
  • As per my edit above, you can ignore the totals with a Where clause. You could write a subquery to determine your start point, but I would suggest having some form of consistency in the formatting of the spreadsheets your users are creating. Perhaps by providing templates you could ensure that they always start on a particular row. – DaveFoyf Jul 08 '15 at 08:49