Solved

Read from text file into sql

Posted on 2014-01-22
7
346 Views
Last Modified: 2014-02-12
I need to look up  text files from a query then do a bulk insert into table so I can retrieve the text file contents from a query.  Need help on getting somthing to work.

based on this query I can pull the file name from the note_id field


SELECT note_id ,enc_id, person_id, table_name FROM patient_notes WHERE person_id = '123' AND table_name in ('assess_comm_')  AND (practice_id = '0001' AND enc_id ='0')   OR enc_id ='xyz'  ORDER BY table_name, field_name, enc_id DESC
             

   note_id
2CB3FB21-05CF-4F11-B2A1-4A19355EE1C0
4334ABEB-D8B1-4716-9FFF-ED8E6B1A981C
B352D09A-4AAC-44C6-B45B-2A616AF98C40
CD07037E-4EE5-45E8-B468-0F0A2B7B55F4

pull the file contents  from each of the records

-- Create a table to hold the data
CREATE TABLE #textfile (line varchar(8000))
-- Read the text file into the temp table
BULK INSERT #textfile FROM 'c:\2CB3FB21-05CF-4F11-B2A1-4A19355EE1C0.TXT'
--Need to add the enc_id to the #textfile table
--need to loop through 4 records then move on to next person_id '1234" and continue --appending to #textfile


If you can help me create a working script I would appreciate it.

Thanks in advance
0
Comment
Question by:pmathis
[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
  • 4
  • 3
7 Comments
 
LVL 3

Expert Comment

by:smilieface
ID: 39801951
Here's an approach which may give you the results you need. it's a little in-elegant, but should work.

The logic for the AND and OR in your select is confusing. You might try putting brackets around the bits you want AND versus OR. I tried to make it clear in the version I have what goes where. At the moment you get every record for enc_id = 'xyz' rather than those with the table_name = 'assess_comm_'
CREATE TABLE #TextFile (
      person_id   varchar(20)
   ,  enc_id      varchar(20)
   ,  table_name  varchar(20)
   ,  line        varchar(max)
   )

DECLARE
      @person_id  varchar(20)
   ,  @enc_id     varchar(20)
   ,  @table_name varchar(20)
   ,  @note_id    varchar(100)
   ,  @bulk_cmd   varchar(100)

DECLARE cur_People CURSOR LOCAL FAST_FORWARD
FOR
   SELECT
         person_id
      ,  enc_id
      ,  note_id
      FROM patient_notes
      WHERE table_name = 'assess_comm_'
      AND   (     practice_id = '0001'
               AND   enc_id ='0')
      OR    enc_id ='xyz'
      ORDER BY table_name, field_name, enc_id DESC

OPEN cur_people

FETCH NEXT FROM cur_people
INTO
      @person_id
   ,  @enc_id
   ,  @file

WHILE @@FETCH_STATUS = 0
BEGIN

   SET @bulk_cmd = 'BULK INSERT #TextFile FROM ''C:\' + @file;

   EXEC (@bulk_cmd);

   UPDATE #TextFile
      SET
            person_id   =  @person_id
         ,  enc_id      =  @enc_id  
         ,  table_name  =  @table_name
         WHERE #TextFile.person_id IS NULL

   FETCH NEXT FROM cur_people
   INTO
         @person_id
      ,  @enc_id
      ,  @file
END

CLOSE cur_people
DEALLOCATE cur_people

SELECT * FROM #TextFile   

Open in new window

0
 

Author Comment

by:pmathis
ID: 39804423
Thanks,

Basically I changed  the bulk insert command to get it to work
SET @bulk_cmd = 'BULK INSERT #TextFile FROM ''C:\' + @file + '.txt''';

which is this

BULK INSERT #TextFile FROM 'C:\2CB3FB21-05CF-4F11-B2A1-4A19355EE1C0.txt'

which is correct
 
but I am getting the following message when I run it.

Msg 4832, Level 16, State 1, Line 1
Bulk load: An unexpected end of file was encountered in the data file.
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".
0
 
LVL 3

Accepted Solution

by:
smilieface earned 500 total points
ID: 39805403
Ok - I think I see what that is

Because I changed the file definition to include additional columns, it wants them to come from the text file.

Rather than playing with format files etc, try this

CREATE TABLE #TextFile (
   line        varchar(max)
   )

CREATE TABLE #ResultTable (
      person_id   varchar(20)
   ,  enc_id      varchar(20)
   ,  table_name  varchar(20)
   ,  line        varchar(max)
   )   

DECLARE
      @person_id  varchar(20)
   ,  @enc_id     varchar(20)
   ,  @table_name varchar(20)
   ,  @note_id    varchar(100)
   ,  @bulk_cmd   varchar(100)
   ,  @file       varchar(200)

DECLARE cur_People CURSOR LOCAL FAST_FORWARD
FOR
   SELECT
         person_id
      ,  enc_id
      ,  note_id
      FROM patient_notes
      WHERE table_name = 'assess_comm_'
      AND   (     practice_id = '0001'
               AND   enc_id ='0')
      OR    enc_id ='xyz'
      ORDER BY table_name, field_name, enc_id DESC

OPEN cur_people

FETCH NEXT FROM cur_people
INTO
      @person_id
   ,  @enc_id
   ,  @file

WHILE @@FETCH_STATUS = 0
BEGIN

   SET @bulk_cmd = 'BULK INSERT #TextFile FROM ''C:\' + @file;

   EXEC (@bulk_cmd);

   INSERT #ResultTable
      SELECT
            @person_id
         ,  @enc_id
         ,  @table_name
         ,  line
         FROM #TextFile

   TRUNCATE TABLE #TextFile

   FETCH NEXT FROM cur_people
   INTO
         @person_id
      ,  @enc_id
      ,  @file
END

CLOSE cur_people
DEALLOCATE cur_people

SELECT * FROM #ResultTable

DROP TABLE #TextFile
DROP TABLE #ResultTable

Open in new window

0
Backup Solution for AWS

Read about how CloudBerry Backup fully integrates your backups with Amazon S3 and Amazon Glacier to provide military-grade encryption and dramatically cut storage costs on any platform.

 

Author Comment

by:pmathis
ID: 39813818
Seems to be close, let me check a few things.

Thanks
0
 

Author Comment

by:pmathis
ID: 39816589
I am hoping you might know why this line  works for testing

 SET @bulk_cmd = 'BULK INSERT #TextFile FROM ''C:\' + @file + '.txt''';

But this doesn't concatenate correctly if I try to pick the files up accross the network.
The .rtf doesn't get added on

SET @bulk_cmd = 'BULK INSERT #TextFile FROM ''\\prod\emr\Templates\notes\' + @file + '.rtf''';
0
 
LVL 3

Expert Comment

by:smilieface
ID: 39816922
I suspect @bulk_cmd is getting truncated due to the filename being longer than I realised!

C:\ - 3 characters
\\Prod - 6 characters

Change the definition of @bulk_cmd to be varchar(200) and see if that works.
0
 

Author Comment

by:pmathis
ID: 39855388
Yes this seemed to be the issue , sorry for the delay got sidetracked on other projects.

Thanks appreciate your help
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
disk usage reporting tools 27 53
SQL Server 2008 Std. License Key owner or vendor 4 55
Need help with another query 10 37
Sql server Error message 3 16
When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

749 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