2

I have a huge file (1400 pages, a single string of text) I am trying to make readable in Excel. The file contains comma seperated values, getting this into Excel is normally not a problem, except that the information should be on multiple lines in Excel, and not all on the same line as it is in the document.

What should be one line of information is seperated by 115 commas.

My plan was to use Notepad++ to count 115 commas, make a new line, count 115 commas, make a new line until it has gone through the whole document. After this operation it should be easy to get it structured in Excel. I have however been struggling putting the needed expressions together.

If there is another more sensible way to do this I am all ears, it might be I'm on the wrong path. I've been trying to use the find/replace tool (CTRL+H) in Notepad++.

I have been trying to use "find what":

(,*?)\,{115}

The problem is that this is looking for 115 consecutive commas, but I need it to count the 115 first commas in a line of text, and then make a parapraph/new line and do it again. Any help/pointers would be much appreciated. I've been spending quite some time trying to find the answers.

1
  • Ctrl+H
  • Find what: (?:.+?,){115}\K
  • Replace with: \n or \r\n
  • check Wrap around
  • check Regular expression
  • NCHECK . matches newline
  • Replace all

Explanation:

(?:         # start non capture group
    .+?     # 1 or more any character, not greedy
    ,       # a comma
){115}      # end group, must appear 115 times
\K          # forget all we have seen until this position

Replacement:

\n          # a line feed, change it to \r\n for windows files

This will keep the comma at the end of each line. If you want to remove it, use:

(?:.+?,){114}.+?\K
1
  • Thank you very much for the detailed reply, will give it a try tomorrow and give feedback. Best regards, John. – John Apr 4 '19 at 15:18
0

put your text in the inputstring variable and run in python. Change numberofcolums variable based on how many colums you want. If you don't have python installed you can use online tool like this one

inputstring = "one, two, three, four,five,six,seven,eight,ten,etc,,,2342354,2,1,,,1,3,,,,"
outputstring = ""

numberofcolums = 115
numberofcommas = 0

print(inputstring)
print(inputstring[3]!=",")
for character in inputstring:
    if(character != ","):
        outputstring += character
    elif(numberofcommas == numberofcolums - 1):
        outputstring += ",\
        numberofcommas = 0
    else:
        outputstring += character
        numberofcommas += 1

print(outputstring)
-1

Ok, The best thing to do is make a script that solves this issue,

If I understand correctly your issue, you need to split a string.

I would do this with pythons split() function.

after you can make Python write a file, and make a new line for each part of your array you got from the split() function.

(I'm not the best at explaining, if you need help I can make the script for you, ask it shouldn't take long at all, just ask)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.