[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

How do I replace CRLF with LF in a .CSV file with a Script Task in SQL 2005 SSIS?

Posted on 2008-10-14
3
Medium Priority
?
1,792 Views
Last Modified: 2013-11-10
I am developing an SSIS package in SQL 2005 that downloads files via FTP and validates them prior to processing. The files are .CSV and generated in a Unix environment, the top line has a Checksum in the first column followed by a line of empty columns followed by the data within the file. The checksum generated is based upon the contents of the file minus the first two lines.

In a script task I am able to read the lines of the file into an array, eliminate the first two lines and calculate the checksum based on the remaining data. My problem is my checksum does not match the checksum provided, most likely because the LF is being replaced with CRLF.

How can I remove the CRLF programatically within VB.NET so I can automatically remove the two lines from the top of the file, eliminate any formating changes Windows may have made (LF to CRLF) and validate the checksum?

This must be an entirely automated process with no intervention of users and also maintain performance as high as possible. My process has 90 minutes to process thousands of these files with 10's of thousands of lines in each file.

Thanks
0
Comment
Question by:Hooznext
[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
  • 2
3 Comments
 
LVL 17

Accepted Solution

by:
HoggZilla earned 2000 total points
ID: 22717782
VB.NET has three codes to look at:
vbCrLf
vbCr
vbLf

Read the contents of the file into a string
Dim str as String = ...text from file
str = REPLACE(str, vbCrLf, vbLf)
0
 
LVL 1

Author Comment

by:Hooznext
ID: 22720730
HoqqZilla,

I had tried that already and removed it, what I did find my error was...I was reading the file line by line into a string array, I would then begin at line 3 of the array and concatenate the lines into a single string then run the checksum on that string. What I failed to notice was the lines I read out of the file were being stripped of all vbCRLF or vbLF and creating a single string. I added '+ vbLF' to the end of each line in the array then used your suggestion of strFile.Replace(vbCRLF, vbLF) as an extra insurance and now the checksums are working properly.

My day yesterday was riddled with distractions and such that I lost enough focus on the issue to miss something this simple. Once the end of the day arrived and the frustration was high, I turned to the Experts for help. Thanks for the reply, I will award the points as your suggestion made me think about the string AFTER it was reconstructed.

Thanks
0
 
LVL 1

Author Closing Comment

by:Hooznext
ID: 31506100
Thanks again for the suggestion, I needed to walk away from this to gather my thoughts.
0

Featured Post

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

656 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