Data load problem.

Question

Data load problem.

s-sql

SSCommitted

Points: 1970
More actions
April 23, 2008 at 12:53 pm

#380142

Data load from excel file into the table. I am using SQL 2005.
I have my physical excel file with data in the following format.
=================Excel File having the data============
Products
=================================================
FeatureFeatures A1 A2 A3 B1 B2 B3........Z10
_ID
==================================================
1 DisplayPanelFlat LCD CRT LCD Plasma CRT............
2 PowerVoltage120 120 140V120V 240V 120V
3 ScrResolution720 500 N/A1080 1080 720
4 Warranty1 1 1 2 3 4...........
=================================================
The list of products run into thousands. The file is horizontally long.
I need to load this data into my DB table. My final desired table structure is
======================================
Feature_ID | ProductID | FeatureValue.
=======================================
1 A1 Flat
2 A1 120
3 A1 720
4 A1 1
1 A2 LCD
2 A2 120
3 A2 500
4 A2 1
======================================
The intial approach I thought was to load the file directly into a temporary table, which would be a direct replica of excel file. The I thought of applying the unpivot operator.
But that approach does not work, because of the large number of columns.
Appreciate help on this, and suggest how can I achive this.
Thanks

Viewing 6 posts - 1 through 5 (of 5 total)

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

GSquared SSC Guru Points: 260824 More actions · Answer 1

At first glance, I'd import into a "staging table" (a table that matches the spreadsheet). Then I'd build a final table that has the structure I really want. Then I'd step through, column by column, and insert into the final table.

Something like:

insert into dbo.FinalTable (FeatureID, ProductID, Value)

select FeatureID, ProductID, A1

from dbo.StagingTable

insert into dbo.FinalTable (FeatureID, ProductID, Value)

select FeatureID, ProductID, A2

from dbo.StagingTable

and so on through the columns.

If it's a ton of columns (it looks like it is), you could build the insert statement into a dynamic command, and then use sys.columns to step through them with a cursor.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

RBarryYoung SSC Guru Points: 143329 More actions · Answer 2

In excel, copy your data, then in a new sheet, use Paste, Transpose to rotate your data.

From there, you should be able to get it into SQL Server and use unpivot.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

bledu SSChampion Points: 12335 More actions · Answer 3

If it's a ton of columns (it looks like it is), you could build the insert statement into a dynamic command, and then use sys.columns to step through them with a cursor.

Just keep in mind that there is a limit of 1024 columns in your base table

Everything you can imagine is real.

bledu SSChampion Points: 12335 More actions · Answer 4

you can create a .net assembly which reads your excel spreadsheet.

Everything you can imagine is real.

s-sql SSCommitted Points: 1970 More actions · Answer 5

The excel file is having tons of columns and I have multiple tabs.

Can I get more details on how to use DotNet assemblies to read this kind of excel file.