SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Stuck on a large update
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

oracle765
Starting Member

Australia
13 Posts

Posted - 02/24/2013 :  01:01:06  Show Profile  Reply with Quote
Hi All


I am trying to do a large import with the below code for many thousands of rows from an excel spreadsheet.

the table is made up as follows

col1, col2, col3 col4, col5

sun microsystems inc, test,test,test,test
sun microsystems, inc, test, test, test, test
adobe inc, test,test,test,test
microsoft,test,test,test,test
microsoft",test,test,test,test
"adobe, inc",test,test,,test

I am having problems for example with rows 2, 5 and 6

all rows are inserting but for instance row 2 is finding the comma after sun microsystems, then putting inc in the next column along
same for row 6 with adobe ones
row 5 is still inserting which is similar two the other rows but i think it is doing the same because of the the double quotes
also the last row is showing no value so is there anyway to put some wording into the row if it is empty say something like 'Empty Value'

thanks

A Lynch

jackv
Flowing Fount of Yak Knowledge

United Kingdom
1908 Posts

Posted - 02/24/2013 :  02:21:41  Show Profile  Visit jackv's Homepage  Reply with Quote
Hi, could you also supply the SQL statement.
Depending on how you are doing the import , you could try a BULK INSERT with error file into a staging table. Fix the rows found in the ERROR file and then UPDATE from staging table
http://www.sqlserver-dba.com/2012/11/sql-server-bulk-insert-with-errorfile.html

Jack Vamvas
--------------------
http://www.sqlserver-dba.com
Go to Top of Page

oracle765
Starting Member

Australia
13 Posts

Posted - 02/24/2013 :  02:40:44  Show Profile  Reply with Quote
Hi There

This is how I am doing the import

BULK
INSERT The_Big_Kahuna
FROM 'c:\users\alynch\Desktop\The_Big_Kahuna.csv'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
GO
SELECT *
FROM dbo.The_Big_Kahuna
GO



thanks

A Lynch
Go to Top of Page

jackv
Flowing Fount of Yak Knowledge

United Kingdom
1908 Posts

Posted - 02/24/2013 :  02:44:01  Show Profile  Visit jackv's Homepage  Reply with Quote
If you add the error file option (check the link) this will document the errors.

Jack Vamvas
--------------------
http://www.sqlserver-dba.com
Go to Top of Page

oracle765
Starting Member

Australia
13 Posts

Posted - 02/24/2013 :  02:49:57  Show Profile  Reply with Quote
Hi Jack.

I am new to sqlserver and I do not know what you mean by clicking the link I do not understand it just points me to something like this


February 23, 2013
Add an extra SQL Transaction log file – ALTER DATABASE ADD LOG
Question: I need to create an extra sql transaction log file to a SQL Server database. What is the correct syntax?

I’ve run out of disk space on the dedicated log file drive and need to run a very large query one-off query.The storage administrators have added a temporary extra disk , for the extra sql transaction log file.

Answer: Use the ALTER DATABASE command to add an extra SQL transaction log file with a destination location . The example below adds an extra log file to an existing database


view sourceprint?1.USE [master]
2.GO
3.ALTER DATABASE [database_name] ADD LOG FILE ( NAME = N'database_nameExtraLog', FILENAME = N'N:\ database_nameExtraLog.ldf' , SIZE = 20480KB , FILEGROWTH = 1024KB )
4.GO


Read More
Modify sql transaction log file size - increase or decrease

SQL Server - Find Default Data location and Default Log Location

SQL Server - Preallocate SQL Transaction Logs for large queries – Initial Size

SQL Server – Monitor SQL Transaction Log AutoGrowth for performance issues



A Lynch
Go to Top of Page

jackv
Flowing Fount of Yak Knowledge

United Kingdom
1908 Posts

Posted - 02/24/2013 :  03:25:07  Show Profile  Visit jackv's Homepage  Reply with Quote
the link I posted earlier : http://www.sqlserver-dba.com/2012/11/sql-server-bulk-insert-with-errorfile.html

Jack Vamvas
--------------------
http://www.sqlserver-dba.com
Go to Top of Page

oracle765
Starting Member

Australia
13 Posts

Posted - 02/24/2013 :  03:51:14  Show Profile  Reply with Quote
I have done that and I am getting the error

Msg 4861, Level 16, State 1, Line 5
Cannot bulk load because the file "c:\users\alynch\Desktop\bulk_insert_BadData.log" could not be opened. Operating system error code 80(failed to retrieve text for this error. Reason: 15105).
Msg 4861, Level 16, State 1, Line 5
Cannot bulk load because the file "c:\users\alynch\Desktop\bulk_insert_BadData.log.Error.Txt" could not be opened. Operating system error code 80(failed to retrieve text for this error. Reason: 15105).

also the fields with the " and the extra comms are inserting into the database and not showing up in the Txt file

A Lynch
Go to Top of Page

jackv
Flowing Fount of Yak Knowledge

United Kingdom
1908 Posts

Posted - 02/24/2013 :  14:09:38  Show Profile  Visit jackv's Homepage  Reply with Quote
Does the SQL Server Service account have enough permissions on the folder?


Jack Vamvas
--------------------
http://www.sqlserver-dba.com
Go to Top of Page

MIK_2008
Flowing Fount of Yak Knowledge

Pakistan
1050 Posts

Posted - 03/04/2013 :  11:42:58  Show Profile  Reply with Quote
specific to the issue of extra commas inside cell's data e.g. "un microsystems, inc". Not sure as if there could be any way to avoid this situation but
how if you double qoute all problematic strings and then open the CSV file into Excel - save it as .xls and use openRowSet method to read/import data into sql table.

Cheers
MIK
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000