error while displaying csv file into datatable.

[archana]

Hi all,

I have csv file which i want to show into datatable.

when i try to use oledb to copy content of csv file into datatable not
all rows are getting copied into datatable. Some last rows are
missing. What i observe it if i open csv file in textpad and save
that in file format 'PC' it starts working.

I am not getting why this is happending?

can any one help me.

thanks in advance.

 

[Marc Gravell]

So it sounds like the line endings are inconsistent (cr, lf and all
combinations). If this is a case, then perhaps the csv is at fault -
or maybe the problem is coming from some multi-line text (in a field)
that has odd line endings.

Personally, I'd look at this:
http://www.codeproject.com/cs/database/CsvReader.asp

Far more efficient than OLEDB - not sure if you can attach it directly
to a DataTable, though (but then - I very rarely use DataTable ;-p)

Marc

 

[archana]

Hi,

thanks for your reply.

I will elaborate my question more clearly. See i have once csv file
which if i open using oledb in datatable is working. then what i did
is open that file using streamreader and write to some temp file using
streamwriter with default encoding. Here why i did this is i want to
add some text as first row so i am counting commas in first line by
opening csv file using streamreader and then insering those many
commas as first columns with data wherever want. Uptil now this logic
was working properly since last 1 year. suddenly it starts giving eror
for one file.

So i am not understanding is it related to encoding which i set or
something else?

Please help me asap.

thanks in advance.

 

[Guest]

Archana,

Which part is breaking exactly? Is it breaking when it tries to read the
file or breaking when it tries to write to the file?

From the information provided, I agree with Marc. It sounds like some piece
of the data in the csv file might have a comma in it causing your columns to
be off. It might be a good idea to write something that will count the
commas in each line and output it to you so you can check for the error line.

Otherwise, I have found that it is usually safer to use tab delimited files
rather than comma delimited files. The reasoning for this is that it is
usually more likely to have table data with commas in it than table data with
tabs in it.

If you think your data may contain commas or tabs, it is even safer to use
excel files, but it will require more effort and the use of Visual Studio
Tools for Office.

 

[archana]

Hi,

thanks for reply.

See my header don't have comma in it. Sor in first row i am inserting
just garbage string like string containing only 'a' character 300
times. Means no comma at all.

Still i am facing problem.

can u tell me whether its related to encoding or realted to some
nonprintable chatacter, What i observer is file have lots of non-
pritable chatacter. I found one strange thing that is if i remove one
character by opening file in textpad every thing starts working
properly.

I am very much confused now.

Please help me asap.
thanks .

 

[Marc Gravell]

I still think "line endings"; it sounds like textpad is fixing the
line-endings when saving, which it only does it it thinks the data is
dirty (i.e. you have removed on character).

I don't know textpad, but I use "Programmers Notepad 2", which has an
option to view line endings. If textpad has something similar, turn it
on and loook at what you see. At the minimum, they should agree on
every line (i.e. "CRLF", "CR", or "LF" on every line). For best
results, they should match what your parser is expecting. But I don't
use OLEDB from .NET, so I don't know what it wants - the link I posted
will accept any suitable line ending.

Marc

 

[Guest]

Still, you never answered my question. Is it breaking when your application
tries to read the file or is it breaking when you try to write to the file?

 

[Rad [Visual C# MVP]]

Hi all,

I have csv file which i want to show into datatable.

when i try to use oledb to copy content of csv file into datatable not
all rows are getting copied into datatable. Some last rows are
missing. What i observe it if i open csv file in textpad and save
that in file format 'PC' it starts working.

I am not getting why this is happending?

can any one help me.

thanks in advance.

Check out a library called FileHelpers. Its really good for this sort
of thing