1

So I am trying to ultimately make a table out of a whole bunch of text, but it's giving me a headache. Basically here is an idea of what is going on:

    matchingvariablelengthupccode1 productdescription1 value value value value value value value value matchingvariablelengthupccode1

matchingvariablelengthupccode2 product
description2 value value value value value value value value value matchingvariablelengthupccode2

And the product description sometimes randomly has line breaks in it. Also the upc at the end and beginning match each other, but are unique to each product.

Even after I deal with this I will have to figure out how to deal with the product descriptions having spaces but supposed to be one column when the values and upc codes are space delimited already. Also the product descriptions sometimes start with a number to make things more difficult.

Trying to use regex in notepad++ but it's a very complicated issue.

  • The first space is after the UPC code and before the last space also? – datatoo Feb 9 '17 at 23:08
  • Are you able to define rules for matchingvariablelengthupccode? Does it consist of something like 7-15 digits (and only digits)? – miroxlav Feb 10 '17 at 13:53
  • Unfortunately no. It always starts with a number, but sometimes it ends in letters and also has a variable length. Some of the upcs only have like 2 digits and some have up to 8. To make it even more complicated some of the product descriptions have references to other upcs, for example 12345 container description length width height cost value value 12345 12345-a container lid fits 12345 description is longer so more line breaks length width height cost value value 12345-a – Areck Degeneffe Feb 10 '17 at 15:40
0

There is a way without programming.

Part 1: Put each record to single line by replacing line breaks in item descriptions by spaces.

Part 2: Remove trailing UPC codes.

Part 3: Convert first N space-delimited words into colums, rest (>N) into last column.


Preparation: Use Find command (set Search Mode to Normal) to find unique character not occuring in the file. I will use #. If you found another one, please adjust below steps accordingly.

Part 1:

Step 1:

Use regex find/replace:

Find what: (\R)(\d+\l* )

Replace with: #\1 (note our special character)

Search Mode: Regular Expression

". matches newline": Unchecked

→ Every UPC code has prefix # at this point at start of the line.

Step 2:

Find what: \R

Replace with: (single space)

Search Mode: Regular Expression

". matches newline": Unchecked

→ All text is now a single line.

Step 3:

Find what: # (note our special character)

Replace with: \r\n

Search Mode: Regular Expression

". matches newline": Unchecked

→ All records are now on their lines. Correct exceptions manually, if there are any.

Part 2:

Step 1:

Use regex find/replace:

Find what: ^(.*) (.*)$

Replace with: \1

Search Mode: Regular Expression

". matches newline": Unchecked

→ Last word from each record (supposedly it was UPC code) was removed.

Part 3:

Step 1:

Use regex find/replace:

Find what: ^(.*?) (there is single space after ))

Replace with: \1# (note our special character)

Search Mode: Regular Expression

". matches newline": Unchecked

→ First space on row is now #.

Step 2:

Repeat Step 1 until all spaces between colums (but not in product descriptions) were turned into #'s.

→ Spaces are now only in product descriptions. There is # between all columns.

Step 3:

Paste all records into Microsoft Excel and use Text Import Wizard to import your values into table.

Data type: Delimited (not Fixed width)

Delimiter: Other: # (set all other delimiter choices unchecked!)

→ You have your data in columns in Excel.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.