Solved

How to use Ms Sql UPDATE command with an Ascci file

Posted on 2010-09-09
542 Views
We have various short text files that we need to use to update some of our tables.

For example, our off-site office sent us their terms in a text/ASCII file.  Its contents:

Code   Description
01       1% 6 N/30
02       2%/20 N/30
03       2 % 20 DAYS NET 40
04       2 % 30 DAYS

We have a table we need to update it with that data.

How can we embed an import or reading of an ASCII file with an UPDATE command?
0
Question by:rayluvs
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• Learn & ask questions
• 15
• 12
• 5
• +3

LVL 11

Expert Comment

ID: 33635518
Hi

You can use SSIS package to do that job.
0

Author Comment

ID: 33635561
Can you give more details
0

LVL 7

Expert Comment

ID: 33636103
You could create a Linked Server or use OpenRowSet to link to a text file.  However, I agree with Rajvja.  Using SSIS is a better solution.  Another option would be to use bulk insert to import the text files into staging tables.
0

Author Comment

ID: 33636493
I'm not too familiar with Linked Server, OpenRowSet, SSIS or staging files.

What we need to do is read the ASCII file line by line, use an UPDATE to update the SQL table with that data.

It seems that the process to use an UPDATE command to update a table with data from an ASCII file is not as simple.

Between Linked Server, OpenRowSet, SSIS or staging files, which is simpler?

Also, can you give an example?
0

LVL 7

Accepted Solution

lundnak earned 200 total points
ID: 33641962
The following is pulled from Microsofts Help pages.
http://msdn.microsoft.com/en-us/library/ms190312.aspx

D. Using OPENROWSET to bulk insert file data into a varbinary(max) column

The following example creates a small table for demonstration purposes, and inserts file data from a file named Text1.txt located in the C: root directory into a varbinary(max) column.

Copy
GO
CREATE TABLE myTable(FileName nvarchar(60),
FileType nvarchar(60), Document varbinary(max));
GO

INSERT INTO myTable(FileName, FileType, Document)
SELECT 'Text1.txt' AS FileName,
'.txt' AS FileType,
* FROM OPENROWSET(BULK N'C:\Text1.txt', SINGLE_BLOB) AS Document;
GO
0

Author Comment

ID: 33642413
DIdn't work.

We have 2 ASCII files and both are payment terms.  One has Code and the other only has the description:

Code   Description
01       1% 6 N/30
02       2%/20 N/30
03       2 % 20 DAYS NET 40
04       2 % 30 DAYS

Description
1% 6 N/30
2%/20 N/30
2 % 20 DAYS NET 40
2 % 30 DAYS

The path is in c:\Program Files\VBtests\
I already have the tables created

I tried your script as follows:

INSERT INTO terms (Terms)
SELECT 'c:\Program Files\VBtests\terms.txt' AS Terms,
'.txt' AS FileType,
* FROM OPENROWSET(BULK N'c:\Program Files\VBtests\terms.txt', SINGLE_BLOB) AS terms

Am i missing something here?
0

Author Comment

ID: 33642420
also tried it woth the ASCII with 2 columns
0

LVL 75

Expert Comment

ID: 33642901
>>Am i missing something here?<<
Yes, something a tad more illustrative than "Didn't work".  That expression is fine for our users to use, we expect a slightly higher standard in this profession.
0

LVL 7

Expert Comment

ID: 33643114
See the example below.  In order to create the fmt file you need to execute this command.

C:\Temp>bcp tempdb.dbo.mytable format nul -c -t" " -f c:\temp\myTable.Fmt -T -S <<SQLINSTANCE>>

Read the Microsoft Help documents regarding OPENROWSET and BCP for details.
use TempDB
go

drop table mytable
CREATE TABLE myTable(id int, name varchar(100));
GO

--#### run BCP command to create fmt file

INSERT INTO myTable(id, name)
SELECT * FROM OPENROWSET(BULK N'C:\temp\Test.txt', formatfile='c:\temp\mytable.fmt') AS Document;
GO

0

Author Comment

ID: 33643399
Let me put it this way, in my entry "ID 33642413", I state I have 2 ASCII files.  Also, I have 2 tables already created, one has only one column (Table Terms) and the other has has 2 column (Table Terms2).  Both resemble the ASCII file I want to import to those tables.

This being said, I used these lines:

For the Table Terms:

INSERT INTO terms (Terms)
SELECT 'c:\Program Files\VBtests\terms.txt' AS Terms,
'.txt' AS FileType,
* FROM OPENROWSET(BULK N'c:\Program Files\VBtests\terms.txt', SINGLE_BLOB)
AS terms

For the Table Terms2:

INSERT INTO terms (CodeId, Terms)
SELECT 'c:\Program Files\VBtests\terms.txt' AS Terms,
'.txt' AS FileType,
* FROM OPENROWSET(BULK N'c:\Program Files\VBtests\terms.txt', SINGLE_BLOB)
AS terms

My question, based on the info provided, is it correct? am I missing a syntax, format, etc.?

lundnak:

I used your example, and it gives a "incorrect document syntax".  Heres the script:

INSERT INTO terms(terms)
SELECT * FROM OPENROWSET(BULK N'c:\Program Files\VBtests\terms.txt',
formatfile='c:\Program Files\VBtests\terms.txt') AS Document

I would like to point out that we are not experts in SQL, we would appreciate any assistance provided.
0

LVL 7

Expert Comment

ID: 33645843
The first openrowset statements won't work for you because they using the SINGLE_BLOB method.  This implies you are going to import the entire file as one binary object.

That is why I posted the second OPENROWSET post.  One problem I see with the syntax is that you don't have a valid formatfile.  In your example the format file is set to the text file.  You need to run the BCP command that I mentioned in my ealier post.  This will create the format file.

Can you post the contents of the terms.txt file?
0

Author Comment

ID: 33647385
Ok... I will, thanx.  Just to understand, whats BCP for and where is the fmt file created?
0

LVL 7

Expert Comment

ID: 33647510
BCP is a tool installed with SQL Server.  I think it stands for BulkCopy.

The format file is defined by the -f parameter.
C:\Temp>bcp tempdb.dbo.mytable format nul -c -t" " -f c:\temp\myTable.Fmt -T -S <<SQLINSTANCE>>
0

Author Comment

ID: 33648432
Ok, so BCP will actually import my ASCII and also it has to be runned via DOS prompt?
0

LVL 7

Expert Comment

ID: 33648507
BCP can perform an import.  But in this case I only used it to create the format file.

You can also use bulk insert to import a text file.  See the attached example.

It might be a better solution for you because it is easier to use.

use TempDB
go

drop table mytable
CREATE TABLE myTable(id int, name varchar(100));
GO

BULK INSERT myTable
FROM 'C:\temp\test.txt'
WITH
(
FIELDTERMINATOR =' ',
ROWTERMINATOR ='\n'
)

0

Author Comment

ID: 33651064
It worked perfectly.

But how can we embed this process with the UPDATE command?
0

LVL 7

Expert Comment

ID: 33659247
You won't be able to with the Bulk Insert command, but you might be able to use the OpenRowSet command with an update statement.  I've never tried.
0

Author Comment

ID: 33659483
Yes we read on that, but everything we do fails.  Again, we have big text files with tab delimiter.  How can we use "OpenRowSet" for the following ASCII file and include an UPDATE for the sql table in the system:

Code   Description
01       1% 6 N/30
02       2%/20 N/30
03       2 % 20 DAYS NET 40
04       2 % 30 DAYS

What we want is to read the ASCII file, search if code exist in the sql table and update the descriptio?.
0

LVL 60

Expert Comment

ID: 33708891
Think the Experts above have provided the answer -- just a matter of applying to your exact environment I think.  The tab delimiter you just mentioned I think is the first time I saw that mentioned -- though I only skimmed through previous postings -- but it is key to how to parse data out of rows as you will see one suggestion used a FIELDTERMINATOR =' ' which would have to change to FIELDTERMINATOR ='\t' or in SQL you would have to split on CHAR(9).

http://msdn.microsoft.com/en-us/library/ms187323.aspx
0

LVL 60

Expert Comment

ID: 33708903
Try the solution above again and you will see it works quite nicely and easily.

Modified from http:#a33648507 << please award appropriately

Results:
id      name
1      1% 6 N/30
2      2%/20 N/30
3      2 % 20 DAYS NET 40
4      2 % 30 DAYS
CREATE TABLE myTable(id int, name varchar(100));
GO

BULK INSERT myTable
FROM 'C:\temp\test.txt'
WITH
(
FIELDTERMINATOR ='\t',
ROWTERMINATOR ='\n'
)

SELECT * FROM myTable;
DROP TABLE myTable;

test.txt
0

LVL 60

Expert Comment

ID: 33708919
To complete the solution for what you are asking regarding UPDATE, you would (before dropping the temp table) JOIN to your original data on the id column -- which I see now should not have been made an INT since your file has '01', '02' ... so just change that to VARCHAR(10) or something -- and do update like this:
UPDATE tto
SET tto.Terms = tfr.Name
FROM Terms tto
JOIN #myTable tfr ON tfr.id = tto.codeid
;

0

LVL 51

Assisted Solution

Mark Wills earned 300 total points
ID: 33711192
With that format file, it is going to be challenging to get "openrowset" working without some extra help. The extra help is needed to specify the terminator in an otherwise straight text file.

That's why the suggestion for BULK INSERT, you do get the chance to specify FIELDTERMINATOR ='\t'

With it not being a real CSV (ideally with column headings), then some of the options are not available.

But, you can still use the openrowset if your really want, just have to help it understand the format. That extra help can come from a "format file". The format file basically describes the content of the file to be imported. So, you would need one per different format / layout and can be either an XML file or a TXT file depending on your preference. I have attached an example of both below.

Then you can use openrowset as per :

select * FROM Openrowset(Bulk 'C:\ee\Q_26461276_test.txt', formatfile = 'c:\ee\Q_26461276_xml_format.xml', firstrow=1) A
--or
select * FROM Openrowset(Bulk 'C:\ee\Q_26461276_test.txt', formatfile = 'c:\ee\Q_26461276_txt_format.txt', firstrow=1) A

Then you can use the openrowset as you would any other table / data source...

-- step 1 create a temporary table as an example only...

if object_id('tempdb..#mytable','u') is not null drop table #mytable
CREATE TABLE #myTable(id int, name varchar(100));

-- use the BULK INSERT method as previously demonstrated and works

BULK INSERT #myTable FROM 'c:\ee\Q_26461276_test.txt' WITH (FIELDTERMINATOR ='\t',ROWTERMINATOR ='\n')

-- now make the name column something different for later UPDATE example

UPDATE #mytable set name = 'OLD:'+name
SELECT * FROM #myTable;

-- We can use OPENROWSET if and only if we can describe the content adequately
-- to do that we use a FORMAT file

-- either an XML format file
select * FROM Openrowset(Bulk 'C:\ee\Q_26461276_test.txt', formatfile = 'c:\ee\Q_26461276_xml_format.xml', firstrow=1) A
-- or txt format file
select * FROM Openrowset(Bulk 'C:\ee\Q_26461276_test.txt', formatfile = 'c:\ee\Q_26461276_txt_format.txt', firstrow=1) A

-- now we can use that directly in our update if we want
-- but should point out it is always "best practise" to load into a staging table first

update #mytable set name = colb
from #mytable
inner join Openrowset(Bulk 'C:\ee\Q_26461276_test.txt', formatfile = 'c:\ee\Q_26461276_txt_format.txt', firstrow=1) A on a.cola = #mytable.id

-- now lets check the content again

SELECT * FROM #myTable;

-- and tidy up...

if object_id('tempdb..#mytable','u') is not null drop table #mytable

Q-26461276-TXT-FORMAT.txt
Q-26461276-XML-FORMAT.xml
q-26461276-test.txt
0

Author Comment

ID: 33715126
mwvisa1:

the Bulk works perfectly and we understand we can import all data in ASCII first and then use
update on our working tables modify the tables according to what was imported in the bulk.
However, we want to directly import and update the table.

mark_wills,

You script works when running them all together and I see this becuase at the beginning there is a BULK importing the data.  However when we use the following lines, it doen't work:

update #mytable set name = colb
from #mytable
inner join Openrowset(Bulk 'C:\ee\Q_26461276_test.txt', formatfile = 'c:\ee\
Q_26461276_txt_format.txt',    firstrow=1) A on a.cola = #mytable.id

EE:

Maybe we are extending this question and maybe its time to close it, so please answer the following:

1. In order to use UPDATE we must first import the ASCII file into a temp table?

2. There is no way to import ASCII and use UPDATE without first importing all into a temp table?

Please excuse our ignorance, we are really trying to understand this.  Though it seems as it is possible what we want, all our testing show that it isn't.

0

LVL 51

Assisted Solution

Mark Wills earned 300 total points
ID: 33715507
Yeah the BULK INSERT was there for two reasons. First to support the previous experts in their assertion that it is a good way to go. Second to simply establish a test table which can then be updated. The update statement most definitely works in conjunction with the openrowset :)

1) No, can use openrowset as I have shown.
2) Yes, use openrowset, but you might need a "format file" to help get the right format.

Now, as I have said before, it is best to first load into a "staging" area, validate that data, then use the validated and vetted data as a source to commit to the "master" / "live" data. That also enables you a moment to incorporate a few contention controls - ie if two different sources are trying to update the same "master" then which one is correct ? What if site 2 has "01"  as being "Nett 30 Days" ? How do you first match / validate and manage those types of contentions...

It is up to you, and maybe not a problem in your case, but it is something as experts we need to raise and possibly discuss an appropriate strategy.

Anyway, at the end of the day, you can use OPENROWSET as if it were its own datasource (ie reading directly from a text file without first importing). Have a look at my previous examples again, and please, ask any questions. And it is not being ignorant - being ignorant would mean that you dont ask questions - so you are doing just fine.

If you need more help to understand then fire away, only too happy to help :)
0

Author Comment

ID: 33715865
Thank you very much for your input and to finally answer our question.

As for our validation and "what if's", you are correct in your assertion.  Howevere, each ASCII file has been validated by one of our staff and has been cleaned from any distortion.  This is so we can just import the ASCII file directly to the tables.

Here is your script configured for our need:

update terms2
set tdescr = a.colb
from terms2 b
inner join Openrowset(Bulk 'C:\pp.txt', formatfile = 'c:\ppformat.txt', firstrow=1) A on a.cola = b.id
WHERE b.tdescr <> '2 % 20 DAYS NET 40' OR a.colb <> '2 % 30 DAYS'

As you can see we want to update only if the row from the table is not  '2 % 20 DAYS NET 40'  ot the row from the ASCII is not  '2 % 30 DAYS'.

When I run it, it runs ok, but no rows are updated.

Please verify the syntax and advice on correction.

Thanx

update terms2
set tdescr = a.colb
from terms2 b
inner join Openrowset(Bulk 'C:\pp.txt', formatfile = 'c:\ppformat.txt', firstrow=1) A on a.cola = b.id
WHERE b.tdescr <> '2 % 20 DAYS NET 40' OR a.colb <> '2 % 30 DAYS'

0

LVL 7

Assisted Solution

lundnak earned 200 total points
ID: 33717433
Change the where clause to the following.
update terms2
set tdescr = a.colb
from terms2 b
inner join Openrowset(Bulk 'C:\pp.txt', formatfile = 'c:\ppformat.txt', firstrow=1) A on a.cola = b.id
WHERE b.tdescr not like '2 % 20 DAYS NET 40' and a.colb not like '2 % 30 DAYS'

0

LVL 60

Expert Comment

ID: 33718633
lundnak:

FYI, I don't believe the % in the terms are meant to be wildcards.  I believe those are in the actual data of the column, so the <> is probably what was desired versus not like.
0

Author Comment

ID: 33719169
Same problem, it displays "(0 row(s) affected)".  I commented the WHERE, still 0 rows.

What can it be?

You are correct mwvisa1, its not wild card.
0

LVL 51

Assisted Solution

Mark Wills earned 300 total points
ID: 33719171
Well, couple of possibilities...

First up always trial selection process as a "select" first, then move to the update - that way you can visualise the rows that will be affected.

The % sign is used as a wildcard in SQL Server - but only comes into play when being used in a situation that understands wildcards - when doing a straight compare it is fine - just another character. If being used in a LIKE clause (or some other pattern matching type construct) and want to check the literal % then you need to "escape it" by wrapping it in square brackets. e.g.

tdescr = '2 % 20 DAYS NET 40'  -- is fine and very specifically that string
tdescr LIKE '2 [%] 20 DAYS NET 40'  -- makes the % a character, not a wildcard
tdescr LIKE '2 % 20 DAYS NET 40'  -- leaves the % as a wildcard so could include other possibilities

Then there is the "OR" in dealing with negatives ie: not "this" nor "that". If meaning you dont want either really becomes: not "this" AND not "that". The other way to write that is: not ("this" or "that") e.g.

tdescr <> '2 % 20 DAYS NET 40'  OR colb <> '2 % 30 DAYS' -- is not quite what it seems will end up choosing everything
tdescr <> '2 % 20 DAYS NET 40'  AND colb <> '2 % 30 DAYS' -- is what you are looking for - it means exclude both those values
not (tdescr = '2 % 20 DAYS NET 40' OR colb = '2 % 30 DAYS' ) -- is what you need to do if using an "OR" - ie not using double negatives

so far so good ?

Now, lets get onto your query and break it up... You'll need to quickly run through the examples below and then get back to us...

-- when testing your update simple swap the UPDATE clause for a SELECT clause

--update terms2 set tdescr = a.colb
select *
from terms2 b
inner join Openrowset(Bulk 'C:\pp.txt', formatfile = 'c:\ppformat.txt', firstrow=1) A on a.cola = b.id
WHERE b.tdescr <> '2 % 20 DAYS NET 40' and a.colb <> '2 % 30 DAYS'

-- Now if that doesnt return any results, then we need to test each part:
-- it might be the "WHERE" clause or it might be the "JOIN"

select *
from terms2 b
WHERE b.tdescr <> '2 % 20 DAYS NET 40'

-- and check the second part (which is where I suspect we will find the problems)
-- first does it read the file properly

select *
from Openrowset(Bulk 'C:\pp.txt', formatfile = 'c:\ppformat.txt', firstrow=1) A
--WHERE a.colb <> '2 % 30 DAYS'

-- what about that where clause...

select *
from Openrowset(Bulk 'C:\pp.txt', formatfile = 'c:\ppformat.txt', firstrow=1) A
WHERE a.colb <> '2 % 30 DAYS'

-- now, what did either one show ? did they both show the expected results
-- are cola and id formatted / presented the same way ? is there a potential conflict in their datatypes ?

0

Author Comment

ID: 33724903
Fully understood the 'WHERE' and 'SELECT' clause with the 'Openrowset'; very useful information, thanx!

And yes, now the update works excellently!

0

LVL 51

Expert Comment

ID: 33725141
Great to hear it is working for you :)

And, always very happy to help...
0

LVL 7

Expert Comment

ID: 33726225
Will there be no points awarded?
0

LVL 51

Expert Comment

ID: 33726772
@lundnak,

Yes, and yours is the "Accepted Solution" look at the very top of this thread - there are the closing comments and how points are being distributed
0

LVL 7

Expert Comment

ID: 33727521
@mark_wills:  I don't see it.
0

LVL 75

Expert Comment

ID: 33727615
>>I don't see it.<<
If you had not objected you would see it.  By objecting to the point allocation you have asked EE Moderators to get involved and start the process all over again.  I hope it is worth it.
0

LVL 7

Expert Comment

ID: 33727624
Never mind.
0

LVL 7

Expert Comment

ID: 33727727
@AcPerkins:  The question was closed in a manner that I am not used to.  I wanted to make sure that someone got points for the question.

@Mark_Willis:  Thank you for taking the time to explain the close process to me.
0

Author Comment

ID: 33730438
You know, I've been haveing the same problem about this new process for awarding & closing the questions.  I myself, the first time I awarded another question last week I noticed the changed and I sent a message because it show that it was awarded.

So do I award again?

0

Author Closing Comment

ID: 33738903
thanx
0

Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses
Course of the Month2 days, 17 hours left to enroll

696 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.