Subscribe Bookmark RSS Feed

Text Explorer: How to extracting numerical values followed by the text term?

Anton

Community Member

Joined:

Jul 23, 2017

Dear Community,

 

What would be the best way to extract comma separated numbers “xxxxx, xxxxx, xxxxx” followed by the text term (“Project #1”).

Please see a complete example below:  

 Project #1 @ 15964, 15899, 15839

Project #1 @ 15961, 15897, 15837

Project #1 @ xxxxx,   xxxxx, xxxxx …….., so 15 numerical characters needs to be extracted. The numbers are vary from project to project.  

Ideally, the extracted numerical values need to be saved in the separate columns of main documents.

 

Thank you.

Anton

1 REPLY
Craige_Hales

Staff

Joined:

Mar 21, 2013

if that is a plain text file, try using the import wizard. There are several ways, Here's the prettiest if the file is as regular as it appears to be:

Open(
	"C:\Users\v1\Desktop\xxxx.csv",
	columns(
		Omitted Column( . ),
		New Column( "c000002 2", Numeric, "Continuous", Format( "Best", 12 ) ),
		Omitted Column( . ),
		New Column( "c000004 2", Numeric, "Continuous", Format( "Best", 12 ) ),
		Omitted Column( . ),
		New Column( "c000006", Numeric, "Continuous", Format( "Best", 12 ) ),
		Omitted Column( . ),
		New Column( "c000008", Character, "Nominal" )
	),
	Import Settings(
		Fixed Column Widths( 9, 2, 2, 5, 2, 5, 2, 101 ),
		Strip Quotes( 0 ),
		Use Apostrophe as Quotation Mark( 0 ),
		Use Regional Settings( 0 ),
		Scan Whole File( 1 ),
		Treat empty columns as numeric( 0 ),
		CompressNumericColumns( 0 ),
		CompressCharacterColumns( 0 ),
		CompressAllowListCheck( 0 ),
		Labels( 0 ),
		Column Names Start( 1 ),
		Data Starts( 1 ),
		Lines To Read( "All" ),
		Year Rule( "20xx" )
	)
)

I used "import with preview" and picked the "fixed width columns" option, positioned the dividers with the mouse, went to the next screen and excluded the unwanted columns.

You could also try delimited columns, and choose comma+space (not spaces) and get something similar.

Craige