Notepad++ need to create line breaks with condition variants
Solved
Lyne
-
maclesglyne Posted messages 11 Status Member -
maclesglyne Posted messages 11 Status Member -
Hello everyone,
I have a file with commands, each command starts with " followed by 5 numbers and the quotation mark is closed.
Example of the writing of a command "32564".
I have line feeds at the end of each line but a command can span one, two, or three lines.
So I would like to have a code that allows me to say every time you see 5 numbers in quotation marks, you insert a line break on the previous line.
This would allow me to have a line break between each command and consolidate them in Excel.
Thank you all
Céline
I have a file with commands, each command starts with " followed by 5 numbers and the quotation mark is closed.
Example of the writing of a command "32564".
I have line feeds at the end of each line but a command can span one, two, or three lines.
So I would like to have a code that allows me to say every time you see 5 numbers in quotation marks, you insert a line break on the previous line.
This would allow me to have a line break between each command and consolidate them in Excel.
Thank you all
Céline
5 answers
-
Hello,
try replacing^("[0-9][0-9][0-9][0-9][0-9]")$with\n\1
using the checked regular expression
we could also do it with^("\d{5}")$for 5 digits
-
Thank you very much for the response :):):)
It didn't work
I entered in the search area
^("[0-9][0-9][0-9][0-9][0-9]")$
I entered in the replace area
\n\1
which is the code for a line break :)
Then I entered in the search area
^("\d{5}")$
Same, checking the regular expression ... but it finds no occurrences
Moreover, since there are postal codes inside also surrounded by quotes, it really needs to be at the beginning of the line (a bit like the isnum formula associated to the left in Excel)
I tried on Excel but it's too complex if we don't go through VB ... and VB is far from me :) -
Here is an example before/replacement/after where it works
The ^ means the beginning of a line and the $ the end of a line, so we only take lines with 7 characters "nnnnn"
It might be the line separator of the file, it's cr+lf or lf
We should put a few lines in a response on the forum but better an example file on a deposit site like cjoint.fr -
-
-
-
-
It is surely possible to skip this step. In Excel, we could imagine a column with a formula that determines whether it is a header or detail row
=IF(AND(MID(A1,1,1)=""""",ISNUMBER(VALUE(MID(A1,2,5)) ),
MID(A1,7,1)="""""),"Header","Detail")
-
Hello again,
sorry for the late reply but I had a family issue.
Thank you so much for your help :)
So in Excel, the csv file doesn't have any " " surrounding the order number
so I can maybe tell him if it starts with 5 digits then... but I can't find anything on the forums.-
Your .csv file, if you want to use it with a comma as a field separator, needs to be renamed to .txt
open Excel, file/open the .txt
select file with separator, choose the comma. This way you have columns.
To differentiate the header row and the following rows, you add an empty column in A, and in A2 you put the following formula=IF(ISNUMBER(CNUM(B2));"Header";"******")
which you duplicate across all the following rows.
-
-
Oh yes, great idea... it works! However, I just have one concern with this method.
You see, in Notepad, the advantage I had before was that there was CR + LF when there was a line break after each command and LF when the command was split across multiple lines.
So I used the search and replace function (I copied the LF and replaced it with nothing), which allowed my CSV to automatically format one line per command, and I would go through Excel using the convert function by comma separator, and... I had an usable file in two clicks.
Now with your solution (which is already great, thank you :)), once the lines are differentiated between the header and the data rows, would you have a way for them to line up automatically? Because when I process a full month, I can have 5 to 6000 Excel lines... doing it manually is going to be tough... -
To process your file, you need to proceed step by step:
add an improbable string#XYZ#XYZ#\1
before the order number^("\d{5}").
remove all LF\n
by nothing .
replace#XYZ#XYZ#
with CRLF\r\n
.
