Menu
I am trying to insert a .csv file into a database with unix linebreaks. The command I am running is:
BULK INSERT table_name FROM 'C:\file.csv' WITH ( FIELDTERMINATOR = ',', ROWTERMINATOR = '\n' ) If I convert the file into Windows format the load works, but I don't want to do this extra step if it can be avoided. Any ideas?
342
The basic syntax for bulk importing data is: INSERT ... SELECT * FROM OPENROWSET(BULK...) When used in an INSERT statement, OPENROWSET(BULK...)
You need to use a 'format file' to implement a text qualifier for bulk insert. Essentially, you will need to teach the bulk insert that there's potentially different delimiters in each field. Create a text file called "level_2. fmt" and save it.
In case of BULK INSERT, only extent allocations are logged instead of the actual data being inserted. This will provide much better performance than INSERT. The actual advantage, is to reduce the amount of data being logged in the transaction log.
1 Answer. You can specify FIRSTROW=2 in your BULK INSERT command to skip the very first row. See the below example. WITH (FIELDTERMINATOR ='\t', ROWTERMINATOR = '\n',FIRSTROW = 2);
I felt compelled to contribute as I was having the same issue, and I need to read 2 UNIX files from SAP at least a couple of times a day. Therefore, instead of using unix2dos, I needed something with less manual intervention and more automatic via programming.
As noted, the Char(10) works within the sql string. I didn't want to use an sql string, and so I used ''''+Char(10)+'''', but for some reason, this didn't compile.
What did work very slick was: with (ROWTERMINATOR = '0x0a')
Problem solved with Hex!
Hope this helps someone.
110
Thanks to all who have answered but I found my preferred solution.
When you tell SQL Server ROWTERMINATOR='\n' it interprets this as meaning the default row terminator under Windows which is actually "\r\n" (using C/C++ notation). If your row terminator is really just "\n" you will have to use the dynamic SQL shown below.
DECLARE @bulk_cmd varchar(1000) SET @bulk_cmd = 'BULK INSERT table_name FROM ''C:\file.csv'' WITH (FIELDTERMINATOR = '','', ROWTERMINATOR = '''+CHAR(10)+''')' EXEC (@bulk_cmd) Why you can't say BULK INSERT ...(ROWTERMINATOR = CHAR(10)) is beyond me. It doesn't look like you can evaluate any expressions in the WITH section of the command.
What the above does is create a string of the command and execute that. Neatly sidestepping the need to create an additional file or go through extra steps.
37
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With