Complicated Find & Replace on a CSV

[mikejdavies]

For what its worth OpenOffice Calc is a great product for working with raw data - I use it a lot for DB stuff and its open source and so free to download.

Also allows CVS to be delimited by whatever character you want, including the pipe "|" character.

[eighteentee]

Ladies & Gents,

This job is turning into a total epic, i.e. War and Peace on Roller Skates on Ice with Flaming Chainsaws.

Still havent got this to work.

I've had varying degrees of success but still no full description on import and also I'm still getting bits of it's old template structure coming through into my 'blank' site.

<bangs head against nearest brick wall>

</cry>

[Chunkford]

Can you post an example description?

[eighteentee]

Just for reference, all the descriptions appear to be enclosed in quotes...

[eighteentee]

Just to clarify, using Norman's Notepad++ method strips out the quotes as pasting the column contents knackers it up a bit.

Going to try the VB macro idea now - I suspect I need to go find an old copy of Office 2003 before I do that though due to the CSV incompatibilities?

EDIT

Actually, simply the fact of importing the CSV *from* Actinic into Excel then exporting the CSV strips out the quotes.

[Chunkford]

Have you tried my method?

[eighteentee]

Have you tried my method?

Yes. The problem when exporting *from* Excel when you've been changing stuff is that the resulting CSV has no quotes surrounding text fields. Is there a way around this?

[Duncan Rounding]

I've never had a problem with needing quotation marks around field data, the separating commas should be fine.

Post an excel file with an extract from your csv with the headers and lets see the issue.

[Mike Hughes]

I believe the problem that came up before was that if the description contained commas then you had enclose the lot in quotations to avoid the commas being seen as separators.

Mike

[Chunkford]

Have you tried a tab delimited import as opposed to comma?

[eighteentee]

Have you tried a tab delimited import as opposed to comma?

I'm dealing with a hierarchical file so tabs aren't possible (I don't think)

[Duncan Rounding]

Tabs should work in an hierarchical file.
Certainly worth a try as it should solve your commas problem.

[eighteentee]

<head_bang on "brickWall/>

*sigh*

I'm absolutely at my wits end with this.

I can't seem to be able to accomplish this import no matter what I do. The problem arrises after I've changed the CSV to strip out all the unwanted 'NOT_CATALOG' references. Bringing it back into a blank Site seems to retain elements of the old (v6 inherited) table-based design and product descriptions are partially missing.

AAARGGGHHHHH!!!!

Sob.

[fergusw]

Phill,
Having re-read this post I would have tackled this from a more straight-forward, direct route. i.e. some simple scripting (VBS or otherwise) that would have looped through the database and stripped out/replaced the required text from the required fields, updating directly as it went. Rather than involving extra importing/exporting steps.
Drop me a PM and I'll see what I can do to give you a helping hand in this direction.
It's the kind of thing we get asked to develop a lot for clients, particularly if the required action is one that involves repeating the process several times.
A couple of hours scripting should be sufficient to do this in one fell swoop.

[eighteentee]

Just wanted to say an absolute MASSIVE thanks to Fergus as he's managed to single-handedly sort this problem out for me.



Now I just need to figure out how to strip out the old theme - I just want to use the default 'Executive' theme for the time being.

Once again, Thanks Fergus - I owe you a pint!