Import EXCEL from .csv with Carriage Returns Solved

Question

Hello,
I have a .csv file with 4 columns (last name, first name, address, phone). Delimiter = quotation mark Separator = comma

The address can span multiple lines, for example:
"Dupont","Jean","Marseille","0145654325"
"Martin","Pierre","15 rue des Fleurs
Paris","01454323454"
"Lefebvre","Marcel","33 rue de la Gare
44000
Nantes","045465675"

I am trying to import this file into EXCEL ensuring that the address is in the third column, and the cell can have multiple lines.
I've tried fiddling with the import settings in EXCEL, but in every case I end up with 6 records. The line break is interpreted as an end of record, even though it is within the quotation marks.

(Same problem under XP-EXCEL 2003 and under VISTA-EXCEL 2007)

Does anyone have a tip?
Thank you in advance
Henri

keke3531 · Accepted Answer

1 - Save the file in XML 2003 format
2 - Open the XML file with Notepad++
3 - Search for "& # 10" (newline character code) and replace it with whatever you want ('#',';')
4 - Save the XML file
5 - Open this file with Excel
6 - Save the file in XLSX or XLS format

Sloubi76 · Answer

Hi Hlifert,

In my opinion, we need to go through an intermediate phase, as your problem is difficult to solve in Excel because substitution possibilities are almost non-existent.
If you can open your .csv file in a word processor (Word), you can replace the carriage returns with " " (nothing), save this file with a TXT extension, and then open it with Excel without difficulty.

See you later
--
We need to bleach the mushrooms!!!

m@rina · Answer

Hello,

Indeed, I confirm that it's necessary to go through an intermediate phase, which can be quite complex if you're not familiar with the ins and outs of Word's Find and Replace! ;))

So, copy your file, open it with Word, and start the Find and Replace:

- In the Replace dialog box, click on the More button
- Check the "Use wildcards" option
- In the Find what box, type: (^13[!"])([0-9])
- In the Replace with box, type: \2
- Replace All

This phase will replace all Returns followed by a digit

Do the same for lowercase and uppercase letters:
(^13[!"])([A-Z])
(^13[!"])([0a-z])

There you go, it should take you less than a minute in total!

Then you should be good to go with Excel.

m@rina

hlifert · Answer

Thank you for your responses.
I had to distinguish between the line breaks corresponding to the record changes and those internal to the data.
I switched to the hex text editor, and I found that the former were CR-LF while the latter were just LF. I managed to replace only the latter.

After that, the import went smoothly.

fred · Answer

well done KEKE3531, simple and effective.  a big thank you.  FRED

Mori · Answer

LibreOffice 3.6

Import EXCEL from .csv with Carriage Returns

6 réponses

Texture issues in enshrouded

Driver his

Peritel to hdmi conversion

How to access the bios on a compaq notebook

Dsi won't turn on anymore

Search for a song with the lyrics

Left mouse click, touchpad click inactive

Codes stronghold crusader

Stuck computer system repair

Disable write protection mp3 player