script to read from cvs - 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

General SQL Server Forums

New to SQL Server Programming

script to read from cvs

Author

Topic

gongxia649
So Suave

344 Posts

Posted - 2006-05-09 : 11:37:09

how do i write a script to read from cvs and be able to create 3 fields.

X002548
Not Just a Number

15586 Posts

Posted - 2006-05-09 : 12:16:12

You could DTS or bcp to load the data in to a table and then do whatever you need done. Read the hint link in my sig.

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

gongxia649
So Suave

344 Posts

Posted - 2006-05-09 : 12:51:45

no..i dont wanna do dts or bcp

X002548
Not Just a Number

15586 Posts

Posted - 2006-05-09 : 13:08:47

so what do you want to do? What does "read" mean to T-SQL?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2006-05-09 : 13:10:59

CVS as in Concurrent Versions System?

X002548
Not Just a Number

15586 Posts

Posted - 2006-05-09 : 13:13:53

Such a wealth of knowledge

http://en.wikipedia.org/wiki/Concurrent_Versions_System

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2006-05-09 : 13:32:17

quote:
Originally posted by gongxia649

no..i dont wanna do dts or bcp

how about you give us some more information about what you are trying to do.

-ec

X002548
Not Just a Number

15586 Posts

Posted - 2006-05-09 : 13:50:30

You can drag a dead horse to water...but you will never make him drink

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

gongxia649
So Suave

344 Posts

Posted - 2006-05-09 : 14:06:17

select * into item_to_delete
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=D:\testing.xls;HDR=YES',
'SELECT * FROM [Sheet1$]')

gongxia649
So Suave

344 Posts

Posted - 2006-05-09 : 14:07:05

i found that scrpit but it does excel files.
besides that i wanna create 3 more fields.

X002548
Not Just a Number

15586 Posts

Posted - 2006-05-09 : 16:25:32

Not to beat a dead horse that won't drink...

But...

You should create the table then use bcp

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2006-05-09 : 17:35:30

I heard a rumour ... [url]http://weblogs.sqlteam.com/phils/archive/2006/04/28/9726.aspx[/url]

I find it easiest to just create a temp table in sql, then use bulk insert.

oops, Brett already said that.

rockmoose

gongxia649
So Suave

344 Posts

Posted - 2006-05-09 : 21:15:43

my task is to create the script not bcp...how many time i have to repeat that.

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2006-05-09 : 21:52:46

quote:
Originally posted by gongxia649

my task is to create the script not bcp...how many time i have to repeat that.

then use bulk insert - I'll let you look that up in BOL for details.

And you really haven't told us anything about what you are trying to do. I thought you were trying to load stuff up from your version control environment at first, but apparently that was just a typo.

-ec

gongxia649
So Suave

344 Posts

Posted - 2006-05-09 : 21:54:47

i want to populate a table with an excel file...excel has 2 columns. my table has 5 columns

but i dont wanna use bcp or dts.

im trying to use this script, but it doesnt work

select * into item_to_delete
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=D:\testing.xls;HDR=YES',
'SELECT * FROM [Sheet1$]')

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2006-05-09 : 21:57:53

quote:
Originally posted by gongxia649

i want to populate a table with an excel file...excel has 2 columns. my table has 5 columns

but i dont wanna use bcp or dts.

im trying to use this script, but it doesnt work

select * into item_to_delete
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=D:\testing.xls;HDR=YES',
'SELECT * FROM [Sheet1$]')

Don't SELECT * then. SELECT the columns you want instead.

-ec

gongxia649
So Suave

344 Posts

Posted - 2006-05-09 : 21:58:26

quote:
Originally posted by eyechart

quote:
Originally posted by gongxia649

my task is to create the script not bcp...how many time i have to repeat that.

then use bulk insert - I'll let you look that up in BOL for details.

And you really haven't told us anything about what you are trying to do. I thought you were trying to load stuff up from your version control environment at first, but apparently that was just a typo.

-ec

it work with my cvs file...great

it doesnt work for xls files.

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2006-05-09 : 22:21:49

google is your friend.

This worked for me:

SELECT * from openrowset('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;DATABASE=c:\book1.xls', Sheet1$)

Here are some KB articles to read:
http://support.microsoft.com/Default.aspx?kbid=321686
http://support.microsoft.com/Default.aspx?kbid=306397

-ec

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2006-05-09 : 22:24:09

also, connectionstrings.com is very useful too.

http://www.connectionstrings.com/

-ec

gongxia649
So Suave

344 Posts

Posted - 2006-05-09 : 22:32:27

quote:
Originally posted by eyechart

google is your friend.

This worked for me:

SELECT * from openrowset('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;DATABASE=c:\book1.xls', Sheet1$)

Here are some KB articles to read:
http://support.microsoft.com/Default.aspx?kbid=321686
http://support.microsoft.com/Default.aspx?kbid=306397

-ec

Server: Msg 213, Level 16, State 4, Line 1
Insert Error: Column name or number of supplied values does not match table definition.

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2006-05-10 : 01:39:50

I think we have given you all the information needed to solve this problem.

don't do a select *, select the columns you want by name. I have tested this against a sample excel spreadsheet using openrowset and it works.
If you still hve problems, please post enough information that we can help you. Make sure to read the KB articles I linked and try using google a little to see if you can solve your problem.

-ec

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources