Sql-server – OPENROWSET Bulk insert Text File NULL Columns

bcpopenrowsetsql-server-2017t-sql

I need to automate a weekly import of .txt log files into a SQL database. I have already created my table, format file and can get some of the data to work using OPENROWSET BULK, but the data from the log files is not consistent.

The table has 10 columns 9 of which are populated from the log file, last 3 columns are all descriptions but the log file may only have a single description column populated (the max is always 3).

In the image below the highlighted rows work because they have the valid 3 column worth of data separated by a comma. Is there a way to force BCP to always populate the 3 columns or mark them as NULL then move onto the next row?

Best Answer

Ended up using a combination of Power Shell and SQL, PS cleans the files first, adds the correct headers and formatting combined with a format file I have been able to get this to work and it is reliable.

If anyone does have any thoughts or suggestions please post them.

Related Solutions

Sql-server – BULK INSERT – best usage

It is technically possible, yes. But you may be able to leverage performance gains by having multiple files on multiple disks as opposed to just one file. Why not try using one file and see if it works, and what the performance is like?

Here is a link for info on BULK INSERT: http://msdn.microsoft.com/en-us/library/ms188365.aspx

Sql-server – How to import a varbinary(max) column with bulk import

A varbinary value is of the form 0x01020304 for SQL Server to read.

So your CSV would be

col1,col2,varbinary
foo,1,0x01020304
bar,2,0x988776655443

Edit May 2013,

After testing, I can't get this to work with BULK INSERT with or without a format file.

I will post something more when I get it working. I can't delete this answer

Best Answer

Related Solutions

Sql-server – BULK INSERT – best usage

Sql-server – How to import a varbinary(max) column with bulk import

Related Question