15

I have a large CSV file that I need to remove the first column of data. I cannot open it in Excel because Excel converts some of the values in the columns to scientific numbers.

I am using Notepad++, and I am trying to string the first column from the file EXE,

1,Value1,value2,value3,value4,value5
3445,Value1,value2,value3,value4,value5
12345,Value1,value2,value3,value4,value5
1234,Value1,value2,value3,value4,value5
11,Value1,value2,value3,value4,value5

to look like

Value1,value2,value3,value4,value5
Value1,value2,value3,value4,value5
Value1,value2,value3,value4,value5
Value1,value2,value3,value4,value5
Value1,value2,value3,value4,value5
0

8 Answers 8

15

Notepad++'s search and replace supports regular expressions (regex) which can be easily used for this.

Use the following regex to search for:

^[^,]+,(.+)

This matches the start of the line followed by as much characters as possible not being a comma followed by a comma followed by the rest of the line. The rest of the line is grouped as first submatch.

Globally replace with this:

\1

This denotes the first submatch (rest of the line). By that each line is replaced by everything after the first column and comma.

After I found the above way to do it in a single global replace (and updated my reply accordingly), I noticed that this reply is basically identical but also gives a comprehensive explanation of the regex used.


Note: The shorter regex ^[^,]+, can't be used for global replace with an empty string since Notepad++ will then replace all columns except the last: After replacing the first column, the second column (which now is the first and matches exactly the regex) will be replaced, then the third, and so on. However, the shorter regex works perfectly with other editors (e.g. with PSPad or vim).

10
  • This is the way to go here. If the OP already has N++ this is the quickest way. I do this a lot with PSPad (which could do this in one go, btw). Also check out how the regex works: rubular.com/r/OiehkBT0vA
    – simbabque
    Aug 3, 2012 at 12:21
  • Notepad++ doesn't process the input line by line, but character by character. That has some neat advantages (like multi-line patterns).
    – Dennis
    Aug 3, 2012 at 12:32
  • +1 for the edit. Sadly, your answer is community wiki now.
    – Dennis
    Aug 3, 2012 at 13:10
  • @Dennis Yes, I edited too often since I wasn't aware of the 10-edits limit.
    – speakr
    Aug 3, 2012 at 13:32
  • Why not ^[^,]+, and replace with empty?
    – knittl
    Aug 3, 2012 at 16:48
10

Press Ctrl + H and perform the following replace:

Find what:          .*?,(.*)
Replace with:       \1
Wrap around:        checked
Regular expression: selected
. matches newline:  unchecked

Now press Alt + A to replace all occurrences.

How it works

  • The regular expression .*?,(.*) matches an entire line:

    • .*?, matches everything before the first comma, including the comma itself.

      .* means any number of occurrences of any character, and the question mark makes the quantifier lazy, i.e., it matches as few characters as possible.

    • (.*) matches everything after the first comma.

      Enclosing .* in parentheses converts it into a subpattern, so the mast can be accessed in the replace field.

  • \1 represents the first submatch (match for (.*)).

    As a result, Notepad++ replaces the line by everything that follows the first comma.

1
  • Just got the same idea after noticing that ^[^,]+, globally replaced with an empty string won't work in Notepad++. (+1)
    – speakr
    Aug 3, 2012 at 12:57
6

In Windows, you can do it as follows.

for /F "tokens=2,3,4,5,6 delims=," %i in (Input.csv) do @echo %i,%j,%k,%l,%m  >> output.csv

I assumed that you have only 6 columns. If you have many more columns, try experimenting with * in tokens field. Idea is taken from Windows for command

1
  • 2
    for arbitrary number of columns, use this: for /F "tokens=1* delims=," %i in (Input.csv) do @echo %j >> output.csv
    – SeanC
    Aug 3, 2012 at 16:11
3

Assuming you have a linux system or some unix style environment (I like gow, or you can snarf the utilities off unixutils) I believe running the file through cut -d , -f2-6 should do the trick - it should, if i recall correctly will do the trick - -d sets the deliminator, and f2-6 prints out the second to 6th character.

cat input.csv | cut -d , -f2-6 > output.csv would do the trick taking input file and kicking out an output file. Its not using notepad, but its fast and really simple.

2
  • Thanks, I just clicked on the link and I got a 403 error?
    – MikeD
    Aug 3, 2012 at 11:58
  • both links work for me - which is wierd. I usually find gow by googling for it - its on a github repo belonging to bmatzelle. Cygwin might also be an option, but its an overkill for this sorta thing
    – Journeyman Geek
    Aug 3, 2012 at 12:00
2

You should be able to load the CSV into excel and have it treat numbers as text (preventing it from converting to scientific numbers).

  1. Open Excel
  2. Data Tab
  3. From Text
  4. Choose Delimited
  5. Choose Other: ","
  6. For all columns select them in the Data Preview window, and choose Text
  7. Remove your column
  8. Save as CSV
5
  • Editing and saving CSV files in Excel often breaks numbers like EAN codes and US-style floats in European Excel. Even if you set up everything when importing, it happens to eat up some things. I cannot recommend it, though it would probably work. In a productive environment, I'll advice against it.
    – simbabque
    Aug 3, 2012 at 12:15
  • This worked great! Thnak you
    – MikeD
    Aug 3, 2012 at 13:00
  • @simbabque I would say that's slighty unfair, I have used it successfully in production environments for large datasets which required manipulation - admittedly at times it was a nightmare. Excel does have a habit of altering data in unexpected ways, but I wouldn't say this risk was especially greater than other approaches.
    – James Wood
    Aug 3, 2012 at 17:13
  • I use it on occasion as well, but most of these times I don't like to do it. It's often a lot faster to use a text editor that supports regex search & replace if one knows how to handle it. No offense, though, as your answer was clear and concise.
    – simbabque
    Aug 5, 2012 at 15:08
  • o i wasnt taking offence :D
    – James Wood
    Aug 5, 2012 at 16:11
1

Notepad++ has built-in column editing and Line Up by (,) command (TextFX plugin) giving a graphical point-and-click solution that is likely to be first time right. This avoids having to use regular expressions or shell programming, both of which typically require some debugging till they do exactly what you want and nothing more.

Starting point: CSV file with columns misaligned, so not easy to edit by column...

    1,Value1,value2,value3,value4,value5
    3445,Value1,value2,value3,value4,value5
    12345,Value1,value2,value3,value4,value5
    1234,Value1,value2,value3,value4,value5
    11,Value1,value2,value3,value4,value5
    ...

Procedure:

  1. Select the rows of interest -- perhaps the entire file.

  2. Use menu command TextFX > TextFX Edit > Line up Multiple Lines by Comma (,) This lines up all of the rows by column and makes column editing easy.

    1    ,Value1,value2,value3,value4,value5
    3445 ,Value1,value2,value3,value4,value5
    12345,Value1,value2,value3,value4,value5
    1234 ,Value1,value2,value3,value4,value5
    11   ,Value1,value2,value3,value4,value5
    
  3. Enter Column Mode at the column after the first comma (,) For a small number of rows, use the keyboard commands: Alt+Shift+DownArrow.
    If needing to work on many rows (large file), use Alt+mouse and click the desired column of the first line, then move to the last line of the file and Alt+Shift+click the same column position. This activates column mode on the ENTIRE file -- you should see a vertical line behind all the commas.

  4. Delete in column mode removes an entire column of characters. So you can delete everything prior to the first comma in all of the rows all at once to get:

    Value1,value2,value3,value4,value5
    Value1,value2,value3,value4,value5
    Value1,value2,value3,value4,value5
    Value1,value2,value3,value4,value5
    Value1,value2,value3,value4,value5
    

Done!

Remark on this solution compared with the various others suggested so far:

Column Mode editing is quite powerful and, when combined with TextFX plugin options, applies to a variety of situations in a point-and-quick way.

For example, if you decided to delete the 2nd column, or the Nth column quickly, this approach works with almost no modification.

Regular expressions, shell scripts, on the other hand, will accomplish the goal, yes, but in complicated problems you'll spend more time "de-bugging" your syntax.

Notepad++'s versatile built-in functionality is one of its main strengths: you get quite a bit of power without having to resort to "programming".

0

If you familiar with the Python console plugin for Notepad++, I can advice you another method. You need to add your text on the Notepad++ document and execute the next script in the console:

res = []
data = editor.getText().split('\r\n')
for i in data:
    res.append(i.split(',')[1:])

for i in res:
    editor.addText(', '.join(i)+'\r\n')

This script added the result text to your current Notepad++ document.

-1

Run vim (in the command mode, if not press Escape).

Type the following command to map the action of removing first column to the q key:

:map q 0df,j0

Which means:

  • 0 - go to the beginning of the line
  • df, - delete everything till , character (including comma),
  • j - go down one line
  • 0 - go to the beginning of the line

Then apply this action to all lines:

:0,$ normal q

Which means, run the action which is assigned to the key q from line the beginning (0) to the end ($).

2
  • Why not just doing a single global replace with :%s/^[^,]\+,//g?
    – speakr
    Aug 3, 2012 at 12:02
  • You could as well, this one is easy to use and to understand rather than regex:) Usually I'm always confused which character I've to escape, so I'm ending in typing the same regex many times.
    – kenorb
    Aug 3, 2012 at 12:07

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .