none
Checking excel file and loading them

    Question

  • Hi guys, one question: I got hundreds of excel file that I load in a table (sql). Now my concern is to load two or more file that are the same. But using the parameter that should be impossible. My really concern is another, there is a girl that collect these data from our software, after this she label manually the file. Now I should check if the name of the file is equal to the first colum data. Let's say that the file is Paris1VB, in the first column I should have Paris1VB000005 for example. I can use left or Instr but whaat I really need is the logic to check these file. 

    Any advice?

    Thanks

    Saturday, July 26, 2014 4:07 PM

Answers

  • You can use a ForEachLoop with file enumerator for this. This will iterate through files in a folder.

    You can declare  variable and use it inside the loop to get filename for each file iterated

    Inside loop add a data flow task and inside that use excel source to point to file

    Add a condition split task and define an output say ValidRow with expression as

    FINDSTRING([ColumnName],@[User::FileName]) > 0

    then join the ValidRow output to your destination

    This will ensure only file with valid data will be added to the table


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Saturday, July 26, 2014 7:06 PM

All replies

  • You can use a ForEachLoop with file enumerator for this. This will iterate through files in a folder.

    You can declare  variable and use it inside the loop to get filename for each file iterated

    Inside loop add a data flow task and inside that use excel source to point to file

    Add a condition split task and define an output say ValidRow with expression as

    FINDSTRING([ColumnName],@[User::FileName]) > 0

    then join the ValidRow output to your destination

    This will ensure only file with valid data will be added to the table


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Saturday, July 26, 2014 7:06 PM
  • Brilliant Visakh, unfortunateley I cannot try in this moment. Anyway, I'm already using the ForEachLoop and it's work perfectly. 

    Now it's interesting your suggestion to use a condition split (I need to brush up my skill in this task), the schema should be IF FINDSTRING >0 that's ok, otherwise not. In terms of flow, what can I do. I mean, if is ok fill the table in Sql, if not..?

    Sunday, July 27, 2014 9:40 AM