CODEWITHSUNDEEP
sqlsql-serverexcel
Mitchi
Mitchi

Reputation: 37

Auto Delete rows in Excel before import to SQL Server

I am trying to import an excel file to an SQL Server Database.

Concern is SQL Server does not recognize the excel file because the first row contains a title and not the table column headers. The table headers are in row 3.

enter image description here

The excel file (.xlsx) is a report extraction from the ITSM tool (generates every 30 minutes) saved on a folder path.

From what I've read from the forums, macros can be implemented however the file needs to be opened manually in order for this to take effect.

Is there a way to remove the 2 rows in excel without any manual interaction? I have the general idea on how to automate the import but I would like the Excel file to be in the correct format.

PS. I am using SQL Server 2012 Express

Thanks!

Other Sources:

https://www.mrexcel.com/forum/excel-questions/511902-delete-rows-without-open-files.html

Upvotes: 2

Views: 1864

Answers (1)

Chaos Legion
Chaos Legion

Reputation: 2970

Create a procedure and schedule its execution according to your ITSM tool jobs, to automate this. Use OPENROWSET to read excel as follows:

SELECT *
FROM OPENROWSET(
    'Microsoft.ACE.OLEDB.12.0',
    'Excel 12.0;HDR=YES;Database=c:\kunal\test.xlsx',
    'SELECT * FROM [sheet1$A3:Z]'
);

Something like this should help you. In the above code, I am skipping first two rows, starting reading from cell A3 of the excel and HDR=YES represents that my data has titles.

Upvotes: 2

Related Questions