Re: problems parsing CSV file

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: John Nurick (j.mapSoN.nurick_at_dial.pipex.com)
Date: 02/01/05 

Date: Tue, 01 Feb 2005 06:55:49 +0000

Hi Amanda,

In general there should be a carriage return and linefeed (i.e. \0x0d0a,
or Chr(13) & Chr(10)). Access 2000 onwards seem to insist on this.
Earlier versions tend to be more accommodating, but I'd check it all the
same. Excel is different again.

In my experience this sort of problem virtually always comes down to an
irregularity in the CSV file. One possibility is quote marks within the
data; this
        123,"Text field","Text field with "quote" marks",889
needs to have the quote marks escaped by doubling them VB-style:
        123,"Text field","Text field with ""quote"" marks",889

On Mon, 31 Jan 2005 16:07:01 -0800, "Amanda Payton"
<AmandaPayton@discussions.microsoft.com> wrote:

>What exactly does Access use as a flag for the end of a record when importing
>from a CSV file?
>
>I have a CSV file that I need to import into Access '97. At unpredictable
>points in the recordset, (I.E. no discernable pattern to these errors) The
>next record is appended 3-6 fields in from the end of the previous record.
>Out of 250 records, only about 50 actually import "properly." The others are
>piggybacked onto one another. This file is generated by an Oracle query, so
>if there's a typo somewhere, it SHOULD be consistant for each record.
>
>My first guess was that there was a comma, quote mark or carriage return
>missing from the oracle query - but ...
>
>A) it imports into Excel just fine - nothing piggybacked, no fields out of
>place... nothing.
>
>B) like I said - there's no rhyme or reason that I can find for the behavior
>of these records and which ones piggyback and which ones don't.
>
>HELP!
>
>:-)
>-Amanda 

--
John Nurick [Microsoft Access MVP]
Please respond in the newgroup and not by email.

Relevant Pages

  • Removing Carriage returns
    ... multi-line textbox, then the csv file is written accordingly. ... file, so that it would not cause excel to advance to the next column, but i ... need to know how to stop the carriage returns from being written to the sql ...
    (microsoft.public.data.ado)
  • .CSV Files
    ... My csv file is delimited by commas and text is delimited by ". ... considers carriage returns and next line characters enclosed in " as ... I am facing a problem when trying to open this file in excel. ...
    (microsoft.public.excel)
  • .CSV Files
    ... My csv file is delimited by commas and text is delimited by ". ... considers carriage returns and next line characters enclosed in " as ... I am facing a problem when trying to open this file in excel. ...
    (microsoft.public.excel.misc)
  • carriage returns after import
    ... I have imported a csv file that contains carriage returns ... in one of the variable fields as well as text. ... i can see them in excel as the little sqares but cannot ...
    (microsoft.public.excel.misc)
  • Re: CSV import, excel corrupts data
    ... I guess if you want to use excel, you'll have to play by its rules. ... If opening .csv files directly doesn't work for you, ... Doing the macro trick corrupts the data as described when the file is a ... empty worksheet, use the data import wizard to select the csv file, ...
    (microsoft.public.excel)