Post reply

ForEach Loop forEXCEL File

  • November 27, 2008 at 4:15 am

    #125236

    Hi,

    Any sample code/steps for conversion of EXCEL file into CSV file using ForEach Loop?

    Thanks

  • November 27, 2008 at 4:56 am

    #904244

    details:

    Excel files exist at C:\MyData1. FileOne.xls

    2. FileTwo.xls

    3. FileThree.xls

    Need to export all excel files data in CSV files:

    1. FileOne.csv

    2. FileTwo.csv

    3. FileThree.csv

  • November 28, 2008 at 9:15 am

    #904869

    Hi ALL,

    Let me share my work:

    Successfully upload data in SQL from multiple CSV files and saved on different location.

    Now I want to convert excel file(s) into CSV file if exists (using For Each Loop) on source location before executing package for uploading data from CSV files into SQL.

    Any tip?

    Help in this regard would be highly appreciated!

    Thanks

  • November 28, 2008 at 2:58 pm

    #904993

    Are the source Excel files with same structure (number of columns, meaning, etc)? If that is the case, it will be easy. You have to setup data flow task with the following components in it:

    * Excel Source component

    * Flat Destination Component.

    Then you have to setup your Excel connection manager to be set through expression, so you can iterate it over with foreach loop.

    If your source Excel files doesn't have same structure, then I would recommend that you avoid using SSIS. It will be not too much of a help and you would have to implement a custom program, which processes the files.

    ---
    SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

  • December 2, 2008 at 2:47 am

    #906097

    Yes, the source excel files are of same structure!

    Could you plz explain more in detail HOW TO SET EXCEL CONNECTION USING EXPRESSION?

    Thanks

  • December 2, 2008 at 2:56 am

    #906103

    SMAZ (12/2/2008)

    Yes, the source excel files are of same structure!

    Could you plz explain more in detail HOW TO SET EXCEL CONNECTION USING EXPRESSION?

    Thanks

    In SSIS simply add a new Excel data flow source in the data flow and connect this using the properties window.

  • December 2, 2008 at 5:37 am

    #906158

    Thanks for replies!

    Let me share the development.

    Now I am able to successfully read the excel file (having same structure in same folder)...

    Now the final objective is remaining!

    The Destination connection is of Flat File...

    I would Like to save the excel file with the same name like 'MyData1.xls, MyData2.xls,.. etc) as

    (MyData1.csv, MyData2.csv,... etc)

    I am trying to replace Xls with CSV in expression of Destination file connection:

    REPLACE(@[User::varExcelFile] , "XLS", "CSV" )

    PS: varExcelFile variable has the full path including name & extension of excel file!

    For temporary purpose I placed a CSV file name + location in DestinationConneciton.

    DelayValidation = True

    Result on Execution:

    The processing of file "C:\MyData1.xls" has started.

    The process cannot access the file because it is being used by another process.

    Cannot open the datafile "C:\MyData1.xls".

    failed the pre-execute phase and returned error code 0xC020200E.

    The processing of file "C:\MyData1.xls" has ended.

    wrote 0 rows

    Task failed:

    Warning: 0x80019002 at Foreach Loop Container: The Execution method succeeded, but the number of errors raised (2) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

    SSIS package "FileConversion.dtsx" finished: Failure.

    I then Set Max Error Count = 0 then

    all error msgs a mention earlier except Max Error Count.

    Any Tip/suggestion?

  • December 2, 2008 at 7:39 am

    #906228

    Resolved the issue!

    1. Declared another variable

    2. Add script task

    3. Passed Excel file name as read only and new variable for readwrite.

    4. Updated new variable value by replacing xls with CSV

    5. In flat file connection property used new variabel in expression for connection string.

    6. In ForEach loop placed script task and on success called data flow.

  • March 27, 2013 at 11:27 am

    #1601283

    hi, do you think you could show me your work? what i'm doing is pretty similar to your work - i have text files (a_points.txt, b_points.txt) and i want to convert them into xlsx files (i.e, a_points.xlsx, b_points.xlsx).

Viewing 9 posts - 1 through 8 (of 8 total)

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