Prev: varbinary(max) field in sql server 2005 problems
Next: Varbinary - Word Documents
From: Michael Coles on 14 Mar 2010 00:21
Couple of questions - are you always guaranteed that the first row of the
spreadsheet will always have f1, f2, f3, ... values in them? And are you
guaranteed that the second row of the spreadsheet will always have "period
to:", Scheme1, Scheme2, etc. values in them?

Assuming the answers to the first two questions above are yes, this can be
done in pure T-SQL with a combination of BULK INSERT, FOR XML, dynamic SQL
and a couple of staging tables. But T-SQL alone may not be the *best* tool
for the job.

What tools are you allowed to use for this task? Is SSIS an option? Just
T-SQL? bcp? Also how much control do you have over the exported file
format (the .csv file)? Could you change it to a tab-delimited file format,
for instance? Or are you stuck with .csv?

--
Thanks

Michael Coles
SQL Server MVP
Author, "Expert SQL Server 2008 Encryption"
(http://www.apress.com/book/view/1430224649)
----------------

"Frank" <francis.moore(a)gmail.com> wrote in message
news:c37f3364-5e59-4452-976a-bbbeb4181ef2(a)g19g2000yqe.googlegroups.com...
> Hi,
>
> I have the data from a flattened spreadsheet in a table in the
> following form:
>
> f1 f2 f3
> period to: Scheme1 Scheme2
> 31/01/2005 Net Gross
> 28/02/2005 Net Gross
> 31/03/2005 Net Gross
> 30/04/2005 Net Gross
> 31/05/2005 Net Gross
> 30/06/2005 Net Gross
> 31/07/2005 Net Gross
> 31/08/2005 Net Gross
> 30/09/2005 Net Gross
> 31/10/2005 Net Gross
> 30/11/2005 Net Gross
> 31/12/2005 Net Gross
>
> f1, f2 and f3 are the column names, the rest is data.
> And there are 120 other columns of similar data to columns f2 and f3.
> What I want to do is join this table to another table using the scheme
> name.
> The problem that I have is that in the table above the scheme name is
> not the name of the column, it is just part of the data.
> And I have no idea up front which scheme each of the 'f' column names
> will map to i.e. whether f2 will equal Scheme1 etc.
> Is there an idiomatic way of transforming this data into a usable
> state?
>
> Many thanks,
> Frank.

From: Frank on 15 Mar 2010 05:15
Hi Michael,

Thanks for the response.
I managed to get this sorted late Friday night.
In the end, a colleague of mine showed me how to use a while loop to
traverse through the f3-f120 columns with some dynamic SQL to pull out
the values in each column. I was working along these lines before I
posted the question, but just had some doubts that this was the best
way to go.

Thanks again,
Frank.
From: Michael Coles on 15 Mar 2010 21:05
Hi Frank,

I imagine there's better ways to do it, but it's a little hard to say
without more information. I suspect pure T-SQL probably isn't the best tool
for the job in this case, but again not quite enough information to make the
call.

--
Thanks

Michael Coles
SQL Server MVP
Author, "Expert SQL Server 2008 Encryption"
(http://www.apress.com/book/view/1430224649)
----------------

"Frank" <francis.moore(a)gmail.com> wrote in message
news:3f1815b7-ee5b-4e40-8376-a15a5a7a434d(a)q23g2000yqd.googlegroups.com...
> Hi Michael,
>
> Thanks for the response.
> I managed to get this sorted late Friday night.
> In the end, a colleague of mine showed me how to use a while loop to
> traverse through the f3-f120 columns with some dynamic SQL to pull out
> the values in each column. I was working along these lines before I
> posted the question, but just had some doubts that this was the best
> way to go.
>
> Thanks again,
> Frank.

First  |  Prev  | 
Pages: 1 2
Prev: varbinary(max) field in sql server 2005 problems
Next: Varbinary - Word Documents