Capable CSV Tool... or make Excel not be "smart"

[cosmin]

I feel like banging my head against the wall. I have about 20000 records
that I export from PHPMyAdmin to CSV, to which I want to make some changes
so I can import them into another MYSQL table which has different columns.
The problem is that once I open that CSV in Excel it gets royally messed up,
due to its cursed "smart" routines.
For example, the fields which are in this format "2005-11-15" get changed to
"2005/11/15" because they are autodetected as dates and Excel knows better
than me what I want, right?
Also, when exporting from PHPMyAdmin, the data is like this

"0","Freeware","WinXP,Windows2000,Windows2003","1989",

After working with it in Excel it gets turned into this:

0,Freeware,"WinXP,Windows2000,Windows2003",1989,

As you can see, only some of the fields now have quotes, (why? It's Excel
being smart again and making the CSV inconsistent) which breaks my CSV
import script.
I'm going nuts. I've tried OpenOffice, which does the same thing, unquoting
some of the fields. Tried 602 PC Suite, it crashes when opening the original
CSV, which is about 23 MB, but whatever the cause, I can't try and see if it
works any better.
Tried a tool called DMCSV which is supposed to be a CSV editor, but it locks
up when trying to open the CSV file, because apparently it can't handle the
file's size.
So I'm really stuck. Is there anyway to get Excel to put quotes around all
the fields, so it can generate a consistent CSV.

 

[Beege]

I think we've talked about this before recently. Did you try:

Rename to .txt file.
open into Excel
Text Qualifier = None
Format each import as text - not general, not number, not date, just text.

HTH

Beege

 

[cosmin]

Doesn't work. It messes up even worse. Now fields from one column end up on
another.

 

[Beege]

cosmin,

So I tried it again. Renamed to .txt file
File /open/ test.txt
fixed width type file
created break lines where needed, including separating out the commas that
didn't want to mix with data.
Left formatted as "general"
finished opening.

Came out with quotes where they were in the .csv file.

Maybe??

Beege

 

[Mark Lincoln]

cosmin wrote:
----------
"0","Freeware","WinXP,Windows2000,Windows2003","1989",
After working with it in Excel it gets turned into this:

0,Freeware,"WinXP,Windows2000,Windows2003",1989,

As you can see, only some of the fields now have quotes, (why? It's
Excel being smart again and making the CSV inconsistent)
----------

I don't see inconsistency here. The imported data without quotes were
surrounded with quotes in the CSV file. Excel removes the quotes when
data is presented this way. The imported fields still containing
quotes only contain one double-quote character each:

"WinXP

Windows2003"

and Excel imported them as is as it cannot be determined that they are
not part of the data.

(If you expected to see a field like:

WinXP,Windows2000,Windows2003

it won't happen because the commas are delimiters.)

 

[cosmin]

You don't get it. Or maybe I wasn't clear. It's the fact that it selectively
removes quotes around columns that annoys me. If you look at the "before"
and "after" case, you can see that it removed the quotes for most of the
columns, and only kept those along the field which contains commas.

 

[cosmin]

I think all the apps I've tried so far have problems with line breaks in the
content


I have a row which looks like this:

,"579","90","font renamer, freeware renamer, fonts rename","Got a font
collection? Got a collection, but no order in it? Start by giving font files
the names corresponding to the full font names. For example, you can rename
MLON_I.TTF to Milion Italic.ttf. This name speaks for itself, it is simple
and clear.

How to do it?

Easily. Select a folder with fonts and click the Rename button. That's all.
The program itself will find out which font each file contains and give it
the corresponding
name.","http://www.styopkin.com/free_font_renamer.html","http://www.styopkin.com/images/Screenshots/Screenshot-Free-Font-Renamer-256-192.gif","http://www.styopkin.com/PAD-Free-Font-Renamer.xml","http://www.styopkin.com/FreeFontRenamerSetup.exe","none","0","90","none","none","none","none","90","90","90","Graphic
Apps::Font Tools","Font Tools,Fonts Tools,Font,Fonts","90","Give each font a
name that speaks for itself.","Give each font a name that speaks for itself
with one click! Batch Font
Renamer.","http://www.styopkin.com/images/ICO_FFR.gif",NULLHow to do it?

Easily. Select a folder with fonts and click the Rename button. That's all.
The program itself will find out which font each file contains and give it
the corresponding name.",,NULL


This, and other rows like this break Excel. It makes a mess of my sheet when
importing.
Any way I can get it to ignore line breaks in content?

 

[Mark Lincoln]

You don't get it. Or maybe I wasn't clear.

I misread your example. My apologies.

It's the fact that it selectively
removes quotes around columns that annoys
me. If you look at the "before"
and "after" case, you can see that it removed
the quotes for most of the columns, and only
kept those along the field which contains commas.

From Excel97 Help:

"The CSV (Comma delimited) file format saves
only the text and values as they are displayed
in cells of the active worksheet. All rows and
all characters in each cell are saved. Columns
of data are separated by commas, and each
row of data ends in a carriage return. If a cell
contains a comma, the cell contents are
enclosed in double quotation marks."

The last sentence is the key here. Excel is apparently importing
fields with commas the same way it writes them. But in a quick test, I
can't make it do that; when I import such a field (from a .csv or a
..txt file) it throws out the quotation marks. Curious.