SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


ForEach Loop forEXCEL File


ForEach Loop forEXCEL File

Author
Message
SMAZ-429882
SMAZ-429882
SSC-Enthusiastic
SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)

Group: General Forum Members
Points: 130 Visits: 63
Hi,

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

Thanks
SMAZ-429882
SMAZ-429882
SSC-Enthusiastic
SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)

Group: General Forum Members
Points: 130 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
SMAZ-429882
SMAZ-429882
SSC-Enthusiastic
SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)

Group: General Forum Members
Points: 130 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
CozyRoc
CozyRoc
SSCertifiable
SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)

Group: General Forum Members
Points: 6958 Visits: 2235
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/


SMAZ-429882
SMAZ-429882
SSC-Enthusiastic
SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)

Group: General Forum Members
Points: 130 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
steveb.
steveb.
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10998 Visits: 7195
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.
SMAZ-429882
SMAZ-429882
SSC-Enthusiastic
SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)

Group: General Forum Members
Points: 130 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?
SMAZ-429882
SMAZ-429882
SSC-Enthusiastic
SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)

Group: General Forum Members
Points: 130 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.
kpann
kpann
SSC Rookie
SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)

Group: General Forum Members
Points: 49 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).
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search