Results 1 to 5 of 5

Thread: [RESOLVED] Need to remove embedded line feeds from csv file

  1. #1

    Thread Starter
    New Member
    Join Date
    Aug 2021
    Posts
    7

    Resolved [RESOLVED] Need to remove embedded line feeds from csv file

    I am trying to load a database from a comma delimited csv file obtained from a commercial application. However I get an error indicating that I have to many columns in my data. The issue is there are embedded line feeds in the data, but the data also has single and double quotes in in it as well. The data I am working with is:

    John's Auto Pros,1795 E. Valley Parkway,"Escondido, CA. 92027",Phone - 760-741-2076 Fax - 760-741-6993,Current Technician Assignments,Report Date : 08/10/2021,Technician,"Charged

    Hours","Pay

    Hours","Invoice /

    Estimate #",Hat Number,Job,Description,Status,Cody Carter,,RO,Complete AAA 40 point Inspection,0,0,COMPLETED,24236,Totals:,15.77,15.77,Grand Total Charged Hours: 95.85,Grand Total Pay Hours: 94.85,Page 1 of 1,"(c) 2012 Mitchell Repair Information Company, LLC CurTechAssig.rpt 07.10.12"

    Every row is the same. Thanks for any help that you can give me.

  2. #2
    PowerPoster
    Join Date
    Nov 2017
    Posts
    3,116

    Re: Need to remove embedded line feeds from csv file

    Is it consistent that the unwanted new lines come in pairs as demonstrated by your post? If so, you could do something like this untested code:

    Code:
    Dim strData As String
    strData = File.ReadAllText("C:\Path\To\Your\File.csv")
    strData = strData.Replace(Constants.vbCrLf + Constants.vbCrLf, " ")
    File.WriteAllText("C:\Path\To\Your\NEWFile.csv", strData)
    That will replace two consecutive crlf's with a single space, and write the modified data to a new file. Note that if this results in an "extra" space in your data, then change the second parameter in the Replace statement to an empty String "".

  3. #3

    Thread Starter
    New Member
    Join Date
    Aug 2021
    Posts
    7

    Re: Need to remove embedded line feeds from csv file

    Thank You - That worked in getting rid of the embedded Line Feeds.

  4. #4
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,302

    Re: [RESOLVED] Need to remove embedded line feeds from csv file

    Quote Originally Posted by JDSTACEY View Post
    The issue is there are embedded line feeds in the data, but the data also has single and double quotes in in it as well.
    No, that's not the issue. If the data contains those characters then those characters are part of the data and you should be honouring that. The reason that you wrap field values in double-quotes is specifically so that you can include delimiters (commas and line breaks) in the data. The actual issue is that you're not reading the data properly. I think I have replied to another of your threads and suggested that you use a TextFieldParser to read the data. That will correct read quoted data that contains delimiters.

  5. #5

    Thread Starter
    New Member
    Join Date
    Aug 2021
    Posts
    7

    Re: [RESOLVED] Need to remove embedded line feeds from csv file

    Quote Originally Posted by jmcilhinney View Post
    No, that's not the issue. If the data contains those characters then those characters are part of the data and you should be honouring that. The reason that you wrap field values in double-quotes is specifically so that you can include delimiters (commas and line breaks) in the data. The actual issue is that you're not reading the data properly. I think I have replied to another of your threads and suggested that you use a TextFieldParser to read the data. That will correct read quoted data that contains delimiters.
    jmcihinney - The reason for the dual line feeds in the middle of the csv file is because the original file was a report converted into a csv file by the application. In the report, there are columns where the fields wrap. In the case of the sample data, it is the headers, Charged Hours, Pay Hours and Invoice Estimate #. TO your point though, your response to my second thread was spot on. Thank you for your help.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width