Need a Help in Importing CSV file into DB

Question

Need a Help in Importing CSV file into DB

Saravanan_tvr

SSCarpal Tunnel

Points: 4558
More actions
November 2, 2010 at 7:04 am

#228701

Dear Experts,
I have a difficulty task in SQL Procedure creations,
I have to Import the csv file in sql server DB,
Example:
One csv file like CSV1.csv
With First row as Column Name
Like
P_ID,P_price,P_discount,P_tax
1 2.00$ 5% vat1.0
But I want to Write a Procedure to Import the CSV file
With Runtime only creating the Table.
That is the Table Name= CSV1
and Column_Names are:
P_ID,P_price,P_discount,P_tax
and values are :
1 2.00$ 5% vat1.0
Please help me To Import the CSV file in TSQl
But the IMPORT data Wizard we are able but I need to do this in TSQL
THanks in Advance

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

WayneS SSC Guru Points: 95460 More actions · Answer 1

Well, you didn't give us any sample data (in comma-delimited format) to play with, so all I can do for you is to point you to this: Click here for the latest Delimited Split Function.

I'd recommend using BULK INSERT to load the file into a table with one large column.

Assuming:

CREATE TABLE #Source(CSVData varchar(7000));

BULK INSERT #Source FROM c:\temp\test.csv;

SELECT ds.item

FROM #Source s

CROSS APPLY dbo.DelimitedSplit8k(s.CSVData, ',') ds;

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

David Burrows SSC Guru Points: 65142 More actions · Answer 2

CREATE TABLE #header (Data varchar(max))

CREATE TABLE #result (dummycolumn int)

BULK INSERT #header FROM 'C:\TEMP\CSV1.csv' WITH (DATAFILETYPE = 'char',ROWTERMINATOR = '',LASTROW = 1)

DECLARE @sql varchar(max)

SELECT @sql = COALESCE(@sql+',['+SUBSTRING(Data, N, CHARINDEX(',', Data + ',', N) - N)+'] varchar(255) NULL',

SUBSTRING(Data, N, CHARINDEX(',', Data + ',', N) - N)+' varchar(255) NULL')

FROM #header

JOIN dbo.Tally

ON N < LEN(Data) + 2 AND SUBSTRING(',' + Data, N, 1) = ','

ORDER BY ROW_NUMBER() OVER (ORDER BY N)

EXEC('ALTER TABLE #result ADD '+@sql)

ALTER TABLE #result DROP COLUMN dummycolumn

BULK INSERT #result FROM 'C:\TEMP\CSV1.csv' WITH (DATAFILETYPE = 'char',FIELDTERMINATOR = ',', ROWTERMINATOR = '',FIRSTROW = 2)

SELECT * FROM #result

Far away is close at hand in the images of elsewhere.
Anon.

Saravanan_tvr SSCarpal Tunnel Points: 4558 More actions · Answer 3

Thanks

But I got this Below error

Msg 208, Level 16, State 1, Line 7

Invalid object name 'dbo.Tally'.

I cant understand why u using the above table name..

WayneS SSC Guru Points: 95460 More actions · Answer 4

Saravanan_tvr (11/2/2010)
Thanks
But I got this Below error
Msg 208, Level 16, State 1, Line 7
Invalid object name 'dbo.Tally'.
I cant understand why u using the above table name..

You might want to check out this article: The "Numbers" or "Tally" Table: What it is and how it replaces a loop.[/url]

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

David Burrows SSC Guru Points: 65142 More actions · Answer 5

Ooops!

Thanks Wayne, forgot about that in my haste. :blush:

Far away is close at hand in the images of elsewhere.
Anon.

WayneS SSC Guru Points: 95460 More actions · Answer 6

David Burrows (11/2/2010)
Ooops!
Thanks Wayne, forgot about that in my haste. :blush:

Actually, with the OP having close to 200 points, I would have assumed (s)he would have known what one was also. But, this is also one reason that I code an inline tally table instead (with remarks to the article, and to build a permanent one for better performance) when answering posts.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Saravanan_tvr SSCarpal Tunnel Points: 4558 More actions · Answer 7

Thanks Now working fine, But In CSV file format, if one field like Product

descriptions having Multiple Comma Inside, but our SQL splitting them in to multiple column,

but We want to only one column with that Product descriptions, Is it possible, or we have to go any other file format..?

WayneS SSC Guru Points: 95460 More actions · Answer 8

Column delimiters need to be unique in the file. If one of the columns allows commas, change to something else (like the |).

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 9

Saravanan_tvr (11/3/2010)
Thanks Now working fine, But In CSV file format, if one field like Product
descriptions having Multiple Comma Inside, but our SQL splitting them in to multiple column,
but We want to only one column with that Product descriptions, Is it possible, or we have to go any other file format..?

Now we're only guessing. Please attach the first 10 rows of the file as a text file so we can help you properly.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Saravanan_tvr SSCarpal Tunnel Points: 4558 More actions · Answer 10

Thanks for all Replies,

We have changed into CSV format to Txt format with Pipline as Column Delimeter.. Now Bulk insert working fine..