Need to extract csv from text file

grubbyjeans

I need to extract comma delimited data from text files that were created by exporting from Thunderbird. There is no info in the msg. body other than that which I need. The problem is that ALL email header info is included in the exported files.

Can this be done in Notepad++
Thanks

Claudia Frank

Hello @grubbyjeans,

I think so. If you have a csv export I assume that it has a fixed length
meaning the number of commas per line is always the same.
Then you could use find/replace dialog (regular expressions) to get the
data you want.

Cheers
Claudia

grubbyjeans

Thanks Claudia
The emails come singularly each time a registrant completes an online form. The body of the message is formatted as below:
field1,data1
field2,data2
etc.

I have the ability to select multiple msgs and export them as a csv file. The problem is that all the header info is also exported for each message. That places 6 lines of text before the data. The only way I’ve found to collect just the data is to highlight and delete the header information for each message, leaving the sets of data stacked on one another in columns as above.

I was just hoping for a method of parsing for field names and extracting the data to spreadsheet form.

Any suggestions appreciated
Ray

Claudia Frank

Hello Ray,

if the field do have unique characteristics we can search for it.
But I’m still unsure what you exactly want to achieve?
Could you provide a real (sensitive data replaced of course) example
of what you have and how it should look at the end?

Cheers
Claudia

grubbyjeans

@Claudia-Frank said:

Hello Ray,

if the field do have unique characteristics we can search for it.
But I’m still unsure what you exactly want to achieve?

**I need to remove the header lines from the messages, leaving the csv data for manipulation.
**

Could you provide a real (sensitive data replaced of course) example
of what you have and how it should look at the end?

**I’ve created a macro that will convert the columnar data to comma delimited form which can be imported to spreadsheet.
I have screen captures of each.
First, a Thunderbird message exported to ‘Spreadsheet.csv’

“TEST Acoustic Music Retreat 2017 Registration”,“Website builder@sitebuilderservice.com”,“grubbyjeans@yahoo.com”,3/24/2016 16:26, ,"Subject:
TEST Acoustic Music Retreat 2017 Registration
From:
““Website”” builder@sitebuilderservice.com
Date:
3/24/2016 4:26 PM
To:
grubbyjeans@yahoo.com

firstname,Ray
lastname,New
street1,1234 my street
street2,1234
city,mytown
state,mystate
zipcode,77777
emailaddress,grubbyjeans@yahoo.com
homephone,123456789
workphone,1
instrument1,Guitar
skill_level1,Intermediate
instrument2,Mandolin
skill_level2,Beginner
instrument3,Mountain Dulcimer
skill_level3,Intermediate
"
Second:
Header lines removed manually and conversion macro run, converting to single line csv (no word wrap)
Ray,New,1234 my street,1234,mytown,mystate,77777,grubbyjeans@yahoo.com,123456789,1,Guitar,Intermediate,Mandolin,Beginner,Mountain Dulcimer,Intermediate

**

Cheers
Claudia

Claudia Frank

Hello Ray,

I guess by copying the example you messed up the structure, because in earlier post
you said 6 lines (header) must be deleted. So I assumed the following data is the one
which need to be modified (Note the double quote " on the end)

“TEST Acoustic Music Retreat 2017 Registration”,"Website builder@sitebuilderservice.com“,”grubbyjeans@yahoo.com",3/24/2016 16:26, ,"Subject:
TEST Acoustic Music Retreat 2017 Registration
From: ““Website”” builder@sitebuilderservice.com
Date: 3/24/2016 4:26 PM
To: grubbyjeans@yahoo.com

firstname,Ray
lastname,New
street1,1234 my street
street2,1234
city,mytown
state,mystate
zipcode,77777
emailaddress,grubbyjeans@yahoo.com
homephone,123456789
workphone,1
instrument1,Guitar
skill_level1,Intermediate
instrument2,Mandolin
skill_level2,Beginner
instrument3,Mountain Dulcimer
skill_level3,Intermediate
"

One macro recording with 2 regex could result in

Ray,New,1234 my street,1234,mytown,mystate,77777,grubbyjeans@yahoo.com,123456789,1,Guitar,Intermediate,Mandolin,Beginner,Mountain Dulcimer,Intermediate,

This is your goal, isn’t it.

If so,
record macro and
press CTRL+HOME (to get cursor located at first position)
press CTRL+H
click regular expression in lower pane
put into find what:

((.*?\R){6})((.*,.*\R)*)"

put into replace with:

\3

press Replace all

-> now we should only have left

firstname,Ray
lastname,New
street1,1234 my street
street2,1234
city,mytown
state,mystate
zipcode,77777
emailaddress,grubbyjeans@yahoo.com
homephone,123456789
workphone,1
instrument1,Guitar
skill_level1,Intermediate
instrument2,Mandolin
skill_level2,Beginner
instrument3,Mountain Dulcimer
skill_level3,Intermediate

change find what with:

(.*,(.*)\R)

and

replace with:

\2,

press replace all -> you should see the expected result
press close and stop recording.

Is this what you expected?

Cheers
Claudia

grubbyjeans

@Claudia-Frank
Brilliant!! It works perfectly. I converted over 300 records and imported to spreadsheet in seconds.

I don’t know where the ‘6 lines’ came from. I changed {6} to {9} and that worked.

Now I just have to analyze your coding for understanding. I’m a total noobe to Notepad++.

What is the process for posting examples, as you did in your response?

Thanks so much.
Ray

Claudia Frank

Hi Ray,

when posting formatted code/text you need to indent by at least 4 spaces, then it will keep its layout.
The regular expression is explained, for searching here and for replacing here

What it does is the following: (high level - detailed infos on the mentioned website)
( ) = this is a caturing group
\R = newline chars
{6} = should be repeated exactly 6 times
(.*?\R){6}) = basically means match 6 lines
(.*,.*\R) = match lines which have or not chars followed by a comma and again have chars or not followed by newline
(now I’m thinking .+,.+\R would be better because this means lines having atleast one char followed by comma and again at least one char followed by newline)
(.*,.*\R)* = the added * means it could be any number of lines or no line at all
" at the end basically means, last line needs to be the double quote

In replace, as we only use what was matched in \3, anything else get deleted.

As said, the links provide better and detailed explanation.

Cheers
Claudia