regex for copy pasted mysql table data

How could I format this so it fits a csv format? I accidentally deleted my table and now I need to export this data.

我怎么能这样格式化它适合csv格式？我不小心删除了我的表，现在我需要导出这些数据。

+-----+---------------------------------+-------------+-------------------+
| id  | email                           | firstName   | lastName          |
+-----+---------------------------------+-------------+-------------------+
|   6 | [e-mail]                 | Person1     | Person1           |
|   7 | [e-mail]                | Person2     | Person2           |
|   8 | [e-mail]                | Person3     | Person3           |
+-----+---------------------------------+-------------+-------------------+

This is a text file I made before, so how could I remove the lines so that it goes like:

这是我之前制作的文本文件，所以我怎么能删除这些行，如下所示：

"6","[e-mail]","Person1","Person1"

2 个解决方案

#1

You can do it manually in Excel (open file, Data -> Text to Columns, Fixed width.

您可以在Excel中手动执行此操作（打开文件，数据 - >文本到列，固定宽度。

Regex-wise, 2 options:

正则表达式，2个选项：

• replace stuff:

  替换东西：

  s/^\|\s+/"/;
  s/\s+\|\s+/","/g;
  s/\s*\|$/"/;
  

• match stuff:

  比赛的东西：

  s/^\|\s+(\d+)\s+\|\s+(\S+)\s+\|\s+(\S+)\s+\|\s+(\S+)\s+\|$/"$1","$2","$3,"$4"/
  

Of course, the exact syntax depends on the language you use.

当然，确切的语法取决于您使用的语言。

#2

Answer in Javascript - RegEx(No language is mentioned, picked JS by looking at your profile)

用Javascript答案 - RegEx（没有提到语言，通过查看你的个人资料选择JS）

[\w\s.@]+(?=\|)

Demo

演示

JsFiddle

的jsfiddle

For the given case, only \w, \s, ., @ are allowed. You can allow more characters as per your convenience

对于给定的情况，只允许\ w，\ s，。，@。您可以根据自己的方便允许更多角色

For Multiline Text - TextArea

对于多行文本 - TextArea

var str = $('#regex_string').val()

or For Single Line - Enter or \n removed to make it a single line for storing it in a variable

或对于单行 - 输入或\ n删除使其成为单行以将其存储在变量中

var str = '+-----+---------------------------------+-------------+-------------------+| id  | email                           | firstName   | lastName          |+-----+---------------------------------+-------------+-------------------+|   6 | [e-mail]                 | Person1     | Person1           ||   7 | [e-mail]                | Person2     | Person2           ||   8 | [e-mail]                | Person3     | Person3           |+-----+---------------------------------+-------------+-------------------+'

var extractValidString = str.match(/[\w\s.@]+(?=\|)/g).join("").trim().split(/\s+/)
//output ["id", "email", "firstName", "lastName", "6", "[e-mail]", "Person1", "Person1", "7", "[e-mail]", "Person2", "Person2", "8", "[e-mail]", "Person3", "Person3"]

//to remove Headers - id, email, firstName, lastName
extractValidString.splice(0,4)

//extract all rows
var arr = [];
while(extractValidString.length>0) {
    //Extract set of 4 elements as you have only 4 columns
    //change second no to allow other columns as per your requirement
    arr.push(extractValidString.splice(0,4))
}

//output
[Array[4], Array[4], Array[4]]
0: Array[4]
    0: "6"
    1: "[e-mail]"
    2: "Person1"
    3: "Person1"
    length: 4
    __proto__: Array[0]
1: Array[4]
    0: "7"
    1: "[e-mail]"
    2: "Person2"
    3: "Person2"
    length: 4
    __proto__: Array[0]
2: Array[4]
    0: "8"
    1: "[e-mail]"
    2: "Person3"
    3: "Person3"
    length: 4
    __proto__: Array[0]
length: 3
__proto__: Array[0]