Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Read from text file into sql

Posted on 2014-01-22
7
Medium Priority
?
369 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
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 2000 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
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 

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

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
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.

886 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