Click here to monitor SSC
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
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 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 (11 views, 135.00 KB)
Jack Corbett
  Jack Corbett
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11020 Visits: 14858
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
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 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
SSCertifiable
SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)

Group: General Forum Members
Points: 7001 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
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1108 Visits: 2015
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
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

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

Group: General Forum Members
Points: 13 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
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1108 Visits: 2015
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
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 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-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45131 Visits: 39923
@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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

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