Import CSV files into sql - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Import CSV files into sql

Author

Topic

Msandlana
Starting Member

33 Posts

Posted - 2009-01-28 : 01:31:10

I have successfully imported CSV file to my table Stoppage using this sql.
BULK
INSERT Stoppage
FROM 'D:\FHG\Stoppage Report\Stoppage - Dec 2008.csv'
WITH
(

FIELDTERMINATOR = '","',
ROWTERMINATOR = '\n'

)

"14th Ave Spar - 101071-303","Fanie Roos","2008-12-10 12:05:08","2008-12-10 12:06:44","00:01"

above is the example of whats inside my .csv file every field have double quotes. The record will be inserted but it doesn't take out the double quotes on the first column i just want the statement to remove it.
it will look like this on the table the commas are columns
"14th Ave Spar - 101071-303,Fanie Roos,2008-12-10 12:05:08,2008-12-10 12:06:44,00:01"

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2009-01-28 : 01:40:38

take a look at this,,,,

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926

May Be This solve your problem/

Kokkula
Starting Member

41 Posts

Posted - 2009-01-28 : 03:52:41

Hello,

In the bulk insert there is no opetion to get it done, but we have do it manually.

Test.csv
"A","B","C"
"B","C","D"

CREATE TABLE [dbo].[BInsert]
(
Col1 NVARCHAR(20)
,Col2 NVARCHAR(20)
,Col3 NVARCHAR(20)
)

BULK INSERT BInsert
FROM 'FilePath\Test.csv'
WITH
(
FIELDTERMINATOR = '","',
ROWTERMINATOR = '\n'
)

SELECT * FROM BInsert

UPDATE [dbo].[BInsert]
SET Col1 = RIGHT(Col1,LEN(Col1)-1)
,Col3 = LEFT(Col3,LEN(Col3)-1)

SELECT * FROM BInsert

Drop Table [dbo].[BInsert]

--
Thanks,
Pavan

Msandlana
Starting Member

33 Posts

Posted - 2009-01-28 : 07:34:01

Thaks guys it worked.
I have another problem the first column is called customer which have this data: 14th Ave Spar - 101071-303
I want to break that customer to have the Customer_name and customer_code like this:

Customer_Code Customer_Name
101071-303 14th Ave Spar

The rules are these, customer_name is always before the first hyphen("-") and every thing after that is customer_code so the first hyphen("-") separates the customer_code and name.

Rajesh Jonnalagadda
Starting Member

45 Posts

Posted - 2009-01-28 : 08:14:19

SELECT RTRIM(SUBSTRING('14th Ave Spar - 101071-303',1,Charindex('-', '14th Ave Spar - 101071-303')-1)) AS Customer_Name
, LTRIM(SUBSTRING('14th Ave Spar - 101071-303',Charindex('-', '14th Ave Spar - 101071-303')+1, Charindex('-', '14th Ave Spar - 101071-303')-1)) AS Customer_Code

Rajesh Jonnalagadda
[url="http://www.ggktech.com
"]GGK TECH[/url]

Msandlana
Starting Member

33 Posts

Posted - 2009-01-28 : 08:36:39

quote:
Originally posted by Rajesh Jonnalagadda

SELECT RTRIM(SUBSTRING('14th Ave Spar - 101071-303',1,Charindex('-', '14th Ave Spar - 101071-303')-1)) AS Customer_Name
, LTRIM(SUBSTRING('14th Ave Spar - 101071-303',Charindex('-', '14th Ave Spar - 101071-303')+1, Charindex('-', '14th Ave Spar - 101071-303')-1)) AS Customer_Code

Rajesh Jonnalagadda
[url="http://www.ggktech.com
"]GGK TECH[/url]

THANKS RAJ IT WORKED

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources