?
Solved

Convert  .csv in SSIS

Posted on 2014-10-29
7
Medium Priority
?
188 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Quiz: What Do These Organizations Have In Common?

Hint: Their teams ended up taking quizzes, too.

 

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

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

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

My client sends a request to me that they want me to load data, which will be returned by Web Service APIs, and do some transformation before importing to database. In this article, I will provide an approach to load data with Web Service Task and X…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…
Suggested Courses

765 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