| Author |
 Topic  |
|
|
oracle765
Starting Member
Australia
13 Posts |
 Posted - 02/24/2013 : 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
Flowing Fount of Yak Knowledge
United Kingdom
1795 Posts |
|
|
oracle765
Starting Member
Australia
13 Posts |
Posted - 02/24/2013 : 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
Flowing Fount of Yak Knowledge
United Kingdom
1795 Posts |
Posted - 02/24/2013 : 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
Australia
13 Posts |
Posted - 02/24/2013 : 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
Flowing Fount of Yak Knowledge
United Kingdom
1795 Posts |
|
|
oracle765
Starting Member
Australia
13 Posts |
Posted - 02/24/2013 : 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
Flowing Fount of Yak Knowledge
United Kingdom
1795 Posts |
Posted - 02/24/2013 : 14:09:38
|
Does the SQL Server Service account have enough permissions on the folder?
Jack Vamvas
--------------------
http://www.sqlserver-dba.com |
|
|
|
MIK_2008
Aged Yak Warrior
Pakistan
923 Posts |
Posted - 03/04/2013 : 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 |
|
|
| |
Topic |
|
Stuck on a large update
