March 16, 2009 at 4:29 am
Hi!
I have an Excel file and want to transfer the data to SQL Server. The problem is that the columns has different types of data in the same columns. To demonstrate an example:
Col 1 Col 2 Col 3 Col 4 Col 5
Row 1 name: Brian
Row 2 age: 27
Row 3
Row 4 month: weight: height: Income:
Row 5 1 90,5 190 2000
Row 6 2 90 190 2050
Row 7 3 89 190 2050
Row 8
Row 9
Row 10 name: Dick
Row 11 age: 22
Row 12
Row 13 month: weight: height: Income:
Row 14 1 80 180 2100
Row 15 2 81 180 2120
Row 16 3 83 181 2120
Row 17 ... ... ... ...
How can I do this in SSIS? As a first step I need to be able to do this manually but later it needs to be automized. Grateful for all the help I can get.
With regards
Marcus
March 16, 2009 at 7:21 am
Yuk, that is a horrendous requirement. Is there any way that you can tidy up the source data, rather than having to code around it - I am almost certain you will have to code this.
Where are you planning on putting the data? Two separate tables (Person and Stats, or whatever)?
I guess that you want to ignore the blank rows & throw away the headings?
I would be tempted to import the whole thing into a staging table - all fields as VARCHAR(32) (or something big enough to accommodate the largest anticipated source data) and then run a stored proc to
a) Delete the rows not required
b) Move the remaining rows to the required destinations
You should be able to refine your package so that rows which are not required are not even imported ... rendering (a) superfluous.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
March 16, 2009 at 8:07 am
Hi Phil and thanks for your answer.
Actually the real data looks a little bit different and is already sent out as excel-file to about 50 big companies so changing it now is not possible. Do you know if there is a way that I can copy individual rows or cells into some temporary file/cache/etc so that I can separate the information?
Phil Parkin (3/16/2009)
Yuk, that is a horrendous requirement. Is there any way that you can tidy up the source data, rather than having to code around it - I am almost certain you will have to code this.Where are you planning on putting the data? Two separate tables (Person and Stats, or whatever)?
I guess that you want to ignore the blank rows & throw away the headings?
I would be tempted to import the whole thing into a staging table - all fields as VARCHAR(32) (or something big enough to accommodate the largest anticipated source data) and then run a stored proc to
a) Delete the rows not required
b) Move the remaining rows to the required destinations
You should be able to refine your package so that rows which are not required are not even imported ... rendering (a) superfluous.
March 16, 2009 at 8:48 am
As I suggested, you could create a new 'staging' table in your SQL Server database - just a table of text fields wide enough to accommodate your data - and import into that.
From there, you are free to run whatever SQL you need to get rid of the data you do not want and INSERT/UPDATE that which you do.
So your package would do something similar to the following:
1) Truncate staging table (don't want to import the same data twice)
2) Import data from Excel to staging table
3) Run SQL / stored proc to perform INSERTs and UPDATEs as required.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
March 17, 2009 at 2:57 am
Marcus
I agree with Phil. What I do prefer in a matter like this is to save the Excelfile
as a tab separated text file and use bulkinsert.
But looking at your data I would use VBA in Excel with ADO SQL etc.
I can do it for you but it takes to much effort to show the code in this thread
and ofcourse you must be open to use whats inside Excel.
Send me an E-mail if you feel like it.
/Gosta
In Uppsala Sweden
March 17, 2009 at 9:58 am
In Excel, I would write a VBA script that does the following:
In Col6 Row5, '=IF(A1="Name:",B1,IF(ISNUMBER(A5),E4,""))'
to place the name in Col6 then
In Col7 Row5, '=IF(A2="age:",B2,IF(ISNUMBER(A5),F4,""))'
to place the age in Col7.
copy both fields down the whole length of the sheet. Then you can save it to a tab delimited file and import, then delete all cells with blanks in the name or age columns, or you can delete the rows with blanks in the name or age columns in excel, then save it as tab delimited then import, which ever you are more comfortable with.
Of course, save the script and create an add-in from it, with a button on a tool-bar or menu option, so that you can load any version of the sheet, and just run the script, close and go.
Sometimes I even use MS Access to do both. I will connect to the Excel object and run the script, save the file, then import it into access, then with an ODBC connection to the SQL Table, Append the imported data right into SQL server.
Lots of options
Dan
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply