problem in export of csv file into sql server

I am trying to export a CSV file into sql server. I wrote a query to perform select fields from csv file using OPENROWSET. I am getting all the values in a single column, but I need to get in three different columns. Any can body can help me



The query is as follows

Select * from OpenRowset ('MSDASQL','Driver= {Microsoft Text Driver (*.txt; *.csv)}; DefaultDir=” Directory name” \;Extended properties=''ColNameHeader=True; Format=Delimited;''','select * from Sample.csv')



Sample.csv

col1, col2, col3 ---- all the values are in single cell of excel csv file
1, a, abc
2, sfasf, sdgagas



Output of query is

col1,col2,col3 --- all the values are coming in one column and with comma between them
1,a,abc
2,sfasf,sdgagas

can any body help me where i am going wrong?

Thanks in advance
suryamight be easier to just import the entire file to a staging table and the work with it from there.|||would u like to give me some sample code|||Doh - something wrong with my pc, please ignore 2 duplicate answers and refer the top one.|||Have you used DTS in this case to import the rows which is an easier solution or what is requirement to use OPENROWSET statement.|||Have you used DTS in this case to import the rows which is an easier solution or what is requirement to use OPENROWSET statement.
See this http://www.sql-server-helper.com/tips/read-import-excel-file-p01.aspx fyi.

Problem in Displaying NTEXT field from database?

Hello,

I have around 7 ntext fields in my data base table and I am getting data from the data base table through executing stored procedure, But when I am displaying data using record set, few of the ntext fields in recored set are empty .Iam sure that these are having data in table.

I am not sure why recordset is lossing that ntext field data?Because of this I am unable to display that data in web form.

any ideas really appriciated.

Thanks

Ram

text and ntext fields should always be either the only data returned or the last item in a select statement.

For example:

SELECT ntextField FROM myTable WHERE something = true

or

SELECT myFirstField, myOtherField, ntextField FROM myTable WHERE something = true

This is a limitation of MSSQL server and (some versions) of MySQL. If you are using MSSQL Server 2005, I would suggest changing the database column to a nvarchar(MAX) as this will store the same number of characters as an ntext column. (I believe they intended to remove text and ntext from the next release of MSSQL Server in favor of varchar(MAX) and nvarchar(MAX), but don't quote me on that.)

|||

I am using SQLserver 2000, What will be the possible solutions for this problem.

Thanks

|||

You will have to select each ntext field individually. So you will need 7 select statements each time.

However, I would recommend changing the database to use a different type of column. If you know the data will never be larger than 8K you can still use nvarchar(8000) on SQL Server 2000. Or you could combine some of the columns and then use delimiters so that you have one huge column with something like ||| between entries (but this may cause other problems.)

In general text, ntext and blob fields should be used as rarely as possible, it is usually easier to put that much data into a file and then store the files name and location in SQL Server.

Let me know if this doesn't really answer your question.

|||

Hi,

Its working for me now..What I did is ...

I have got all ntext fields from the recordset first into some varialbles before acesing non ntext fields and mapped to the form controls.

thats it.

thanks

Problem in creating reports using BI fields

Hi,

I have created a cube using 3 Dimensions and 1 measure. One of the dimensions is a time dimension. I have also used the ADD BI command to get the Year to Date and Year over year % . This works perfectly with the cube. But when I try and create a report I am unable to display the figures for the above mentioned columns. I get Null data there.

Does anybody have any suggestions as to why this is happening and what the way to overcome this.

Thanks in advance
MNJ

Could you provide more information on how you are creating the report and which columns are returning nulls?

Problem in creating reports using BI fields

Hi,

I have created a cube using 3 Dimensions and 1 measure. One of the dimensions is a time dimension. I have also used the ADD BI command to get the Year to Date and Year over year % . This works perfectly with the cube. But when I try and create a report I am unable to display the figures for the above mentioned columns. I get Null data there.

Does anybody have any suggestions as to why this is happening and what the way to overcome this.

Thanks in advance
MNJ

Could you provide more information on how you are creating the report and which columns are returning nulls?

Problem in creating reports using BI fields

Hi,

I have created a cube using 3 Dimensions and 1 measure. One of the dimensions is a time dimension. I have also used the ADD BI command to get the Year to Date and Year over year % . This works perfectly with the cube. But when I try and create a report I am unable to display the figures for the above mentioned columns. I get Null data there.

Does anybody have any suggestions as to why this is happening and what the way to overcome this.

Thanks in advance
MNJ

Could you provide more information on how you are creating the report and which columns are returning nulls?sql

Problem doing a count query using 2 tables

I've got one table that holds various information fields and a field for the
city, and another table that holds city and state (and zip, etc). I want to
query both tables to show the number of states for which records in the first
table were submitted. Problem is that although I can query a count of
records by city in table 1, or I can combine the fields from both tables, I
can't seem to manage to do both in 1 query. All I want is to select a date
range, reference the city field in one table to "city" in the other, and show
a count of the states. It's got to be simple but I can't figure it out. Can
anyone help with this? Thanks!
chazz
adding date component to my earlier query can have query as shown in
following example.
ex:
create table emp_master(name varchar(25),city varchar(25), dt datetime)
create table city_master(city varchar(25),state varchar(25), zip
varchar(25))
go
insert into emp_master values('name1','city1', getdate())
insert into emp_master values('name2','city1', getdate())
insert into emp_master values('name3','city2', getdate() -1)
insert into emp_master values('name4','city2', getdate() -1)
insert into emp_master values('name5','city2', getdate() -2)
insert into emp_master values('name7','city4', getdate() -2)
insert into emp_master values('name8','city5', getdate() -3)
insert into emp_master values('name9','city5', getdate() -3)
insert into city_master values('city1', 'state1', 'xxxx')
insert into city_master values('city2', 'state1', 'xxxx')
insert into city_master values('city3', 'state1', 'xxxx')
insert into city_master values('city4', 'state2', 'xxxx')
insert into city_master values('city5', 'state3', 'xxxx')
go
--query to get the number of states for which records in the first
table(emp_master) were submitted
select b.state, count(b.state) as 'no_of_counts'
from emp_master a join city_master b
on a.city = b.city
where a.dt between '20041016' and '20041018 23:59:59'
group by b.state
If above does not satisfy your requirement post DDL/some sample records and
expected result-set.
Vishal Parkar
vgparkar@.yahoo.co.in | vgparkar@.hotmail.com

Problem doing a count query using 2 tables

I've got one table that holds various information fields and a field for the
city, and another table that holds city and state (and zip, etc). I want to
query both tables to show the number of states for which records in the first
table were submitted. Problem is that although I can query a count of
records by city in table 1, or I can combine the fields from both tables, I
can't seem to manage to do both in 1 query. All I want is to select a date
range, reference the city field in one table to "city" in the other, and show
a count of the states. It's got to be simple but I can't figure it out. Can
anyone help with this? Thanks!
chazz
If i understand your requirement correctly, following example may help you.
Please post relevent table structure/sample records/expected result set to
understand your problem correctly.
ex:
create table emp_master(name varchar(25),city varchar(25))
create table city_master(city varchar(25),state varchar(25), zip
varchar(25))
go
insert into emp_master values('name1','city1')
insert into emp_master values('name2','city1')
insert into emp_master values('name3','city2')
insert into emp_master values('name4','city2')
insert into emp_master values('name5','city2')
insert into emp_master values('name7','city4')
insert into emp_master values('name8','city5')
insert into emp_master values('name9','city5')
insert into city_master values('city1', 'state1', 'xxxx')
insert into city_master values('city2', 'state1', 'xxxx')
insert into city_master values('city3', 'state1', 'xxxx')
insert into city_master values('city4', 'state2', 'xxxx')
insert into city_master values('city5', 'state3', 'xxxx')
go
--query to get the number of states for which records in the first
table(emp_master) were submitted
select b.state, count(b.state) as 'no_of_counts'
from emp_master a join city_master b
on a.city = b.city
group by b.state
Vishal Parkar
vgparkar@.yahoo.co.in | vgparkar@.hotmail.com

Problem Creating TRIGGER on a VIEW

Hello,

I have a problem that definitely has me stumped.

I have a view that looks at data in a different database. Some of the fields in the view are updateable and some are not. I am trying to create a trigger against the view that will allow me to audit the updates into an audit table. I am having problems when trying to execute the CREATE TRIGGER statement.

I keep getting the message...

Server: Msg 208, Level 16, State 4, Procedure updDocInfo, Line 1
Invalid object name 'vwDC_DocInfo'.

Where vwDC_DocInfo is the name of the view.

Does anyone have any idea why I might be getting this error? The VIEW definitely does exist and I am executing the script in the same database as the view.

The script is included below...

CREATE TRIGGER updDocInfo
ON [vwDC_DocInfo]
FOR UPDATE AS

DECLARE @.ModifiedDate AS DATETIME
SELECT @.ModifiedDate = GETDATE()

-- Audit OLD record.
INSERT tblAudit_DC_DocInfo
SELECT
0 AS AuditType,
ItemID,
Comment,
VersionComment,
CheckedOut,
Title,
BaseParagonDocumentNumber,
Author,
ClientDocumentNumber,
ClientDocumentType,
ClientJobNumber,
[Module],
Unit,
SequenceNumber,
RevisionDate,
ApprovedBy,
CheckedDepartmentManager,
CheckedLeadEngineerDesigner,
IssueType,
RevisedByDesigner,
RevisedByEngineer,
RevisionCode,
HSECheck,
CurrentVersionNumber,
CurrentVersionDate,
USER AS ChangedByUser,
@.ModifiedDate AS DateChanged
FROM DELETED DEL

-- Audit NEW record.
INSERT tblAudit_DC_DocInfo
SELECT
0 AS AuditType,
ItemID,
Comment,
VersionComment,
CheckedOut,
Title,
BaseParagonDocumentNumber,
Author,
ClientDocumentNumber,
ClientDocumentType,
ClientJobNumber,
[Module],
Unit,
SequenceNumber,
RevisionDate,
ApprovedBy,
CheckedDepartmentManager,
CheckedLeadEngineerDesigner,
IssueType,
RevisedByDesigner,
RevisedByEngineer,
RevisionCode,
HSECheck,
CurrentVersionNumber,
CurrentVersionDate,
USER AS ChangedByUser,
@.ModifiedDate AS DateChanged
FROM INSERTED INSI didn't think you could create a trigger on a view, but BOL says otherwise...

Still doesn't keep my example here from exploding though

USE Northwind
GO

CREATE VIEW myView99 AS SELECT * FROM Orders
GO

CREATE TRIGGER myTrigger99 ON myView99 FOR INSERT AS PRINT 'HI'
GO

DROP VIEW myView99
GO|||BOL:

Designing Triggers
Microsoft SQL Server 2000 provides two options when designing triggers:

INSTEAD OF triggers are executed in place of the usual triggering action. INSTEAD OF triggers can also be defined on views with one or more base tables, where they can extend the types of updates a view can support.

AFTER triggers are executed after the action of the INSERT, UPDATE, or DELETE statement is performed. Specifying AFTER is the same as specifying FOR, which is the only option available in earlier versions of SQL Server. AFTER triggers can be specified !!! ONLY !!! on tables.|||Good catch, snail! How about that, Brett, huh? huh? huh?|||Originally posted by snail
BOL:

Designing Triggers
Microsoft SQL Server 2000 provides two options when designing triggers:

INSTEAD OF triggers are executed in place of the usual triggering action. INSTEAD OF triggers can also be defined on views with one or more base tables, where they can extend the types of updates a view can support.

AFTER triggers are executed after the action of the INSERT, UPDATE, or DELETE statement is performed. Specifying AFTER is the same as specifying FOR, which is the only option available in earlier versions of SQL Server. AFTER triggers can be specified !!! ONLY !!! on tables.

Thanks,

I did some experimenting after I created the post. I finally realized that I could only create a INSTEAD OF TRIGGER. It is not obvious in the documentation. The only problem was that I then had to go and recreate the transaction, as I really did want the transaction to go through. Very odd use of triggers...

problem converting smalldatetime to 12hr time value

Hi,

I have two smalldatetime fields starttime and endtime

I want to display them like

select convert(varchar,starttime,108) + ' to ' + convert(varchar,endtime,108) from tbTest

the data in the field is

starttime endtime

1/3/2006 9:00:00 1/3/2006 6:00:00

the result I am getting is

09:00:00 to 18:00:00

Where as I want it something like this

9.00 AM to 6.00 PM

Is it possible? what query should be used for this?

Hi,

by taking a look to BOL ...

PRINT ' HERE IS MON DD YYYY HH:MIAM (OR PM) FORMAT ==>' +

CONVERT(nvarchar(30),GETDATE(),109)

HERE IS MON DD YYYY HH:MIAM (OR PM) FORMAT ==>M?r 9 2007 10:25:52:250AM

....

DECLARE @.ST SMALLDATETIME, @.ET SMALLDATETIME

SET @.ST = '1/3/2006 9:00:00'

SET @.ET ='1/3/2006 18:00:00'

SELECT (CONVERT(CHAR(11),@.ST,111) +

SUBSTRING(CONVERT(CHAR(19),@.ST,100),13,19) + ' To ' +

CONVERT(CHAR(11),@.ET,111) +

SUBSTRING(CONVERT(CHAR(19),@.ET,100),13,19))

-

2006/03/01 9:00AM To 2006/03/01 6:00PM

|||

What if i have an input like:

time

0910

1000

0530

0620

How do i convert this input into smalldatetime?

|||

Here is one option (uses the default date of [01/01/1900]):

Code Snippet

DECLARE @.MyTable table
( MyTime varchar(4) )

SET NOCOUNT ON

INSERT INTO @.MyTable VALUES ( '0910' )
INSERT INTO @.MyTable VALUES ( '1000' )
INSERT INTO @.MyTable VALUES ( '0530' )
INSERT INTO @.MyTable VALUES ( '0620' )

SELECT MyTime = ( cast( 0 as smalldatetime ) + stuff( MyTime, 3, 0, ':' ))
FROM @.MyTable

problem converting smalldatetime to 12hr time value

Hi,

I have two smalldatetime fields starttime and endtime

I want to display them like

select convert(varchar,starttime,108) + ' to ' + convert(varchar,endtime,108) from tbTest

the data in the field is

starttime endtime

1/3/2006 9:00:00 1/3/2006 6:00:00

the result I am getting is

09:00:00 to 18:00:00

Where as I want it something like this

9.00 AM to 6.00 PM

Is it possible? what query should be used for this?

Hi,

by taking a look to BOL ...

PRINT ' HERE IS MON DD YYYY HH:MIAM (OR PM) FORMAT ==>' +

CONVERT(nvarchar(30),GETDATE(),109)

HERE IS MON DD YYYY HH:MIAM (OR PM) FORMAT ==>M?r 9 2007 10:25:52:250AM

....

DECLARE @.ST SMALLDATETIME, @.ET SMALLDATETIME

SET @.ST = '1/3/2006 9:00:00'

SET @.ET ='1/3/2006 18:00:00'

SELECT (CONVERT(CHAR(11),@.ST,111) +

SUBSTRING(CONVERT(CHAR(19),@.ST,100),13,19) + ' To ' +

CONVERT(CHAR(11),@.ET,111) +

SUBSTRING(CONVERT(CHAR(19),@.ET,100),13,19))

-

2006/03/01 9:00AM To 2006/03/01 6:00PM

|||

What if i have an input like:

time

0910

1000

0530

0620

How do i convert this input into smalldatetime?

|||

Here is one option (uses the default date of [01/01/1900]):

Code Snippet

DECLARE @.MyTable table
( MyTime varchar(4) )

SET NOCOUNT ON

INSERT INTO @.MyTable VALUES ( '0910' )
INSERT INTO @.MyTable VALUES ( '1000' )
INSERT INTO @.MyTable VALUES ( '0530' )
INSERT INTO @.MyTable VALUES ( '0620' )

SELECT MyTime = ( cast( 0 as smalldatetime ) + stuff( MyTime, 3, 0, ':' ))
FROM @.MyTable