Notepad++ How do I add a comma at a specific column position?

Question

I have pretty large ASCII file (1.7mil rows) that I need to insert commas into at specific column positions. I am doing this because I am trying to convert the file to csv so I can import it into mysql. Unless there is a better approach (no doubt), what I am trying to do is insert comma at the specific column positions where I know fields end. This is not a job for column mode as dragging through 1.7mil rows would be insane.

I have tried this solution - How do I add a character at a specific postion in a string?

but it did not work. Does anyone have a suggestion?

Thanks!

How many total columns will there be? – SQLChao Dec 08 '15 at 16:36 — SQLChao, Dec 08 '15 at 16:36

score 4 · Accepted Answer · answered Dec 08 '15 at 16:41

4

To insert after the 4th character on each line:

Find:    ^(.{4})
Replace: \1,

(Ticking Regular Expression in the find/replace dialog)

answered Dec 08 '15 at 16:41

Alex K.

171,639
30
264
288

score 1 · Answer 2 · answered Dec 08 '15 at 17:04

Another way to do it is to import the txt file you have into mysql as a table with a single column. Then split the string using SUBSTRING()

SELECT
    SUBSTRING(col, 1, 8) AS Column1
  , SUBSTRING(col, 9, 8) AS Column2
  , SUBSTRING(col, 17, 16) AS Column3
FROM table

You can modify this query to do SELECT INTO or INSERT INTO. Depends on how you want to get it to the final table.

I've had to do it this way before because it was a recurring process and needed to be automated.

Thanks for the reply JChao. Going with Alexs solution but will put yours in my files. — RushVan, Dec 08 '15 at 17:22

Notepad++ How do I add a comma at a specific column position?

2 Answers2