Ugly Excel Import clean up... - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Ugly Excel Import clean up...

Author

Topic

murrayb3024
Yak Posting Veteran

79 Posts

Posted - 2013-04-15 : 13:51:51

One of our vendors gives up an excel report that is great for reading but not when it comes to importing and using the data.

I finally have it importing into a table with all of the data. Here is where I am having a problem. I have a load table. It has about 20 columns in it. This is part of it:

Column1 Column2
Location: 1 - LocationName
100 21
102 33
202 99
Location: 17 - LocationName
220 6
298 88
444 11
Location: 19 - LocationName
776 66
889 88

The 3 digit codes on the left are employees and they are associated with the location that they are under and I need to get that on each line. What I would like is for it to look something like this:

Col1 Col2 Col3
100 21 1
102 33 1
202 99 1
220 6 17
298 88 17
444 11 17
776 66 19
889 88 19

If only the vendor would just give us a flat file of raw data this would not be an issue, but that is not the case.

Thoughts?

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-04-15 : 14:40:55

>>Thoughts?

1. first I would ask if the vendor can provide the data in a format that you specify. They should be able to easily.

2. If you're more comfortable in Excel then t-sql then use Excel functions to manipulate the data into a form you can directly import. Often, rather than import, I will create a function that resolves to an INSERT VALUES statement. Copy that down all your rows and just copy the results into a query window.

3. if you have to use only t-sql then you'll need to add a sequence column in the excel data so that you'll have a way to associate rows without a location to the location in the last row that a location was specified.

Be One with the Optimizer
TG

murrayb3024
Yak Posting Veteran

79 Posts

Posted - 2013-04-15 : 15:12:39

We are setting up a meeting with the vendor, but from past experience I think they are going to try to bill us a high amount to do this. This vendor is my nemesis.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources