problem with Excel in SSIS

Question

Post reply

problem with Excel in SSIS

pat-261672

Hall of Fame

Points: 3325
More actions
December 17, 2008 at 3:56 pm

#128748

Hi all,
I am trying to get the file load from SSIS package btu the problem si i have file with first row is file and other information name!! How can i get that only row as on filed in to database. Also after 6 rows data starts. How can i import data in to SQL?
Thakns
File for ex:
row1 name
row3 date
row4 gpg
row5 t1
row6 t4
row7 tpg cgp
How can i get file data to SQL?
Thanks

Viewing 14 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

mamsay Say Hey Kid Points: 701 More actions · Answer 1

is it your source of data an excel sheet???????????

if yes means you can change in the open row set as (sheetname$)A6:MM6000

pat-261672 Hall of Fame Points: 3325 More actions · Answer 2

here is the file. Not understanding the way to get the data form this sample file. attached

steveb. SSC-Forever Points: 46733 More actions · Answer 3

pat (12/17/2008)
Hi all,
I am trying to get the file load from SSIS package btu the problem si i have file with first row is file and other information name!! How can i get that only row as on filed in to database. Also after 6 rows data starts. How can i import data in to SQL?
Thanks

I am not sure if i understand what you want to do, are you trying to import the data from row 8 and ignore the header information.. ?

pat-261672 Hall of Fame Points: 3325 More actions · Answer 4

yes. I will to the header by colum but only need certain informatoin from it. but it is hard for me to get it here is the information that i need to import from excel file:

Date : (the date shown in the 2nd row)

File name : name shown in 1st row

Day of month : Day of month

Orale : Orale

Written: Written:

Exam Score : Exam

so far that one i need but have no clue at all!!

steveb. SSC-Forever Points: 46733 More actions · Answer 5

Okay, there are a couple of ways you can deal with this..

You have merged cells in your worksheet this will lead to problems. I would write some VBA to loop through the worksheet and remove all the cell merging, then you can import the worksheet and split out the columns that have Null for the first column (day of month) these will be the header rows that you can dump.

Another way would be to write some more VBA to go through your worksheet and remove all the header rows, then import this file.

Either way it will take some prep work.

also this file looks like it was generated from a stored_proc from a database, it may be easier for you to go directly to the source of the data and import from there rather than using the spread sheet.

pat-261672 Hall of Fame Points: 3325 More actions · Answer 6

it was not generated from DB but hand written and I need to put it in to DB now!!

ps. One Orange Chip Points: 29423 More actions · Answer 7

If you're importing to db for analysis, i've some queries...

If the spreadsheet is meant to store test scores, i dont find a studentID.

Assuming each row contains details of 1 student, this implies one student will give two tests and it has to be given on a single day.

Also, to me, the field Orale seems sum of First, Second and Third field. Question is whether it contains aggregate from test1 or test2?

ALso relation between various fields will be helpful...

Pradeep Singh

pat-261672 Hall of Fame Points: 3325 More actions · Answer 8

hi,

Sorry, the column Day of month is the id for student!!

Thanks

Gosta Munktell SSCertifiable Points: 6004 More actions · Answer 9

Pat

I looked at your file. Is it just a one time shot or will you have more files of the same kind to

import?

I will as SteveB says use VBA and ADO to loop the sheet and insert the rows. I have done a lot of

things like this. But if your are not familiar with VBA and ADO in Excel this might not be the way to

do it. I can provide you with a suggestion to solution if you like!

//Gosta

pat-261672 Hall of Fame Points: 3325 More actions · Answer 10

Hi

I am not familiar with that at all. and yeah there will be more files in future as well.

Gosta Munktell SSCertifiable Points: 6004 More actions · Answer 11

Pat

I cannot see any way to import the file as it looks in your example without some kind of

"washing". And I don't think you will do that manually each time.

Try do learn (record a makro in Excel) and then you can save the file as a Tab sep text file

and use Bulk insert instead. I don't recommend to use SSIS to import native Excel files.

http://msdn.microsoft.com/en-us/library/ms188365(SQL.90).aspx

/Gosta

ps. One Orange Chip Points: 29423 More actions · Answer 12

i would suggest you to design the spreadsheet in the format of X rows and Y Columns and remove all those merged columns.

Make sure you enter the raw data in proper format. You should exclude formulas as they can be calculated once u've successfully imported the excel sheet.

for example, to begin with your excel sheet may contain columns like..

studentID, T1Parameter1, T1Parameter2, T1Parameter3... T2Parameter1, T2Parameter2, T2Parameter3 and so on....

where T1 denotes tets1 and parameter denotes the parameters on which u're conducting the test...

This is most basic table structure. Ideally you should normalize these tables, where you seperate student details with test master and test details etc....

Pradeep Singh

steveb. SSC-Forever Points: 46733 More actions · Answer 13

You will def need to do some sort of cleansing of the data first. Depending on your programming experience VBA is not that difficult to learn, start simple and build on the code. Think of it as good oppurtunity to learn some new skill and boost your CV.

Also there is plenty of help available on-line if needed