help with a CSV file

[SpartanX]

Hi Peeps

I'm busy with a CSV file, the delimiter used it's a semicolon. Now, the problem is, some of the URL's on the lines contain a semicolon. Now those semicolons act as a separator/delimiter.

I'd like to ask what can I do to solve this issue?

I read somewhr that if the CSV is comma separated then I'd put double quotes ( "" ) around the text. I havent tried this since I think it might mess up the URLs.

 

[HavocXphere]

CSV is pretty much a free for all...

Just throw in some logic that if the first part has a http and the last part a .com then ignore the delimiter or something like that

 

[koeksGHT]

urlencode data before?

 

[DrJohnZoidberg]

Hi Peeps

I'm busy with a CSV file, the delimiter used it's a semicolon. Now, the problem is, some of the URL's on the lines contain a semicolon. Now those semicolons act as a separator/delimiter.

I'd like to ask what can I do to solve this issue?

I read somewhr that if the CSV is comma separated then I'd put double quotes ( "" ) around the text. I havent tried this since I think it might mess up the URLs.

Yes, you want to enclose your fields using something like double quotes. Generally the tools that export CSVs should do this automatically when there are conflicts with the delimiters.

I've often found tab delimited files work better for some types of data.

 

[HavocXphere]

Wait...are you reading an existing file or writing one? Isn't quite clear from OP

 

[dadecoza]

I read somewhr that if the CSV is comma separated then I'd put double quotes ( "" ) around the text.

That works for me (reading and writing).

 

[biometrics]

Double quotes (ascii 34) around text, no quotes for values, seperated by commas. Cr/lf/crlf to end a line.

 

[Messugga]

Yes, you want to enclose your fields using something like double quotes. Generally the tools that export CSVs should do this automatically when there are conflicts with the delimiters.

I've often found tab delimited files work better for some types of data.

That's called a text qualifier.

 

[DrJohnZoidberg]

That's called a text qualifier.

Yep, sometimes it is called a text qualifier, sometimes it's called field enclosures depending on what software you're working with.

 

[SpartanX]

Yes, you want to enclose your fields using something like double quotes. Generally the tools that export CSVs should do this automatically when there are conflicts with the delimiters.

I've often found tab delimited files work better for some types of data.

Ok, I'll try the double quotes. The sample of the program I'll be exporting the data to came with semicolon delimiter, so I just carried on using them, even though I can used other types such as tab.

Wait...are you reading an existing file or writing one? Isn't quite clear from OP

Writing one.

 

[SpartanX]

CSV is pretty much a free for all...

Just throw in some logic that if the first part has a http and the last part a .com then ignore the delimiter or something like that

That sounds like something that can work. Just not sure how to do it to be honest

 

[SpartanX]

That works for me (reading and writing).

The double quotes did not work in my case.

urlencode data before?

Worked like a charm Thanks mate!

And thanks to everyone who tried helping!

 

[Drifter]

Use a different delimiter ,like a tilde ~ or pipe |.

 

[SpartanX]

Use a different delimiter ,like a tilde ~ or pipe |.

Already had over 400 lines, was way to far to change to another! But thanks for the suggestion, KoeksGHT's suggestion worked.

 

[Batista]

Sounds like you are using a french format csv file.Join the club.

 

[_kabal_]

You are obviously doing something wrong if double quotes didn't work. There is no difference between using a comma and a colon/semi colon. URL encoding is just hiding a fundemental flaw that you have in your process

Use this, http://csved.sjfrancke.nl, it is the best csv editor

 

[SpartanX]

You are obviously doing something wrong if double quotes didn't work.

Yes, double qoutes did not work, and maybe you're right, I'm doing some thing wrong. Here's how I did it:

Line: startvalue;http//www.domain.com/category/title_p_44489484;jsessionid=162EB89A95F6D2F4CD354E8FA670A408;end value;

Now, what I did, I entered double quotes like below on the URL to avoid the semicolon inside it: startvalue;"http://www.domain.com/category/title_p_44489484;jsessionid=162EB89A95F6D2F4CD354E8FA670A408";end value;

Not sure what I did wrong.

There is no difference between using a comma and a colon/semi colon. URL encoding is just hiding a fundemental flaw that you have in your process

Yes but I cannot use both on the same document right?

Use this, http://csved.sjfrancke.nl, it is the best csv editor

Wow, that might come in as a life saver!!! I just checked the screen shot on the site and it seems like it might help me save some time. Will let you know how it works out, thanks for the suggestion man!

 

[_kabal_]

startvalue;"http://www.domain.com/category/title_p_44489484;jsessionid=162EB89A95F6D2F4CD354E8FA670A408";end value;

yes, that is indeed now a valid semi-colon delimited line.

when you say that it doesnt work, what do you mean.

 

[SpartanX]

yes, that is indeed now a valid semi-colon delimited line.

when you say that it doesnt work, what do you mean.

Well, for some reason it did not work.

By the way, I came back to let you know that the software you recommended is already saving me hours of work! Thank you very much for recommending it!

One thing though, the software that I use to crawl saves the files in CSV format. BUT, it stored the values in different cells without the delimiters. So I have to edit the file as in copying and pasting all the data in one cell while also putting the delimiter.

So I thought if I could open the file in CSVed and then save it as CSV it might be structured correctly. So I loaded it, sure it inserted the delimiter and the values where inside a single cell. But when I save the file and open it in excel, everything goes beck to where it was.

Do you have any idea what I'm suppose to do on CSVed to make this work? Hopefully I have made my problem clear.

 

[HavocXphere]

One thing though, the software that I use to crawl saves the files in CSV format. BUT, it stored the values in different cells without the delimiters. So I have to edit the file as in copying and pasting all the data in one cell while also putting the delimiter.

If its the exact same then you can use Notepad++ and record a macro and just replay that a billion times.