Community

Hi Guys

I'm new to this forum and inexperienced when it comes to macros so please bare with me if I don't explain myself very well.

I have a spreadsheet that is in csv format. I need to convert it to sql format.

The csv has 2 types of row, unpopulated and populated (example below)

1,Sunday,00:00:00,,,,
17,Sunday,16:00:00,19:00:00,Sunday Afternon Show,Mark & Aly,Rubbish

I need them to look like this..

(1,'Sunday','00:00:00',null,null,null,null),
(17,'Sunday','16:00:00','19:00:00','Sunday Afternon Show','Mark & Aly','Rubbish'),

However, I can only get part way. I do the following.

1 Add ( to start of each line. Replace ^ with (
2 Add ' around text. Replace , with ','
3 Replace 00','','','',' with 00',null,null,null,null),

With this I get the following..

(1','Sunday','00:00:00',null,null,null,null),
(17','Sunday','16:00:00','19:00:00','Sunday Afternon Show','Mark & Aly','Rubbish

From this you will see that I have an unwanted ' after the first number. Without doing each of the 168 line individually I cannot get rid of it.

My other problem is putting ), on the end of the populated lines. I can exclude the ), from the unpopulated lines in stage 3 above if this makes it easier.

Thank you in advance for any help you can give.

Ray

Use Posix regular expression syntax:

Configure | Preferences | Editor

[X] Use POSIX regular expression syntax

Here's one way to do it in three steps.

Enclose each line in ( ... ),

Find what: .+
Replace with: (\0),

[X] Regular expression

Replace All

Enclose each non-numeric field in ' ... '

Find what: [^,()]*[^0-9,()][^,()]*
Replace with: '\0'

[X] Regular expression

Replace All

Put in the nulls

Find what: ,,,,
Replace with: ,null,null,null,null

Replace All

Thank you Ben, perfect reply.

Regards
Ray

Enclose each non-numeric field in ' ... '

Find what: [^,()]*[^0-9,()][^,()]*
Replace with: '\0'

[X] Regular expression

Replace All

can someone explain how this works?

Thanks in advance,
fd98701

An expression in square brackets, [...], matches exactly one character (in TextPad it never matches a newline). Normally the expression matches any one of the characters within the square brackets. For example,
[abc] matches an a, a b or a c.

A bracketed expression may contain ranges. For example,
[0-9] matches any single digit.

If the first character within the square brackets is a ^ the expression matches exactly one character that is not one of the other characters within the square brackets. So
[^abc] matches any one character that isn't an a, a b or a c, and
[^,()] matches any one character that isn't a comma or parenthesis, and
[^0-9,()] matches any one character that isn't a digit, comma or parenthesis.

If E is a regex, E* matches zero or more occurrences of what E matches. So
[^,()]* matches any text within a line that doesn't contain a comma or parenthesis.

Thus [^,()]*[^0-9,()][^,()]* matches
[^,()]* any text not containing commas or parentheses, followed by
[^0-9,()] any single character that is not a digit, comma or parenthesis, followed by
[^,()]* any text not containing commas or parentheses.

That is, it matches fields that contain at least one non-numeric character. That is, it matches non-numeric fields.

The replacement '\0' is
' a single quote, followed by
\0 all of the text that was matched by the regex, followed by
' a single quote, followed by

TextPad's help on its regular expressions is rather brief. Look under
Reference Information | Regular Expressions,
Reference Information | Replacement Expressions and
How to... | Find and Replace Text | Use Regular Expressions.

There are many regular expression tutorials on the web, and you will find recommendations for some of them if you search this forum.

A standard reference for regular expressions is

Friedl, Jeffrey E F
Mastering Regular Expressions, 3rd ed
O'Reilly, 2006
ISBN: 0-596-52812-4
http://regex.info/

But be aware that the regular expression recogniser used by TextPad is very weak compared with modern tools. So you may get frustrated if you discover a handy trick that works elsewhere but doesn't work in TextPad.

Thanks a lot Ben. It was really helpful.