August 5, 2008 at 6:12 am
I need to load several txt files into a table, I need to do this fast, meaning I need to know if there is a way
to read those txt files and break down the columns and insert into the sql server 2005 tables
ex: this is the txt file
1.0" 5.1" 5.1" 1.4" .2
1.0" 5.1" 5.1" 1.4" .2
1.0" 5.1" 5.1" 1.4" .2
1.0" 5.1" 5.1" 1.4" .2
1.0" 5.1" 5.1" 1.4" .2
1.0" 5.1" 5.1" 1.4" .2
1.0" 5.1" 5.1" 1.4" .2
1.0" 5.1" 5.1" 1.4" .2
1.0" 5.1" 5.1" 1.4" .2
1.0" 5.1" 5.1" 1.4" .2
the 2 last columns on the right are numeric the others are char, I need to read it from the txt files and
insert into the table, I want to know if there is a way to do this directly without need to put in a temp file
and then cut the columns to insert into the final table
any ideas? thanks
August 5, 2008 at 6:52 am
this example may be much more than you need.
This is very fast.
in my case, I had thousands of .txt files, all the same format.
they were distributed in 4 folders.
Each file had a header row (note the FIRSTROW=2 in the bulk insert)
you need to create the table to insert into prior to importing...my table is called BULKACT in the example...
this gets the file names, then imports/appends each file into the staging table BULKACT:
[font="Courier New"]#T=MULTIPLE FILE BULK INSERT
-a TABLE TO loop thru filenames DROP TABLE ALLFILENAMES
CREATE TABLE ALLFILENAMES(WHICHPATH VARCHAR(255),WHICHFILE VARCHAR(255))
--the source table: yours already exists, but needed for this example.
CREATE TABLE BULKACT(COL1 VARCHAR(10),COL2 INT,RAWDATA VARCHAR (8000))
--some variables
DECLARE @filename VARCHAR(255),
@path VARCHAR(255),
@sql VARCHAR(8000),
@cmd VARCHAR(1000)
--get the list of files to process:
--^#^#^#^#^#^#^#^#^#^#^#^#^#^#^#^#^#^#^#^#^#^#^#^#^#^#^#^#^#^#^#^#^#^#^#^#^#^#^#^#^#
SET @path = 'C:\DB\'
SET @cmd = 'dir ' + @path + '*.txt /b'
INSERT INTO ALLFILENAMES(WHICHFILE)
EXEC MASTER..xp_cmdShell @cmd
UPDATE ALLFILENAMES SET WHICHPATH = @path WHERE WHICHPATH IS NULL
SET @path = 'C:\DB2\'
SET @cmd = 'dir ' + @path + '*.txt /b'
INSERT INTO ALLFILENAMES(WHICHFILE)
EXEC MASTER..xp_cmdShell @cmd
UPDATE ALLFILENAMES SET WHICHPATH = @path WHERE WHICHPATH IS NULL
SET @path = 'C:\DB3\'
SET @cmd = 'dir ' + @path + '*.txt /b'
INSERT INTO ALLFILENAMES(WHICHFILE)
EXEC MASTER..xp_cmdShell @cmd
UPDATE ALLFILENAMES SET WHICHPATH = @path WHERE WHICHPATH IS NULL
SET @path = 'C:\DB4\'
SET @cmd = 'dir ' + @path + '*.txt /b'
INSERT INTO ALLFILENAMES(WHICHFILE)
EXEC MASTER..xp_cmdShell @cmd
UPDATE ALLFILENAMES SET WHICHPATH = @path WHERE WHICHPATH IS NULL
--^#^#^#^#^#^#^#^#^#^#^#^#^#^#^#^#^#^#^#^#^#^#^#^#^#^#^#^#^#^#^#^#^#^#^#^#^#^#^#^#^#
--cursor loop
DECLARE c1 CURSOR FOR SELECT WHICHPATH,WHICHFILE FROM ALLFILENAMES WHERE WHICHFILE LIKE '%.txt%'
OPEN c1
FETCH next FROM c1 INTO @path,@filename
WHILE @@fetch_status <> -1
BEGIN
--bulk insert won't take a variable name, so make a sql and execute it instead:
SET @sql = 'BULK INSERT BULKACT(COL1,COL2,RAWDATA) FROM ''' + @path + @filename + ''' '
+ ' WITH (
DATAFILETYPE = ''char'',
FIELDTERMINATOR = '','',
ROWTERMINATOR = '''',
FIRSTROW = 2
) '
PRINT @sql
EXEC (@sql)
FETCH next FROM c1 INTO @path,@filename
END
CLOSE c1
DEALLOCATE c1
[/font]
Lowell
August 5, 2008 at 7:04 am
ok I'm trying to test this way and I'm getting an error:
BULK INSERT #temp1 (field1,field2,field3,field4,field5) FROM 'c:elson.txt'
WITH
(
DATAFILETYPE = 'char',
FIELDTERMINATOR = ',',
ROWTERMINATOR = '',
FIRSTROW = 2
)
error says ; Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '('.
August 5, 2008 at 7:35 am
sorry...once your table #temp exists, i\BULK INSERT inserts into all columns....you can't identify the columns.
it should be this:
[font="Courier New"]BULK INSERT #temp1 FROM 'c:elson.txt'
WITH
(
DATAFILETYPE = 'char',
FIELDTERMINATOR = ',',
ROWTERMINATOR = '',
FIRSTROW = 2
)[/font]
your row terminator shou.d probable be [slash]n, but the forum code won't let that symbol stay in place...it strips it out.
Lowell
August 5, 2008 at 7:50 am
OK i'm doing your way and still get an error this is my code;
drop table nelson_test1
CREATE TABLE nelson_test1 (
Field1 int NULL,
Field2 VarChar(50) NULL,
Field3 VarChar(50) NULL,
Field4 smalldatetime NULL )
declare @sql varchar(8000)
declare @path varchar(255)
set @path='c:elson1.txt'
set @sql='BULK INSERT nelson_test1 (field1,field2,field3,field4) FROM ''' + @path + ''' '
+'WITH
(
FIELDTERMINATOR = '','',
ROWTERMINATOR = '' '',
FIRSTROW = 1
)'
exec @sql
Getting this error:
Msg 203, Level 16, State 2, Line 18
The name 'BULK INSERT nelson_test1 (field1,field2,field3,field4) FROM 'c:elson1.txt' WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = ' ',
FIRSTROW = 1
)' is not a valid identifier.
August 5, 2008 at 8:09 am
[font="Courier New"]--all the columns must exist in the table...bulk insert doesn't let you choose.
--if the data was structured like this:
--1.0" 5.1" 5.1" 1.4" .2
--1.0" 5.1" 5.1" 1.4" .2
DROP TABLE nelson_test1
--i'd make my table look like this, right?
CREATE TABLE nelson_test1 (
Field1 VARCHAR(50) NULL,
Field2 VARCHAR(50) NULL,
Field3 VARCHAR(50) NULL,
Field4 VARCHAR(50) NULL,
Field5 DECIMAL(10,4) NULL )
DECLARE @sql VARCHAR(8000)
DECLARE @path VARCHAR(255)
SET @path='c:[slash]nelson1.txt'
SET @sql='BULK INSERT nelson_test1 FROM ''' + @path + ''' '
+'WITH
(
FIELDTERMINATOR = ''" '', --note i'm using [dblquote][space] AS the field terminator based ON the data above
ROWTERMINATOR = ''[slash]n '',
FIRSTROW = 1
)'
exec @sql
'[/font]
Lowell
August 5, 2008 at 8:22 am
sorry bother you with this, but, I alter my code and source
source is this:
1,James,Smith,19750101
2,Meggie,Smith,19790122
3,Robert,Smith,20071101
4,Alex,Smith,20040202
code is this:
CREATE TABLE nelson_test1 (
Field1 VARCHAR(50) NULL,
Field2 VARCHAR(50) NULL,
Field3 VARCHAR(50) NULL,
Field4 smalldatetime NULL )
DECLARE @sql VARCHAR(8000)
DECLARE @path VARCHAR(255)
SET @path='c:elson1.txt'
SET @sql='BULK INSERT nelson_test1 FROM ''' + @path + ''' '
+'WITH
(
FIELDTERMINATOR = ''" '',
ROWTERMINATOR = '''',
FIRSTROW = 1
)
'exec @sql'
'
Still getting this error:
Msg 203, Level 16, State 2, Line 19
The name 'BULK INSERT nelson_test1 FROM 'c:elson1.txt' WITH
(
FIELDTERMINATOR = '" ',
ROWTERMINATOR = '',
FIRSTROW = 1
)
' is not a valid identifier.
August 5, 2008 at 8:48 am
i think it's parenthesis around the @sql....exec(@sql)
this works on my machine:
DECLARE @sql VARCHAR(8000)
DECLARE @path VARCHAR(255)
SET @path='c:[slash]nelson1.txt'
SET @sql='BULK INSERT nelson_test1 FROM ''' + @path + ''' '
+'WITH
(
FIELDTERMINATOR = '','',
ROWTERMINATOR = ''[slash]n'',
FIRSTROW = 1
)
'
exec (@sql) ----no single quotes around this
Lowell
August 5, 2008 at 8:59 am
I think it's something with the txt files, if I change the field 4 for a char(5000) it works
but put all in one line
like this
1JamesSmith19750101 2,Meggie,Smith,19790122 3,Robert,Smith,20071101 ,Alex,Smith,20040202
and I need number 2 to be a next record and so on
August 5, 2008 at 9:04 am
that is caused by the row terminator:
[slash]n is CrLf, which is found a lot.
try [slash]r which is just Carriage Return, which is common from UNIX type data sources.
Lowell
August 5, 2008 at 10:20 am
I tried with [slash]r and nothing, same error, any other idea??
thanks
Nelson
August 5, 2008 at 11:23 am
You could look at using open Opendatasource with a schema.ini file, which is described in http://www.sqlservercentral.com/articles/OpenDataSource/61552/ .
If this is 2000, DTS should also be able to do it very efficiently and easily.
---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/
August 5, 2008 at 11:28 am
it's not 2000, it's 2005
August 6, 2008 at 12:55 am
In 2005 you can use SSIS instead of DTS. It is more powerful and more standards compliant. The fact it is more standards compliant can make it less fault tolerant at times, but that merely means you may have to provide more specific instructions to it.
---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/
August 7, 2008 at 10:36 pm
BCP utility which comes with SQL Server can handle huge volumes of data very easily. If you already have an exisitng table, create format file using the bcp utility, usually found in the SQL serverinstallation directory/binn
Format file:
bcp table_or_view format nul -f format_file_name -Sservername -Uuserid -Ppassword -n
After creating the format file alter the datat type and postion as per your data file and run the bcp utility again.
Syntax:
bcp database.tablename in datafilename with location -fformatfilename with location -Sservername -Uuserid -Ppassword
For more information:http://msdn.microsoft.com/en-us/library/ms162802.aspx
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply