SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Importing improperly formatted excel file


Importing improperly formatted excel file

Author
Message
watchgeek
watchgeek
SSC Rookie
SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)

Group: General Forum Members
Points: 35 Visits: 52
business people sent me an excel file.
looks like the picture. this is not very well data friendly. there is no column name.

They want to import data from this excel sheet to sql server using ssis.
in SQL server, a table will be created with two columns, Code and Name.

it will be like this in sql server.
Code Name
10 Energy
1010 Energy
101010 Energy Equipment & Services
101020 Oil,Gas & Consumable Fuels
10101010 Oils & gas Drilling
10101020 Oil & Gas Equipments & services

I do not keep the description.
Basically, we want to keep the numeric code and name only. Numeric codes are in column a,c,e,g. names are in column b,d,f,h

is this possible?
Attachments
Untitled.jpg (17 views, 135.00 KB)
Jack Corbett
  Jack Corbett
SSC-Forever
SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)

Group: General Forum Members
Points: 42091 Visits: 14925
Is this a one time process or something that will reoccur? If it is a one time process the business will probably be better off entering the data by hand than trying to write code for it. If it is recurring process I'd probably use a script component in the data flow to grab the data I needed.



Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
watchgeek
watchgeek
SSC Rookie
SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)

Group: General Forum Members
Points: 35 Visits: 52
it is reoccurring. thank you for the reply. there are about 350 rows in the excel file. I don't know if I can pick data by column with script component.
LutzM
LutzM
SSC-Insane
SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)

Group: General Forum Members
Points: 22357 Visits: 13559
One-timer: Adda filter to the EXCEL section, filter out empty rows (for every 2nd column) and copy the relevant cells (CTRL +C, CTRL +V).
Permanent Process: Copy the table to a SQL Server staging table. Either UNION ALL or CROSS APPLY to get the two columns you need and insert it into the target table.
If you want to keep the description (e.g. in cell H7) you could use a self join with an offset of 1 (assuming the staging table has an identity column and you don't use parallel processing for the insert).



Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
MMartin1
MMartin1
SSCertifiable
SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)

Group: General Forum Members
Points: 6825 Visits: 2033
I know this is a SQL forum, but for crazy Excel imports, I've found creating macros in VBA to be very helpful. One can move the data to a new sheet and in the format that you wish. You could start by recording a macro as you perform this manually, and looking at the VBA code generated and try hacking it from there (on a test workbook).

----------------------------------------------------
How to post forum questions to get the best help
watchgeek
watchgeek
SSC Rookie
SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)

Group: General Forum Members
Points: 35 Visits: 52
actually I finished this task with SSIS. it was really nasty and painful.
watchgeek
watchgeek
SSC Rookie
SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)

Group: General Forum Members
Points: 35 Visits: 52
I finished this task with ssis.

I used sql command to import from excel.
select f1,f2 from [sheet1$] where f1 like '[0-9]%'

and do it for other columns.

put them in 2 columns with Union All

get the date with VB and array.
MMartin1
MMartin1
SSCertifiable
SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)

Group: General Forum Members
Points: 6825 Visits: 2033
SSIS is a great tool.Once you connect to your data source within a dataflow task you can create a derived column. Here there are built in string and other operators for which you can pull out a date and re arrange it into a format that a datetime field (as an ole db destination) will recognize. You won't need to pull out a script task every time.

----------------------------------------------------
How to post forum questions to get the best help
watchgeek
watchgeek
SSC Rookie
SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)

Group: General Forum Members
Points: 35 Visits: 52
i could not find other operators for which i can pull out a date from a cell and text. if you know, let me know.
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (203K reputation)SSC Guru (203K reputation)SSC Guru (203K reputation)SSC Guru (203K reputation)SSC Guru (203K reputation)SSC Guru (203K reputation)SSC Guru (203K reputation)SSC Guru (203K reputation)

Group: General Forum Members
Points: 203712 Visits: 41949
@watchgeek,

I see that you've already pounded out a solution. It does't appear that this spreadsheet has any proprietary or private information in it. Would it be possible for you to attach it to a post so I can download it and play with it? I'd like to give a non-SSIS solution a go on it just for fun (heh... yeah, I know... I've gotta get a life. :-P)

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search