Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Importing improperly formatted excel file Expand / Collapse
Author
Message
Posted Thursday, May 1, 2014 10:17 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, June 27, 2014 2:29 PM
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?


  Post Attachments 
Untitled.jpg (11 views, 135.39 KB)
Post #1566768
Posted Thursday, May 1, 2014 11:12 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 9:09 AM
Points: 10,342, Visits: 13,352
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

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
Post #1566798
Posted Thursday, May 1, 2014 11:18 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, June 27, 2014 2:29 PM
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.
Post #1566800
Posted Thursday, May 1, 2014 11:26 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 3:41 PM
Points: 6,842, Visits: 13,369
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
Post #1566803
Posted Tuesday, May 20, 2014 5:30 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: 2 days ago @ 6:40 PM
Points: 421, Visits: 1,000
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).
Post #1572956
Posted Tuesday, May 20, 2014 5:51 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, June 27, 2014 2:29 PM
Points: 13, Visits: 52
actually I finished this task with SSIS. it was really nasty and painful.

Post #1572961
Posted Tuesday, May 20, 2014 5:57 PM This worked for the OP Answer marked as solution
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, June 27, 2014 2:29 PM
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.
Post #1572963
Posted Tuesday, May 20, 2014 10:07 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: 2 days ago @ 6:40 PM
Points: 421, Visits: 1,000
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.
Post #1572990
Posted Tuesday, May 20, 2014 10:18 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, June 27, 2014 2:29 PM
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.
Post #1572992
Posted Wednesday, May 21, 2014 7:11 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 8:51 PM
Points: 35,606, Visits: 32,190
@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. )


--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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1573391
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse