CSV file, but not regular commas
 

Hi everyone,
Got a problem.
I have 100 text files that i combine into 1 csv file. Text files ar
single quote separated and comma seperated, it looks like this

'105139.002','3','PANEL LAYOUT & WIRING','

This is fine, as soon as it becomes a CSV file i remove all the singl
quotes and it's all good. But i get a problem when i encounter
regular comma inside the text, like this:

'105139.002','3','PANEL LAYOUT, WIRING','

Notice after Layout there is a comma, but I don't want that to be in
separate cell.
So I'm trying to figure out something along the lines of this logic.
Look in cell D1
if last character is ' then skip to next one
if last character is not, then name that cell A
Go to the right one cell
if that cell has a ' at the end, then call it a B, and combine A
B
if that cell does not have a ' then name it b and go to next cell t
the right
then i need to delete all the left overs after i combine.
Please help me with coding.
Thanks,
Viktor

--
Vikxce
-----------------------------------------------------------------------
Vikxcel's Profile: http://www.excelforum.com/member.php...fo&userid=1860
View this thread: http://www.excelforum.com/showthread.php?threadid=49564

CSV file, but not regular commas
 

don't use the comma as the delimiter, use the '.

--


Gary


"Vikxcel" wrote in
message ...

Hi everyone,
Got a problem.
I have 100 text files that i combine into 1 csv file. Text files are
single quote separated and comma seperated, it looks like this

'105139.002','3','PANEL LAYOUT & WIRING','

This is fine, as soon as it becomes a CSV file i remove all the single
quotes and it's all good. But i get a problem when i encounter a
regular comma inside the text, like this:

'105139.002','3','PANEL LAYOUT, WIRING','

Notice after Layout there is a comma, but I don't want that to be in a
separate cell.
So I'm trying to figure out something along the lines of this logic.
Look in cell D1
if last character is ' then skip to next one
if last character is not, then name that cell A
Go to the right one cell
if that cell has a ' at the end, then call it a B, and combine A &
B
if that cell does not have a ' then name it b and go to next cell to
the right
then i need to delete all the left overs after i combine.
Please help me with coding.
Thanks,
Viktor.


--
Vikxcel
------------------------------------------------------------------------
Vikxcel's Profile:
http://www.excelforum.com/member.php...o&userid=18607
View this thread: http://www.excelforum.com/showthread...hreadid=495646

CSV file, but not regular commas
 

Hello Viktor,

You can use the Replace function in VBA to change the comma between the
single quotes to another charater. You could use either a Tab or Pipe
"|" as a delimiter. Excel will accept either in flat file (CSV).

Sincerely,
Leith Ross


--
Leith Ross
------------------------------------------------------------------------
Leith Ross's Profile: http://www.excelforum.com/member.php...o&userid=18465
View this thread: http://www.excelforum.com/showthread...hreadid=495646

CSV file, but not regular commas
 

"Vikxcel" wrote in message
...

Hi everyone,
Got a problem.
I have 100 text files that i combine into 1 csv file. Text files are
single quote separated and comma seperated, it looks like this

'105139.002','3','PANEL LAYOUT & WIRING','

This is fine, as soon as it becomes a CSV file i remove all the single
quotes and it's all good. But i get a problem when i encounter a
regular comma inside the text, like this:

'105139.002','3','PANEL LAYOUT, WIRING','

As soon as it becomes a text file, do not remove the
single quotes, but replace them with double quotes.
Excel will then nicely read the thing in.

Cheers,
Dirk Vdm

CSV file, but not regular commas
 

"Dirk Van de moortel" wrote in message
...

"Vikxcel" wrote in message
...

Hi everyone,
Got a problem.
I have 100 text files that i combine into 1 csv file. Text files are
single quote separated and comma seperated, it looks like this

'105139.002','3','PANEL LAYOUT & WIRING','

This is fine, as soon as it becomes a CSV file i remove all the single
quotes and it's all good. But i get a problem when i encounter a
regular comma inside the text, like this:

'105139.002','3','PANEL LAYOUT, WIRING','

As soon as it becomes a text file, do not remove the
single quotes, but replace them with double quotes.
Excel will then nicely read the thing in.

Forgot to mention...
This only works if your data does no contain any double
quotes by itself. To avoid having a problem with that,
first double all double quotes in the file before you replace
the single quotes with doubles.
Example:
'105139.002','3','PANEL "LAYOUT", WIRING'
==
'105139.002','3','PANEL ""LAYOUT"", WIRING'
==
"105139.002","3","PANEL ""LAYOUT"", WIRING"

When you open this, save the result as CSV again, and
look at it with a text editor, you will see:
105139.002,3,"PANEL ""LAYOUT"", WIRING"
Only the fields that contain a comma, will retain the
delimiting double quotes.

Dirk Vdm


Cheers,
Dirk Vdm

CSV file, but not regular commas
 

Thank you all for your help. I can't believe how busy this forum is, 12
hours and I'm on page 13?!?! WOW.

Can anyone help me with making a .bat file to find & replace?
I know how to find, but what about find and replace? is it
complicated?

Thanks ahead to everyone!
Viktor.


--
Vikxcel
------------------------------------------------------------------------
Vikxcel's Profile: http://www.excelforum.com/member.php...o&userid=18607
View this thread: http://www.excelforum.com/showthread...hreadid=495646

CSV file, but not regular commas
 

can I use VBA to find/replace text string in a set of batch files?


--
Vikxcel
------------------------------------------------------------------------
Vikxcel's Profile: http://www.excelforum.com/member.php...o&userid=18607
View this thread: http://www.excelforum.com/showthread...hreadid=495646

CSV file, but not regular commas
 

"Vikxcel" wrote in message
...

Thank you all for your help. I can't believe how busy this forum is, 12
hours and I'm on page 13?!?! WOW.

Can anyone help me with making a .bat file to find & replace?
I know how to find, but what about find and replace? is it
complicated?

Thanks ahead to everyone!
Viktor.

You can use (for instance) the unix utility sed.
Download it from (for instance again)
http://short.stop.home.att.net/freesoft/txtutil4.htm
sed414b.zip and sed414d.zip
Copy sed.exe to a place in the path.
Try to read the pdf-file sed.pdf.
Forget about the other files.
I think this version will only work with simple 8.3 filenames:

Create a bat-file csvquote.bat to call sed twice:
the first call to replace the " with "", and the second
to replace ' with ".
The essence is:
sed -e s/\"/\"\"/g %infile% temp.tmp
sed -e s/\'/\"/g temp.tmp %outfile%

Here's an example of a bat-file:

@echo off
if not "%1" == "" goto okay1
echo CSVQuote.bat - Usage: csvquote infile [outfile]
goto exit
:okay1
set infile=%1
if exist %infile% goto okay2
echo Infile %infile% not found!
got exit
:okay2
set outfile=%2
set tempfile=csvtemp.tmp
if "%outfile%" == "" set outfile=%infile%

sed -e s/\"/\"\"/g %infile% %tempfile%
sed -e s/\'/\"/g %tempfile% %outfile%
del %tempfile%
:exit

Dirk Vdm