Load data from text file with variable columns


This script loads from a formatted text file (E.g. tab delimited) with variable number of columns using BULK INSERT. For instance, the file may have any number of columns and next load (seconds later) the same file may have a different number of columns.

This script reads the first line (column names) extracting the names and using them to create a table dynamically appending the time stamp string to the table name (to ensure uniqueness). After executing the CREATE TABLE directive, and creating a new table, it will execute the second BULK INSERT to load the data to the new table.

The table definition uses varchar(1000) for all the columns to make the load easier. I would suggest to perform data validation the load is complete.

create table ##tbl (line varchar(1000))

bulk insert ##tbl
from '\\[computername]\c$\tbl.txt'
with (FIRSTROW=1)

--select top 1 Replace(Line, char(9), ',') from ##tbl

declare @tempstr varchar(1000)
declare @col varchar(1000)
declare @createtbl varchar (1000)
declare @loadtbl varchar (1000)
declare @newtblname varchar(1000)

/* ====================================================================

Generates a unique identifier for all the Event Log entries 

declare @month char(2)
declare @date char(2)
declare @year char(4)
declare @hour char(2)
declare @min char(2)
declare @sec char(2)
declare @msec char(3)

set @month = DATEPART(m, getdate())
if LEN(RTRIM(DATEPART(m, getdate()))) = 1
set @month = '0'+RTRIM(DATEPART(m, getdate()))
set @date = DATEPART(d, getdate())
if LEN(RTRIM(DATEPART(d, getdate()))) = 1
set @date = '0'+RTRIM(DATEPART(d, getdate()))
set @year = DATEPART(yyyy, getdate())
set @hour = DATEPART(hh, getdate())
if LEN(RTRIM(DATEPART(hh, getdate()))) < 2
set @hour = '0'+RTRIM(DATEPART(hh, getdate()))
set @min = DATEPART(mi, getdate())
if LEN(RTRIM(DATEPART(mi, getdate()))) < 2
set @min = '0'+RTRIM(DATEPART(mi, getdate()))
set @sec = DATEPART(ss, getdate())
if LEN(RTRIM(DATEPART(ss, getdate()))) < 2
set @sec = '0'+RTRIM(DATEPART(ss, getdate()))
set @msec = DATEPART(ms, getdate())
if LEN(RTRIM(DATEPART(ms, getdate()))) < 3
if LEN(RTRIM(DATEPART(ms, getdate()))) = 2
set @msec = '0'+RTRIM(DATEPART(ms, getdate()))
if LEN(RTRIM(DATEPART(ms, getdate()))) = 1
set @msec = '00'+RTRIM(DATEPART(ms, getdate()))

set @col = ''
set @tempstr = (select top 1 RTRIM(Replace(Line, char(9), ',')) from ##tbl)

while CHARINDEX(',',@tempstr) > 0
set @col = @col+SUBSTRING(@tempstr, 1, CHARINDEX(',',@tempstr)-1)+' varchar(1000),'
set @tempstr = SUBSTRING(@tempstr, CHARINDEX(',',@tempstr)+1, len(@tempstr)) 

set @col = @col+@tempstr+' varchar(1000))'

set @createtbl = 'create table load_'+RTRIM(@month+@date+@year+@hour+@min+@sec)+' ('+@col
set @newtblname = 'load_'+RTRIM(@month+@date+@year+@hour+@min+@sec)

--select @createtbl

drop table ##tbl

exec (@createtbl)

--select @newtblname

set @loadtbl = 'bulk insert '+@newtblname+' from '+char(39)+'\\wsana157\c$\tbl.txt'+char(39)+' with (FIRSTROW=2)'

--select @loadtbl

exec (@loadtbl)