CSV Import with SQL Server 2005 Express

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

SQL Server 2005 Forums

SQL Server Administration (2005)

CSV Import with SQL Server 2005 Express

Author

Topic

mikebird
Aged Yak Warrior

529 Posts

Posted - 2008-12-01 : 04:53:31

I don't think I have the tools to do this. Is it possible?

mikebird
Aged Yak Warrior

529 Posts

Posted - 2008-12-01 : 05:15:15

Script is this:

USE [hardcore]

GO

--Add data
BULK
INSERT whatgoeshere?
FROM 'E:\Documents and Settings\Mike\Desktop\Recruitment.csv'
WITH
(
FIRSTROW = 2,
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)

Using this:

USE [hardcore]
GO
/****** Object: Table [dbo].[Recruitment] Script Date: 12/01/2008 10:11:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Recruitment](
[Agency] [nchar](100) NULL,
[Recruiter] [nchar](100) NULL,
[Company] [nchar](100) NULL,
[Contacted] [datetime] NULL,
[Updated] [nchar](100) NULL,
[Interview] [nchar](100) NULL,
[Crushed] [nchar](100) NULL,
[Because] [nchar](100) NULL
) ON [PRIMARY]

Data looks like this:

Agency,Recruiter,Company,Contacted,Update,Interview,Crushed,Because
Niche,Tush,Servista,"July 22, 2004",,,x,
Computer People,Mark Crow,Blackberry,,,,x,
Madison Black,Dwayne Wakil,Vodafone Theale,,,,x,
Project People,Amanda Cager,"Three, Maidenhead",,,,x,
Elan,Stephanie O'Connor,Vodafone Theale?,"September 2, 2004",,,,
,Laura,"Unilever, Walton","September 2, 2004",,,,
,Jon Horton,Harlow,,,,,
,Jon Horton,"NTL, Crawley",,,,,
Progressive,Pesiba Oyetey,Direct Wines,,,"August 22, 2004",x,"Not concise with answers, other had > xp"
Progressive,Pesiba Oyetey,Direct Wines,,,"August 27, 2004",x,
Artemis,Susan Hutchinson,,"September 3, 2004",,,,
RP International,Rachael Chinappen,Dubai (1-2 years imp),"September 2, 2004",,,,
RP International,Rachael Chinappen,Dubai (few weeks),"September 3, 2004",,,,
RP International,Rachael Chinappen,Dubai (6 months),"September 4, 2004",,,,
Harvey Nash,Miranda De Villiers,NDS Digital Broadcast,"September 6, 2004",,,,
e-CRM People,Laura Clarke,Amdocs (want to I/V - Newbury) will get back 1-2 wks = 28 Sep or 5 Oct,"September 2, 2004",,,,
Progressive,Stephen McAnaney,Harlow Distance problem,"September 13, 2004",,,,
Computer People,Cressida Thompson,waiting for detailsÉ!,"September 1, 2004",,,,
Nesco Group,Lauren Coombes,Looks like J Wooldridge,"September 7, 2004",,,,

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-01 : 05:40:25

[code]BULK INSERT [hardcore].[dbo].[Recruitment]
FROM 'E:\Documents and Settings\Mike\Desktop\Recruitment.csv'
WITH
(
FIRSTROW = 2,
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)[/code]

mikebird
Aged Yak Warrior

529 Posts

Posted - 2008-12-01 : 09:56:16

Looks good. When I run it, I get

0 rows affected

There are a lot of rows in the CSV

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-01 : 09:58:27

is path your local machine path? it must be server path.

mikebird
Aged Yak Warrior

529 Posts

Posted - 2008-12-01 : 10:40:46

The row terminator appears as that square symbol in the file in Notepad. Not in the text above, here. All looks simple and correct. Not sure why not importing anything. I must be missing something

Oh, I liked it when I was using UNIX, you know! Years ago! Microsoft's strange...

mikebird
Aged Yak Warrior

529 Posts

Posted - 2008-12-01 : 10:49:11

Ahhh. Good point. There is only one machine though. SQLSERVER service is running from here. One disk.

That CSV file is E:\Documents and Settings\Mike\Desktop\Recruitment.csv
Should I put it in that special place E:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data ? Why? What's wrong with it being in the directory above?

I like UNIX

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-01 : 11:27:42

quote:
Originally posted by mikebird

Ahhh. Good point. There is only one machine though. SQLSERVER service is running from here. One disk.

That CSV file is E:\Documents and Settings\Mike\Desktop\Recruitment.csv
Should I put it in that special place E:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data ? Why? What's wrong with it being in the directory above?

I like UNIX

Nope no need for that. i was checking if you were executing this in machine where sqlserver is running.

mikebird
Aged Yak Warrior

529 Posts

Posted - 2008-12-01 : 11:40:20

I don't know BULK INSERT in Microsoft. Used similar in Oracle, and a custom UNIX platform...

In the above data example, there are a few rows, with the headers at the top, which are ignored. Do those strings need to be quoted? Are the double-quotes OK? I don't see why there aren't some errors. Is that how the command works? I just get 0 rows affected.

mikebird
Aged Yak Warrior

529 Posts

Posted - 2008-12-01 : 11:51:24

I should explain.

I've realised this CSV is rubbish, isn't it? There are spaces in there and just commas to seperate each string!! I want to get a "table" out of Excel and BOY do I hate Excel! It's for schoolkids. Just one column would do but the "table" would be nice. I just want to do some simple distinct counts and grouping on what's in there which will take seconds. It's about 4 years of data I've built up. I need an export to CSV where each comma-delimited field is quoted. Couldn't see a way to do that. You probably say do the work on it in Excel and I tried, but how do you do a distinct count in there? I had a good look. I should have built the table using SQL but didn't.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources