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

• Chris Thom

  SSC Veteran

  Points: 231

  More actions

  February 17, 2005 at 3:13 pm

  #67058

  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

• wz700

  SSCertifiable

  Points: 7133

  More actions

  February 21, 2005 at 11:09 pm

  #543404

  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()

• philcart

  SSC-Forever

  Points: 47797

  More actions

  February 21, 2005 at 11:38 pm

  #543407

  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