Solved

Read from text file into sql

Posted on 2014-01-22
7
335 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 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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
need help in sql 4 65
SQL Query resolving a string conversion issue 26 37
Sql Query: need to find records in table_a with no matches in table_b 14 27
SQL Server stored proc 2 13
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
I have a large data set and a SSIS package. How can I load this file in multi threading?
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 backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

910 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

24 Experts available now in Live!

Get 1:1 Help Now