At 11:44 20/02/2018 +1100, Ivo van Emmerik wrote:
Importing CSV
All the information is in the two documents
It's particularly unhelpful that you provide only pictures of the CSV
files, which makes experimentation and confirmation tedious for
anyone helping you.
I want to import this CSV file. And when I import it using those
settings. I tried to change the import columns to Number but I could not.
You shouldn't need to: Standard should do all you need.
Than every number has that single mark there with three spaces and
the only way to get rid of it is manually delete it!
It's best not to imagine that the leading apostrophe is actually in
the cell but rather that it is just a marker. If you declare Comma as
your separator, your columns of numbers are not just numbers but are
preceded by a number of explicit spaces. These strings are imported
as text, and the apostrophe is an indicator that what is there is
text, not the number which it looks like and that you presumably want.
You can sort the problem after you have imported the text. Here's what to do:
o Select all the data cells - in other words. rows 4 onwards.
o Go to Edit | Find & Replace... (or Ctrl+F).
o For "Search for", enter " +" (that's space-plus, no quotes). That
regular expression matches any number of consecutive spaces.
o Leave "Replace with" empty.
o Click More Options and tick "Current selection only" and "Regular
expressions".
o Click Replace All.
The trick here is that after you have removed the spaces, Calc
reinterprets the values as it would do if you typed them initially.
It recognises the text without its leading spaces as numbers and
converts them and stores them as numbers. The apostrophes have disappeared!
But there is an easier way:
o In the Text Import dialogue, at "Separated by", tick Comma, Space,
and "Merge delimiters". Now you just have a little tidying up to do.
o Select A4 to G4 - all the parts of your now disconnected first line.
o Go to Format | Merge Cells (or click the Merge Cells button in the
Formatting toolbar).
o Reply Yes to moving the contents of the cells.
o Now use Merge Cells again to separate the merged cells. Your text
in now in A1.
o Repeat with A2 to B2.
o Repeat with C3 to D3 to reassemble "Dsn Height"
o Select A1 to C3.
o Cut and paste into B1.
o Delete column A.
Here's probably an even easier way:
o In the Text Import dialogue, at "Separated by", tick Comma, Space,
and "Merge delimiters", but also change "From row" to 4.
o Delete column A.
o If required, insert three new rows at the top and paste in or
retype the first three lines manually.
Another idea is to create a new spreadsheet and to use Insert | Sheet
From File... and to open the CSV file that way. Then you can import
the data twice with different Text Import settings and reassemble
different parts together on your real sheet.
I trust this helps.
Brian Barker
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
