SQL SERVER - Import CSV File Into SQL Server Using Bulk Insert - Load Comma Delimited File Into SQL Server

February 6, 2008 by pinaldave

This is very common request recently - How to import CSV file into SQL Server? How to load CSV file into SQL Server Database Table? How to load comma delimited file into SQL Server? Let us see the solution in quick steps.

CSV stands for Comma Separated Values, sometimes also called Comma Delimited Values.

Create TestTable

USE TestData
GO
CREATE TABLE CSVTest
    (ID INT,
                FirstName VARCHAR(40),
                LastName VARCHAR(40),
                BirthDate SMALLDATETIME)
GO

Create CSV file in drive C: with name csvtest.txt with following content. The location of the file is C:\csvtest.txt

1,James,Smith,19750101
2,Meggie,Smith,19790122
3,Robert,Smith,20071101
4,Alex,Smith,20040202

Now run following script to load all the data from CSV to database table. If there is any error in any row it will be not inserted but other rows will be inserted.

BULK 
INSERT CSVTest
        FROM 'c:\csvtest.txt'
            WITH
    (
                FIELDTERMINATOR = ',',
                ROWTERMINATOR = '\n'
    )
GO

Check the content of the table.

SELECT *
    FROM CSVTest
GO

Drop the table to clean up database.

SELECT *
    FROM CSVTest
GO

Reference : Pinal Dave (http://www.SQLAuthority.com)

Posted in Pinal Dave, SQL, SQL Authority, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, SQL Utility, T SQL, Technology | 151 Comments

151 Responses to “SQL SERVER - Import CSV File Into SQL Server Using Bulk Insert - Load Comma Delimited File Into SQL Server”

on February 7, 2008 at 2:10 pm rana

Good one.

It solves my problem.

Keep it up. Pinal.
on February 7, 2008 at 8:04 pm Alireza

Thanks for your great share.
on February 7, 2008 at 8:38 pm Branden

How about csv files that have double quotes? Seems like a formatfile will be needed when using BULK INSERT. Would like to see if you have another approach.
on February 8, 2008 at 5:56 pm Dnyanesh

Hi all,

I am getting following error in the bulk insert of the .csv file.

The bulk load failed. The column is too long in the data file for row 1, column 25. Verify that the field terminator and row terminator are specified correctly.

Please help me.

Dnyanesh
on February 9, 2008 at 2:25 am Dragan

What would be the process to import a CSV file into existing MS SQL database tables? I have a CSV file that contains data which is comma delimited that I need to import into a SQL production database which is being used by another application. The CSV file contains a list of defects that basically needs to be imported into the Defects table in our SQL database.

Thanks for all of your help in advanced.. This is a really good website to obtain SQL information.
on February 11, 2008 at 1:39 pm vonPryz

I had some problems with bulk inserts too. The data set to insert was somewhat large, around a few hundred megabytes, resulted in some two million rows. Not all data was required, so massaging was needed.

So I wrote a smallish C#/.NET program that reads the input file in chunks of 25,000 rows, picked appropriate rows with regular expression matching and sent results to DB.

String manipulation is so much more easy in about any general-purpose programming language.

Key components: DataTable for data-to-be-inserted and SqlBulkCopy to do the actual copying.

@Dnyanesh: Your input data is likely to not have correct column/row separator characters.

I’d guess you have a line-break as row separator. If that is the case, the problem is, in Windows CR LF (0×0a 0×0d) is often used, in other systems only CR or LF are used. Check your separator characters. Use a hex editor to be sure.

@Branden: Just use sed to change quote characters :-)

@Dragan: Use Import/Export wizard or write a program to do inserting like I did.
on February 13, 2008 at 1:39 am Scott

A CSV file can contain commas in a field ‘,’ as well as be used as the delimiter. How does this Bulk import handle these circumstances? Does it handle all the CSV standards?

@vonPryz: See Example Below (don’t just replace quotes)

Example CSV lines with format (Name,Address):

Scott,123 Main St
Mike,”456 2nd St, Apt 5″

More on CSV files can be found here
http://en.wikipedia.org/wiki/Comma-separated_values
on February 13, 2008 at 12:02 pm Kumar

Thanks for all in this forum,

I have used BULK INSERT command load CSV file into existing SQL server table. It is executing sucessfully withougt giving any error but only concern is it is inserting every alternative records.
BULK INSERT EDFE_Trg.dbo.[tblCCodeMast]
FROM ‘c:\temp\Catalogue.txt’
WITH
(
FIELDTERMINATOR = ‘,’,
ROWTERMINATOR = ‘\n’
)
GO
this is my code. Please help me on this.
With advance thanks…
kumar
on February 18, 2008 at 9:58 pm Hassan tga

thank you very mush for this,
it willy helped me.

regards
on February 20, 2008 at 6:56 am Martin

Hi,

I need to import a significant number of Latitude and Longitude data into one of my SQL tables… (running SQL2005)

Tried the above, but got you do not have permission to use Bulk Load statement.

Logged in as administrator… any thoughts?

Martin
on February 20, 2008 at 8:56 am Martin

ok…solced that issue…logged in using Windows Authentication, and gave my SQL admin the rights for BULKINSERT

SQL code now runs with no errors… and if I do a select * from the table, it shows the field headings, but no data … 0 rows affected

Any thoughts? This one has me stumped… no error message leaves me nowhere to look

Cheers
on February 21, 2008 at 2:55 pm Siva

Hi Pinal

Please help me
Iam using sql server 2005,..I want to do a similar operation..

I have my file in C:\Test.txt. Also I create the table in my DB.

BULK INSERT CSVTest
FROM ‘c:\Test.txt’
WITH
(
FIELDTERMINATOR = ‘\t’,
ROWTERMINATOR = ‘\n’
)
GO

When I run this …am getting error saying…

Cannot bulk load. The file “c:\Test.txt” does not exist.

infact, I have the file given in the path.

Thanks,
on February 23, 2008 at 9:37 pm John Doe

@Siva
SQL Sever 2005 must have access rights to the file (“c:\Test.txt”). Make sure the file is accessible for the database account
on February 27, 2008 at 11:37 am Sumit pal Singh

How to import data from a Excel Sheet using “Bulk Insert” command?
on February 28, 2008 at 6:38 pm JB

Will this method work for SQL Server Compact Edition?
on February 29, 2008 at 9:21 pm Abdul

Panel, Excellent Job, Congrats.
Please help me for this….
I have on line in .csv file as …
863807129312681,G052360310001

I want to do BulkInsert as….
Column1: 86380 71293 12681
Column2: ;
Columns3: G052360310001

please help me…..
on March 3, 2008 at 5:22 pm ViSheN

Great stuff, codes perfect, helped me alot lot
on March 4, 2008 at 4:01 pm sabarish

hai dave,
i want to iterate the record from collection of records without using cursors. how can i achieve this
on March 5, 2008 at 9:30 pm Confused

Hi,

What if my Table has 4 Columns and the CSV file has 3 Columns and I wanna the last column of the Table to be a variable?
on March 6, 2008 at 12:25 pm Jerome

THANK YOU!
on March 7, 2008 at 5:05 am LineByLine

Hey there, thanks for the great article.

I have one question, is it possible to read a CSV file line-by-line without using bulk import?

It’s just more convenient in my situation so please let me know if its a possible option,

Thank you
on March 9, 2008 at 12:55 am Abdul

Please help me for this….
I have on line in .csv file as …
863807129312681,G052360310001

I want to do BulkInsert as….
Column1: 86380 71293 12681
Column2: ;
Columns3: G052360310001

please help me…..
on March 10, 2008 at 6:28 pm anusha

i want to get data from CSv file into sql database.suppose the CSV file changes that is updations will be done on CSV file for every 8 hrs or 4 hrs like that.Then i have to get the updations reflected in my sql datbase also. Then how it is possible ????
on March 11, 2008 at 7:41 am different scott

you never answered scott’ question above. thoughts?

his question:

A CSV file can contain commas in a field ‘,’ as well as be used as the delimiter. How does this Bulk import handle these circumstances? Does it handle all the CSV standards?

@vonPryz: See Example Below (don’t just replace quotes)

Example CSV lines with format (Name,Address):

Scott,123 Main St
Mike,”456 2nd St, Apt 5″
on March 14, 2008 at 2:22 pm Krish

I am using SQL Express Edition to Run the code..
But I am Getting the error
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near ‘‘’.
Msg 319, Level 15, State 1, Line 4
Incorrect syntax near the keyword ‘with’. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.

Please help me to resolve the error…

Thanx in advance…
on March 17, 2008 at 7:58 pm Scott Kellman

What happens to the rows that fail? I wuold like to save those off to allow the user to fix them….
on March 17, 2008 at 9:25 pm Lakeside

to different scott

I have fields with commas as well. Convert the csv (using Excel) to a tab-delimited text file and then use the ‘\t’ delimiter instead.

to Abdul

I have used Excel to break longer fixed-length columns into smaller columns. I believe this will help you. Open the file in Excel, add two columns after the first column. use the Data –> Text to Columns option. you can break the data into additional columns and then save the file back out to a csv.
on March 18, 2008 at 5:14 am Dave M

I have over 1,200 separate csv files to import and have two issues where I could use some help.

First, all of the files contain date fields but they are in the standard MM/DD/YYYY format. Is there a way to import them and then change them to an acceptable format for SQL Server?

Next, each file name is unique and I have created a temporary table that has the directory and file name. Is it possible to nest a query something like this:

BULK INSERT MyTable
FROM (Select FileName from Files;)
WITH
(
FIELDTERMINATOR = ‘,’,
ROWTERMINATOR = ‘\n’
)
GO
on March 18, 2008 at 8:25 am Dave M

As an addendum, I discovered that some columns are in scientific notation so I need to change all numeric columns in the CSVs to be numeric with 6 decimal
on March 21, 2008 at 9:02 am Dave M

Did it. I used a combination of tools:

1. Lots of brute force - over 20 hours formatting, saving and converting the data. There has to be a more elegant solution but I couldn’t find it.

2. Excel 2007 because it can open a csv file that has over 200,000 rows. I loaded 25 files at a time into a worksheet (max of computer resources), reformatted the columns in bulk and used a nifty VBA script to write each worksheet out to a separate csv with the name of the worksheet.

3. Found a great procedure that uses XP_CMDSHELL and the BCP utility in SQL 2005 that loads all files located in a specified directory. Loaded over 1,300 files in less than 30 minutes.

Now daily maintenance is loading one file a day.

D.
on March 27, 2008 at 7:03 pm ali

im getting the foll err can u help
ulk Insert fails. Column is too long in the data file for row 1, column 1. Make sure the field terminator and row terminator are specified correctly.
on April 1, 2008 at 7:46 pm Dave B

Hi,
Does BULK INSERTing into an existing table that has an index on it take longer than inserting into an existing table without an index?

Cheers
on April 2, 2008 at 12:17 pm Puneet Narang

Hi Pinal,

I was wondering how will i insert thousands of row from excel sheet.
But I converted the file to csv (comma seperate file ) and then usiing your bulk import statement i created all of my rows into my Table.

This was really wonderful

Thanks,
Keep it up

Happy Coding!!!
Puneet
on April 2, 2008 at 8:52 pm George

I’m doing exactly what is said above but when I try the
BULK
INSERT CSVTest
FROM ‘c:\csvtest.txt’
WITH
(
FIELDTERMINATOR = ‘,’,
ROWTERMINATOR = ‘\n’
)
GO

I’m recieving the following error??? I was wondering could someone help me with this or is it just some tiny error that I can’t see, help would be really appreciated!!

Msg 102, Level 15, State 1, Line 3
Incorrect syntax near ‘‘’.
Msg 319, Level 15, State 1, Line 4
Incorrect syntax near the keyword ‘with’. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.
on April 2, 2008 at 9:43 pm Sheldon

Hi,

I was using BULK insert to import a csv file to a database table(following the original example by Pinaldave in the beginning of this email stream), but got errors. I have granted “Everyone” full control to this file.

Thanks so much,

Sheldon

============================================

BULK INSERT dbo.CSVTest
FROM ‘c:\csvtest.csv’
WITH
(
FIELDTERMINATOR = ‘,’,
ROWTERMINATOR = ‘\n’
)
GO

Msg 4860, Level 16, State 1, Line 1
Cannot bulk load. The file “c:\csvtest.csv” does not exist.
on April 3, 2008 at 2:30 am pronnie

i have a text file that is not comma or tab delimited. the data needs to be separated using fixed width counts. ie: characters 1-3 go into column1. characters 4-12 go into column 2 and so on. can this method be adapted to handle pos statements?
on April 5, 2008 at 3:35 pm megha

Hi,
I Just want to Thank’s for this Code realy help me a lot.

and i want to how to inport a .csv file through imprt command.
on April 7, 2008 at 5:04 am alaxi

I ran code in sql 2000 worked fine. During cut and paste it has problem with ‘ ‘. I had to re-enter from my keyboard. Also like to suggest for those it doesn’t run, please name extension .csv in than .txt for sql 2000.
on April 7, 2008 at 5:18 am Navalkishore Arya

Thanks it works!

Also just wanted to point out that the query you said uses ” ` ” instead of ” ‘ ” shouldn’t you change it?
on April 8, 2008 at 12:56 pm nur

Hi Pinal

Please help me
Iam using sql server 2000,..I want to do a similar operation..

I have my file in C:\Test.txt. Also I create the table in my DB.

BULK INSERT CSVTest
FROM ‘c:\Test.txt’
WITH
(
FIELDTERMINATOR = ‘\t’,
ROWTERMINATOR = ‘\n’
)
GO

When I run this …am getting error saying…

Cannot bulk load. The file “c:\Test.txt” does not exist.

infact, I have the file given in the path.

Thanks,
on April 12, 2008 at 11:00 am Waseem

What is the Max size that SQL server can read and insert into database table? Please help me for this because i am inserting up to 4MB file into a table using .NET code, it is not inserting records in the table. Up to 3 MB files are inserting into the table.

Thanks
on April 18, 2008 at 4:29 pm Rahul

Hi,
i have to upload data from a fixed width text file to sql server 2000.

data in file is like rahulankilprogrammer

we have two create a table of three column fname,lname and job, first 5 letters will go in first column , next 5 letters will go in second and than next 10 letters will go in third column .

please help me to do this ,as it is not a CSV file.

this file come to us on daily basis, daily we hav to upload it on sql server.

pls pls help me out ASAP

ThanX in advance
on April 25, 2008 at 8:42 pm Teo

Hello,
I have a proplem with BULK in stored procedures.
I want to do a BULK from a file (.CSV) wich name changes every day. So I have an attribute “@FileName” in order to change the name every single day.

BULK INSERT dbo.TableName
FROM @FileName
WITH ( FIELDTERMINATOR=’;', ROWTERMINATOR=’\\n’)

But it gives me an error telling that there’s a sintax error after FROM. I have tried with all kind of punctuation (’,”,`,´,+)

It would be gratefull if some one could answer to my question.

Thanks in advance
on April 29, 2008 at 5:40 pm Vijay Ananth P

Hi,
I try that code. But my saved location is D drive.
But i shows one error. That is Incorrect syntax near ‘ ‘ ‘.
Give the solution.

Thanks,
Vijay Ananth
on April 29, 2008 at 6:09 pm rajesh

hi
it solved my problem
can u please tell how can i schedule stored procedure in sql server2005

thanks
rajeh
on May 1, 2008 at 4:31 am MB

Hello,

I’m using bulk insert and everything works fine but I cannot get latin characters to import properly. For example, my flat data file contains Sertãozinho but the imported data displays Sert+úozinho. The “a” character changes to something with plus sign. I tried using nvarchar datatype but it did not help.

Here is my code:

DROP TABLE [DumpData]
GO
CREATE TABLE [dbo].[DumpData](
[DataField1] [varchar](255) NULL
ON [PRIMARY]

–import data using bulk insert
DECLARE @bulk_cmd varchar(1000)
SET @bulk_cmd = ‘BULK INSERT [NalcoSAPDump]
FROM ”c:\DataImport\import.dat”
WITH (FIELDTERMINATOR = ”;”,ROWTERMINATOR = ”’+CHAR(10)+”’)’
EXEC(@bulk_cmd)

Thanks!!
on May 5, 2008 at 12:11 pm Teo

Hello,

thanks for your answer, Vijay Ananth. :D

Does anybody know something about incremental bulk? It’s just the “normal” bulk insert or there is another thing that I have to add to the bulk sentence?

I want to do a BULK from a csv file. Firstly I do a Bulk of the whole file.

But that csv file changes every time, and in order to have those new rows in my DB I would like to know if there is another way to bulk just the new rows. With that incremental bulk or something else.

At the moment I’m doing it with bulk every second. So I don’t know if somebody that is writting the file can get an error while the bulk is working.

Any suggestions will be wellcome.

Thanks
on May 6, 2008 at 3:36 pm Manvendra Singh

insert Excel file record in a table + SQL SERVER + C#.net
on May 7, 2008 at 12:16 am dminer

Thanks, helpful post.
on May 8, 2008 at 3:36 am Panagiotis Lepeniotis

Pinal one more time you saved my life, althought it is the first time that I submit you a commend! Mate you are the best! I hope to reach your level one day!

Panagiotis Lepeniotis
MCDBA,
MSc Database Professional Student!

As for the dataMate who asked about the quotations, I will suggest to edit the csv file, at least thats what I did and worked!

Cheers
on May 8, 2008 at 5:25 pm Deepa

Hi Pinal,

i want to batch upload from excel to sqlserver where i want to insert into multiple table as dependency is there between tables. How to do it? Is it possible to do in c#?
Thank you in advance. I would be very thankful if I get answer quickly as I’m badly in need of it?
on May 15, 2008 at 11:46 pm LK

Dave B
Regarding your question:
Does BULK INSERTing into an existing table that has an index on it take longer than inserting into an existing table without an index?
The answer is no. We tried it out on a large Table (10 mil rows) into which we are loading even more rows. WITHOUT the index, the application timed out. With the index, it loaded in fine time.
on May 27, 2008 at 2:09 pm Website design Melbouren

thanks a lot!

exactly what i was looking for.
on May 27, 2008 at 3:52 pm Zeenat

BULK INSERT CSVTest
FROM ‘c:\CSVTest.txt’
WITH
(
FIELDTERMINATOR = ‘,’,
ROWTERMINATOR = ‘\n’
)
GO

I got the following error
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near ‘‘’.
Msg 319, Level 15, State 1, Line 3
Incorrect syntax near the keyword ‘with’. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.

Can you please let me know what needs to be done in this case?
on May 27, 2008 at 6:22 pm Elavarasan

Can we do conversions while doing Bulk inserts? (like string to decimal conversions, string to date, string to time etc..,)
Any suggestions is highly appreciated! :)
on May 27, 2008 at 7:49 pm Anke

I also get the error: Msg 4860, Level 16, State 1, Line 1
Cannot bulk load. The file “c:\csvtest.txt” does not exist.

How do I grant SQL access to the file?
I am running on SQL2005 on an instant I created for myself.

I tried to change the file to have access approved for ‘everyone’, but still this didn’t make any difference. I still get the file doesn’t exist error.

Thank you,
Anke
on May 27, 2008 at 9:07 pm Anke

I was able to resolve.
Thank you!
on May 29, 2008 at 6:46 pm wim

I have the same problem as Kumar. Runs without errors but only every second (alternative) record gets inserted. I have tried different rowterminators, but the problem remains.

bulk insert Gen_ExcelImports from ‘C:\Software\invoices\April2008\csvfile.txt’
with ( FIELDTERMINATOR = ‘,’,FIRSTROW=2,ROWTERMINATOR = ‘\n’)

Any help would be greatly apprciated. Thanks in advance!
on June 3, 2008 at 3:05 am Plight

I have imported a flat (csv) into SQL Server 2005 using the wizard. I have field called Product Code which has 3 numbers e.g. 003. I imported it as text (default). When I open the ipmorted table, i have a plus sign next to the Product Codes e.g. +003. Why is it there and how do i get rid of that plus sign? I just want it to show 003.

It is particularly annoying and I want to concatenate the Product Codes with other codes to create an ID.

Please Help
on June 4, 2008 at 4:38 am hi

Hola your example i good

How conncted remote computer for read file in this remote computer.

for example
BULK
INSERT dbo.tblPsoArchivosCalificacion

FROM ‘\\10.63.200.28\Paso\LibroDatos.csv’

WITH
(
FIELDTERMINATOR = ‘,’,
ROWTERMINATOR = ‘\n’
)

the question is?

i need net use userid and password to access remote computer.

sorry but my english is bad.
on June 11, 2008 at 4:54 pm Solomon

Hi All,

Appreciate help on below

I want to do conditioal sum in an SQL table as follows

Column1 column2 column3 column 4 column5
a b zzz jjj 4
b a yyy rrr 7
a a fff hhh 3
a b ccc kkk 2
b a kkk ttt 7
b a ggg lll 4
a b yyy kkk 3

what i want to do is,

For values of column1 = ‘a’ then add(sum) column 5 by grouping by column3. for values of column2 = ‘b’ then add (sum) co lumn5 by grouping by column4.

please help!
on June 13, 2008 at 2:32 am Tiga

Krish

Replace ` with ‘ (single quotes)
on June 18, 2008 at 10:34 pm Max

When I run this particular stored proc all of my data comes out in the table with double quotes around it. How would I get rid of these double quotes?

Thanks
on June 19, 2008 at 4:29 pm Tamil

Simple and Suberb information.

Thanx.
on June 20, 2008 at 12:38 am rohan

Hi

Can you tell me if it is possible to execute tis code with sqlcmd in VB.NET.

I am using express editions of VS 2008 and SQL server 2005
on June 21, 2008 at 4:46 pm Haushila

I get the error: Msg 4860, Level 16, State 1, Line 1
Cannot bulk load. The file “c:\csvtest.txt” does not exist.

OR

Cannot bulk load. The file “c:\csvtest.csv” does not exist.
on June 24, 2008 at 2:30 am Arunabh

Thanks, it was a very simple solution to meet my requirement.
on June 25, 2008 at 3:30 am Cathy

I’m using the bulk insert statement

BULK INSERT Received
FROM ‘\\Admfs1\users\CPurnell\Responses\C0009348.iff’
WITH
(
FIELDTERMINATOR =’|',
ROWTERMINATOR =’ {CR}{LF}\n’
)

This works great for one file. But what I really need to do is bulk insert all .iff files from the Responses folder.

Any suggestions?
on June 27, 2008 at 10:25 am sathya

Hi

I tried to insert the data in the table using INSERT Bulk Query.

I used the Query like this:

BULK INSERT insertdatdata FROM ‘D:\mani_new\standard.Dat’
WITH (
DATAFILETYPE = ‘char’,
FIELDTERMINATOR = ‘ ‘,
ROWTERMINATOR = ‘\n’
)
GO

The file is there in correct path.
But i got the following error.

Cannot bulk load because the file “D:\mani_new\standard.Dat” could not be opened. Operating system error code 3(The system cannot find the path specified.).

please give me some solution.

Its urgent.

Thanks,
Sathya.
on June 27, 2008 at 10:57 am geetha

I got the error:

Msg 4860, Level 16, State 1, Line 1
Cannot bulk load. The file “D:\test.txt” does not exist.

Plz give the solution.

Thanks
Sathya.
on June 30, 2008 at 8:14 pm Rajat

How do we create a table in SQL server through VB.NET code by importing the schema from a TEXT file?
on July 1, 2008 at 3:11 pm AJAY DHIMAN

g8

its a true solution
on July 1, 2008 at 3:16 pm AJAY DHIMAN

hi,
sathya

i think u r using sql server client verson. on ur pc
so u have to put the text file on ur data base server’s
“D:\mani_new\standard.Dat” paths.

just do it …………
on July 1, 2008 at 6:19 pm sai

hi pinale,

While working on it i m getting the error file doesnt exist can u suggest me any thing to be done

Thanks
on July 2, 2008 at 12:27 am Anvie

Hi,

Thank you for sharing your code, it really works well ;-)

I tried to open a text file in web but it did not work. Do you have any idea to work it around?

BULK
INSERT bod.temp
FROM ‘http://www.test.com/test.txt’
WITH
(
FIELDTERMINATOR = ‘|’,
ROWTERMINATOR = ‘\n’
)
GO

Thanks!
on July 4, 2008 at 12:01 pm ram r

hi,

can u tell how to load the particular field from dastination file into database…
i am using simple notepad files which is contain 46 columns…

so i want to read the input file and insert only three columns (column 1, column 15,column 46) into the tables…

is there any commands existing for doing this type of file handling…

i m using sqlserver 7

plz help me…
on July 8, 2008 at 9:52 pm Amel K

I have to import a csv file into an existing table that has different column names from the originating one. How do I match differently named fields ?

Thank you
on July 9, 2008 at 10:18 am masky

hi, i want to upload a cvs file frm asp.dot page and the file should automatically extract into sql database table. the table is created, plz help out…
on July 10, 2008 at 6:38 am Mikez

I just wanted to say that this solution is so quick and easy. I found a ton of other way too complicated examples. This just cuts right to the quick and gets the job done.

Thanks for this excellent code snippet!

Mike
on July 15, 2008 at 9:48 am Nick

Hi Pinal,

I am attempting to use the bcp utility (via command prompt) in order to create a comma-separated text file named Inside Sales Coordinator. Here is the new table created in the Northwind database:
CREATE TABLE [dbo].[NewEmployees](
[EmployeeID] [int] NOT NULL CONSTRAINT [PK_NewEmployees] PRIMARY KEY,
[LastName] [nvarchar](20) NOT NULL,
[FirstName] [nvarchar](10) NOT NULL,
[Title] [nvarchar](30) NULL,
[TitleOfCourtesy] [nvarchar](25) NULL,
[BirthDate] [datetime] NULL,
[HireDate] [datetime] NULL,
[Address] [nvarchar](60) NULL,
[City] [nvarchar](15) NULL,
[Region] [nvarchar](15) NULL,
[PostalCode] [nvarchar](10) NULL,
[Country] [nvarchar](15) NULL,
[HomePhone] [nvarchar](24) NULL,
[Extension] [nvarchar](4) NULL,
[Notes] [ntext] NULL,
[ReportsTo] [int] NULL,
[PhotoPath] [nvarchar](255) NULL
) The new comma-separated text file should contains the following columns from the NewEmployees table: EmployeeID, LastName, FirstName, HireDate (date part only; no time), and Extension. Only those employees with a Title of “Inside Sales Coordinator” should be returned.
Here is what I came up with so far:
bcp “select EmployeeID, LastName, FirstName, HireDate, Extension from Northwind.dbo.NewEmployees” out C:\InsideSalesCoordinators.csv –c –t , –T -S SDGLTQATEAM\SQLExpress can you give me a little insight on how to populate the .csv file. Thanks
on July 15, 2008 at 8:03 pm PSilva

This Procedure can help in ASP.NET

CREATE PROCEDURE ImportFile
@File VARCHAR(100)
AS

EXECUTE (’BULK INSERT TableName FROM ”’ + @File + ”’
WITH
(
FIELDTERMINATOR = ”;”
, ROWTERMINATOR = ”\n”
, CODEPAGE = ”RAW”
)’ )

/*
EXEC dbo.ImportFile ‘c:\csv\text.csv’
*/

C# Code

SqlConnection cnn = new SqlConnection(
string.Format(@”Data Source=.\SQLEXPRESS;Initial Catalog=DB_Name;Persist Security Info=True;User ID=;Password=” ));

SqlCommand cmd = new SqlCommand(”ImportFile”, cnn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(”@File”, SqlDbType.VarChar).Value = txtFile.text;
cnn.Open();
cmd.ExecuteNonQuery();
cnn.Close();
on July 16, 2008 at 7:25 pm Jignesh

Hi,

Most of the time, I search this site for answers of my SQL questions. And here I have one more….

I am using SQL Server 2005 Enterprise Edition on Windows Server 2003.

I wanted to load Double quote enclosed, comma delimited text file into SQL Server 2005. There was no header record in the file.
I used “SQL Server Import Export Wizard” to perform this task. I chose Data Source = Flat File Source, selected .csv file using Browse button, specified Text qualifier as double quote (”). There are total 17 fields in the upload file and under Advanced section they were named from “Column 0″ to “Column 16″. The default length of each column was 50. I specified length of the “Column 5″ as 200 because I know the length data in that column was more than 50. I have checked rest of the columns and made sure that length 50 was enough for other fields.
I clicked on Finish to start uploading and I got following error:

————————————-
Error 0xc02020c5: Data Flow Task: Data conversion failed while converting column “Column 4″ (26) to column “Column 4″ (136). The conversion returned status value 2 and status text “The value could not be converted because of a potential loss of data.”.
(SQL Server Import and Export Wizard)

Error 0xc0209029: Data Flow Task: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The “output column “Column 4″ (136)” failed because error code 0xC020907F occurred, and the error row disposition on “output column “Column 4″ (136)” specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure.
(SQL Server Import and Export Wizard)

Error 0xc0047022: Data Flow Task: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component “Data Conversion 1″ (112) failed with error code 0xC0209029. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure.
(SQL Server Import and Export Wizard)

Error 0xc0047021: Data Flow Task: SSIS Error Code DTS_E_THREADFAILED. Thread “WorkThread0″ has exited with error code 0xC0209029. There may be error messages posted before this with more information on why the thread has exited.
(SQL Server Import and Export Wizard)

Error 0xc02020c4: Data Flow Task: The attempt to add a row to the Data Flow task buffer failed with error code 0xC0047020.
(SQL Server Import and Export Wizard)

Error 0xc0047038: Data Flow Task: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component “Source - Giftlink_2008_csv” (1) returned error code 0xC02020C4. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure.
(SQL Server Import and Export Wizard)

Error 0xc0047021: Data Flow Task: SSIS Error Code DTS_E_THREADFAILED. Thread “SourceThread0″ has exited with error code 0xC0047038. There may be error messages posted before this with more information on why the thread has exited.
(SQL Server Import and Export Wizard)
————————————-

I have checked “Column 4″ length and it was 50 which was much enough for data in that column.

Is anyone has any idea on this?

Any help would be appreciated.

Thank you in advance.
on July 21, 2008 at 5:04 pm iftekhar

Please help me
Iam using sql server 2000,7.0 ..I want to do a similar operation..
on July 25, 2008 at 1:06 am vijay

Hi there

I’m facing an issue in Bulk insertion. Whenever i query the command -
BULK INSERT vijay FROM ‘c:\vijay.csv’ WITH (FIELDTERMINATOR = ‘,’, ROWTERMINATOR = ‘\n’);

sql throwing an error “ORA-00900: invalid SQL statement”.

Could anyone help me out in this issue?

Thanks in advance.
on July 25, 2008 at 12:53 pm shahbaz

i am facing an issue that is i have text file like below format

AAAAAAAAAAAAAAAAAAAAA BBBBBBBBBBBBB CCCCCCCCCCCC
DDDDDDDDDDDDDDDDDDD EEEEEEEEEEE

AAA BBB CCC DDD EEE FFF
121 john Albert 1-1-08 ok No
121 john Albert 1-1-08 ok No
121 john Albert 1-1-08 ok No

in this format AAA,BBB,CCC…FFF this are heads of column Now
i want to insert this text data of each head in Sqlserver database table in separate column mens AAA head data sould be save in AAA column in SqlServer Databse
on July 25, 2008 at 2:01 pm charles

hi..my text file is quite complicated, the field terminator only can use by ‘length’ to seperate…please refer the text file i copy paste as example

VENDORID INVNO INVDATE PONO PODATE
S0016 TB080767924/07/08BD-01/07/08

the fieldterminator surely cannot use by space bar…can i use the length to seperate the field? if yes…please guide how to…

thanks and regards
on August 1, 2008 at 3:24 am nicole

Pinal, nice and simple code, but I cannot solve the problem

——————————————————————————

Meldung 4860, Ebene 16, Status 1, Zeile 2
Massenladen ist nicht möglich. Die Datei “c:\csvtest.txt” ist nicht vorhanden.

(0 Zeile(n) betroffen)

FILE DOES NOT EXIST

——————————————————————————

There are many users with the same issue, can you advice what I need to do

many thanks
on August 1, 2008 at 10:18 am Rubavathi

Hi,

How to import from CSV to new table? I tried with OPENROWSET . but it shows error if there are any special characters in the file name(say for example ‘-’).
on August 2, 2008 at 3:23 am Robert

I was getting the same problem as other here with the error reading

The file “c:\Test.txt” does not exist.

Looks like the the SQL commands will only work when the file itself is on the actual SQL server box. I was getting this error using SQL Admin on another box and as soon as I moved the file to the server itself, I could run the command from any machine but would still reference the file on the actual SQL box. Enjoy!
on August 5, 2008 at 1:30 am Saman

I’m trying to do an import from a text file using the BULK INSERT. The text file is separated by fixed width (no delimiters).

Can anybody give me an example query for this?
on August 5, 2008 at 10:38 am Sultan

Hi Pinal,

How to create a CSV or Text file from a Stored Procedure?

Thanks

Sultan
on August 5, 2008 at 8:41 pm Saman

I found how to do this by inserting all the data into one column and then using the substring function to split the columns. It worked…
on August 6, 2008 at 12:54 am hrishikesh

hi pinal
i have got the same problem as charles. my text file has no spcific delimiter. the columns can be only separated in terms of length.how can such text filebe loaded?

thanks
on August 8, 2008 at 4:56 am Ron

I’m wanting to upload a csv file into phpmyadmin, but you last me after you said, “February 6, 2008″!

Can you give me any help on a 5th grade level?

Ron
on August 8, 2008 at 1:53 pm Chris

Firstly, thanks for this article, it is great to see the whole code posted rather than individual bits all around.

Please could you assist me with this scenario:
I have 6 columns created in the CSVTemp table, the last two columns could have null values so I have specified this using LastName VARCHAR(40)NULL

When I execute the query, the next record is placed in the first of the NULL columns instead of the next line e.g.:
File contains information: 1,2,3,4,5
20,30,40,50,60,70

CSVTemp table Col1 Col2 Col3 Col4 Col5 Col6
1 2 3 4 5 20

I am using your example the ROWTERMINATOR = ‘\n’ but yet sql still reflects this record as above. How to I inform SQL to insert that 20 into the column 1, rather than continuing?
on August 11, 2008 at 11:03 pm Hafz

Okay here is my dilemma…

I have multiple csv with data in each file. They are connected via foreign keys (with each table having its on primary key). I want to load these tables into an Oracle database using SQL. If i know the order in which the tables should be loaded how do i go about loading the tables into the Oracle db.

E.g.

// CSV 1
Fields = ID_NUMBER | Name | Date
Data = 12345 (PK)| Hafiz | 12-12-2008 |

// CSV2
Field = ID_PRODUCT_ NUMBER | ID_NUMBER | Colour | Type
Data = 54321 (PK) | 12345 (FK) | Black| Car |

The Oracle DB has the tables setup with the same field name. I want to load CSV 1 first into Oracle Table 1 and then CSV 2 into Oracle Table 2 (must be in this order or will run into referential integrity issues.

The next step is to make this applicable to csv files with 100 lines of data each (probably using some sort of iterative process). Can you help??????????
on August 14, 2008 at 11:14 am Emeka

@MD
The data you imported is stored and sorted based on the collation setting you choose when sql server was installed or when the database was created.

Read more about collation settings, then you will be able to troubleshoot the problem.
on August 14, 2008 at 11:16 am Emeka

@MB
The data you imported is stored and sorted based on the collation setting you choose when sql server was installed or when the database was created.

Read more about collation settings, then you will be able to troubleshoot the problem.
on August 17, 2008 at 4:33 am verma

Hi,

This article was very helpful to me. However I do have one question, how would you modify this script if you have a text file that doesn’t have set number of columns. E.g.:

row1: col1,col2,col3
row2: col1,col2
row3: col1,col2,col3,col4
row4: col1,col2,col,3,col4,col5

I tried to load this file using the above script, i was able to load the data but the data couldn’t be loaded correctly.

Help please.

Thanks
on August 21, 2008 at 3:58 pm Harminder Singh

Hi ,
those who are facing problem that the text file does not exists
copy ur file to server where ur sql server exists
nice article
on August 22, 2008 at 6:58 pm MaryO

Hi,
Great forum! I am encountering an issue that I have always been able to overcome, but this time it has me stuck. I have a .txt file that is comma delimited. There are some data issues in the file. Normally, I would import all records into one column, then parse from there. I am using {LF} as both the column delimiter and record delimiter, so that all data will go into my table that has 1 large (nvarchar (4000)) field. When the import hits line 62007, I get an error message “Column Delimiter Not Found.” I can look at the text file in jujuEdit and see the {LF} at the end of each column.

Has anyone encountered anything like this before?

Thanks!
on August 25, 2008 at 10:01 pm Melissa

The command is run from the sql server, you can put the file anywhere on the network and you should no longer get the error that the file does not exist.
on August 25, 2008 at 10:02 pm Melissa

Did anyone ever get a solution to the text qualifiers (double quotes in the text)?
on August 26, 2008 at 3:19 pm brammam

Hi Melissa

I tried to insert text which has double quotes in that text, its executed successfully with out any error.

my text file data is like this

12 I need “my” car brakes to be changed
14 ertetr etert ert etert
15 i need a land line phone in my home

if its not the case can you describe problem details.
on August 28, 2008 at 6:06 pm Subhrangshu

Hi all!

my problem is that during bulk insertion, it is showing that
“You do not have permission to use the bulk load statement.”
so can anyone tell me how to give permission for bulk insertion.
on August 30, 2008 at 7:12 pm Shimu

Thanks .it’s realy helpfull for me
on August 30, 2008 at 7:26 pm Shimu

now i do it from server side. i want to do t from frontant. i use VB.NET 2005 and SQL SERVER 2005.From a form i want to do the work when i click on butto. now where i write the code.
on September 5, 2008 at 7:36 pm Mandar

Pinalbhai!

It has helped a lot. Especially since Microsoft has not provided SSIS in SQL Server 2005 express edition.
on September 10, 2008 at 3:41 pm GV

Hi All,

I want to read the the csv file and insert into table using sql server 2005. I have code below. This will do that insert using OPENROWSET function. My pblm is when i give th .csv file with out header it throws an exception like ‘Duplicate column names are not allowed in result sets obtained through OPENQUERY and OPENROWSET. The column name “NoName” is a duplicate.’ can anyone solve this pblm

Dim strSQL As String
strSQL = “Select * ” & _
” INTO ” & DATABASE & “.dbo.[List_staging] ” & _
“FROM OPENROWSET(’MSDASQL’,'Driver={Microsoft Text Driver (*.txt; *.csv)}; DEFAULTDIR=C:\VoicenetSQL\project\tampa\Politic\” & projectfile & “; Extensions=CSV; HDR=No;’,'SELECT * FROM at1008.csv’) ”

Thanks in advance..
G.V
on September 12, 2008 at 3:11 am PeterB

This may be the best tech blog I have ever seen. Keep up the good work!
on September 12, 2008 at 9:50 am Nadeem

Hi Pinal,

Thank’s a lot. It has solved my problem.

Regards,
Nadeem.
on September 15, 2008 at 11:58 am Minchala Murali

Hi,

I need to restore the data from SQL server 2005 to 2000. In my data there are many languages(it means that data has mixure of all characters).

Are datatypes similar in SQL server 2000 and 2005? am receiving the problem datatype problem..

can any one suggest me how do i restore it?

Regards,
Minchala
on September 20, 2008 at 1:29 am Mario

excellent!!!!!!
on September 20, 2008 at 2:32 am Michael Arnold

Just wanted to say thanks. This code worked perfectly for a simple program i wrote to run at a scheduled time. It saved me so much work from having to manually parse the CSV!
on September 25, 2008 at 1:42 am Edson

Hi, thanks for the post, I noted that the size of the table is too big you can reduce it?

grateful,

Edson
on September 27, 2008 at 4:44 am Mark

Hi Dave,
You are amazing, thanks for sharing your knowledge.

I need a stored proc to import data from a .txt file to a table in sql server 2000 but I need to do it using BCP command and the stored proc should also perform error handling, if possible could you please post the stored proc which performs this job.
on September 27, 2008 at 11:58 am Oyen

Thanks! I’m glad your blog always comes up in one of the first google hits. You help a lot :)
on October 7, 2008 at 12:24 am Mehdi Anis

It is an excellent example indeed .Thanks.

I am trying to load a CSV file from a VB .NET 2.0 app. Since the CSV file is in user’s local drive, I can not use BULK INPUT method, can I?

Bulk Input method requires the CSV file to be present in the SQL Server’s local drive/folder, right?

I will appreciate if you can comment on my assumptions. Thanks.

Regards,
Mehdi Anis
on October 10, 2008 at 2:16 am Jannable

Dude. You just saved my life.
on October 15, 2008 at 7:59 am wad3v4

Hi
I’m having some problem something similar to what you have explained, bt the only difference is that my data in csv file is not seperated with commas, its all chunk together bt i want my output to be like yours. Any idea how i may be able to do that?
Thanks in advance. =)
on October 16, 2008 at 11:52 am raghav

Hi,
Excellent Code to import data from text file into Database
Thanks.
on October 16, 2008 at 12:10 pm Hemanth

HI dave,

i need to store a file content in database, it could be a text file or image file how can i??????
on October 17, 2008 at 9:32 pm Bhavin

HI,

The above code works great for me, But I have several problem

1] I have the CSV file with 1155585 records the format of data is

“Bhavin”,”Mumbai”,”10/2/2004″,1,2,3,4,5
“Bhavin12″,”Mumbai12″,”10/2/2005″,1,2,3,4,5

so what i did i replace all string which contains Quotes to null i.e nothing

now the data looks like

Bhavin,Mumbai,10/2/2004,1,2,3,4,5
Bhavin12,Mumbai12,10/2/2005,1,2,3,4,5

Now I tried using the above code with comma as a deliminator, but I can only see 1155195 records

plz help me how to proceed…..
on October 22, 2008 at 7:12 pm Foxy09ro

Fisrt of all, thank you for your example with bulk insert it solved my problem, but I styill have a little problem: I have in my .csv file special characters like (şţăîâ) and I’d like some help on this matter, please!
Thank you!
on October 24, 2008 at 5:10 am Joe

Excellent article!

I am importing a csv file and several fields have a very long field length (200 characters). I want to truncate it (25 characters) and add a special character (for example a tilde to advise me the data is truncated) after importing. Any suggestion would be appreciated.

Thank you.
on October 30, 2008 at 12:13 am Rose

I get this error msg on simple 6 row table(same format and file location as described above):

Msg 4832, Level 16, State 1, Line 1
Bulk load: An unexpected end of file was encountered in the data file.
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider “BULK” for linked server “(null)” reported an error. The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider “BULK” for linked server “(null)”.

any help for this?
on October 30, 2008 at 2:09 am Chief

hello,

Looking to import a txt file that at present errors when dts package enncounters nagative vlaues. Any suggestions?

Negative values are assoicated with Money

0000003.34 will pass
00000-3.34 will fail

Thanks,

Cheif
on November 1, 2008 at 11:51 pm Chris H

I used the script to import a CSV into a new table in Database Explorer in Visual Web Developer. It works! Thanks for your help.
on November 3, 2008 at 10:42 pm Carolin

I am using your query to import CSV file to Sql Server.

But I am getting the following error”Cannot bulk load. The file “C:\CSVTest.txt” does not exist.”

What should I do now?
Any suggestions are welcome.
on November 5, 2008 at 12:26 am Mayank Srivastava

While runnning BULK INSERT statement, i am getting error

BULK INSERT #temptb FROM ‘/home/msrivast/temptable.txt’
go

sg 102, Level 15, State 0

ASA Error -131: Syntax error near ‘BULK’ on line 1

Please help
on November 6, 2008 at 6:05 am Suranga

Hi Pinal,

How do we load CSV file has embedded newlines (in varchar colomuns) into MS SQL server?

For instance, how do we load the CSV file below. Note that the second record has a “newline”/”line break” in Column 2

id, reason, season
==, =====, =====
10,’cold’, ‘winter’
12,’cold
flu’,'autumn’
on November 7, 2008 at 3:39 am Brian

i couldn’t find anyone with an answer for dealing with CSV’s that have quotes in the text with commas within them.

The key issue being importing this:
Col1, Col2, Col3
“Joe”,”Smith”,”Sr Architect”
“Nicole”,”Dawson”,”Manager”
“Jon”,”Stephens, PHD”,”PM”

I was able to work out this solution. Basically describe the delimiter (field terminator) as “,” like this using XML Format Files for SQL Server:

And notice i had to describe the ” as " format for each field (i only listed 1 as the example).

Thanks
-B
on November 10, 2008 at 12:14 am A.prasad

I am loading lattitude longitude information into database using load file command.I loaded them into database successfully .But after checking using “select * from data base name” it’s showing all zeros.Any help would be helpful.Thanx in advance.
on November 10, 2008 at 11:12 am Sanobur Mahmood

What if I want to load hundred of files with one script saved into one folder? Above script is just a fun. Do something actual through which many people can find the solution to their problems.
on November 12, 2008 at 1:04 pm Amrit

Please help.

i get large amount of information in an excel file which i need to store in MSSql database. i used to import but i need to make an ASP interface that picks the information from excel or csv and inserts into the database.

is it possible?
on November 13, 2008 at 7:36 am Mohfeza

please help me. I got following error when i run insertion script below-
USE WATCHDOIT
BULK
INSERT CSVTest1
FROM ‘C:\Inetpub\wwwroot\WatchDoit\BusinessList1.txt’
WITH
(
FIELDTERMINATOR = ‘,’,
ROWTERMINATOR = ‘\n’
)

Msg 4832, Level 16, State 1, Line 2
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.
Msg 7330, Level 16, State 2, Line 2
Cannot fetch a row from OLE DB provider “BULK” for linked server “(null)”.
on November 15, 2008 at 3:18 am Jaideep

Msg 4832, Level 16, State 1, Line 2
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.
Msg 7330, Level 16, State 2, Line 2
Cannot fetch a row from OLE DB provider “BULK” for linked server “(null)”.
on November 19, 2008 at 8:39 am SivaSakthi

Really Superb Pinal Sir..

I m njoying your SQL Tips daily…

Continue with your Favours…

Bye…
on November 20, 2008 at 1:25 pm Ketan

Hi,
insert Only one row
Plz help me

as early as possible
on November 28, 2008 at 5:22 pm jas

Hi,
I’m using bulk insert and everything works fine but I cannot get ‘£’ sign characters to import properly. It always change into ‘?’ sign. I tried using nvarchar datatype but it did not help.

Please Help me.

Thanks
on December 9, 2008 at 2:55 pm Vaibhav

Hi,

I wants to use bulk insert for a text file with fieldterminator as a space. How do I use It.

Regards,

Vaibhav
on December 10, 2008 at 12:56 pm mukulbudania

Sir,
that is already done. can you suggest me a way to defin th table by its own using the CSV file. i.e. my first row is the column name. Now how do i create a table using the variable column names??

tank in advance.

Mukul.
on December 11, 2008 at 7:34 pm Puja Shah

Hi Pinal,

I have used Bulk command, but want to get the file path of CSV file. I want file path using T-SQL.

Please tell me how to get the file path only by providing file name.

Thanks in advance….
on December 18, 2008 at 2:20 pm sai

Hi Pinal,

Yours query is marvellous but i want the same query to access a text file to online server with the text placed in client machine

Can any one suggest me

Thanks in advance
on December 19, 2008 at 8:00 pm samrat bhattacharjee

Hi!!
i am facing a problem…..

i am trying to bulk insert data into a table.

the stored procedure goes like this…

====================================
CREATE PROCEDURE [dbo].[insbulk]
@circle varchar(20),@path varchar(100)
AS
bulk insert @circle from @path with (fieldterminator=’,')
GO
======================================

The thing is that this is a wronh syntax…..

My frontend application goes like this…
which takes input the filename ie the path,the tablename
===================================
SqlCommand cmd=new SqlCommand(”insbulk”,conn);
cmd.CommandType=CommandType.StoredProcedure;

cmd.Parameters.Add(”@circle”,this.cmbcircle.SelectedItem.ToString());
cmd.Parameters.Add(”@path”,this.fldg.FileName);
cmd.Parameters.Add(”@insertiondate”,insertiondate);
cmd.ExecuteScalar();

}
catch
{
MessageBox.Show(”Error!!!Connection terminating with database”);
conn.Close();
}
on December 20, 2008 at 7:57 am Imran Mohammed

@samrat

First try executing that stored procedure in SQL Server Client tools.

Check if it is working fine ?

Regards
IM
on December 27, 2008 at 5:41 am Santosh

Thanks
on December 29, 2008 at 10:07 pm harinath clavib

Hi
how to transfer data into multitables from csv file and is it possible to tranfer the data into constraint tables.
on January 1, 2009 at 6:34 am Tab

I am trying to import data from multiple text files into sql server 2005. After the data has been imported in the sq server, i want to move the files to another folder. Does anyone have a solution to this?

Thanks in advance.
on January 1, 2009 at 3:58 pm Abhishek Hingu

Hi this article is very helpfull

I was trying to run same query with SQL Server Compact Edition Database.

But faild to do same can u please help me in this
on January 2, 2009 at 10:31 am tejasnshah

Hi Abhisek,

To move file after processing, you need to use SSIS for that.

In that you can process file and later you can move/Delete that file too.

Tejas