Importing Delimited Text - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Importing Delimited Text

Author

Topic

jprendes
Starting Member

5 Posts

Posted - 2011-10-07 : 05:52:49

Hello all - I am using SQL Express 2005 and need to import delimited text files being constantly written to a network folder to a table and then move the files to a subfolder (so they are not imported again).

Luckily for me, Nigel Rivett (http://www.nigelrivett.net/) did most of my work for me and posted the following script on his site (thanks Nigel!).

My problem is in the final portion of the script, inserting data to the production table: my file is delimited and fields are of varying lengths, so using the SUBSTRING command kills my data fields.

Here is a sample of the incoming text file:

10/ARPINV/000015|ABC COMPANY|10|INV1010|01-JUN-2010|10537.32|PHL0001
13/ARPINV/002041|XYZ COMPANY|12|INV2222|14-JUN-2010|2132.22|PHL0002
17/ARPINV/000363|DEF COMPANY|14|INV3333|27-JUN-2010|345.35|PHL0003
10/ARPINV/000016|ABC COMPANY|10|INV4444|30-MAY-2010|12501.23|PHL0004

And Nigel's script:

====================================================================

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ImportFiles]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[ImportFiles]
GO

create procedure ImportFiles
@FilePath varchar(1000) = 'c:\Transfer\' ,
@ArchivePath varchar(1000) = 'c:\Transfer\Archive\' ,
@FileNameMask varchar(1000) = 'bcp*.txt' ,
@MergeProc varchar(128) = 'MergeBCPData'

AS

set nocount on

declare @ImportDate datetime
select @ImportDate = getdate()

declare @FileName varchar(1000) ,
@File varchar(1000)

declare @cmd varchar(2000)

create table ##Import (s varchar(8000))
create table #Dir (s varchar(8000))

/*******************************/
-- Import file
/*******************************/
select @cmd = 'dir /B ' + @FilePath + @FileNameMask
delete #Dir
insert #Dir exec master..xp_cmdshell @cmd

delete #Dir where s is null or s like '%not found%'
while exists (select * from #Dir)
begin
select @FileName = min(s) from #Dir
select @File = @FilePath + @FileName

select @cmd = 'bulk insert'
select @cmd = @cmd + ' ##Import'
select @cmd = @cmd + ' from'
select @cmd = @cmd + ' ''' + replace(@File,'"','') + ''''
select @cmd = @cmd + ' with (FIELDTERMINATOR=''|'''
select @cmd = @cmd + ',ROWTERMINATOR = ''' + char(10) + ''')'

truncate table ##Import

-- import the data
exec (@cmd)

-- remove filename just imported
delete #Dir where s = @FileName

exec @MergeProc

-- Archive the file
select @cmd = 'move ' + @FilePath + @FileName + ' ' + @ArchivePath + @FileName
exec master..xp_cmdshell @cmd
end

drop table ##Import
drop table #Dir
go

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[MergeBCPData]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[MergeBCPData]
GO

create procedure MergeBCPData

AS
set nocount on

-- insert data to production table
insert BCPData
(
fld1 ,
fld2 ,
fld3 ,
fld4
)
select
fld1 = substring(s,1,3) ,
fld2 = substring(s,4,3) ,
fld3 = convert(int,substring(s,7,2)) ,
fld4 = convert(datetime,substring(s,9,8))
from ##Import

go

======================================================================

So, my question: How can I modify the final portion of that script to break fields using the pipe-delimiter instead of fixed length?

Your input would be greatly appreciated. And apologies to Nigel for reposting his script.

JP

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-07 : 05:59:35

try like


....
insert	BCPData
(
fld1 ,
fld2 ,
fld3 ,
fld4
)
select	
fld1	= parsename(replace(s,'|','.'),4) ,
fld2	= parsename(replace(s,'|','.'),3) ,
fld3	= convert(int,parsename(replace(s,'|','.'),2)) ,
fld4	= convert(datetime,parsename(replace(s,'|','.'),1))
from	##Import

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jprendes
Starting Member

5 Posts

Posted - 2011-10-07 : 06:54:37

Hi visakh16 - Thank you for the speedy reply. Looks like it's on the right track, but the import left the table populated with NULL values - same number of rows as the incoming text file, so it looks like it's processing correctly up to a point. I have modified the script as follows (this uses my actual fields as opposed to Nigel's generic fields):

....
insert KCL_MERGEDATA
(
COAREF ,
COMPNAME ,
COMPREF ,
INVNO ,
INVDATE ,
INVAMOUNT ,
POREF
)
select
COAREF = parsename(replace(s,'|','.'),7) ,
COMPNAME = parsename(replace(s,'|','.'),6) ,
COMPREF = parsename(replace(s,'|','.'),5) ,
INVNO = parsename(replace(s,'|','.'),4) ,
INVDATE = parsename(replace(s,'|','.'),3) ,
INVAMOUNT = parsename(replace(s,'|','.'),2) ,
POREF = parsename(replace(s,'|','.'),1)
from ##Import
....

Thansk in advance.

JP

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-07 : 07:00:43

ok...great

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jprendes
Starting Member

5 Posts

Posted - 2011-10-07 : 07:09:02

quote:
Originally posted by visakh16

ok...great

Sorry, I was unclear - the above is populating NULL values in the target table. Any advice?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-07 : 07:13:30

quote:
Originally posted by jprendes

quote:
Originally posted by visakh16

ok...great

Sorry, I was unclear - the above is populating NULL values in the target table. Any advice?

Do you have values coming for all the columns always?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jprendes
Starting Member

5 Posts

Posted - 2011-10-07 : 08:04:55

Yes - this is for integration from an accounting package - for each invoice the app will write out a text file to this folder. We need to import the data into SQL for further processing with a second application, so each field should always have a value.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-07 : 12:11:05

[code]
insert KCL_MERGEDATA
(
COAREF ,
COMPNAME ,
COMPREF ,
INVNO ,
INVDATE ,
INVAMOUNT ,
POREF
)
select
COAREF = max(case when id=1 then val else null end),
COMPNAME = max(case when id=2 then val else null end) ,
COMPREF = max(case when id=3 then val else null end) ,
INVNO = max(case when id=4 then val else null end),
INVDATE = max(case when id=5 then val else null end) ,
INVAMOUNT = max(case when id=6 then val else null end) ,
POREF = max(case when id=7 then val else null end)
from ##Import i
cross apply dbo.ParseValues(i.s,'|') f
group by i.s
[/code]

ParseValues can be found in below link

http://visakhm.blogspot.com/2010/02/parsing-delimited-string.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jassi.singh
Posting Yak Master

122 Posts

Posted - 2011-10-08 : 10:17:35

I think you should use bulk utility or bulk insert.

Please mark answer as accepted if it helped you.

Thanks,
Jassi Singh

jprendes
Starting Member

5 Posts

Posted - 2011-10-10 : 07:56:55

VisakhM - That, my friend, has made my week and it's only Monday.

I came across your ParseValues script searching the web, but could not adapt it for my usage.

I've taken your final suggestion and modified my SP's, built a third SP to launch the others, created the ParseValues function using your script, automated via Scheduled Tasks, et voila - automated delimited text file imports that move the text file to an archives folder.

EXACTLY what I needed. Thanks very much again for your help - could not have done it without you.

Jassi - I would use SSIS or even DTS, but the customer runs SQL Express 2005 and would not upgrade. I needed the scripts to get this done automatically and move the incoming text file once processed. I don't know how to mark the answer as accepted, but consider Visakh's answer accepted.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-10 : 07:59:19

quote:
Originally posted by jprendes

VisakhM - That, my friend, has made my week and it's only Monday.

I came across your ParseValues script searching the web, but could not adapt it for my usage.

I've taken your final suggestion and modified my SP's, built a third SP to launch the others, created the ParseValues function using your script, automated via Scheduled Tasks, et voila - automated delimited text file imports that move the text file to an archives folder.

EXACTLY what I needed. Thanks very much again for your help - could not have done it without you.

Jassi - I would use SSIS or even DTS, but the customer runs SQL Express 2005 and would not upgrade. I needed the scripts to get this done automatically and move the incoming text file once processed. I don't know how to mark the answer as accepted, but consider Visakh's answer accepted.

Welcome
Glad that I could help you out

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources