Solved

Read from text file into sql

Posted on 2014-01-22
7
353 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
Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

 

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

Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

Question has a verified solution.

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

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

734 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