Importing CSV and Posting questions on forum

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

Importing CSV and Posting questions on forum

Ivo van Emmerik
  1. Importing CSV

All the information is in the two documents

 

 

 

 

 

 

 

 

 

 

 

 

 

2) Posting questions on forum

 

Sorry I want to ask the forum a question! But I can not see a button to start a new message, I logged in there is no clear indication where to press, if I am blocked because I am new than tell me, this is very frustrating!

 

Sent from Mail for Windows 10

 



---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

ptlist.csv (9K) Download Attachment
Importing CSV.odt (110K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: Importing CSV and Posting questions on forum

David Robley


On 02/20/2018 11:14 AM, Ivo van Emmerik wrote:
>
> 2) Posting questions on forum
>
> Sorry I want to ask the forum a question! But I can not see a button
> to start a new message, I logged in there is no clear indication where
> to press, if I am blocked because I am new than tell me, this is very
> frustrating!
>
>
Go to the forum and log in. Click on the section of the forum in which
you want to post a question, e.g. Calc. Once in the selected section,
look for an icon New Topic in the upper left hand side of the page.


Cheers
--
David Robley

There's no life after death. Just Los Angeles.
 

Reply | Threaded
Open this post in threaded view
|

Re: Importing CSV and Posting questions on forum

Martin Groenescheij
In reply to this post by Ivo van Emmerik


On 20/02/18 11:44 AM, Ivo van Emmerik wrote:
>
>  1. Importing CSV
>
> All the information is in the two documents
>

Delete the first two lines from the CSV file to get the columns correct
remember that each spreadsheet looks at the first line to
find what columns to use, then open it in Calc.
Because there are a lot of spaces in front of the values it is interpret
as text not as numbers, the way OpenOffice handles text
it puts the ` in front of the number.
If you remove all the spaces it will load correctly as numbers. To save
you a lot of time there is an option with importing CSV files
tick the Detect special numbers and it will handle the values as numbers
instead of text.

> 2) Posting questions on forum
>
> Sorry I want to ask the forum a question! But I can not see a button
> to start a new message, I logged in there is no clear indication where
> to press, if I am blocked because I am new than tell me, this is very
> frustrating!
>
> Sent from Mail <https://go.microsoft.com/fwlink/?LinkId=550986> for
> Windows 10
>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [hidden email]
> For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Importing CSV and Posting questions on forum

David Robley
In reply to this post by Ivo van Emmerik


On 02/20/2018 11:14 AM, Ivo van Emmerik wrote:
>
>  1. Importing CSV
>
> All the information is in the two documents
>

On the Text Import dialogue box, make sure Detect special numbers is
checked.

Cheers
--
David Robley

To err is human. To really screw up it takes a computer!
 

Reply | Threaded
Open this post in threaded view
|

Re: Importing CSV [...]

Brian Barker
In reply to this post by Ivo van Emmerik
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: [hidden email]
For additional commands, e-mail: [hidden email]