load csv into SQL Server 2005

I have been pulling my hair out all afternoon trying to figure out an easy
way to import csv files into SQL Server 2005. They are perfmon logs in csv
format with TONS of columns, so creating the table and columns beforehand is
not practical. Isn't there some very easy, straightfoward way to simply
create a table and its columns based up on a csv file import?
thanks in advance,
BillIn SQL Server Managemetn Studio, right click on the database name and
choose Tasks... Import Data. This opens the Import Export Wizard.
Specify a Flat File data source. Give it your .csv file name. If
your csv file has column names in the first line, check that box. Use
Preview to see how it looks, and make whatever adjustments it takes to
make it look right. Keep following the steps, and you should end up
with a new table in your database. It will probably not be exactly
what you want!!! Data types will be a bit screwy, names will probably
not be formatted correctly.
Now script that table so you have a CREATE TABLE command. Edit it
until the names and types are right and make the new table. Then
repeat the process above, but this time in the panel labelled Select
Source and Table Views change the Desitnation to the new table you
created. Take through the rest of the steps and the data should load.
I have simplified things a bit - there are far too many details to go
into here - but the approach should get you where you want to go.
Good luck!
Roy Harvey
Beacon Falls, CT
On Tue, 2 May 2006 16:49:10 -0400, "bu" <bu@.nospam.com> wrote:

>I have been pulling my hair out all afternoon trying to figure out an easy
>way to import csv files into SQL Server 2005. They are perfmon logs in csv
>format with TONS of columns, so creating the table and columns beforehand i
s
>not practical. Isn't there some very easy, straightfoward way to simply
>create a table and its columns based up on a csv file import?
>thanks in advance,
>Bill
>|||Take a look at the Windows utility relog.exe
Linchi
"bu" wrote:

> I have been pulling my hair out all afternoon trying to figure out an easy
> way to import csv files into SQL Server 2005. They are perfmon logs in cs
v
> format with TONS of columns, so creating the table and columns beforehand
is
> not practical. Isn't there some very easy, straightfoward way to simply
> create a table and its columns based up on a csv file import?
> thanks in advance,
> Bill
>
>|||Do you know if that same procedure applies in SQL Server 2005 Express as
well as Standard and the others?
thanks again!
Bill
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:06jf529da1ocliljc48nhkihkli7mm5j99@.
4ax.com...[vbcol=seagreen]
> In SQL Server Managemetn Studio, right click on the database name and
> choose Tasks... Import Data. This opens the Import Export Wizard.
> Specify a Flat File data source. Give it your .csv file name. If
> your csv file has column names in the first line, check that box. Use
> Preview to see how it looks, and make whatever adjustments it takes to
> make it look right. Keep following the steps, and you should end up
> with a new table in your database. It will probably not be exactly
> what you want!!! Data types will be a bit screwy, names will probably
> not be formatted correctly.
> Now script that table so you have a CREATE TABLE command. Edit it
> until the names and types are right and make the new table. Then
> repeat the process above, but this time in the panel labelled Select
> Source and Table Views change the Desitnation to the new table you
> created. Take through the rest of the steps and the data should load.
> I have simplified things a bit - there are far too many details to go
> into here - but the approach should get you where you want to go.
> Good luck!
> Roy Harvey
> Beacon Falls, CT
>
> On Tue, 2 May 2006 16:49:10 -0400, "bu" <bu@.nospam.com> wrote:
>
easy[vbcol=seagreen]
csv[vbcol=seagreen]
is[vbcol=seagreen]|||I do not believe it applies to Express, as I do not think Express
comes with SSIS. SSIS is what the Wizard builds.
Roy
On Tue, 2 May 2006 21:26:51 -0400, "me" <me@.nospam.com> wrote:

>Do you know if that same procedure applies in SQL Server 2005 Express as
>well as Standard and the others?
>thanks again!
>Bill
>
>"Roy Harvey" <roy_harvey@.snet.net> wrote in message
> news:06jf529da1ocliljc48nhkihkli7mm5j99@.
4ax.com...
>easy
>csv
>is
>

load csv into SQL Server 2005

I have been pulling my hair out all afternoon trying to figure out an easy
way to import csv files into SQL Server 2005. They are perfmon logs in csv
format with TONS of columns, so creating the table and columns beforehand is
not practical. Isn't there some very easy, straightfoward way to simply
create a table and its columns based up on a csv file import?
thanks in advance,
BillIn SQL Server Managemetn Studio, right click on the database name and
choose Tasks... Import Data. This opens the Import Export Wizard.
Specify a Flat File data source. Give it your .csv file name. If
your csv file has column names in the first line, check that box. Use
Preview to see how it looks, and make whatever adjustments it takes to
make it look right. Keep following the steps, and you should end up
with a new table in your database. It will probably not be exactly
what you want!!! Data types will be a bit screwy, names will probably
not be formatted correctly.
Now script that table so you have a CREATE TABLE command. Edit it
until the names and types are right and make the new table. Then
repeat the process above, but this time in the panel labelled Select
Source and Table Views change the Desitnation to the new table you
created. Take through the rest of the steps and the data should load.
I have simplified things a bit - there are far too many details to go
into here - but the approach should get you where you want to go.
Good luck!
Roy Harvey
Beacon Falls, CT
On Tue, 2 May 2006 16:49:10 -0400, "bu" <bu@.nospam.com> wrote:
>I have been pulling my hair out all afternoon trying to figure out an easy
>way to import csv files into SQL Server 2005. They are perfmon logs in csv
>format with TONS of columns, so creating the table and columns beforehand is
>not practical. Isn't there some very easy, straightfoward way to simply
>create a table and its columns based up on a csv file import?
>thanks in advance,
>Bill
>|||Take a look at the Windows utility relog.exe
Linchi
"bu" wrote:
> I have been pulling my hair out all afternoon trying to figure out an easy
> way to import csv files into SQL Server 2005. They are perfmon logs in csv
> format with TONS of columns, so creating the table and columns beforehand is
> not practical. Isn't there some very easy, straightfoward way to simply
> create a table and its columns based up on a csv file import?
> thanks in advance,
> Bill
>
>|||Do you know if that same procedure applies in SQL Server 2005 Express as
well as Standard and the others?
thanks again!
Bill
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:06jf529da1ocliljc48nhkihkli7mm5j99@.4ax.com...
> In SQL Server Managemetn Studio, right click on the database name and
> choose Tasks... Import Data. This opens the Import Export Wizard.
> Specify a Flat File data source. Give it your .csv file name. If
> your csv file has column names in the first line, check that box. Use
> Preview to see how it looks, and make whatever adjustments it takes to
> make it look right. Keep following the steps, and you should end up
> with a new table in your database. It will probably not be exactly
> what you want!!! Data types will be a bit screwy, names will probably
> not be formatted correctly.
> Now script that table so you have a CREATE TABLE command. Edit it
> until the names and types are right and make the new table. Then
> repeat the process above, but this time in the panel labelled Select
> Source and Table Views change the Desitnation to the new table you
> created. Take through the rest of the steps and the data should load.
> I have simplified things a bit - there are far too many details to go
> into here - but the approach should get you where you want to go.
> Good luck!
> Roy Harvey
> Beacon Falls, CT
>
> On Tue, 2 May 2006 16:49:10 -0400, "bu" <bu@.nospam.com> wrote:
> >I have been pulling my hair out all afternoon trying to figure out an
easy
> >way to import csv files into SQL Server 2005. They are perfmon logs in
csv
> >format with TONS of columns, so creating the table and columns beforehand
is
> >not practical. Isn't there some very easy, straightfoward way to simply
> >create a table and its columns based up on a csv file import?
> >
> >thanks in advance,
> >Bill
> >|||I do not believe it applies to Express, as I do not think Express
comes with SSIS. SSIS is what the Wizard builds.
Roy
On Tue, 2 May 2006 21:26:51 -0400, "me" <me@.nospam.com> wrote:
>Do you know if that same procedure applies in SQL Server 2005 Express as
>well as Standard and the others?
>thanks again!
>Bill
>
>"Roy Harvey" <roy_harvey@.snet.net> wrote in message
>news:06jf529da1ocliljc48nhkihkli7mm5j99@.4ax.com...
>> In SQL Server Managemetn Studio, right click on the database name and
>> choose Tasks... Import Data. This opens the Import Export Wizard.
>> Specify a Flat File data source. Give it your .csv file name. If
>> your csv file has column names in the first line, check that box. Use
>> Preview to see how it looks, and make whatever adjustments it takes to
>> make it look right. Keep following the steps, and you should end up
>> with a new table in your database. It will probably not be exactly
>> what you want!!! Data types will be a bit screwy, names will probably
>> not be formatted correctly.
>> Now script that table so you have a CREATE TABLE command. Edit it
>> until the names and types are right and make the new table. Then
>> repeat the process above, but this time in the panel labelled Select
>> Source and Table Views change the Desitnation to the new table you
>> created. Take through the rest of the steps and the data should load.
>> I have simplified things a bit - there are far too many details to go
>> into here - but the approach should get you where you want to go.
>> Good luck!
>> Roy Harvey
>> Beacon Falls, CT
>>
>> On Tue, 2 May 2006 16:49:10 -0400, "bu" <bu@.nospam.com> wrote:
>> >I have been pulling my hair out all afternoon trying to figure out an
>easy
>> >way to import csv files into SQL Server 2005. They are perfmon logs in
>csv
>> >format with TONS of columns, so creating the table and columns beforehand
>is
>> >not practical. Isn't there some very easy, straightfoward way to simply
>> >create a table and its columns based up on a csv file import?
>> >
>> >thanks in advance,
>> >Bill
>> >
>

Listing foreign keys

Hi all,
I've got a couple of queries that I can't figure out exactly how to build
them. Both go over the 'sys' tables.
All my tables have the primarykey called 'id_no'. So if I have a table
'customers', the identity field is called 'id_no'. If I have a second table
'orders' with a foreign key on 'customers', the field is called
'id_no_customer'...an so on...
I need 2 queries:
1. The first should return a list of those columns (including source and
target tables) that are defined as foreignkeys (ie. columnname starts with
'id_no_') but no foreignkey actually exacts.
2. The second query should return a list of foreignkeys with the following
information:
target_table
target_column
source_table
source_column
I've tried the following, but on a table of 3 foreignkeys (1 is actually
missing), I get a result of 50, instead of just 3.
select target_table.name, target_column.name, source_table.name,
source_column.name from sysforeignkeys
inner join sysobjects target_table on sysforeignkeys.fkeyid =
target_table.id
inner join syscolumns target_column on sysforeignkeys.fkeyid =
target_column.id
inner join sysobjects source_table on sysforeignkeys.rkeyid =
source_table.id
inner join syscolumns source_column on sysforeignkeys.fkeyid =
source_column.id
where target_table.name = 'xxx'
Thanks for help!
IvanIvan
If I remember well , OJ had written this script
create procedure usp_findreferences
@.tbname sysname=null
as
set nocount on
Print 'Referenced:'
select c1.table_name,
c1.column_name,
fkey=r.constraint_name,
referenced_parent_table=c2.table_name,
c2.column_name
from information_schema.constraint_column_usage c1 join
information_schema.referential_constraints r on
c1.constraint_name=r.constraint_name
join information_schema.constraint_column_usage c2 on
r.unique_constraint_name=c2.constraint_name
where c1.table_name=coalesce(@.tbname,c1.table_name)
order by case when @.tbname is null then c1.table_name else c2.table_name end
print ''
print 'Referencing:'
select c1.table_name,
c1.column_name,
fkey=r.constraint_name,
referencing_child_table=c2.table_name,
c2.column_name
from information_schema.constraint_column_usage c1 join
information_schema.referential_constraints r on
c1.constraint_name=r.unique_constraint_name
join information_schema.constraint_column_usage c2 on
r.constraint_name=c2.constraint_name
where c1.table_name=coalesce(@.tbname,c1.table_name)
order by case when @.tbname is null then c1.table_name else c2.table_name end
go
--test run
exec usp_findreferences 'Orders'
drop proc usp_findreferences
"Ivan Debono" <ivanmdeb@.hotmail.com> wrote in message
news:eWrQJySuFHA.3684@.TK2MSFTNGP09.phx.gbl...
> Hi all,
> I've got a couple of queries that I can't figure out exactly how to build
> them. Both go over the 'sys' tables.
> All my tables have the primarykey called 'id_no'. So if I have a table
> 'customers', the identity field is called 'id_no'. If I have a second
> table
> 'orders' with a foreign key on 'customers', the field is called
> 'id_no_customer'...an so on...
> I need 2 queries:
> 1. The first should return a list of those columns (including source and
> target tables) that are defined as foreignkeys (ie. columnname starts with
> 'id_no_') but no foreignkey actually exacts.
> 2. The second query should return a list of foreignkeys with the following
> information:
> target_table
> target_column
> source_table
> source_column
> I've tried the following, but on a table of 3 foreignkeys (1 is actually
> missing), I get a result of 50, instead of just 3.
> select target_table.name, target_column.name, source_table.name,
> source_column.name from sysforeignkeys
> inner join sysobjects target_table on sysforeignkeys.fkeyid =
> target_table.id
> inner join syscolumns target_column on sysforeignkeys.fkeyid =
> target_column.id
> inner join sysobjects source_table on sysforeignkeys.rkeyid =
> source_table.id
> inner join syscolumns source_column on sysforeignkeys.fkeyid =
> source_column.id
> where target_table.name = 'xxx'
> Thanks for help!
> Ivan
>|||I solved the 2nd query this way:
select target_table.name as target_table, target_column.name as
target_column,
source_table.name as source_table, source_column.name as source_column from
sysforeignkeys
inner join sysobjects target_table on sysforeignkeys.fkeyid =
target_table.id
inner join syscolumns target_column
on sysforeignkeys.fkeyid = target_column.id
and sysforeignkeys.fkey = target_column.colid
inner join sysobjects source_table on sysforeignkeys.rkeyid =
source_table.id
inner join syscolumns source_column
on sysforeignkeys.rkeyid = source_column.id
and sysforeignkeys.rkey = source_column.colid
where target_table.name = 'xxx'
But I now have to solve the first query.
Ivan
"Uri Dimant" <urid@.iscar.co.il> schrieb im Newsbeitrag
news:OUrX$1SuFHA.3236@.TK2MSFTNGP14.phx.gbl...
> Ivan
> If I remember well , OJ had written this script
> create procedure usp_findreferences
> @.tbname sysname=null
> as
> set nocount on
>
> Print 'Referenced:'
> select c1.table_name,
> c1.column_name,
> fkey=r.constraint_name,
> referenced_parent_table=c2.table_name,
> c2.column_name
> from information_schema.constraint_column_usage c1 join
> information_schema.referential_constraints r on
> c1.constraint_name=r.constraint_name
> join information_schema.constraint_column_usage c2 on
> r.unique_constraint_name=c2.constraint_name
> where c1.table_name=coalesce(@.tbname,c1.table_name)
> order by case when @.tbname is null then c1.table_name else c2.table_name
end
>
> print ''
> print 'Referencing:'
> select c1.table_name,
> c1.column_name,
> fkey=r.constraint_name,
> referencing_child_table=c2.table_name,
> c2.column_name
> from information_schema.constraint_column_usage c1 join
> information_schema.referential_constraints r on
> c1.constraint_name=r.unique_constraint_name
> join information_schema.constraint_column_usage c2 on
> r.constraint_name=c2.constraint_name
> where c1.table_name=coalesce(@.tbname,c1.table_name)
> order by case when @.tbname is null then c1.table_name else c2.table_name
end
> go
>
> --test run
> exec usp_findreferences 'Orders'
> drop proc usp_findreferences
> "Ivan Debono" <ivanmdeb@.hotmail.com> wrote in message
> news:eWrQJySuFHA.3684@.TK2MSFTNGP09.phx.gbl...
build
with
following
>

Liste of report made with ReportBuilder

Hi !

I'm trying to figure out how can i get a list of the reports made with reportbuilder. I have create a folder in ReportManger and all my report are in this folder.
When i create report with ReportDesigner i get a .RDL file, but when i create a report using reportBuilder i can't file the file created. What i want to do is to create in our web application a Web page with a list of report that our client has create using ReportBuilder. I was thinking about doing a loop on the folder for each report File but i can't find those files.

Any idea ?

Thanks!I've found the TABLE Catalog in the report server DB. This table seems to contains a list of all reports. Using this i think i can generate a list of reports. Is there a better to do this ?|||

You should use the ListChildren method on the ReportingService2005 web service endpoint to enumerate the items on the report server. You cannot easily determine whether a report was created by Report Builder using that interface, but one good approximation would be to also call GetItemDataSources and find out if the report has exactly one data source reference, and that reference points to a report model. I'm not sure about performance of this approach if you have a very large number of items on your report sever, however.

Hope that helps!

|||Thanks a lot ! This is exactly what i was searching for.|||

I have another question . I've add a web references for the ReportingService. In my .net code i've done this:

Dim ServerRapport As New ReportingService2005

ServerRapport.Credentials = System.Net.CredentialCache.DefaultCredentials

Dim items As CatalogItem() = Nothing

items = ServerRapport.ListChildren("/", True)

Now i was wondering how can i iterate throught all the children. I only want report that are stored in a specific virtual folder on reportServer. I thought that using the collection CatalogItem() i could iterate but it seems that it's not possible. So how can i iterate throught my report to get the liste of the report in a specific virtual folder ?

Thanks !

|||

Right after posting i found the solution. Since this is a collection all i have to do was to create an objet CatalogItem and then do a FOR EACH. Now it's working and the performance seems not to be a problem. We have over 600 reports and it's working fine!

Thanks again!

|||Hi,
How to get the list of folder from reporting server. I know we can get the list of reports from folder but before i want display a list of folder available to my user.

Is this possible?

Liste of report made with ReportBuilder

Hi !

I'm trying to figure out how can i get a list of the reports made with reportbuilder. I have create a folder in ReportManger and all my report are in this folder.
When i create report with ReportDesigner i get a .RDL file, but when i create a report using reportBuilder i can't file the file created. What i want to do is to create in our web application a Web page with a list of report that our client has create using ReportBuilder. I was thinking about doing a loop on the folder for each report File but i can't find those files.

Any idea ?

Thanks!I've found the TABLE Catalog in the report server DB. This table seems to contains a list of all reports. Using this i think i can generate a list of reports. Is there a better to do this ?|||

You should use the ListChildren method on the ReportingService2005 web service endpoint to enumerate the items on the report server. You cannot easily determine whether a report was created by Report Builder using that interface, but one good approximation would be to also call GetItemDataSources and find out if the report has exactly one data source reference, and that reference points to a report model. I'm not sure about performance of this approach if you have a very large number of items on your report sever, however.

Hope that helps!

|||Thanks a lot ! This is exactly what i was searching for.|||

I have another question . I've add a web references for the ReportingService. In my .net code i've done this:

Dim ServerRapport As New ReportingService2005

ServerRapport.Credentials = System.Net.CredentialCache.DefaultCredentials

Dim items As CatalogItem() = Nothing

items = ServerRapport.ListChildren("/", True)

Now i was wondering how can i iterate throught all the children. I only want report that are stored in a specific virtual folder on reportServer. I thought that using the collection CatalogItem() i could iterate but it seems that it's not possible. So how can i iterate throught my report to get the liste of the report in a specific virtual folder ?

Thanks !

|||

Right after posting i found the solution. Since this is a collection all i have to do was to create an objet CatalogItem and then do a FOR EACH. Now it's working and the performance seems not to be a problem. We have over 600 reports and it's working fine!

Thanks again!

|||Hi,
How to get the list of folder from reporting server. I know we can get the list of reports from folder but before i want display a list of folder available to my user.

Is this possible?