Truncation of fields when CSV file is read using ADO.NET

I encountered a major problem with ADO.NET CSV reader in one of the projects where CSV File Import played a major role.
When a field in the CSV file with a “–“(hyphen) is read, the characters before the “–“are discarded. For example the model F-150 is read as –150 and T-Bird in the model field is not being read. And some of the values were missing in some of the fields.
Given below is the code I used :

 string strConnString = “Provider=Microsoft.Jet.OLEDB.4.0;Data Source=” + folderName + “;Extended Properties=”text;HDR=Yes; FMT=Delimited; IMEX=1””;
string sqlSelect = “select * from [" + fileName + "]“;
System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection(strConnString.Trim());
conn.Open();
System.Data.OleDb.OleDbDataAdapter adapter = new System.Data.OleDb.OleDbDataAdapter(sqlSelect, conn);
DataSet ds = new DataSet();

ds.ReadXmlSchema(Server.MapPath(”xmlschema.xsd”));

ds.EnforceConstraints = false; 
F-100 reads as -100
K-100 reads as -100
S-100 reads as -100
100-F reads as -100
100-K reads as -100
100-S reads as -100
100$F reads as 100
100$K reads as 100
100$S reads as 100
F100 reads as 100
K100 reads as 100
S100 reads as 100
F100 reads as 100
K100 reads as 100
S100 reads as 100
F.100 reads as .1
K.100 reads as .1
S.100 reads as .1
K-.\$$$.\1 reads as -0.1
K\-22..$$\21 reads as -22.221
-$.FSK1 reads as -0.1
This happens only wen there is only one row in thecsv file or more than half the values in a column is having the avove specifiedvalues

 

string strConnString = “Provider=Microsoft.Jet.OLEDB.4.0;Data Source=” + folderName + “;Extended Properties=”text;HDR=Yes; FMT=Delimited””;
string sqlSelect = “select * from [" + fileName + "]“;
System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection(
strConnString.Trim());
conn.Open();
System.Data.OleDb.OleDbDataAdapter adapter = new System.Data.OleDb.OleDbDataAdapter(sqlSelect, conn);
DataSet ds = new DataSet();
adapter.Fill(ds, “Inventory”);
return ds.Tables[0];

Samir found a solution, to add a schema file which identifies all fields in the csv file as string values.
And call DataSet.ReadXMLSchema() method to attach the schema to the DataSet. Also the schema constraints are not enforced .

modified code looks like this..

 

 

adapter.Fill(ds, “Inventory”);
return ds.Tables[0];

Even after using this code values like “F-150″ was read as -150 if there is only one row in the csv file.

May be because ADO.NET does some internal calculation to treat F as floating point or something.. The fun part is that f-150 is read correctly.. problem is with capital letters  . 
So we ended up using a custom third party csv reader . 
Moral of the story… never use ADO.NET csv reader… Always go for a custom CSV parser or a third party library.

147 comments

Just wanted to say thanks for this.

Nice post . keep up the good work

i know this is not exactly on topic, but i run a blog using the blogengine platform as well and i’m having issues with my comments displaying. is there a setting i am missing? maybe you could help me out? thanx.

Thank you for another fantastic blog. Where else could I get this kind of information written in such an incite full way? I have a project that I am just now working on, and I have been looking for such information… Regards…

Hello, I have browsed most of your posts. This post is probably where I got the most useful information for my research. Thanks for posting, maybe we can see more on this. Are you aware of any other websites on this subject… Regards..

<P>I’m glad China is starting to loosen some of their restrictions on the internet. Now more websites are accessible.</P>

Thanks for taking the time to discuss this, I feel strongly about it and love learning more on this topic. If possible, as you gain expertise, would you mind updating your blog with more information? It is extremely helpful for me.

Thank you! This software is the best I have used.

I can see that you are an expert in this area. I am starting a website soon, and your information will be very useful for me.. Thanks for all your help and wishing you all the success in your business.

I can see that you are an expert in this area. I am starting a website soon, and your information will be very useful for me.. Thanks for all your help and wishing you all the success in your business.

I determined your web log on yahoo and know a couple of of the posts. Excellent activity!

Thanks for another fab article – I&nbsp; enjoyed&nbsp; it! its amazing what you can find with yahoo, by the way how do i subscribe to the rss feed?

I’ve read some posts and i like your blog.I’m just starting up my own and only hope that i can write as well , thanks!.
my site : <A href="http://fieracasa.com">agenzie casa</A>

I really like your posts. I really appreciate the excellent quality content you are posting here for free for your readers. May I ask which blog platform you are using?

Very helpful blog.. Thank you for the information…

That’s great information. I learned a lot especially about what you mentioned. This is all new to me. Thanks for the great article, If I’m using firefox, how do I add RSS and feeds to my browser?

Hard work spotlights the character of people: some turn up their sleeves, some turn up their noses, and some don’t turn up at all.

This is food for thought, it’s great material.

Err that was strange, my comment got eaten. Nevertheless I wanted to say thank you for the update.

Hate to be off topic but I just wanted to find out on what is the difference between blogenenigne and wordpress blogs? Is it easier to use or more efficient? I amseeing a lot of blogs powered by this software popping up lately and wondering if it is better or not? Thanks…

Pretty good post. I just stumbled upon your blog and wanted to say that I have really enjoyed reading your blog posts. Any way I’ll be subscribing to your feed and I hope you post again soon.

it’s good to see this information in your post, i was looking the same but there was not any proper resource, thanx now i have the link which i was looking for my research.

WoW i love ur blog dude. Nice story and really fun for me to read
Regards budi

Thanks for post. It’s really informative stuff.
I really like to read.Hope to learn a lot and have a nice experience here! my best regards guys!

Good post man, I find your stuff really useful!

Thank you for the articles and information on this blog. It is very interesting. I learnt a lot! 🙂

This is very interesting post. Thanks for the useful information provided.

This is very interesting post. Thanks for the useful information provided.

Great post, thanks for the info, i really needed it.

I have been searching everywhere for this info.

Thanks

This is a really nice article.

You’ve made some good arguments

Hope you like feedback. You’re gonna get a few on this post 🙂

Nice Post. It’s really a very good article.

I really love shoes, I’ve no idea exactly what my problem is LOL. I just love being able to mix match outfits in which coordinate from head to toe, specifically with shoes becoming a big focal point.

i am really appreciating,woow very informative…..great job thank’s…!

I truly love shoes, I’ve no idea exactly what my issue is LOL. I just love being able to mix match outfits that coordinate from head to toe, specifically with shoes being a big focal point.

Perhaps you have considered putting some yahoo clips to your site to keep the readers more entertained? I mean I just read thru the entire site here and it was good but since folks are more of a visual learner, I found that to become more helpful. Well you should let me know how it turns out! I like what you people are doing and up too. Such smart work and reporting! Keep up the great work guys. I’ve added you guys to my blogroll. This is a great article thanks for sharing this informative information.. I will visit your blog regularly for some latest post.

i am really appreciating,woow very informative…..great job thank’s…!

It is tough to find a piece of jewelry that i’ll wear upon a daily basis.

Hi there. This blog seems a lot like my site American Job Advocate. I just wrote top 10 Interview Questions. Just trying to spread the word. 🙂 If you want to check it out, it’s at <a href="http://www.americanjobadvocate.com/content/top-10-job-interview-questions">http://www.americanjobadvocate.com/content/top-10-job-interview-questions</a&gt;. Thanks!

Superb Blog, thanks for helping me with this useful Article. I think it is really a great topic to write about on my Site. Also here is some good information if needed: <A href="http://bestrefurbisheddesktopcomputers.com">refurbished desktop computers</A>

I love you not because of who you are, but because of who I am when I am with you.

I currently have poor credit due to some old credit card bills. I need a car badly, so I’ve been learning about buy here pay here dealerships. Thanks for the info!

This is such a great resource that you’re supplying and also you give it away for zero cost. I appreciate seeing websites that comprehend the worth of supplying a prime resource for absolutely free. I really loved reading your post. Thanks!

I like that you’ve touched on this subject, it’s somewhat of a rariety.

Extremely inspiring. It’s amazing what can be done when we put our minds to it.

I really appreciate your professional approach. These are pieces of very useful information that will be of great use for me in future.

Thank you for sharing this article. So excellent.Welcome to our website,we can exchange our idea.Thank you!

Ok this is super !!!

I was interested in birds , all kind of birds . And you?
http://www.nikeairjordan.cc/jordan-ajf8-77/

Pretty good post. I just stumbled upon your blog and wanted to say that I have really enjoyed reading your blog posts

Hey very nice blog!! Man .. Beautiful .. Amazing .. I will bookmark your blog and take the feeds also…

I have to admit the post is well written and the point of view is quite interesting. I had no idea about this until now.

This is the good step u have taken. Thanks for writing this, its clear you have spent a good amount of time on your sites development.

Your current article usually have a lot of really up to date info. Where do you come up with this? Just saying you are very innovative. Thanks again.

There are several posts out there about this, I think taking there reference could have made this post or article really informative. I am not saying this information is not good. But I must say that the information provided here was unique, but to make it more near to complete, supporting with other previous information will have been really good. The points you have touched here are really important, so I will post some of the information here to make this really good for all the newbie’s here. Thanks for this information. Really helpful!

Good post. I bookmarked this website

Good post. I bookmarked this website

Good post. I bookmarked this website

Good post. I bookmarked this website

Good post. I bookmarked this website

Good post. I bookmarked this website

Good post. I bookmarked this website

Good post. I bookmarked this website

Good post. I bookmarked this website

This is just such an Excellent post!
You are really preventing me from working today, but it’s just great to see more people writing about this. Oh, Yes… Do you have an RSS feed?

I had just finished reading your articles,and this is very good! timberland boots uk Being a comprehensive trade solution provider offering sale services with free shipping to international buyers who are interested in shopping online.The products here are timberland boots outlet, timberland roll top boots and men timberland boots. As an online business platform mainly retailing, waterproof boots we provide our buyers with an efficient and manageable procurement process covering every phase of the international supply chain and streamlining trade channels. Also welcome wholesaling, feedback now!www.timberlandshoesonline.com

Its Pleasure to study your blog.The above articles is extremely outstanding, and I truly enjoyed reading your weblog and points that you expressed. I enjoy to appear back on the typical basis,post far more about the topic.Thanks for sharing…maintain writing!!!

Hey just testing nice blog

Cool thanks for posting this

This is very detailed and informative article. Thanks for helping me solve my dilemma!

Thanks for your post! It’s very interesting!

Wow – now that’s perspective! I think we often react in agreement or disagreement because of our emotions, but hearing another side, passionately presented, really makes us think!

I don’t know if I am any smarter but I enjoyed reading your post – Thank You

yeah, all of these dating sites just get bombarded with spam, kinda like all these comments.

To all the commentators (both past and future), thank you in advance for sharing your opinions and for being respectful from the opinions of others are well.

After analyzing many applicable studies and products currently sold on the market, only Zero Nicotine Quit Smoking Patches guarantee smoking cessation in a very short period. Withdrawal symptoms will be milder due to the all-natural nicotine substitute, which helps you shake the nicotine habit gradually, with less physical and emotional stress.

Considerably, the post is in reality the freshest on this worthy topic. I agree with your conclusions and will eagerly look forward to your forthcoming updates. Saying thanks definitely will not simply be sufficient, for the fantasti c clarity in your writing. I can directly grab your rss feed to stay informed of any updates. Gratifying work and much success in your business enterprize!

For me, I reside in rural area, know plenty of individuals who raise meat animals, and am not a vegetarian. I reside in an region where coyotes and similar animals can be a predation difficulty for pets and livestock. There are generally laws allowing the shooting of predation animals, although in truth that rarely happens. I generally really don’t mind the idea on the eating or meat & utilizing of leathers so long as the animal is treated as humanely as achievable through the entire process, and that there is certainly little waste with the sacrifice made for food and clothing. I feel I’ve stated in comments elsewhere that’s one with the reasons luxury furs do not appeal to me. Making use of an animal just for the skin/fur with no use with the meat is wasteful and distasteful to me unless you reside in an area where the temperatures do get so cold that animal furs are the only economical way to get the necessary warmth into clothing needed for extreme temperatures (but these conditions as well as the folks who reside in them a vast minority of fur wearers).

wow, I am glad to see the coding to truncating fields when CSV file is being read with ADO.net. I just copied your code for future use 😀

I live in Melbourne and I can attest to the truth that kangaroos are overpopulated here (you will find a great deal more roos than the entire population of Australia), so much so that they’re harming the other animal species and need to be shot down. Yes the Aussies do eat roo meat and they could be purchased in supermarkets, and the hide goes into producing bags, footwear and footballs. In the event you really feel this is inhumane, wearing cowhide shoes is no distinct.

Found you on google, gonna bookmark now!

I wanted to thank you for this excellent read!! I definitely loved every little bit of it.Cheers for the info!!!! & This is the perfect blog for anyone who wants to know about this topic. You know so much its almost hard to argue with you ………

Resources like the one you mentioned here will be very useful to me! I will post a link to this page on my blog. I am sure my visitors will find that very useful.

just guessed i’d distribute and let you know your aticles is beneficial for shown the invaluable hidden secret.I genuinely enjoy your blog.Efficaciously, the article is in truth the very best on this worth despite the fact that subject. I concur along with your findings and will consistently look forward for your forthcoming tweets. Just saying thanks will not just be enough, for the remarkable lucidity within your posting. I will quickly capture your rss feed to remain abreast of any updates.Trustworthy operate and a good deal being successful in your own operate and business endeavors.At any rate hold up the very good work.Thanks a lot.

Hi webmaster, commenters as well as everyone else !!! The website is positively fantastic! Lots of good information and creativity, both of which everyone need to have! Keep them coming… you all carry out such a great job at such Principles… cannot tell you just how much I, for one truly appreciate all you do!

I am glad I am not the only one.

Thanks for the info. If I use this I’ll give you credit.

Its a pity you dont have a donate button, i would donate some =)

I’ll follow your captions always, thank you

Keep functioning ,fantastic job!

Very helpful content. The particular reason you identified is easy to understand, i love your current composing model.I was also amazed simply by the amount of data bundled about the topic.Many thanks. Paul.

"No one likes a xp slow, when you startup your computer you want it to be ready to use immediately.SlowXPFix is a risk-free tool that would help you to speed up your slow Windows XP, and get your XP system back to its optimal performance.
"

I was looking for the issue I was having with the truncation of fields. Thanks for solving it for me I really appreciate it!!!!!

Great post and straight to the point. I am not sure if this is actually the best place to ask but do you folks have any thoughts on where to hire some professional writers? Thank you 🙂
<A href="http://brightestflashlights.com">led flashlight </A>

I am constantly looking online for ideas that can assist me. Thank you!

<A href="http://brightestflashlights.com">surefire flashlight </A>

this blog is so very interesting., i love reading it., thanks for the information here….

This site is cool. i visit here evaryday.

Some PDF to Word Converter performs fast and accurate conversions from PDF to Word Doc with columns, tables, headers, footers, graphics and layout reproduced just as what they were.

Hey,excellent blog please keep on posting blogs similar to this…if you need information on truck insurance please go to our page.

This is great information. Folks, listen to this guy. He’s very bright and knows what he’s talking about. You can’t go wrong with following people who can walk the walk – like him. Great information, dude. Just the way I like it!

It is my honor to read your aticle!Succent language,concise blog,while the content is full of connotation.I like your writing styles very much.Thank you for sharing.

The LAST resort
Skeleton Crew 2010
living death

http://www.eljnoub.com/

Amazing ideas! I’ve bookmarked your site and will be back for more. Keep it up. You’ve got some great information, unlike many other regurgitated and rehashed blogs.

Very usefull Info Very Kind of You for sharing

Brilliant blog post.I have bookmarked it already

I think you have to improve a bit the design and usability of your blog.

Your post really helped. It seems very interesting. Hope to read more about your Fashion tips. keep it up!

nice article, I merely passed this to a friend that was doing a little amount of research on this subject

LG Optimus 7 Combining Style And Substance In One Stunning Handset | Cheaphomeideas.com

Have you considered about including some social bookmarking buttons to these blogs. At least��for twitter.

I do not think the same way, but part you make make a good point

I’ve stopped by here a couple times and it looks like your blog posts get more informative each time. Keep it up I enjoy reading them.

I was looking for as post about this and found myself here.Nice work and good post.

"No one likes a xp slow, when you startup your computer you want it to be ready to use immediately.SlowXPFix is a risk-free tool that would help you to speed up your slow Windows XP, and get your XP system back to its optimal performance.
"

ErrorCodeFix is a risk-free program that will get you out of the embarrassing situation of every error code in your PC.You could find a way to speed up your computer and stay away from the irritating error code error messages.

The database seems to become unstable at my website too. I suppose is a plugin problem and I will verify what is happen !

Thanks for the Information, thanks for this great Post. I will come back soon…

Sorry for the huge review, but I’m really loving my new home here in Coppell, and hope this, as well as the excellent reviews some other people have written, will help you decide if Coppell is the right choice for you.

nice good website yea nice job our blog will soon be adding reviews on blogs and add them to our blogs as the top best 100 blogs to visit we also do reviews on Consumer Reports reviews��all types of reviews we will get back to you

I am a gay Christian. The struggle for me was denying what God created ME just as I am. My journey is to discover that which God created in me, as me, and through me. When I allowed what God created just to "BE," I found true completeness. When I began acknowledging God’s perfection in my soul, my life shifted for the better. The sadness many zealot Christians assume gays and lesbians have is just not the case. I have found 99% more straight men and women who are miserable with their lives. I find 99% more straight people caught in the void of not knowing themselves or how to celebrate their own lives as creations of God. I stopped feeling bad about myself and I started to experience the TRUE JOY of being a believer who just so happens to be gay.

This is my second visit to this blog. We are starting a brand new initiative in the same niche as this blog. Your blog provided us with important information to work on. You have done a fantastic job.

It is not mandatory. But if it is there, it would be nice to have such links.

Once I thought about things like: why such information is for free here? Because when you write a book then at least on selling a book you get a percentage. Thank you and good luck on informing people more about it!

i know i’m a little off topic, but i just wanted to say i like the layout of your blog. i’m new to the blogegine platform, so any advice on getting my blog looking nice would be appreciated.

I found this article with Yahoo and it is really interesting. Congratulations, I’ll return to comment your blog. Come to visit mine if you want 🙂

Sorry for the huge review, but I’m really loving my new home here in Frisco, and hope this, as well as the excellent reviews some other people have written, will help you decide if Frisco is the right choice for you.

A little off-topic but I saw even more evidence reported in the news today that statins are bad for you – pleased I stopped them when I did. Makes me worry what else is being fed to us that’s secretly killing us…

Hello! I just would like to give a huge thumbs up for the great info you have here on this post. I will be coming back to your blog for more soon.

Couldn’t say it better myself, keep up the good work mate!

Amazing ideas! I’ve bookmarked your site and will be back for more. Keep it up. You’ve got some great information, unlike many other regurgitated and rehashed blogs.

The new SwessPharma Labs <A href="http://www.swesspharma.com"><STRONG>Legal Steroids</STRONG> </A>are surprisingly good, but not as good as the illegal anabolic steroids. It works well, but does’nt have any side effects, and the gains stay. If you occasionally plan on using illegal steroids, that’s not an issue, but if you’re planning to get a great physique ,the benefits of legal steroids may be important.

This is a quite valuable post, I found it looking through Yahoo. I believe most people would agree with your views. Finally – a person with common sense!
PS I quite like the template you are using – where did you get it from?

This was refreshing. I wished I could analyze every post, but i have to go back to work now… But I’ll be back.

resources like the one you mentioned here will be very useful to me! i will post a link to this page on my blog. i am sure my visitors will find that very useful.

I like Windows 7 is so nice compared to Vista. It may be a little harder to use than Window’s XP, but, it is more stable especially if you are running multiple applications.

searching for th is for some time now – i guess luck��is a great deal more advanced than search engines

this post remind me of a post i made on one of my blogs

Leave a reply