This page looks best with JavaScript enabled

Data Processing with sed

 ·  ☕ 3 min read  ·  ✍️ Syed Dawood

Input and output

I have received a file which was roughly SQL. Here is the sample

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
CREATE TABLE DimState 				
				
StateSK 		int 	NOT NULL	
StateBK 		int 	NOT NULL	
StateCode 		varchar(10) 	NULL	
StateDescription 		nvarchar(50) 	NULL	
				
				
CREATE TABLE DimLeadType 				
				
LeadTypeSK 		int 	NOT NULL	
LeadTypeBK 		int 	NOT NULL	
LeadTypeCode 		nvarchar(255) 	NOT NULL	
LeadTypeDescription 		nvarchar(255) 	NOT NULL	
LeadTypeCRMBK 		int 	NULL	
LeadTypeActiveStatus 		nvarchar(10) 	NULL	
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
CREATE TABLE DimState (
StateSK int NOT NULL,
StateBK int NOT NULL,
StateCode varchar(10) NULL,
StateDescription nvarchar(50) NULL,
);
CREATE TABLE DimLeadType (
LeadTypeSK int NOT NULL,
LeadTypeBK int NOT NULL,
LeadTypeCode nvarchar(255) NOT NULL,
LeadTypeDescription nvarchar(255) NOT NULL,
LeadTypeCRMBK int NULL,
LeadTypeActiveStatus nvarchar(10) NULL,
);

The Algorithm

  1. Deleting tabs
  2. Deleting Empty lines
  3. Opening brace
  4. Closing brace
  5. Comma,
  6. Clean up

Implementation

Deleting tabs

Tab character are usually not displayed in most non-developer focused text-editors. they fall under hidden characters category, this can be removed using s command.

1
sed 's/\t//g' source.txt > stage_1.txt
Deleting Empty lines

In, The next stage of our data processing pipeline, we will eliminate all empty line from the file generated in step 1. This is achieved with d command.

1
sed '/^$/d' stage_1.txt > stage_2.txt
Opening brace

Line starting with CREATE should have an opening brace at the end the line.

1
sed '/CREATE/s/$/(/' stage_2.txt > stage_3.txt
Closing brace

This was an interesting one, The goal was to mark end of the table definition ie. ); .This was solved by inserting line before line containing CREATE. this uses i command

1
sed '/^CREATE/i);' stage_3.txt > stage_4.txt
Comma,

For the output file to be valid SQL, every column definition must be separated by a comma. The below command does a reverse match, So it matches all lines not starting with CREATE and appends a comma at the end of the line.

1
sed '/CREATE/!s/$/,/' stage_4.txt > stage_5.txt
Clean up

This command is handling the undesired byproduct created in above step. The reverse match in previous step matched all lines including the table definition endings. This removes that extra comma

1
sed 's/);,/);/g' stage_5.txt > output.txt

Complete program

1
2
3
4
5
6
7
sed 's/\t//g' source.txt > stage_1.txt
sed '/^$/d' stage_1.txt > stage_2.txt
sed '/CREATE/s/$/(/' stage_2.txt > stage_3.txt
sed '/^CREATE/i);' stage_3.txt > stage_4.txt
sed '/CREATE/!s/$/,/' stage_4.txt > stage_5.txt
sed 's/);,/);/g' stage_5.txt > output.txt
# rm stage_*

Conclusion

Did I get this working in one attempt? No. It was a series of trial and errors involving refering official manual and some quick searches on the web.

Here are the key takeaways.

  • Breaking down problem into small sub-problem will always help
  • Avoid using -i flag with sed, this performs in updates to the file, which may not be desirable depending on the end goal.

References

Share on

ALLSYED
WRITTEN BY
Syed Dawood
< frontend | backend | fullstack > Developer