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 ««12

Removing noisi commas in CSV file (SSIS) Expand / Collapse
Author
Message
Posted Monday, October 7, 2013 2:40 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 12:24 PM
Points: 13,238, Visits: 11,017
damien.gulluni (10/7/2013)
I fully agree when you have the control of the file produced .. The file comes from Business Object world and I have only choice between tab, semicolon, and comma which are all delimiter that can be used in the file I receive.

But for me the main problem is that the excel engine can handle that case while SSIS cannot.

Regards,
Damien


It's not SSIS' fault that your csv is malformed.
As I see it, the text qualifiers are not correctly handled. (as I see multiple text qualifiers in one column)




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1502033
Posted Monday, October 7, 2013 2:46 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, January 30, 2014 9:19 AM
Points: 9, Visits: 16
Anyway this is not the concern of my request to say if SSIS is responsible or not ...

What I am looking for, and thank you again for your time, is to find a solution with all the constraint I have.
Actually the process is fully manual and I am trying to automate it with SSIS.

In order to open the file with excel in SSIS I am trying to use a script task. But now the issue I am facing is that I cannot find Excel Interop component to be used in vb.net script (SSIS 2005 version).

Regards,
Damien
Post #1502036
Posted Monday, October 7, 2013 2:46 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, June 18, 2014 3:28 AM
Points: 1,044, Visits: 1,839
Can you see all the columns in the file along with the header names?

Regards/Raunak
Now a member of Linkedin

Please visit the all new Performance Point Forum
Please visit the all new Data Mining and Business Analytics Forum
Post #1502037
Posted Monday, October 7, 2013 2:48 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, January 30, 2014 9:19 AM
Points: 9, Visits: 16
Yes Raunak.

Damien
Post #1502039
Posted Monday, October 7, 2013 2:54 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 12:24 PM
Points: 13,238, Visits: 11,017
damien.gulluni (10/7/2013)
Anyway this is not the concern of my request to say if SSIS is responsible or not ...

What I am looking for, and thank you again for your time, is to find a solution with all the constraint I have.
Actually the process is fully manual and I am trying to automate it with SSIS.

In order to open the file with excel in SSIS I am trying to use a script task. But now the issue I am facing is that I cannot find Excel Interop component to be used in vb.net script (SSIS 2005 version).

Regards,
Damien


It's a .csv file, not an Excel file. You can use the StreamReader class to read the file.




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1502043
Posted Monday, October 7, 2013 3:01 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, January 30, 2014 9:19 AM
Points: 9, Visits: 16
You can open csv in excel and I obviously know the difference between excel and csv files.

I don't think that using StreamReader class will help me a lot.
Post #1502045
Posted Monday, October 7, 2013 3:04 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, June 18, 2014 3:28 AM
Points: 1,044, Visits: 1,839
The first 1100+ rows are loaded with no errors. Can you please analyze the issue with the row(s) which are failing?

Regards/Raunak
Now a member of Linkedin

Please visit the all new Performance Point Forum
Please visit the all new Data Mining and Business Analytics Forum
Post #1502047
Posted Tuesday, October 8, 2013 5:57 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, January 30, 2014 9:19 AM
Points: 9, Visits: 16
Hi,

I have solved my issue using the following:
- create a script task that will open my csv files with excel (yes you can do that ;)) and execute a macro that will replace all the noisy commas (only noisy for the SSIS csv engine) with space.
- open the csv files with the SSIS Flat File Source
- load the csv files in database using Ole DB Destination

Thanks again for your time,
Regards,
Damien
Post #1502562
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse