我有一个包含很多行的数据文件，并且字段列表可能会有所不同。以下是示例行格式。每个字段由以下分隔@@@：
runAs="X094174"@@@format="excel2007"@@@path="/Path1"@@@name="X143122"@@@name="X182881"@@@name="X094174"@@@address="[email protected]"@@@address="[email protected]"@@@AgentLoc="/loc1"
我想获取这种格式的数据，更像是数据库表 - 列/行格式。
runAs format path AgentLoc name address
X094174 excel2007 /Path1 /loc1 X143122 [email protected]
X094174 excel2007 /Path1 /loc1 X182881 [email protected]
X094174 excel2007 /Path1 /loc1 X094174
您能否建议我如何使用文件读取循环和awk.
如果很容易产生以下格式的数据，也可以
runAs format path AgentLoc name address
X094174 excel2007 /Path1 /loc1 X143122
X094174 excel2007 /Path1 /loc1 X182881
X094174 excel2007 /Path1 /loc1 X094174
X094174 excel2007 /Path1 /loc1 [email protected]
X094174 excel2007 /Path1 /loc1 [email protected]
答案1
这awk生成所需的表格。
$ cat dat
runAs="X094174"@@@format="excel2007"@@@path="/Path1"@@@name="X143122"@@@name="X182881"@@@name="X094174"@@@address="[email protected]"@@@address="[email protected]"@@@AgentLoc="/loc1"
首先，将数据放入每行一个条目的形式：
$ awk -F '@@@' '{ for(i=1;i<=NF;i++){ print $i } }' dat > tmp.dat
然后，生成表并清理行结尾：
$ awk -F '=' '{
head[$1]++;
dat[$1,head[$1]]=$2
} END{
max=0;
for(i in head){
printf i"\t"
}
print "";
for(i in dat){
split(i, arr_i, SUBSEP);
if(arr_i[2]>max){
max=arr_i[2]
}
}
for(j=1;j<=max;j++){
for(i in head){
if(head[i]==1){
printf dat[i,1]"\t"
}else{
printf dat[i,j]"\t"
}
}
print ""
}
}' tmp.dat | awk -F '\t' '{ for(i=1;i<NF;i++){ printf $i"\t" } print $NF }' > dat.xls
$ cat dat.xls
runAs format address AgentLoc name path
"X094174" "excel2007" "[email protected]" "/loc1" "X143122" "/Path1"
"X094174" "excel2007" "[email protected]" "/loc1" "X182881" "/Path1"
"X094174" "excel2007" "/loc1" "X094174" "/Path1"
将eg导入excel后，选择TAB停止分隔符：
请记住，值的出现顺序决定了表中各行的关联。
tmp.dat通过使用管道，上述操作可以一步完成，从而避免临时文件。
答案2
从 Java 11 开始有这个直接从文本文件运行 Java 代码的巧妙功能，那么为什么不使用 Java 来解决这个问题呢？
假设您安装了 Java 11，请将下面的代码保存为ConvertToTable.java，然后在终端会话中运行它，如下所示：
java ConvertToTable.java < /path/to/input.txt | tee /path/to/output.txt
import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStreamReader;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.util.stream.Collectors;
/**
* <p>
* Reads STDIN, converts it to the desired table representation, and writes the result to STDOUT.
* </p>
* <p>
* Assumes that the incoming data uses the system character set and the system line separator.
* </p>
* <p>
* Requires Java 11. Example invocation in Bash:
* </p>
*
* <pre>
* java ConvertToTable.java < /path/to/input.txt | tee /path/to/output.txt
* </pre>
*
* @author eomanis
*/
public class ConvertToTable {
private static final Pattern FIELD_SEPARATOR_INPUT = Pattern.compile( "@@@", Pattern.LITERAL );
private static final Pattern KEY_VALUE_SEPARATOR = Pattern.compile( "=", Pattern.LITERAL );
private static final Pattern VALUE_IN_DOUBLE_QUOTES = Pattern.compile( "^\"(.*)\"$" ); // Captures the value in capturing group 1
private static final String FIELD_SEPARATOR_OUTPUT = "\t";
public static void main( String[] args ) {
Matcher matcherValueInDoubleQuotes = VALUE_IN_DOUBLE_QUOTES.matcher( "" );
Map<String, List<String>> keysAndValues = new LinkedHashMap<>(); // A map that maps a key to a list of values
boolean firstLine = true;
String line;
String[] keyAndValue;
String key;
String value;
int outputLinesCount;
try (BufferedReader reader = new BufferedReader( new InputStreamReader( System.in ) )) {
// Read and convert the incoming data, one line at a time
while ((line = reader.readLine()) != null) { // For each line...
// Discard the previous line's data
keysAndValues.values().stream().forEach( List::clear );
// Collect the line's keys and values into the map
for (String field : FIELD_SEPARATOR_INPUT.split( line )) { // For each key=value in the text line...
keyAndValue = KEY_VALUE_SEPARATOR.split( field, 2 ); // Split key=value into key and value
key = keyAndValue[0];
value = keyAndValue[1];
// Strip the double quotes from the value
if (matcherValueInDoubleQuotes.reset( value ).matches()) {
value = matcherValueInDoubleQuotes.group( 1 );
}
// Add the value to the key's list of values
if (!keysAndValues.containsKey( key )) { // If required, create a new empty list in the map for the key
keysAndValues.put( key, new ArrayList<>() );
}
keysAndValues.get( key ).add( value );
}
// First line: Generate and write the column headers (assume that the first line contains all possible keys)
if (firstLine) {
firstLine = false;
String columnHeaders = keysAndValues.keySet().stream().collect( Collectors.joining( FIELD_SEPARATOR_OUTPUT ) );
System.out.println( columnHeaders );
}
// Figure out how many output lines we will be writing for the single input line
outputLinesCount = keysAndValues.values().stream().mapToInt( List::size ).max().getAsInt();
// Write the output line(s)
for (int index = 0; index < outputLinesCount; index++) {
int indexFinal = index;
String outputLine = keysAndValues.values().stream() //
.map( list -> getValue( indexFinal, list ) ) //
.collect( Collectors.joining( FIELD_SEPARATOR_OUTPUT ) );
System.out.println( outputLine );
}
}
} catch (IOException e) {
throw new RuntimeException( e );
}
}
/**
* @return The value for the given index, with certain workarounds
*/
private static String getValue( int index, List<String> values ) {
if (values.isEmpty()) {
// The text line did not contain the key at all
return "";
} else if (values.size() == 1) {
// Value of a key that occurred exactly once in the text line: These are repeated on all output rows
return values.get( 0 );
} else {
// Value of a key that occurred multiple times in the text line: Only print them for their respective output row
return (index < values.size()) ? values.get( index ) : "";
}
}
}