Solved

Read from text file into sql

Posted on 2014-01-22
7
333 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
  • 4
  • 3
7 Comments
 
LVL 3

Expert Comment

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

 

Author Comment

by:pmathis
Comment Utility
Seems to be close, let me check a few things.

Thanks
0
 

Author Comment

by:pmathis
Comment Utility
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
Comment Utility
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
Comment Utility
Yes this seemed to be the issue , sorry for the delay got sidetracked on other projects.

Thanks appreciate your help
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
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.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

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