Post reply

Taking certain Excel worksheets and making them text files

  • June 30, 2010 at 10:21 am

    #147904

    I need to take an Excel Workbook "Ebill.xls" look for 2 tabs named 'Import' and 'Card Aging'

    read in those workbooks, find and replace then save each tab as a txt file.

    So start with file "Ebill.xls" and end up with "Import.txt" and "Card Aging.txt"

    in addition to the "Ebill.xls" in the folder.

  • June 30, 2010 at 1:59 pm

    #1187318

    Here's a T-SQL way to accomplish this (idea from this web site[/url]):

    declare @sql varchar (8000),

    @tbname varchar (100)

    -- First table will excel import content to a global temporary table

    select @tbname ='[tempdb].[dbo].[temp'+ cast (newid () as varchar (40 ))+']',

    @sql = 'select * into ' + @tbname +

    ' from OpenRowset (''MICROSOFT.JET.OLEDB.4.0'',''Excel 8.0;HDR=YES;IMEX=2;DATABASE=C:\test.xls'',[Sheet1$]) x'

    exec (@sql)

    -- And then use bcp derived from the overall temporary table to a text file

    set @sql = 'bcp "' + @tbname + '" out "C:\test.txt" /T /c'

    exec master .. xp_cmdshell @sql

    -- Removing the temporary table

    exec ( 'drop table ' + @tbname)

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • July 2, 2010 at 2:03 pm

    #1188421

    Convert into two text files using SSIS, then read data from script task and replace as you wanted and save to new file, long method but feasible.

    RB

Viewing 3 posts - 1 through 2 (of 2 total)

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