How do I load a SYLK file into SQL Sever using DTS?

  • I need help either automating the conversion of a SYLK file or help figuring out how to make the DTS package recognize the format.  Currently we are trying to setup a load from a file that a customer sends us.  They dump everying into a SYLK file and ftp to us.  Thiw works fine if we open it into EXCEL but if I'm trying to import it into SQL Server I can't get it to work.

     

    chirs

  • Use script to convert to xls first.

    Here is a sample. It converts aa.slk to bb.xls, then you can DTS into SQL Server.

    Dim wb

    set wb = CreateObject("Excel.Application")

    wb.Workbooks.Open("C:\temp\aa.slk")

    wb.Workbooks(1).Activate

    wb.ActiveWorkbook.SaveAs "C:\temp\bb.xls", 1

    wb.Workbooks.Close()

  • While the script above is handy, it doesn't work if you don't have Excel installed on the server.

    I did have an occasion to import a SYLK file in a previous life. From memory we used a VBScript to write out a new file. The SYLK is basically a text file that contains data in a special format. All you need to worry about is the rows that start with "C". These are the rows with the data. The others are just formatting. I think it's something like,

    C;Y<row number>
    C;X<column number>;K<data>
    C;X<column number>;K"<text data>"
    etc...
    C;Y<row number>
    C;X<column number>;K<numeric data>
    C;X<column number>;K"<text data>"
    etc...
    

    So you can put together a short VBScript to parse through the file and write out a new text file the import the new text file.

     

    --------------------
    Colt 45 - the original point and click interface

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

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