Load multi tab Excel data into SQL Server table using SSIS
-
I have a requirement that I need to load an Excel file with multiple tabs into the SQL Server table. The Excel file contains multiple tabs with each month of data in it. I should treat the current month tab like a daily month file and import the full tab each day as a truncate and reload.
Truncate and reload raw tables for current tab and last month tab.
How do we do dynamic tab variables for identifying ingestion in SSIS?
FYI.. TABS in the Excel file are named Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec.
These tabs are named at random - not in a sequence..
Can anyone please guide me?
FYI..I wanted to load just 2 months data at the same time not all months. For Instance,
This is August so i would like to load July and august data every day. I truncate the table and reload every day.
When i am in September then i should load Sep and Aug data.Appreciate your response.
Here is the example how tabs are arranged.
Thank You,
Swathi - swathi9.chinni - Monday, August 7, 2017 1:23 PM
I have a requirement that I need to load an Excel file with multiple tabs into the SQL Server table. The Excel file contains multiple tabs with each month of data in it. I should treat the current month tab like a daily month file and import the full tab each day as a truncate and reload.
Truncate and reload raw tables for current tab and last month tab.
How do we do dynamic tab variables for identifying ingestion in SSIS?
FYI.. TABS in the Excel file are named Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec.
These tabs are named at random - not in a sequence..
Can anyone please guide me?
FYI..I wanted to load just 2 months data at the same time not all months. For Instance,
This is August so i would like to load July and august data every day. I truncate the table and reload every day.
When i am in September then i should load Sep and Aug data.Appreciate your response.
Here is the example how tabs are arranged.Thank You,
SwathiIf the tab names are 'random and in no sequence', how would you ever expect code to be able to detect 'just 2 months of data'?
The absence of evidence is not evidence of absence.
Martin ReesYou can lead a horse to water, but a pencil must be lead.
Stan Laurel -
Is there a way that we can write a code on sheet numbers...But the sequence is always going to be this.there is no change in the tab names.
Jan, Feb, Mar, Apr, May, Jun, Aug, Jul,Sep, Oct, Nov, Dec.I am not sure if there is a way to do this requirement.
Thank you for the response. - swathi9.chinni - Monday, August 7, 2017 1:46 PM
So you mean that there are always 12 sheets, in Jan–Dec order? That would imply rather strongly that there is a sequence 🙂
It can be done, I think – at least I know how to iterate around the sheets in C#, but I am not 100% sure that the order is the same as the display order.
Are you happy writing some C# to give this a go? I can provide enough to get you started.The absence of evidence is not evidence of absence.
Martin ReesYou can lead a horse to water, but a pencil must be lead.
Stan Laurel -
You are correct there will always be 12 sheets but not in order jan-Dec... If you look at my post.. the order is off near july and Aug...
Here is the order .Jan, Feb, Mar, Apr, May, Jun, Aug, Jul,Sep, Oct, Nov, Dec.
I am poor at C# but i can understand. This will be good time to learn something new .
If can provide me the code that could be much helpful. I will try to add your code to my script task in ssis and see how it works.Appreciate your help!.
- swathi9.chinni - Monday, August 7, 2017 2:12 PM
Jan, Feb, Mar, Apr, May, Jun, Aug, Jul,Sep, Oct, Nov, Dec.
is still a sequence, albeit a rather odd one.This C# goes a little beyond beginner level, but see how you get on. This routine is untested and will need to be merged into your code.
private void SomeName()
{
string connectionString = @"Provider = Microsoft.ACE.OLEDB.16.0; Data Source = c:\temp\someFile.xlsx;Extended Properties=""Excel 12.0 XML; HDR = No; IMEX = 1\""";using (OleDbConnection connection = new OleDbConnection(connectionString))
{
connection.Open();
DataTable dt;
dt = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);foreach (DataRow row in dt.Rows)
{
MessageBox.Show(row["TABLE_NAME"].ToString());
}
}
}
You'll need to modify the connection string to use whatever ACE driver you want (also to point at the file path you are interested).
You'll need to add
using System.Data.OleDb;
into your Using section.
This code should run through your sheets and display a message box for each sheet it finds.The absence of evidence is not evidence of absence.
Martin ReesYou can lead a horse to water, but a pencil must be lead.
Stan Laurel -
Additionally here are a few sites that have helped me out trying to accomplish something similiar:
https://danajaatcse.wordpress.com/2010/05/05/extracting-data-from-multiple-sheets-in-an-excel-file-in-ssis/
https://www.mssqltips.com/sqlservertip/4157/how-to-read-data-from-multiple-excel-worksheets-with-sql-server-integration-services/Hope this helps!
-
Just chiming in here, but want to understand something. In your original post you mention you want the load "this month and last month's". So, for the current month that would be July and August (according to you post). You then mention that next month would be September and August (which makes sense.). I'm not sure, therefore, why worksheet ordering is an issue is the names of the sheets are known. Even if (for some bizarre reason) the sheets are in Descending order, with July & August switched, if you always loading the current and previous month's data according to a calendar year, then the order doesn't matter.
Am I missing something?
If not, Phil, I don't think having to loop through the sheets would even be needed. You can query Excel sheets using SQL statements in the Source Components. If you know the format of the sheet, then you could easily set up an variable with an Expression for the 2 different months.
So, speaking hypothetically, let's say you are running the process today, and your work sheet has data in columns A - AK, with the header rows on Row 2. For the July sheet, your "SQL" to get that data would be:
SELECT *
FROM [Jul$A2:AK];
As a quick explanation, the table name is split into two parts, either side of the $ sign. The left is the name of the work sheet, and the right is the Cell references. I don't declare a row for AK, as that could be unknown, thus it is go to the bottom row in the worksheet that has been used (this would include rows that have had data and then had it removed). Depending on your file, you might therefore retrieve a lot of NULLs, so you may want to add a WHERE clause in your above SQL statement.Now, to get that query from an expression is quite easy as well (although, unless I'm mistaken, SSIS doesn't have CASE Statements). Firstly, an expression to get your previous month's name would look like this:
DATEPART( "M", GETDATE() ) == 1 ? "Dec" :
DATEPART( "M", GETDATE() ) == 2 ? "Jan" :
DATEPART( "M", GETDATE() ) == 3 ? "Feb" :
DATEPART( "M", GETDATE() ) == 4 ? "Mar" :
DATEPART( "M", GETDATE() ) == 5 ? "Apr" :
DATEPART( "M", GETDATE() ) == 6 ? "May" :
DATEPART( "M", GETDATE() ) == 7 ? "Jun" :
DATEPART( "M", GETDATE() ) == 8 ? "Jul" :
DATEPART( "M", GETDATE() ) == 9 ? "Aug" :
DATEPART( "M", GETDATE() ) == 10 ? "Sep" :
DATEPART( "M", GETDATE() ) == 11 ? "Oct" : "Nov"But you need to get that as a SQL statement, so:
"SELECT *
FROM [" +
(DATEPART( "M", GETDATE() ) == 1 ? "Dec" :
DATEPART( "M", GETDATE() ) == 2 ? "Jan" :
DATEPART( "M", GETDATE() ) == 3 ? "Feb" :
DATEPART( "M", GETDATE() ) == 4 ? "Mar" :
DATEPART( "M", GETDATE() ) == 5 ? "Apr" :
DATEPART( "M", GETDATE() ) == 6 ? "May" :
DATEPART( "M", GETDATE() ) == 7 ? "Jun" :
DATEPART( "M", GETDATE() ) == 8 ? "Jul" :
DATEPART( "M", GETDATE() ) == 9 ? "Aug" :
DATEPART( "M", GETDATE() ) == 10 ? "Sep" :
DATEPART( "M", GETDATE() ) == 11 ? "Oct" : "Nov") +
"$A2:AK]"
You can then, in your Excel Source, set your Data access mode to "SQL command from variable", and select your variable you just created (with the above expression). That expression, should, also, give you the answer on how to create one for the current month.Hope that helps, or I've totally derailed on the OP's initial intent. :hehe:
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk -
Hi Thom, in what follows, I made my best guess about what the OP wants, based on responses to my questions. I could easily have jumped to the wrong conclusions.
This is the sentence from the OP which sent me down the coding avenue:
These tabs are named at random - not in a sequence..
So despite the apparent user-friendliness of the tab names in the example, I assumed that sometimes we have Dec, Dcmbr, December, Dec. ... whatever.
I then asked about the 'not in a sequence' bit. The answer to that suggests that the tabs are always in the same (twisted) sequence:
Jan, Feb, Mar, Apr, May, Jun, Aug, Jul,Sep, Oct, Nov, Dec.So we have a sequence of 12 tabs, with varying names, but always in the above order
1 Jan
2 Feb
3 Mar
4 Apr
5 May
6 Jun
7 Aug
8 Jul
9 Sep
10 Oct
11 Nov
12 Dec
If this is the case, the logic required is to determine which numbers in the sequence are required (eg, (6,8) for June and July) and then to iterate round the collection, in order, to get the 'random' tab names in those positions.The absence of evidence is not evidence of absence.
Martin ReesYou can lead a horse to water, but a pencil must be lead.
Stan Laurel -
Yeah, see you're point. I focused on the statements:
The Excel file contains multiple tabs with each month of data in it. I should treat the current month tab like a daily month file and import the full tab each day as a truncate and reload.
...
This is August so i would like to load July and august data every day. I truncate the table and reload every day.
When i am in September then i should load Sep and Aug data.A little conflicting to what is explained later. Hopefully the OP can use something either of us have posted to get where they want. 🙂
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply