May 5, 2008 at 1:14 am
Hi guys,
Cany anyone provide me the help to import the csv format data in to sqlserver using BULK INSERT
And also provide me the samples.
May 5, 2008 at 1:45 am
Hi sram24_mca,
something like this:
c:\csvtest.csv:
1,James,Smith,19750101
2,Meggie,Smith,19790122
3,Robert,Smith,20071101
4,Alex,Smith,20040202
Database Table:
CREATE TABLE CSVTest
(ID INT,
FirstName VARCHAR(40),
LastName VARCHAR(40),
BirthDate SMALLDATETIME)
GO
BULK INSERT in CSVTest
BULK
INSERT CSVTest
FROM 'c:\csvtest.txt'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\ n ')
GO
[font="Verdana"]CU
tosc[/font]
May 6, 2008 at 11:33 am
Thanks for the great example! Two quick typos that prevented it from running for me that may trip some people up:
1. The file name should be consistent: either c:\csvtest.csv or c:\csvtest.txt.
2. The ROWTERMINATOR should not have spaces in it. This gave me a fairly unhelpful error message: Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 4 (BirthDate).
May 6, 2008 at 11:52 am
Good example Torsten. Only change ROWTERMINATOR = ' \ n 'should be read without spaces
SQL DBA.
May 7, 2008 at 12:00 am
Hi,
you both are right!
But i have a problem with the forum's code 🙂
CU
[font="Verdana"]CU
tosc[/font]
May 8, 2008 at 11:51 pm
Torsten Schüßler (5/5/2008)
Hi sram24_mca,something like this:
c:\csvtest.csv:
1,James,Smith,19750101
2,Meggie,Smith,19790122
3,Robert,Smith,20071101
4,Alex,Smith,20040202
Database Table:
CREATE TABLE CSVTest(ID INT,
FirstName VARCHAR(40),
LastName VARCHAR(40),
BirthDate SMALLDATETIME)
GO
BULK INSERT in CSVTest
BULK
INSERT CSVTest
FROM 'c:\csvtest.txt'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\ n ')
GO
very good example
i made two simple changes in your example. one is bulk insert from file is not *.txt but is *.csv
and removed SPACE IN ROWTERMINATOR VALUE
THANKS
May 9, 2008 at 12:05 am
hi shamshudheen,
THX for perfecting, but what about posting the example.
[font="Verdana"]CU
tosc[/font]
May 9, 2008 at 12:15 am
Torsten Schüßler (5/7/2008)
Hi,you both are right!
But i have a problem with the forum's code 🙂
CU
the code is calling system procedure called sp_MSforeachdb which will return some records and the records are storing into created new table
but could not understand what is the sp_MSforeachdb procedure is doing and i still have doubt in generating the @command string
better wait for other response
thanks in advance
May 9, 2008 at 1:18 am
Hi shamshudheen,
did you mean something like this:
EXEC sp_MSforeachdb @command1="print '?' DBCC CHECKDB ('?')"
sp_MSforeachdb is an undocumented stored proc., will put each database and accomplish DBCC CHECKDB for each database.
[font="Verdana"]CU
tosc[/font]
May 9, 2008 at 2:51 am
hey right!!!!!
but my question is what the stored procedure does and if possible explain @COMMAND string with ?. some thing new to me
May 9, 2008 at 3:10 am
Torsten Schüßler (5/9/2008)
Hi shamshudheen,did you mean something like this:
EXEC sp_MSforeachdb @command1="print '?' DBCC CHECKDB ('?')"sp_MSforeachdb is an undocumented stored proc., will put each database and accomplish DBCC CHECKDB for each database.
you very very noodu!!!!!!:D
Now i understand what is ? , it replace each database name. and the stored procedure loop thru all database
thanks
June 10, 2008 at 5:04 am
I'm using Bulk Insert command for inserting data from csv (comma dilimited) file which was converted from excel file using MS Excel 2003. But I think excel does not follow the same standard always.
This is the story...
When we covert to a CSV (comma dilimited) file, no. of commas generated are not the same from one row to another.. though the no. of columns are the same for every row. Especially that happens when some cells in last column was blank..
eg.
a, b , c ,d,e
1,KYAW ,12.155,0,0
2,BU THI ,75.38 ,0,
3,TAUNG ,49.59 ,0
4,zIN ,40 ,0,0
you will see line 3 does not have any comma in the last row.. If you insert that file with Bulk Insert command .. it will only insert only 4 records into database including heading row.. that was wrong .."zIN ,40 ,0,0" was inserted as a cell value..
But if u change the above file like the following (commans are placed correctly) and try inserting again.....
a, b , c ,d ,e
1, KYAW ,12.155 ,0 ,0
2, BU THI ,75.38 ,0 ,
3, TAUNG ,49.59 ,0 ,
4, zIN ,40 ,0 ,0
It will insert 5 records.. that was the correct answer...
But, MS Excel does not convert to CSV format correctly like second example. sometimes it converts to CSV file like my first example..... That was a big problem for me.. Could anyone help me on this matter.. how can I do with MS Excel or with Bulk Insert .. ???? Any expert idea will be appreciated ... !
June 11, 2008 at 1:03 am
Hi,
here is a way which i used to import data from xls file, and it worked perfect for me. I hope it will work for csv as well:
Run this query:
SELECT * into Table from OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'c:\file1.xls';;'', Sheet$1)
Excel file placed in C:Data placed in "Sheet1" in that file.
Excel Data will be moved to table named "Table" , In this case, a new table named "Table" will be created in Database. If you want to move data into a table already created, then write:
Insert into Table1 from OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'c:\file1.xls';;'', Sheet$1)
where Table1 is already created there.
CAUTION: xls file should NOT BE OPEN while query is executing.
sometimes, it causes an error which comes because of insufficient permissions or something like this. for which i had to run this code before actual import query:
EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
EXEC sp_configure 'Ad Hoc Distributed Queries', 1;
GO
RECONFIGURE;
GO
and here is the xls import which i made successfully:
Insert into AreaInfo
Select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\ZipCode.xls;HDR=YES',
'SELECT * FROM [ZIP_CODES$]')
i hope it will work fine.
June 11, 2008 at 5:46 am
Thanks for ur reply...
I tried like this.....
INSERT INTO Mytable
SELECT *
FROM OPENROWSET ('MSDASQL', 'Driver={Microsoft Text Driver (*.txt; *.csv)};
DefaultDir=C:\ME\;Extensions=csv;', 'SELECT * FROM 1_2007_9.csv')
That inserted rows to Mytable. It solved 2/3 of my problem. But the problem is it does not import the first row and also removed some text values. while Bulk Insert take everything from the CSV file..... I would like to get the first row as a record inserted into the table..
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply