Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

ForEach Loop forEXCEL File Expand / Collapse
Author
Message
Posted Thursday, November 27, 2008 4:15 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, November 11, 2009 8:59 AM
Points: 14, Visits: 63
Hi,

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

Thanks
Post #609676
Posted Thursday, November 27, 2008 4:56 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, November 11, 2009 8:59 AM
Points: 14, Visits: 63
details:

Excel files exist at C:\MyData\
1. 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

Post #609699
Posted Friday, November 28, 2008 9:15 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, November 11, 2009 8:59 AM
Points: 14, Visits: 63
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

Post #610445
Posted Friday, November 28, 2008 2:58 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Sunday, September 14, 2014 3:17 PM
Points: 1,118, Visits: 2,224
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/

Post #610588
Posted Tuesday, December 2, 2008 2:46 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, November 11, 2009 8:59 AM
Points: 14, Visits: 63
Yes, the source excel files are of same structure!

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

Thanks
Post #611905
Posted Tuesday, December 2, 2008 2:56 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, May 30, 2014 6:27 PM
Points: 2,808, Visits: 7,175
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.
Post #611912
Posted Tuesday, December 2, 2008 5:37 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, November 11, 2009 8:59 AM
Points: 14, Visits: 63
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?
Post #611983
Posted Tuesday, December 2, 2008 7:39 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, November 11, 2009 8:59 AM
Points: 14, Visits: 63
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.

Post #612064
Posted Wednesday, March 27, 2013 11:27 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, January 22, 2014 11:33 AM
Points: 15, Visits: 79
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).
Post #1436062
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse