Import Text file into SQL....?

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Import Text file into SQL....?

Import Text file into SQL....?

Rate Topic

Display Mode

Topic Options

Author

Message

Minnu

Posted Wednesday, January 02, 2013 3:53 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 2:03 AM
Points: 119, Visits: 361

Hi Team,

HAPPY NEW YEAR.
--

I have a table with below structure.

Create table temp_ttt
(
col1 varchar(100),
col2 Decimal(9,2),
col3 Decimal(9,2)
)

i want to insert data from text file to above table.

text file contains.

2004/04/28,10
UBC001458,90.94,2367.38
DWL006291,0,5369.12
0042672095,0,3050
0045502095,0,2300
2105863602,0,1082.39
2105863603,0,292.62
0012552106,0,4140
LUT000028,5.36,1.04
THY005087,1018.51,3082.57
GPB010655,12428.1,15471.8

am using below query,

BULK INSERT temp_ttt
FROM 'C:\Documents\book11.txt'
WITH
(
FIELDTERMINATOR = '|',
ROWTERMINATOR = ','
)

Error msg :
Msg 4861, Level 16, State 1, Line 1
Cannot bulk load because the file "C:\Documents\book11.txt" could not be opened.
Operating system error code 3(The system cannot find the path specified.).

Please help....

Post #1401745

anthony.green

Posted Wednesday, January 02, 2013 3:57 AM

SSCertifiable

Group: General Forum Members
Last Login: Friday, April 12, 2013 3:51 AM
Points: 5,075, Visits: 4,831

Does the file exist in that location on the server?

Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1401748

Minnu

Posted Wednesday, January 02, 2013 4:00 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 2:03 AM
Points: 119, Visits: 361

Yes.

file exists in the same path / location.

Post #1401751

anthony.green

Posted Wednesday, January 02, 2013 4:03 AM

SSCertifiable

Group: General Forum Members
Last Login: Friday, April 12, 2013 3:51 AM
Points: 5,075, Visits: 4,831

On the server or on your local machine?

Post #1401753

Minnu

Posted Wednesday, January 02, 2013 4:05 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 2:03 AM
Points: 119, Visits: 361

Ooops,

I have that file in my local machine......

how to read the same file from local machine.

Post #1401756

anthony.green

Posted Wednesday, January 02, 2013 4:08 AM

SSCertifiable

Group: General Forum Members
Last Login: Friday, April 12, 2013 3:51 AM
Points: 5,075, Visits: 4,831

Use the import export data wizard

Post #1401758

yuvipoy

Posted Wednesday, January 02, 2013 4:11 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 12:41 AM
Points: 110, Visits: 513

Ensure that SQL server service A/c has permission to access that file, logins should have access to that folder.

Thanks!

Post #1401759

« Prev Topic | Next Topic »

Permissions