how do we interact with csv files in SQL

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

how do we interact with csv files in SQL

Author

Topic

rtutus
Aged Yak Warrior

522 Posts

Posted - 2006-12-23 : 13:00:45

Thanks

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2006-12-23 : 14:22:27

if you want to import a csv to a table, I prefer bcp.exe.

look for "bcp Utility" on BOL.

www.elsasoft.org

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2006-12-23 : 15:23:55

Are you looking to import it or parse it ?

see this
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50648
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=best,split,function

rtutus
Aged Yak Warrior

522 Posts

Posted - 2006-12-23 : 16:35:05

like to check if the columns contents of a csv file re in certain table?
or the other way: output the content of certain table to a csv file?
But all in SQL
Thanks

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2006-12-23 : 18:55:29

To compare a csv with a table, I would import the csv to staging table and then compare the two tables in the normal way using t-sql. after comparison you can drop the staging table.

to export, there's no way to do it in SQL unless you call an external program (like bcp.exe) via xp_cmdshell.

to import, you can do it in sql with the BULK INSERT syntax. see BOL for details on BULK INSERT.

www.elsasoft.org

rtutus
Aged Yak Warrior

522 Posts

Posted - 2006-12-24 : 04:34:09

staging table is it the same as temporary table? #tempTB
another question: is there a notion of sql server accounts? or is there only the notion of users?
If there is an account in Database level, is that account (or user) gonna be necessarily part of the list of the instance users or not necessarily?
Thanks a lot

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2006-12-24 : 10:02:32

normally a staging table would be a permanent table, but it could be a temp table also. often it's used as an intermediate place to check data for validity, etc, before putting it in another table.

in sql server there are logins (server level), roles (both server and database level), and users (database level). in 2005 there are more security related objects, such as certificates and keys.

You need to read about sql server security in BOL.

www.elsasoft.org

rtutus
Aged Yak Warrior

522 Posts

Posted - 2006-12-24 : 16:45:31

cool tools.
One little thing about Bulk Insert to import csv file: does that assume that the target table structure is already created
also does bulk insert also work in sql server 2000?
tell me one more thing, can we also import csv and export them without using t-sql, i mean by dts or some other mean.

Muchas gracias.

rtutus
Aged Yak Warrior

522 Posts

Posted - 2006-12-24 : 17:29:06

I also put few questions at the end of this thread:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50648
If some one doesn t mind helping me since i haven t fully competed that function succefully.
Thanks.

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2006-12-24 : 18:00:53

1. bulk insert requires the destination table exist.
2. yes, bulk insert works on 2000
3. and yes, you can use other tools to bulk insert, such as DTS or SSIS

www.elsasoft.org

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-12-24 : 23:05:40

Will this help you?

http://vyaskn.tripod.com/passing_arrays_to_stored_procedures.htm

Madhivanan

Failing to plan is Planning to fail

rtutus
Aged Yak Warrior

522 Posts

Posted - 2006-12-25 : 16:52:59

I get this error with bulk insert:

Bulk load: An unexpected end of file was encountered in the data file.

Msg 7399, Level 16, State 1, Line 2

The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.
Here is the csv file sample from http://www.creativyst.com/Doc/Articles/CSV/CSV01.htm#ExampleData in Example Data section: (don t forget the carraiage return at the end of the file)

John,Doe,120 jefferson st.,Riverside, NJ, 08075
Jack,McGinnis,220 hobo Av.,Phila, PA,09119
"John ""Da Man""",Repici,120 Jefferson St.,Riverside, NJ,08075
Stephen,Tyler,"7452 Terrace ""At the Plaza"" road",SomeTown,SD, 91234
,Blankman,,SomeTown, SD, 00298
"Joan ""the bone"", Anne",Jet,"9th, at Terrace plc",Desert City,CO,00123
I saved that file in 'C:\myDev\SQL\CSVproject\myCSV.txt' then I create a table with five columns for which here is the object script:

USE [DbTest1]
GO
/****** Object: Table [dbo].[mycsvtable1] Script Date: 12/25/2006 16:45:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[mycsvtable1](
[col1] [nchar](10) NULL,
[col11] [nchar](10) NULL,
[col12] [nchar](10) NULL,
[col13] [nchar](10) NULL,
[col14] [nchar](10) NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
Then I run :

bulk insert mycsvtable1 from 'C:\myDev\SQL\CSVproject\myCSV.txt'

I get the error mentioned above. Thanks a lot for help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources