Log in

No account? Create an account
B. Henderson Asher's Moments of Mirth [entries|archive|friends|userinfo]
Listen in, listen Ian!

[ website | Flickr ]
[ userinfo | livejournal userinfo ]
[ archive | journal archive ]

Help me livejournal, you're my only hope. [Sep. 14th, 2009|11:43 am]
Listen in, listen Ian!
I have a spreadsheet with two columns in, which are a client code and some notes. I want to extract these into a fixed format text file so that I can use SQL to put them in a database. I can get the two columns into one cell, padded with blanks so that the format is right, but some of the text in the notes column has embedded carriage returns. When I copy and paste these cells into notepad or similar, it puts quotes round them so that the ones with carriage returns in them are misaligned with the ones that don't. How do I get rid of the carriage returns so that it works? I've tried messing with SUBST() and doing find replace thingys, but it made no difference.

[User Picture]From: carsmilesteve
2009-09-14 10:52 am (UTC)
oh hey, i have a little "save text" widget that exports from excel to a text file which might be useful?

just tried it with text with carriage returns in and it looks ok...

(Reply) (Thread)
[User Picture]From: carsmilesteve
2009-09-14 10:53 am (UTC)
shall i email it to you?
(Reply) (Parent) (Thread)
[User Picture]From: ruudboy
2009-09-14 10:54 am (UTC)
Well, I've just realised that I was over-engineering it, and search replacing '"' with '' in the text file might work. But yes pls, in case the next step doesn't work.
(Reply) (Parent) (Thread)
[User Picture]From: carsmilesteve
2009-09-14 11:06 am (UTC)
sent to yr gmail.
(Reply) (Parent) (Thread)
[User Picture]From: spoonrefuter
2009-09-14 11:09 am (UTC)
My Excel skills are meagre - but couldn't you simply get rid of the CR/LFs in the SQL query (using the REPLACE function)?
(Reply) (Thread)