Fetching Data from Microsoft Excel using SQL.

Question

0.00/5 (No votes)

See more:

Can I read data from Opened Excel file where we dont know the end cell. if it change day by day. in that case is there a way to read all records with out giving end cell. Please sugget.

ex: Today I have 300 records in the file and tomorrow it may be 1000 or 200.

Posted 4-Nov-15 2:45am

Member 12113293

Add a Solution

Comments

Suvendu Shekhar Giri 4-Nov-15 9:55am

By saying "SQL" do you refer to SQL Server/MySQL/Oracle or what?

Member 12113293 5-Nov-15 1:25am

SQL Server

Kuthuparakkal 10-Nov-15 8:56am

Read Excel with what ? C# , SSIS , etc ?

Member 12113293 12-Nov-15 1:00am

SSIS

Kuthuparakkal 12-Nov-15 8:35am

Okay. End of Cell means row or columns ? Are the number of columns fixed ?

Member 12113293 13-Nov-15 1:01am

Number of Columns are fixed. End cell means rows not fixed.
ex: Today my excel sheet would have 1000 rows tomorrow it may be more than 1000 or less. In this scenario how can I read the data? Please help..

Kuthuparakkal 13-Nov-15 6:13am

This is the normal operation, Excel is read until the last row every time, you do not need any specific logic. Just go ahead with SSIS package creation.
A conditional Split can be inserted to spit our blank rows - this may occur when the contents of last few rows are emptied.
If you are looking for more sophisticated automation then look at my article: http://www.codeproject.com/Articles/368530/Dynamic-Excel-file-loading-with-SSIS

Member 12113293 13-Nov-15 9:57am

Thanks for the reply. But my excel will have Logo and Created time some unnecessary data. from 19 row I need to pick the data. 19th row is having column headers.

Kuthuparakkal 13-Nov-15 13:34pm

You could use SQL to fetch the data starting from 19th Position. Create Excel File Connection, Excel Data Source and use SQL like below and put it in destination table like any other SSIS package.
SELECT [YourColumnName1], [YourColumnName2], ....
FROM [ExcelSheetName$A19:IV]

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

GabrieleTronchin · Answer 1 · 2016-02-15T22:50:00

Solution 1

You can use SQL Linked Server to read the excel files. These links should help you:

https://support.microsoft.com/en-us/kb/306397[^]

Using a SQL Server Linked Server to Query Excel Files[^]

Posted 15-Feb-16 22:50pm

GabrieleTronchin