Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Bulk Insert from file to table Expand / Collapse
Author
Message
Posted Monday, January 28, 2013 2:48 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, March 27, 2014 9:37 AM
Points: 20, Visits: 581
Hi, I have .dat file with following format data.

1,2,3,
4,5,6,
7,8,9,


i want load this format file into sql table using bulk insert statement.

how can we fit ROWTERMINATOR in bulk statement.

please let me know....
Post #1412280
Posted Monday, January 28, 2013 3:50 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 6:52 AM
Points: 5,131, Visits: 4,918
Use the ROWTERMINATOR option of the BULK INSERT command.

http://msdn.microsoft.com/en-us/library/ms188365.aspx

, \ n

or something along them lines, just remove the spaces.




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 #1412308
Posted Monday, January 28, 2013 4:31 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, March 27, 2014 9:37 AM
Points: 20, Visits: 581
i tried with .. but no luck
Post #1412341
Posted Monday, January 28, 2013 4:56 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 6:52 AM
Points: 5,131, Visits: 4,918
bulk insert ... from '...' with (fieldterminator = ',', rowterminator = ', \ n') --Remove the spaces between , \ n

Need to ensure that there is a carrage return at the end of the last line in the file

1,2,3,(CR)
4,5,6,(CR)
7,8,9,(CR)




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 #1412347
Posted Monday, January 28, 2013 11:03 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, March 27, 2014 9:37 AM
Points: 20, Visits: 581
Hi anthony.green,

Thank you for your reply.

when i opend the file and delere " ," and type again... at that move ment your functionality is working.


with out open file.. all rowsdata insrted into single row.....

is there any functionality to edit the last charecter in file
Post #1412762
Posted Tuesday, January 29, 2013 1:55 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 6:52 AM
Points: 5,131, Visits: 4,918
Try ROWTERMINATOR of '','+CHAR(10)+''', will need to build it dynamic, might be getting confused as relates to line feed carrage return

DECLARE @bulk_cmd varchar(1000);
SET @bulk_cmd = 'BULK INSERT Table
FROM ''C:\file.txt''
WITH (ROWTERMINATOR = '','+CHAR(10)+''', FIELDTERMINATOR = '','')';
EXEC (@bulk_cmd);




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 #1412816
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse