DTS package that can read from Excel to determine field names and sizes

• thgamble1

  Ten Centuries

  Points: 1212

  More actions

  November 29, 2004 at 9:24 am

  #102292

  What I'm looking for is something that can do the following:

  Read from an excel document for field names / lengths and then import a file (or set of files)

  An example of the excel document I get is:

  Field Name	Start	End	Len.	Values
  Address ID	1	10	10	Numeric
  State Code	11	12	2	Numeric
  State Abbr.	13	14	2	Alpha
  Zip Code	15	19	5	Numeric

  I'm not too concerned about the field type (I generally set them all to default of varchar's anyhow.

  Is there something out there than can recursively read through and do something like this?  Or is it something that I'll want to have custom written?

  Thanks in advance.

• December 2, 2004 at 8:00 am

  This was removed by the editor as SPAM

• sqlemail@yahoo.com

  SSC Eights!

  Points: 919

  More actions

  December 5, 2004 at 2:44 pm

  #532587

  How about these steps

  1. Read this excel file (the one you have shown as example above) and store all its rows in a WORK TABLE
  2. Now your WORK table will have the schema for the EXCEL file that you want to import. Say this file is "InputData.xls"
  3. Once the data in the WORK Table is uploaded, execute a SP, that will loop theru eack of the ROWS in this WORK table, dynamically generate the SCHEMA for the TABLE.  You may call the Table name same as EXCEL file name
  4. Now read the "InputData.xls" and insert into SQL Table "InputData". I believe there is a VB code to do this, in http://www.sqldts.com

  
  paul