Solved

How to use Ms Sql UPDATE command with an Ascci file

Posted on 2010-09-09
43
536 Views
Last Modified: 2012-06-27
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
Comment
Question by:rayluvs
  • 15
  • 12
  • 5
  • +3
43 Comments
 
LVL 11

Expert Comment

by:rajvja
Comment Utility
Hi

You can use SSIS package to do that job.
0
 

Author Comment

by:rayluvs
Comment Utility
Can you give more details
0
 
LVL 7

Expert Comment

by:lundnak
Comment Utility
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

by:rayluvs
Comment Utility
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

by:
lundnak earned 200 total points
Comment Utility
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
USE AdventureWorks2008R2;
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

by:rayluvs
Comment Utility
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

by:rayluvs
Comment Utility
also tried it woth the ASCII with 2 columns
0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
>>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

by:lundnak
Comment Utility
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

Open in new window

0
 

Author Comment

by:rayluvs
Comment Utility
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

by:lundnak
Comment Utility
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

by:rayluvs
Comment Utility
Ok... I will, thanx.  Just to understand, whats BCP for and where is the fmt file created?
0
 
LVL 7

Expert Comment

by:lundnak
Comment Utility
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

by:rayluvs
Comment Utility
Ok, so BCP will actually import my ASCII and also it has to be runned via DOS prompt?
0
 
LVL 7

Expert Comment

by:lundnak
Comment Utility
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'
      )

Open in new window

0
 

Author Comment

by:rayluvs
Comment Utility
It worked perfectly.

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

Expert Comment

by:lundnak
Comment Utility
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

by:rayluvs
Comment Utility
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 59

Expert Comment

by:Kevin Cross
Comment Utility
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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 59

Expert Comment

by:Kevin Cross
Comment Utility
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;

Open in new window

test.txt
0
 
LVL 59

Expert Comment

by:Kevin Cross
Comment Utility
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

; 

Open in new window

0
 
LVL 51

Assisted Solution

by:Mark Wills
Mark Wills earned 300 total points
Comment Utility
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

Open in new window

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

Author Comment

by:rayluvs
Comment Utility
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

by:Mark Wills
Mark Wills earned 300 total points
Comment Utility
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 :)

To answer your questions :

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

by:rayluvs
Comment Utility
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'

Open in new window

0
 
LVL 7

Assisted Solution

by:lundnak
lundnak earned 200 total points
Comment Utility
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'

Open in new window

0
 
LVL 59

Expert Comment

by:Kevin Cross
Comment Utility
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

by:rayluvs
Comment Utility
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

by:Mark Wills
Mark Wills earned 300 total points
Comment Utility
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 ?

Open in new window

0
 

Author Comment

by:rayluvs
Comment Utility
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

by:Mark Wills
Comment Utility
Great to hear it is working for you :)

And, always very happy to help...
0
 
LVL 7

Expert Comment

by:lundnak
Comment Utility
Will there be no points awarded?
0
 
LVL 51

Expert Comment

by:Mark Wills
Comment Utility
@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

by:lundnak
Comment Utility
@mark_wills:  I don't see it.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
>>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

by:lundnak
Comment Utility
Never mind.
0
 
LVL 7

Expert Comment

by:lundnak
Comment Utility
@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

by:rayluvs
Comment Utility
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?

Please advice!
0
 

Author Closing Comment

by:rayluvs
Comment Utility
thanx
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

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

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

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now