Google-sheets – Import a CSV file to Google Spreadsheets

csvgoogle sheets

I am trying to upload a CSV file and importing it to Google Spreadsheets. The file contains the following:

hi,hello\r\nwelcome,to\r\npeaceful,world

What I expect in the Google spreadsheet is:

hi,hello
welcome,to
peaceful,world

But it is imported as a single line separated by comma.

In other words, what is Google Spreadsheets’ new line character?

Best Answer

I acknowledge that this does not answer "what is Google Spreadsheets’ new line character?".

However, if not for OP for others wanting to get from a text file containing:

hi,hello\r\nwelcome,to\r\npeaceful,world

to :

WA67626 example

a process of the kind below may suit.

The technique is basically to 'feed' Google Sheets with a replacement for \r\n that it recognises as time to move into the next cell.

The easiest way for this may be to open the text file in Word, replace \r\n with ^p and copy/paste the result into Google Sheets.

However, Word could be an expensive solution if required only for this purpose and there are free alternatives. One of the most popular of which is Notepad++.

Notepad++ recognises \r\n as a CR LF pair (carriage return, line feed) but only when Search Mode is Extended. In that mode Find what: \r\n in the text file will not find the character to be replaced (since as we know, as text, that combination is not CR LF - or none of this would be necessary).

So an extra step is necessary. First, in Search Mode Normal, Find what: \r\n and Replace with: something not elsewhere present (pipe | is often suitable for this).

Another possibility but perhaps only preferable for a limited requirement is to accept entry as hi,hello\r\nwelcome,to\r\npeaceful,world into a single cell (say A1) and then: 

=transpose(split(A1,"\r\n",0))
Related Topic