Text File - How to use second row as column names

• JMI

  SSChampion

  Points: 11831

  More actions

  October 31, 2005 at 9:16 am

  #61194

  Hi,

  We've been using CSV Text File as a Source for Transformation.

  First row was used as column names.

  Now they add a HEADER (with date and name of data feed) as the first row

  and in my Transformation I have to skip it and

  use the second row as column names.

  So I go to Source File Properties in my DTS

  and select "skip 1 row" and "Use first row as column names".

  I excpect DTS now ignores first row and treats second row as "first row"?

  But it doesn't seem to be the case.

  It still treats the original first row as first row.

  So the only solution is probably to "Skip 2 rows"

  and just do the transformation from Col001,Col002,etc.

  which I don't really like.

  Is there maybe another way to solve this problem?

  Thanks,

  Rob

• rschaeferhig

  SSCrazy Eights

  Points: 8776

  More actions

  November 1, 2005 at 6:20 am

  #601526

  You could always write an ActiveX step prior to your transformation to read the original file and create a new file minus the header record. You would need to change the source for the transformation to the new file...

   
  
  

• Jeff Gray

  SSChampion

  Points: 10667

  More actions

  November 1, 2005 at 7:39 am

  #601540

  Hopefully there is a better way, but I know of none.  I would use something to preprocess the file, removing the first line.  That something, in my world, would be a tool/language called AWK.

  The command line would be something like this:

  awk "{if (i++) print}" InputFile>OutputFile

  This will copy the file, minus the first line.  You can then proceed with a normal "first row has column names" data source

  You can find AWK or its GNU cousin GAWK on the Internet.

  hth jg