Bulk Insert, SQL Server 2000, Unix Linebreaks

Bulk insert, SQL Server 2000, unix linebreaks

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.

Bulk Insert a File with a Text Field with Carriage Return (Enters)

EDIT: you don't need a format file:

IF OBJECT_ID('tempdb..#rawdata') IS NOT NULL DROP TABLE #rawdata
create table #rawdata (
  xyz_id VARCHAR(200)
, notes text
, create_date varchar(32)
, modify_date varchar(32)
, create_user varchar(12)
, modify_user varchar(12)
)

BULK INSERT #rawdata 
FROM 'C:\temp\file.txt'
WITH (
    FIELDTERMINATOR = '**|**'
  , ROWTERMINATOR = '**|**\n'
  , TABLOCK
  )

SELECT * FROM #rawdata

You may need to play with **|**\n (LF) vs **|**\r\n (CRLF) vs **|**\r (CR), depending on whether the file is Unix, DOS or MAC-like.

/EDIT

It can't be done without a format file. So, create a format file:

8.0
7
1 SQLCHAR 0 0 "**|**" 1 xyz_id        ""
2 SQLCHAR 0 0 "**|**" 2 notes         ""
3 SQLCHAR 0 0 "**|**" 3 create_date   ""
4 SQLCHAR 0 0 "**|**" 4 modify_date   ""
5 SQLCHAR 0 0 "**|**" 5 create_user   ""
6 SQLCHAR 0 0 "**|**" 6 modify_user   ""
7 SQLCHAR 0 0 "\n"    0 omitted       ""

Then:

IF OBJECT_ID('tempdb..#rawdata') IS NOT NULL DROP TABLE #rawdata
create table #rawdata (
  xyz_id VARCHAR(200)
, notes text
, create_date varchar(32)
, modify_date varchar(32)
, create_user varchar(12)
, modify_user varchar(12)
)

BULK INSERT #rawdata 
FROM '\\folder\file.txt'
WITH (
    FORMATFILE = '\\folder\file.fmt'
  , TABLOCK
  )

Or, in SQL 2005+:

SELECT * FROM OPENROWSET(
    BULK '\\folder\file.txt'
  , FORMATFILE = '\\folder\file.fmt'
  ) a

Getting error while bulk insertion

The Issue

BULK INSERT may not work with xlsx files, try converting the .xlsx file to .csv file to achieve this (using BULK INSERT)

1st Solution - using OPENROWSET

Try using OPENROWSET with Microsoft.ACE.OLEDB.12.0 provider:

Insert into <rawdatatable> 
select * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 
                         'Excel 12.0;Database=D:\SSIS\FileToLoad.xlsx;HDR=YES', 
                         'SELECT * FROM [Sheet1$]')

OR

SELECT * INTO Data_dq
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel 12.0; Database=D:\Desktop\Data.xlsx', [Sheet1$]);

2nd Solution - using OPENDATASOURCE

SELECT * INTO Data_dq
FROM OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0',
'Data Source=D:\Desktop\Data.xlsx;Extended Properties=Excel 12.0')...[Sheet1$];

References

• Import/Export Excel (.Xlsx) or (.Xls) File into SQL Server
• Import data from Excel to SQL Server or Azure SQL Database
• How to Bulk Insert from XLSX file extension?

Bulk insert using sql server openxml with dynamic sql from .Net

I observed that SqlClient.BulkCopy as the better performant than the above OpenXML approach. Hence I changed my logic accordingly.

Can SQL Server bcp in a file with Unix line endings?

You have to use a format file with bcp and specify the terminator as \n. The interactive command line will always append \r, where a format file will use exactly what you specify. Reference http://www.eggheadcafe.com/software/aspnet/32239836/bcp-out-with-char10-as-row-terminator.aspx.

Creating a format file is explained pretty well in BOL but comment/update your original post if you need help.

Bulk Insert Failed Bulk load data conversion error (truncation)

The problem is likely the row terminator is not working due to the file format.

Try:

ROWTERMINATOR = '0x0a'

EDIT

Actually I just notice you are using forward slash, it should be backslash, so this may work:

ROWTERMINATOR = '\n'

---

Related Topics