Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Convert  .csv in SSIS

Posted on 2014-10-29
7
Medium Priority
?
193 Views
Last Modified: 2016-02-10
I have a file called VMPIC.29Oct. This is a new way file is presented to me. Currently, these files looked like NP.29OCT. In fact we are still using current NP.29OCT files. Will slowly change over to new format.

If you looked at attached files you will see that VMPIC.29Oct has all records strung together whereas NP.29Oct has the records showing as 1 line 1 record with embedded commas.

Both files have comma separated values.

In my SSIS package, I need a way to get VMPIC.29Oct to have its records (with commas still embedded in) converted so they look the same way as NP.29OCT, 1 line 1 record with commas.

I would need to do this right after the File Sytem Task 'Copy File' but staying in same SSIS container.
This would have to be done before it goes to next container.

This is what VMPIC.29OCT looks like before I called it .txt so I could attach. It seems when I attached it it does not look the same.
SSIS.PNG
File.PNG
NP.29Oct.PNG
VMPIC.29Oct.PNG
0
Comment
Question by:thayduck
  • 3
  • 3
7 Comments
 
LVL 27

Accepted Solution

by:
Zberteoc earned 1500 total points
ID: 40413163
It seems that the new file doesn't have the CrLf characters at the end of the row or they only have the Lf. This could happen with files coming from UNIX/Linux environment.

I suggest you to download and install the Notepadd++ , which is an excellent FREE product:

http://notepad-plus-plus.org/

and then open the files in it and click on the PI (like in the Greek letter) icon. That will show what invisible characters are in use in the file at the end of the row.

If that is the case you could setup the SSIS to use only Lf as row delimiter or talk to the part that produces the files.
0
 

Author Comment

by:thayduck
ID: 40413179
Is there a way to parse out this file in SSIS and create a new file ?
The records start with VMPIC. All data from VMPIC until the next VMPIC would be 1 record.
Then I could pass this on into existing SSIS.
0
 
LVL 27

Expert Comment

by:Zberteoc
ID: 40413246
What you can do is to open the file first in Notepad++ I told you about above and then do a search/replace(Ctrl+H) in the file for VMPIC with \r\nVMPIC. In the Replace window you will have to make sure that at the bottom left you choose Regular expression option.

After replace you save the file but make sure you first remove the top empty line at the top that will be created.
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

Author Comment

by:thayduck
ID: 40413263
This job will be executed on remote servers. Don't believe I can have NotePad++ loaded on them.
0
 
LVL 27

Expert Comment

by:Zberteoc
ID: 40413279
You can do it locally and then move it back. If not, or if that needs to be an automated process you will have to talk to the people who provide that file and let them know is unusable,
0
 
LVL 14

Expert Comment

by:Christopher Gordon
ID: 40415838
You may be able to parse this file in a DataFlow task using the "Script Component" to create a custom source.  Going this route, you'll be using C# to parse the file using that "VMPIC" as an indicator to end the record and then overriding the CreateNewOutputRows() method in that "Script Component".

This is some work to say the least, especially if you don't have a ton of experience parsing files with C#.

In short, it's not impossible.  From an ROI perspective though, it would be better if you could get a file with row delimeters.
0
 

Author Closing Comment

by:thayduck
ID: 40440755
This helped, thanks.
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

578 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question