Post reply

Reading from a text file

  • March 6, 2007 at 2:25 pm

    #117769

    hi,

    I'm using sql server 2000. I would like to read data from a text file, massage it, and store it to a different file.

     

    In a stored procedure, can you please show me how to read from a text file, line by line.

     

    Thanks

  • March 6, 2007 at 5:27 pm

    #693445

    I would bulk insert it into a table, massage it, then use bcp to write to the file.  Search the forums here or look in BOL for some useful information.

    Luck, Dave

    There is no "i" in team, but idiot has two.

  • March 6, 2007 at 10:17 pm

    #693460

    you might want to consider writing a program to do the work(open file, manipulate, save file) instead of in TSQL; most languages are more robust in their ability to edit files than SQL; vb/vb.net can bring the whole file into a single variable greater than 8000 chars and manipulate it; you can certainly do it in TSQL, but if a better tool is available, consider using it.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • March 6, 2007 at 11:07 pm

    #693463

    Text files of this nature are easy to load and you don't need BCP or any of that so long as you have the right privs...

    Let's say you have a file called MyTest.txt on the server's hard drive in a directory called "C:\Web".  The following will load that file right the heck now...

    DROP TABLE #MyHead

    CREATE TABLE #MyHead(RowNum INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,Content VARCHAR(8000), ContinuationRow INT)

    INSERT INTO #MyHead (Content,ContinuationRow)

    EXEC sp_ReadErrorLog 1,'C:\Web\HTMLTest.html'

    SELECT * FROM #MyHead

    The neat thing is that it supposedly (haven't tested it fully) splits real long lines and marks continuation rows...

    You will need to use either BCP or OSQL or xp_CmdShell to get the text file back out...

     

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • March 7, 2007 at 7:43 am

    #693575

    Thank you very much guys. That was really helpful. I really appreciate all your help.

  • March 9, 2007 at 3:05 pm

    #694200

    I have tried Jeff's method of using sp_ReadErrorLog on our SQL2000 and ran into the following:

    When the records shorter than the Content field it works no problem.

    When the records were longer initially I got an error:

    Server: Msg 8152, Level 16, State 2, Procedure xp_readerrorlog, Line 19
String or binary data would be truncated.

    and I had to set Ansi_Warnings off. But when I did this it only truncated the records. It did not do anything with the continuation.

    Is there some trick I need to do to get that part to work?

     

     

    If you don't stand for something, you'll fall for anything!,

    Don Urquhart

  • March 9, 2007 at 8:55 pm

    #694209

    I sure missed something, there... sorry.  I've not had the same problem with it... lemme dig a little deeper...

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • March 12, 2007 at 7:37 am

    #694356

    Jeff,

     

    I have used your method and it worked perfect. I massaged the data, but I'm having some trouble writing that created table into a text file with putting back the lines together.

    please help.

    Thanks

     

    Bakr

  • March 12, 2007 at 6:01 pm

    #694541

    Sure, Bakr... but I need a wee bit more information...

    1. Did you end up with any continuation lines?
    2. Can you use xp_CmdShell?
    3. Can you post the DDL for the table (be sure to indicate the primary key) you loaded and the first, say, 10 or 20 rows of data from the table NOT INCLUDING the actual text you imported (would be too long to post here).  And, please make sure that you do NOT post any personal information for individuals/businesses or company proprietary info.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • March 13, 2007 at 7:10 am

    #694665

    Thank you for replying.

     

    Yes, I did end up with continuation lines for every line, because the file that I'm reading from is huge.

    Yes I can use the xp_CmdShell.

    I used exactly the same structure as you instructed me.

    CREATE TABLE #MyHead(RowNum INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,Content VARCHAR(8000), ContinuationRow INT)

    Sorry I can not post any of the data that I have

     

    Thank you for your help and let me know if you need any more information.

     

    Bakr

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply