Click here to Skip to main content
14,335,764 members
Rate this:
Please Sign up or sign in to vote.
See more:
I am having an issue with importing a CSV file. The problem arises when an address field has multiple comma seperated values e.g. home no, street no, town etc.

I have googled but the problem did not solved because of a single field containing multiple comma separated values.

Any idea or solution? because I didnt found any help


for example

my file have these column

ID |Instrument |Units |EntryDate |Comments

2C | pen,screw |1 |1/1/2010 |rer


my problem is that the value of Instrument column it reads this two column (pen and screw) not(pen,screw) .the take screw as next(Unit) column value.....
Posted
Updated 24-Oct-13 21:07pm
v5
Rate this:
Please Sign up or sign in to vote.

Solution 1

The file should be properly escaped, see for instance: "Dealing with commas in a CSV file" at Stack Overflow[^].
   
Comments
   
5ed. For a record, it depends on the file format; proper delimiting information can be simple lost, so it would be good to see an example of such line.
—SA
thatraja 24-Oct-13 9:11am
   
5!
ErBhati 24-Oct-13 9:12am
   
my problem not solved by this
Ranjan.D 24-Oct-13 15:18pm
   
Tell us what exactly is your problem ?
ErBhati 25-Oct-13 3:03am
   
my file have these column
ID |Instrument |Units |EntryDate |Comments
2C346 pen,screw 1 1/1/2010 rer


my problem is that the value of Instrument column it reads this two column (pen and screw) not(pen,screw) .the take screw as next(Unit) column value.....
Rate this:
Please Sign up or sign in to vote.

Solution 2

You can see clearly, from the line you posted that the file is not comma delimited as you said; it's rather a pipe delimiter; so change the delimiter in your code to pipe ie. "|" instead of a comma "," and it should work fine. Let's know if that does not work out.
   
v2
Comments
BillW33 24-Oct-13 17:05pm
   
You should not post a comment to a solution or an addition to your question as a "Solution". Either add a comment to a previous solution by pressing the "Have a Question or Comment?" button or update your question by using the "Improve question" button. I did not vote, but I thought you should know why this might be downvoted.
ErBhati 28-Oct-13 3:40am
   
I can't Change the delimiter in my code to pipe ie. "|" instead of a comma "," .becouse client say i want enter value with comma(",").


my problem exactly is

my file have these column
ID |Instrument |Units |EntryDate |Comments
2C346 pen,screw 1 1/1/2010 rer


my problem is that the value of Instrument column it reads this two column (pen and screw) not(pen,screw) .It takes screw as next(Unit) column value.....
bassofa1 28-Oct-13 6:05am
   
Well Noted!! Thanks
bassofa1 28-Oct-13 6:15am
   
If I understood you clearly, you want to have Pen as separate column value and Screw as a another separate column value. As far as I am concerned, the example you posted is not even a correctly delimited record. for example I expect to see a delimiter between any two separate column values. eg.

ID | Instrument|Units | EntryDate | Comments
2C346 |pen,screw |1 |1/1/2010 |rer

confirm if your example is exactly as mine.
ErBhati 28-Oct-13 6:28am
   
yes my is exactly same as you..

but I want clear one thing I want to have pen,Screw as a column......
bassofa1 28-Oct-13 10:38am
   
If yours is exactly as mine, then like I told you the very first time; your file is rather pipe-delimited "|" not ",". Which is the reason why you have an issue. To get "Pen,Screw" as one Column, the delimiter CANNOT be ",". Clearly it is showing "|" and that should be it.
ErBhati 29-Oct-13 6:05am
   
not my file is not pipe-delimited "|" here it how in example for better understanding....i have csv file.. here "|" for seprate the coluns my file does not contain "|".
bassofa1 30-Oct-13 8:26am
   
Well, if it is comma-separated and you don't have control over the file then I am wondering how you can get Pen,Screw in the same column without any changes by your client. The idea of a delimited file is so that you can present data in way we can use the delimiter to get different fields- and your scenario defeats that purpose. Your answer is to get back at your client and tell him/her that they have to add an escape character where they want to use comma "," not as delimiter but as real character. For example, you can ask him to do "Pen &,Screw" representing "Pen,Screw". And then you will handle that whenever you receive a file.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100