How to import tab delimited files into a table using BCP 4

[DCSage]

Hello.

I need to import text files over a four year period (which is well over 1000 text files). The text files are tab delimited (text is organized in columns...tab delimited right?) and I only want 4 columns from the files.
The text files have headers. I was thinking that my best option would be to use the bcp utility.

1) How would I properly use bcp to import the data into my table DUMP_Vids?

2) I also wanted an error log so that I could see what occurred during the import.

3) Is there also a way to import using conditions from the text files too?

This is what I have so far:

/*BCP Import to pull in records from a directory to DUMP_Vids table*/

bcp DUMP_Vids in "I:\My Documents- Episodes\Video Logs" -c -T -e

Thank you in advance.

 

[DCSage]

After reviewing this, I realize that I actually need to do a bulk insert. how can I insert multiple files using bulk insert?

I tried the following:

BULK INSERT DUMP_Vids
	FROM "C:\Video LOGS\*"
	WITH
    ( 
	FIRSTROW = 2,	
	MAXERRORS = 0,        
	FIELDTERMINATOR = '\t', 
        ROWTERMINATOR = '\t\n' 
    )

 

[Aluminum]

You could also look into using SSIS/DTS

 

[DCSage]

So using a dts to load the files into the table is better than bulk insert or bcp?

 

[Aluminum]

I think so. You can have error records re-direct into another table on import. Sounded like you wanted to do that. Using SSIS is also a bit more robust. You can set up a for each loop, put the file name into a variable and have the import loop through all of your text files. It also may be easier to debug since your not as familiar with the bulk copy.

 

[DCSage]

I don't have access to the business tools for SSIS. However, can this be done as a stored procedure?
I tried the following, but I am not sure if I am in the right direction:

/*
PROCESS

1.  Create a format file to import every file
2.  Determine size and other parameters of file 
3.  Execute BULK Insert to insert the file into the table

*/

-----------------------------
-- CreaTxtFmt Procedure
-- Automatically create a flat file for every .txt file in the directory
-----------------------------

Create procedure CreaTxtFmt 
@dir varchar(255), --directory ended with '\'
@length int --file length
AS

--WRITE THE FOLLOWING PER FIELD IN THE FILE

DECLARE @cmd varchar (8000)

--REMOVE TXT.FMT IF EXISTS

SET @cmd = 'del'+ @dir + 'txt.fmt'

EXEC master.dbo.xp_cmdshell @cmd, no_output

--CREATE 1ST LINE OF FILE FORMAT, FOR THE VERSION # OF SQL 2000 BCP

set @cmd = 'echo 8.0 >>' + @dir + 'txt.fmt'

EXEC master.dbo.xp_cmdshell @cmd, no_output

--WRITE THE 2ND LINE IS THE NUMBER OF FIELDS THE SOURCE FILE CONTAINS

set @cmd = 'echo 12 >>' + @dir + 'txt.fmt'

EXEC master.dbo.xp_cmdshell @cmd, no_output

--WRITE the third line in the file

set @cmd = 'echo 12 VARCHAR 0 ' + CONVERT(varchar(10), @length) + ' "" 

-----------------------------
-- InsertTxtFiles Procedure
-----------------------------
Create procedure InsertTxtFiles
@dir varchar(300)

AS

DECLARE @sdir varchar(300)

set @sdir = 'dir' + @dir + '*.txt'

/*INSERT TEMPORARY OUTPUT INTO TEMP ASRUN TABLE*/

INSERT Dump_ASRUN_temp
Airdate, ActualTime, Episode, IfSegment, Program, 
Duration, Status, Device, CH, Reconcile, Type, SEC

EXEC master.dbo.xp_cmdshell @sdir

DECLARE @Airdate varchar(50)
DECLARE @ActualTime varchar(50)
DECLARE @Episode varchar(8000)
DECLARE @IfSegment varchar(50)
DECLARE @Program varchar (8000)
DECLARE @Duration varchar (50)
DECLARE @Status varchar(50)
DECLARE @Device varchar(50)
DECLARE @CH varchar(50)
DECLARE @Reconcile varchar(50)
DECLARE @Type varchar(50)
DECLARE @SEC varchar(50)
DECLARE @sql varchar(8000)
DECLARE @file varchar (300)
DECLARE c_files CURSOR

FOR SELECT Airdate, ActualTime, Episode, IfSegment, Program, 
Duration, Status, Device, CH, Reconcile, Type, SEC 
FROM Dump_ASRUN_temp

OPEN c_files

FETCH NEXT FROM c_files 
INTO 
@Airdate, @ActualTime, @Episode, @IfSegment, @Program, 
@Duration, @Status, @Device, @CH, @Reconcile, @Type, @SEC, @file

WHILE @@FETCH_STATUS = 0

BEGIN


/*Import the files*/

SET @sql = 'BULK INSERT Dump_ASRUN_temp  FROM '''
+ @dir
+ @file
+ '''With (FORMATFILE = '''
+ @dir
+ '.txt'')'

EXECUTE (@sql)

GO

 

[Aluminum]

I can't help you to much with BCP since I have never used it. There is nothing wrong using BCP. Looks like your just looping through a cursor for the files and then running your BCP statement. Test it on a small subset and verify record counts.

 

[DCSage]

hmmm...i modified my stored procedure and it still doesn't work. my files weren't imported into the database at all. I am not sure what to do or how to correct this as my options seem limited.

-----------------------------
-- CreaTxtFmt Procedure
-- Automatically create a flat file for every .txt file in the directory
-----------------------------

CREATE PROCEDURE CreaTxtFmt 
@dir varchar(255), --directory ended with '\'
@length int --file length

AS

--WRITE THE FOLLOWING PER FIELD IN THE FILE

DECLARE @cmd varchar(8000)

--REMOVE TXT.FMT IF EXISTS

SET @cmd = 'del'+ @dir + 'txt.fmt'

EXEC master.dbo.xp_cmdshell @cmd, no_output

--CREATE 1ST LINE OF FILE FORMAT, FOR THE VERSION # OF SQL 2000 BCP

set @cmd = 'echo 8.0 >>' + @dir + 'txt.fmt'

EXEC master.dbo.xp_cmdshell @cmd, no_output

--WRITE THE 2ND LINE IS THE NUMBER OF FIELDS THE SOURCE FILE CONTAINS

set @cmd = 'echo 12 >>' + @dir + 'txt.fmt'

EXEC master.dbo.xp_cmdshell @cmd, no_output

--WRITE the third line in the file

set @cmd = 'echo 1 VARCHAR 0 ' + CONVERT(varchar(10), @length) + ' "" 1 txt "" >>' + @dir + 'txt.fmt'

EXEC master.dbo.xp_cmdshell @cmd, no_output

GO
-----------------------------
-- InsertTxtFiles Procedure
-----------------------------

Create procedure InsertTxtFiles
@dir varchar(300)

AS

DECLARE @sdir varchar(300)

set @sdir = 'dir' + @dir + '*.TXT'

/*INSERT TEMPORARY OUTPUT INTO TEMP ASRUN TABLE*/

INSERT Dump_ASRUN_temp(Airdate, ActualTime, Episode, IfSegment, Program, Duration, Status, Device, CH, Reconcile, Type, SEC)

EXEC master.dbo.xp_cmdshell @sdir

DECLARE @file varchar(300)
DECLARE @length int
DECLARE @sql varchar(8000)
DECLARE @Airdate varchar(50)
DECLARE @ActualTime varchar(50)
DECLARE @Episode varchar(8000)
DECLARE @IfSegment varchar(50)
DECLARE @Program varchar(8000)
DECLARE @Duration varchar(50)
DECLARE @Status varchar(50)
DECLARE @Device varchar(50)
DECLARE @CH varchar(50)
DECLARE @Reconcile varchar(50)
DECLARE @Type varchar(50)
DECLARE @SEC varchar(50)

DECLARE c_files CURSOR
FOR SELECT Airdate, ActualTime, Episode, IfSegment, Program, 
Duration, Status, Device, CH, Reconcile, Type, SEC 
FROM Dump_ASRUN_temp

OPEN c_files

FETCH NEXT FROM c_files INTO 
@Airdate, @ActualTime, @Episode, @IfSegment, @Program, @Duration, @Status, @Device, @CH, @Reconcile, @Type, @SEC, @file

WHILE @@FETCH_STATUS = 0

BEGIN

EXEC CreaTxtFmt @dir, @length

/*Import the files*/

SET 
@sql = 'BULK INSERT Dump_ASRUN_temp FROM ''' + @dir + @file 
+ ''' WITH(FORMATFILE = ''' + @dir + 'txt.fmt'')'

EXECUTE (@sql)

GO

EXEC InsertTxtFiles 'C:\Documents and Settings\ASRUN LOGS\'
[code]

 

[NastiaMurzin]

Here are some of the options that you have:

bcp
dbo.tableschema.tablename
"select column1, column2, column3, column4 into dbo.tableschema.DUMP_Vids
from dbo.tableschema.tablename order_by somecolumnnameoutofchosenfour"
queryout outputfilename.txt
in outputfilename.dat
-f outputformatfilename.xml
-e errorfilename.dat
-o outputfilename2.dat
-U<loginname>
-P<password>

 

[DCSage]

Hi NastiaMurzin.

So, I can use bcp to import the multiple text files into the sql server table? is your example for data from a table to a file? I have over 1500 files (and growing) to export to a sql table. i am stuck on how to set this up properly.

 

[gmmastros]

DCSage,

Are you required to do this completely within TSQL? I am working on a similar project now. What I am doing is....

I use a front end language to get the files I need to import. In my case, I have several thousand folders each with about 12 files in them. I created a format file that can be used with all of my files. This is just one file. I then use a Bulk Insert command (BCP could also be used). My point is.... it's often easier to use a front end language to do the dirty work (like enumerating files). The front end language would then shell out to BCP or connect to the DB to run the Bulk Insert command.

Often times, people will initially bulk insert data in to a temporary table, and then do some clean up work and data validation. And then eventually move the data to wherever it belongs. By using a staging table, you ultimately slow down the process a little (usually not noticeable), but you now have more flexibility because you can clean up your data before importing to real tables. You can make all the columns in your staging table be nvarchar's and then validate data types (int's, decimals, etc....).

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[DCSage]

hi gmmastros.

by front-end language, do you mean C# or vb and then use a loop to export the data from the text files? or did i misunderstand?

thanks in advance.

 

[gmmastros]

You understood. I prefer VB, but that's because I am more familiar with it.

In psuedo code....

For each File in Folder
Shell to command prompt to run BCP command
Next

or

For each file in folder
Execute bulk insert command to temp table
validate data
move valid data to real table
report on invalid data
Next



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[DCSage]

thanks gmmastros.

the text files are tab delimited (in columns). i created a table in my database so that when I export to the tables, the data should be inserted in those columns. Is this possible? I am going to use c#

 

[DCSage]

Hi. I tried a few options, and then finally resorted to using C# and calling the bcp utility from my console app.

The problem is that I get the error:

User name not provided, use -T for trusted connection. I don't need a user name or password as the authentication is windows authentication. I already have -T, so I am not sure why this doesn't work.

My app, to insert the text files data into the table, is pretty simple. if i am looping through the folder, why would I still receive this error:

 static void Main(string[] args)
        {
            Process myProc = new Process();
            string sourceDir = @"C:\ASRUN Sample\";                        

            string[] fileEntries = Directory.GetFiles(sourceDir);
            DirectoryInfo di = new DirectoryInfo(@"C:\ASRUN Sample\");

            try
            {
                // use it to start from testing environment
                myProc.StartInfo.UseShellExecute = false;

                // redirect outputs to have it in testing console
                myProc.StartInfo.RedirectStandardOutput = true;
                myProc.StartInfo.RedirectStandardError = true;

                foreach (FileInfo fi in di.GetFiles())
                {
                    myProc.StartInfo.FileName = "bcp";
                    //CREATE formatfile
                    myProc.StartInfo.Arguments = @"""LegalCueSheets.dbo.Dump_ASRUN_temp"" format nul -f ""c:\ASRUN Sample\Log.fmt"" -c -T -t";
                    //BEGIN BCP process
                    myProc.StartInfo.Arguments = @"""LegalCueSheets.dbo.Dump_ASRUN_temp"" in ""c:\ASRUN Sample\"" -S Corp-Appdev\Dev2000 -c \t, -T";
                    myProc.Start();

                    // get output to testing console.
                    System.Console.WriteLine(myProc.StandardOutput.ReadToEnd());
                    System.Console.Write(myProc.StandardError.ReadToEnd());
                    Console.ReadLine();

                }
            }
            catch (Exception someErr)
            {
                Console.Write(someErr);
            }
        }

 

[gmmastros]

try removing the comma.

[tt]myProc.StartInfo.Arguments = @"""LegalCueSheets.dbo.Dump_ASRUN_temp"" in ""c:\ASRUN Sample\"" -S Corp-Appdev\Dev2000 -c \t[!],[/!] -T";[/tt]

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[DCSage]

I tried that and the console reads: User name not provided:

myProc.StartInfo.Arguments = @"""LegalCueSheets...Dump_ASRUN_temp"" in ""c:\ASRUN Sample\"" -S" + newdb + "-T -c \t";

 

[DCSage]

Nothing works. are there other alternatives?

 

[philhege]

You can, given the rights to xp_cmdshell, do this all in T-SQL. Pipe your DIR result into a temporary table. Now you have a list of the files you need to import. Spin through the work table, assign the file name to a variable, and cobble together some dynamic SQL (or use sp_executeSQL) to construct and invoke the bcp commands.

Phil H.
Some Bank
-----------
Time's fun when you're having flies.

 

[NastiaMurzin]

bulk insert dbo.tableschema.tablename
from '\\foldername\filename.txt';
go

I think that could work.