+ Reply to Thread
Results 1 to 5 of 5

CSV import wrecked by carriage returns in source file

  1. #1
    Registered User
    Join Date
    06-08-2019
    Location
    BC Canada
    MS-Off Ver
    2013
    Posts
    6

    CSV import wrecked by carriage returns in source file

    Hello and thank you for the great community.

    The goal of my work in Excel is to change and update CSV files exported from my CAD tool and then these files and then write them back into the CAD software.
    The problem is that sometimes these exported .csv files contain carriage returns in some fields.
    It is important that these carriage return characters get back in there safely.

    I am using following code to load the source file but this breaks when it finds a CR.


    Please Login or Register  to view this content.
    I checked VBA documentation related to the workbook.opentext method but but I do not see alternate formats, settings that will deal with the CR

    Do I need to write a custom input parser to deal with this?

  2. #2
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Excel for Windows & Mac - all versions.
    Posts
    25,693

    Re: CSV import wrecked by carriage returns in source file

    Please upload the csv file or a representative cut down copy, anonymised if necessary. It is always easier to advise if we can see your request in its context.


    To upload a file click the Go Advanced button at the foot of your post, look underneath the post area for the Manage Attachments section and take it from there.
    Richard Buttrey

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Registered User
    Join Date
    06-08-2019
    Location
    BC Canada
    MS-Off Ver
    2013
    Posts
    6

    Re: CSV import wrecked by carriage returns in source file

    text file attached. I had to convert to .zip because txt file limited to 100kB.
    if you look at the file in an editor you will see an entry with a load of wild characters. This is the field causing the issue. The CAD program is able to export this and then reload it but Excel doesn't like this at all.
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    06-08-2019
    Location
    BC Canada
    MS-Off Ver
    2013
    Posts
    6

    Re: CSV import wrecked by carriage returns in source file

    I've attempted to write a parser but immediately hit a snag.
    Please Login or Register  to view this content.
    generates an input past end of file error. I believe this is because the file contains multiple lines and a non binary input doesn't like a read operation pulling in more characters than the line length, is that correct?

    if I input as binary then I do not get the error but I am not sure how to recover the contents of the file at that point. I played with a simple binary to text converter but it seems to produce gibberish.

    reminder of the scenario here - file is a normal CSV with multiple lines, but one field is hosting binary data (photometric information) which is known to contain carriage returns characters occasionally.
    Sample file posted. Hoping someone can take a look at that file.

    I do not believe that quote characters or commas appear in the photometric record but if they can that throws a bit of a wrench into this. This data seems to be fixed length (15210 characters) and the field is identified as "None#Other" so the parser could be coded to understand that when it gets to this field it should simply copy in those 15210 characters into the next record and move on.

    I observe also that the number of records per line is determined by the first row so the carriage returns could be ignored completely by a parser armed with that information.

    This photometric stuff does not exist in all files we are trying to process.

    thank you for any advice.
    Last edited by Brian Muir; 06-12-2019 at 06:54 AM.

  5. #5
    Registered User
    Join Date
    06-08-2019
    Location
    BC Canada
    MS-Off Ver
    2013
    Posts
    6

    Re: CSV import wrecked by carriage returns in source file

    I discovered this morning that if i read line by line the photometric data comes in as clean text. I thought it was encoded as binary because of how it shows up after the excel CSV input gets hold of it.
    I believe that my path forward is to simply process the file line by line with a few smarts to deal with the photometric field.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1