Stuck on a large update - 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 2012 Forums

Transact-SQL (2012)

Stuck on a large update

Author

Topic

oracle765
Starting Member

13 Posts

Posted - 2013-02-24 : 01:01:06

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
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2013-02-24 : 02:21:41

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

oracle765
Starting Member

13 Posts

Posted - 2013-02-24 : 02:40:44

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

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2013-02-24 : 02:44:01

If you add the error file option (check the link) this will document the errors.

Jack Vamvas
--------------------
http://www.sqlserver-dba.com

oracle765
Starting Member

13 Posts

Posted - 2013-02-24 : 02:49:57

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

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2013-02-24 : 03:25:07

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

oracle765
Starting Member

13 Posts

Posted - 2013-02-24 : 03:51:14

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

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2013-02-24 : 14:09:38

Does the SQL Server Service account have enough permissions on the folder?

Jack Vamvas
--------------------
http://www.sqlserver-dba.com

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2013-03-04 : 11:42:58

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources