Bulk Insert Problem 1

[dendic]

When I do the import file manually from table import dropdown this text document populates the table normally. But when I use Bulk Insert I get no records returned message. I tried this with a primary key auto mumber and without. Same result. My front end is Microsoft ADP and the bake end is SQL Server 2008. Running this as a stored procedure. The real message is the procedure finshed normally but with no records. I opened the text file in a hex editor and confirmed there is a cr and lf for each line but there was two at the EOF I removed one but the result is still the same. Please help I've been all over the internet and can't find a solution. Been working on this for several days. If you don't have an answer maybe there is another solution. I know there are programmers out there who are importing thousands of records daily how are they doing it?

Stored Procedure:
AS
CREATE TABLE EDI3
(

Field1 varchar(30), Field2 varchar(30), Field3 varchar(30), Field4 varchar(30), Field5 varchar(30), Field6 varchar(30), Field7 varchar(30), Field8 varchar(30), Field9 varchar(30), Field10 varchar(30), Field11 varchar(30), Field12 varchar(30),
Field13 varchar(30), Field14 varchar(30), Field15 varchar(30), Field16 varchar(30), Field17 varchar(30), Field18 varchar(30), Field19 varchar(30), Field20 varchar(30), Field21 varchar(30), Field22 varchar(30), Field23 varchar(30), Field24 varchar(30)
)

declare @sql1 varchar(1000), @path varchar(255)
set @path = '\\Elite\c_dns\EDITest\'
--
set @sql1 = 'BULK INSERT edi3
FROM ' @Path + 'edi.txt' )
+ ' WITH (
FIELDTERMINATOR = "*'',
ROWTERMINATOR = ' \n')
exec(@sql1)

Text File:
ST*322*000124314
Q5*A*20010919*1215*LT**NASHVILLE*TN
N7*CSXU*982744*********CN*REAZ***4800*****114*98
DTM*140*20010919*142543*ET
W2*CSXU*982744*WINE*CN*L
NA***BRAN* 4741*F
R4*5*SL*434300
N1*RO*OAKLAND*20*876430
N1*RD*MIAMI*20*499600
N1*SH*CSXINTERM
N9*BM*5646445
N9*WY*186502**20010913
N9*UT*Q12519
SE*14*000124314

 

[JeffModen]

1. In the text you posted, it looks like you have a real double quote just before the asterick.
2. It looks like you have a space before the backslash-N. Is that really the end of line combination?
3. It looks like you are missing some doubled up single quotes for that same row terminator. In fact, it looks like you're missing quite a few of these...
4. It also looks like you have an extra parenthesis.
5. You're missing a "+" sign near path

I suspect you want the code to look like the following...

SET @sql1 = 'BULK INSERT ''edi3''
FROM ''' + @Path + 'edi.txt''
WITH 
(
FIELDTERMINATOR = ''*'',
ROWTERMINATOR = ''\n''
)'

My recommendation in the future is to first write non-dynamic code that actually works. Once that's done, then it's a simple matter of replacing single quotes with two single quotes and then adding the concatenated path (or whatever) along with it's own set of quotes.

If you run into problems, then print the Dynamic SQL (instead of executing it) and see if you did anything wrong.

--Jeff Moden
-------------------------------------------------------------------------------
"RBAR" is pronounced "ree-bar" and is a "Modenism" for "Row By Agonizing Row

 

[dendic]

Thank you for your input. I created the procedure as you suggested but the results are the saem "no records returned". Please don't give up on me. I've tried so many different combinations. I got the one I sent you from the internet. I don't understand the difference between dynamic and non-dynamic code. Also, how would I print instead of execute? Perhaps you would be kind enough to show me the simplest way to create this procudure. Thanks again it sounds like you really know your stuff

AS
CREATE TABLE EDI3
(

Field1 varchar(30), Field2 varchar(30), Field3 varchar(30), Field4 varchar(30), Field5 varchar(30), Field6 varchar(30), Field7 varchar(30), Field8 varchar(30), Field9 varchar(30), Field10 varchar(30), Field11 varchar(30), Field12 varchar(30),
Field13 varchar(30), Field14 varchar(30), Field15 varchar(30), Field16 varchar(30), Field17 varchar(30), Field18 varchar(30), Field19 varchar(30), Field20 varchar(30), Field21 varchar(30), Field22 varchar(30), Field23 varchar(30), Field24 varchar(30)

)

declare @sql1 varchar(1000), @path varchar(255)
set @path = '\\Elite\c_dns\EDITest\'
SET @sql1 = 'BULK INSERT ''edi3''
FROM ''' + @Path + 'edi.txt''
WITH
(
FIELDTERMINATOR = ''*'',
ROWTERMINATOR = ''\n''
)
exec(@sql1)'

 

[JeffModen]

Sorry for my slow response... I get about 300 emails a day and I just about missed this one.

I didn't look at it real close before but I notice the text file contains a different number of "*" delimiters on each line. Just to make sure, the only thing you want to do is import the whole line, right?

Lemme know and I'll post an example and answer your other questions. Not being evasive here... I just can't get to this right this second.

--Jeff Moden
-------------------------------------------------------------------------------
"RBAR" is pronounced "ree-bar" and is a "Modenism" for "Row By Agonizing Row

 

[dendic]

The lines are not equal length. The other delimiters are fields that may be used for time to time. I do want to import the whole line because I won't know until I parse the record to determine if the particular field is populated. Thank you

 

[JeffModen]

Importing the data as flat lines will be no problem. Splitting them and putting them into the correct columns is a bit more difficult and will require some explanation... I'll be back this afternoon (EDST)... on my way to a meeting right now.

--Jeff Moden
-------------------------------------------------------------------------------
"RBAR" is pronounced "ree-bar" and is a "Modenism" for "Row By Agonizing Row

 

[JeffModen]

I'm working on your problem now. Sorry it took so long to get back to it...

--Jeff Moden
-------------------------------------------------------------------------------
"RBAR" is pronounced "ree-bar" and is a "Modenism" for "Row By Agonizing Row

 

[JeffModen]

Ok... lemme just say that of all the text file formats available in the world, that I hate EDI formats the most. ;-) Worse than being "difficult" to parse using SQL, it must be done in the order that the lines appear in the file. Bulk Insert will certainly do that but, once they're in the database, there's no guarantee of order unless you keep track of the order as the lines are inserted. We need a table with an IDENTITY column in a staging table to do that. In order import on that, we need a BCP format file because we can't make a view to do it because we want the staging table to be a temp table so this process can run multiple times concurrently. Here's what the BCP format file should look like for SQL Server 2008 (I think)...

10.0
2
1 SQLCHAR 0 7    ""     0 RowNum  ""
2 SQLCHAR 0 1000 "\r\n" 2 RawData ""

Save that file as "EDI3.fmt" somewhere where SQL Server can get at it like it does the .txt files. Notice that the "RowNum" will NOT be loaded from the file (0 just to the left of RowNum) and that the "RawData" will be loaded into column 2 of the staging table. If you haven't worked with format files in the past, do yourself a favor and read about them in Books Online (format files [SQL Server], non-XML format files).

Because of the unequal number of delimiters on each line, be can't do a direct import into a table using Bulk Insert (actually, you should NEVER import to the final table. ALWAYS use a staging table so you can validate the data. It's the SAFE thing to do). We have to import the whole line and split it.

There're a lot of ways to "split" data. One of the most effective is to use a "Tally" or "Numbers" table. Obviously, you'll need the code for that which I've included below. If you want to know how a Tally table actually works (and you should... it has dozens of uses), please see the article at the following URL...
[URL unfurl="true"]http://www.sqlservercentral.com/articles/T-SQL/62867/[/url]

So, here's the plan...

1. Bulk Insert the data into a staging table as whole numbered rows so we can work on the data.

2. Split the data all at once without using a UDF, etc... this will also unpivot the data and we have to fix that.

3. Reassemble the rows using a classic CROSS TAB which, by it's very nature, will pivot the data back as we want it. At the same time, we'll insert the reassembled data into the EDI3 table.

Ready? Read the comments in the code... this is the whole 9 yards...

--===== Declare some obviously named variables that will be used as parameters
     -- in a stored procedure.  Obviously, these will need to be moved when you
     -- turn this into a stored procedure.  
DECLARE @pPathTextFile VARCHAR(512),
        @pNameTextFile VARCHAR(512)
;
--===== Create the staging table (conditional drop included to make testing easier)
     IF OBJECT_ID('TempDB..#Staging','U') IS NOT NULL
        DROP TABLE #Staging
;
 CREATE TABLE #Staging (RowNum INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, RawData VARCHAR(1000))
;
--===== Declare some obviously named local variables
DECLARE @SQL VARCHAR(500),
        @FullNameTextFile VARCHAR(1024)
;
--===== The following are some presets to test this script with.  
     -- They would not be included in the final stored procedure.
     -- You DO need to change them for testing.
 SELECT @pPathTextFile    = 'C:\Temp\Dodah',
        @pNameTextFile    = 'edi.txt'
;
--===== Assemble the full path and file name.
     -- The CASE just makes sure the path ends with a backslash.
 SELECT @FullNameTextFile = CASE 
                                WHEN RIGHT(@pPathTextFile,1) = '\'
                                THEN @pPathTextFile
                                ELSE @pPathTextFile + '\'
                            END
                          + @pNameTextFile
;
--===== This creates the SQL for the BULK INSERT
 SELECT @SQL = '
   BULK INSERT #Staging
   FROM ''' + @FullNameTextFile + '''
   WITH (FORMATFILE = ''C:\Temp\Dodah\EDI3.fmt'')' --LOOKY HERE!!!! YOU NEED TO CHANGE THIS LOCATION!!!
;
--===== Print the command just see what it looks like.
     -- Comment this code out for production... it's just a sanity check for testing.
PRINT @SQL
;
--===== Execute the dynamic BULK INSERT command
   EXEC (@SQL)
;
--===== Now that we have everything loaded into the staging table,
     -- let's split it, pivot it, and insert it into EDI3... all in one smooth stroke.
WITH 
cteSplit AS
(--==== Splits and unpivots the data in each row using the Tally table method
 SELECT RowNum,
        ElementNumber = ROW_NUMBER() OVER (PARTITION BY RowNum ORDER BY s.RowNum, t.n),
        ElementValue  = NULLIF(SUBSTRING(s.RawData,N,CHARINDEX('*',s.RawData+'*',N)-N),'')
   FROM dbo.Tally t
  CROSS JOIN #Staging s
  WHERE N <= LEN(s.RawData)+1
    AND SUBSTRING('*'+s.RawData,t.N,1) = '*'
)
 INSERT INTO dbo.EDI3 
        (--==== I always include a column list on the inserts just to be safe
         Field1,  Field2,  Field3,  Field4, 
         Field5,  Field6,  Field7,  Field8, 
         Field9, Field10, Field11, Field12,
        Field13, Field14, Field15, Field16, 
        Field17, Field18, Field19, Field20, 
        Field21, Field22, Field23, Field24
        ) --===== Classic crosstab recombines and repivots the split data
 SELECT MAX(CASE WHEN split.ElementNumber =  1 THEN split.ElementValue END) AS Field1,
        MAX(CASE WHEN split.ElementNumber =  2 THEN split.ElementValue END) AS Field2,
        MAX(CASE WHEN split.ElementNumber =  3 THEN split.ElementValue END) AS Field3,
        MAX(CASE WHEN split.ElementNumber =  4 THEN split.ElementValue END) AS Field4,
        MAX(CASE WHEN split.ElementNumber =  5 THEN split.ElementValue END) AS Field5,
        MAX(CASE WHEN split.ElementNumber =  6 THEN split.ElementValue END) AS Field6,
        MAX(CASE WHEN split.ElementNumber =  7 THEN split.ElementValue END) AS Field7,
        MAX(CASE WHEN split.ElementNumber =  8 THEN split.ElementValue END) AS Field8,
        MAX(CASE WHEN split.ElementNumber =  9 THEN split.ElementValue END) AS Field9,
        MAX(CASE WHEN split.ElementNumber = 10 THEN split.ElementValue END) AS Field10,
        MAX(CASE WHEN split.ElementNumber = 11 THEN split.ElementValue END) AS Field11,
        MAX(CASE WHEN split.ElementNumber = 12 THEN split.ElementValue END) AS Field12,
        MAX(CASE WHEN split.ElementNumber = 13 THEN split.ElementValue END) AS Field13,
        MAX(CASE WHEN split.ElementNumber = 14 THEN split.ElementValue END) AS Field14,
        MAX(CASE WHEN split.ElementNumber = 15 THEN split.ElementValue END) AS Field15,
        MAX(CASE WHEN split.ElementNumber = 16 THEN split.ElementValue END) AS Field16,
        MAX(CASE WHEN split.ElementNumber = 17 THEN split.ElementValue END) AS Field17,
        MAX(CASE WHEN split.ElementNumber = 18 THEN split.ElementValue END) AS Field18,
        MAX(CASE WHEN split.ElementNumber = 19 THEN split.ElementValue END) AS Field19,
        MAX(CASE WHEN split.ElementNumber = 20 THEN split.ElementValue END) AS Field20,
        MAX(CASE WHEN split.ElementNumber = 21 THEN split.ElementValue END) AS Field21,
        MAX(CASE WHEN split.ElementNumber = 22 THEN split.ElementValue END) AS Field22,
        MAX(CASE WHEN split.ElementNumber = 23 THEN split.ElementValue END) AS Field23,
        MAX(CASE WHEN split.ElementNumber = 24 THEN split.ElementValue END) AS Field24
   FROM cteSplit split
  GROUP BY split.RowNum
  ORDER BY split.RowNum
;
-- ... and that's "all" there is to it.

--Jeff Moden
-------------------------------------------------------------------------------
"RBAR" is pronounced "ree-bar" and is a "Modenism" for "Row By Agonizing Row

 

[JeffModen]

Sorry... it was a long post and I forgot two very important things. The first thing is the code for the Tally table...

--===== Create and populate the Tally table on the fly
 SELECT TOP 11000 IDENTITY(INT,1,1) AS N
   INTO dbo.Tally
   FROM Master.sys.All_Columns sc1
  CROSS JOIN Master.sys.All_Columns sc2

--===== Add a Primary Key to maximize performance
  ALTER TABLE dbo.Tally
    ADD CONSTRAINT PK_Tally_N 
        PRIMARY KEY CLUSTERED (N) WITH FILLFACTOR = 100

--===== Allow the general public to use it
  GRANT SELECT ON dbo.Tally TO PUBLIC

The second thing is, you need to find the person that insisted on the EDI format. Then, invite that person to a wonderfully satisfying pork chop dinner... tie them to the chair... and feed them frozen pork chops at point blank range with a sling shot. ;-)

--Jeff Moden
-------------------------------------------------------------------------------
"RBAR" is pronounced "ree-bar" and is a "Modenism" for "Row By Agonizing Row

 

[dendic]

Thank you very much. You are a master.

 

[JeffModen]

Thanks for the feedback, dendic... let us know how it works out.

--Jeff Moden
-------------------------------------------------------------------------------
"RBAR" is pronounced "ree-bar" and is a "Modenism" for "Row By Agonizing Row

 

[dendic]

Hi Jeff,
I get an error when I run the procedure to create the tally table. invalid object name master.sys.all_columns. I ran as a stored procudure.

--===== Create and populate the Tally table on the fly
SELECT TOP 11000 IDENTITY(INT,1,1) AS N
INTO dbo.Tally
FROM Master.sys.All_Columns sc1
CROSS JOIN Master.sys.All_Columns sc2

--===== Add a Primary Key to maximize performance
ALTER TABLE dbo.Tally
ADD CONSTRAINT PK_Tally_N
PRIMARY KEY CLUSTERED (N) WITH FILLFACTOR = 100

--===== Allow the general public to use it
GRANT SELECT ON dbo.Tally TO PUBLIC

 

[markros]

Worked fine for me. SQL Server 2008

 

[dendic]

I to am running 2008. Am I doing it right? I created the stored procudure and double clicked it to run.

 

[markros]

In order to run SP you need to use EXECUTE statement, e.g.

create procedure PrepareTallyTable ...
as
begin
   code
end
go

execute PrepareTallyTable

 

[JeffModen]

Not supposed to run the Tally table build script as a stored procedure... just run it as a script from SSMS. It's a one time script.

--Jeff Moden
-------------------------------------------------------------------------------
"RBAR" is pronounced "ree-bar" and is a "Modenism" for "Row By Agonizing Row

 

[markros]

Yes, you're right. I was just showing the general idea of running SP.

 

[JeffModen]

What is the compatability mode of the server you're trying to run the Tally Table creation script on, dendic?

--Jeff Moden
-------------------------------------------------------------------------------
"RBAR" is pronounced "ree-bar" and is a "Modenism" for "Row By Agonizing Row