Removing Extra Line Breaks from a field in CSV file

I have a CSV file that was exported from a Goldmine database and I am attempting to import into another Goldmine database.

There are roughly 30ish fields that are exported with field headers.

One of the fields in NOTES.  In the notes field, there are line breaks/returns that break up having each record on its own row. I have to bring into Crystal Reports and then export to be "Goldmine" Friendly.

The problem is that it's reading these line breaks and screwing up all the data - there are roughly 90kish records so this isn't something I can go and fix manually.

How can I go about removing the line breaks just in this NOTES field before bringing it into Crystal Reports?

NOTE ADDITIONAL: This notes field has html code for formatting that the version of Goldmine I am importing into can't support anyway.  If I have to delete the NOTES column altogether I might have to, but I have no way of accessing that column due to the fact the line breaks push data into other columns!
webdevsquareAsked:
Who is Participating?
 
stevengraffConnect With a Mentor Commented:
Other options:

1. Why export/import? Are you changing GoldMine versions? Otherwise you could simply copy/paste and re-attach.
2. If you really must export/import, use dbf, not csv or ascii. Text-based "databases" are the least manageable, least reliable to work with.
0
 
lcohanDatabase AnalystCommented:
Why not export the data again into a new csv file that does not include the "notes" column?
0
 
lcohanDatabase AnalystCommented:
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
AmickCommented:
In Windows PowerShell try  Get-Content .\MyDocument.txt | %{ $_.Replace("`n",''); }
0
 
webdevsquareAuthor Commented:
@stevengraff yes they are different Goldmine Versions and I don't have access to original source any more

@icohan I can't get access to source anymore.  On those utilities, how does it know not to replace the line break where it needs to be: at the end of the record only?

@Amick, I have no idea what windows Powershell is
0
 
lcohanDatabase AnalystCommented:
True - you don't know what it would replace however if you have the CSV file only and can't re-export then I would just open it with EXCEL or OpenOffice and delete the "notes" column entirely if data is no use anyway!
0
 
lcohanDatabase AnalystCommented:
Obviously....save a origunal copy somehere safe before any edits to the file. Sorry I missed that
0
 
stevengraffCommented:
If you import it into Excel... doesn't Excel prompt you to identify field and record delimiters?
0
 
webdevsquareAuthor Commented:
That sounds good, except I cant open with Excel/Open Office accurately. Everything gets jumbled in other columns.

when I open in Wordpad and remove the line breaks in the notes column, I'm golden. But cant do that for everyone - there are 90k records!
0
 
stevengraffConnect With a Mentor Commented:
Ouch!!! I'm sorry.

Hey, check out GoldBox. I'm not sure, specifically, what it can do with that particular csv, but one of its purposes in life is to import data into GoldMine. It's no longer supported, but I know you can still buy it. It's either at the add-on store or at redstonesoftbase.com.
0
 
AmickCommented:
@webdevsquare
Windows PowerShell is Microsoft's task automation framework.

From the Windows command prompt type powershell to access the tool.
0
 
lcohanDatabase AnalystCommented:
Did you tried Open Office?

http://download.openoffice.org/
0
 
SuperdaveConnect With a Mentor Commented:
The possibilities of how it is supposed to work in the first place are:
1.  Use linefeed character within the field, but carriage-return linefeed combination at the end of lines.  Excel does this, so if that didn't work, either Goldmine is using a different convention, or your file got mangled by some piece of software trying to fix line endings.

2.  Maybe the fields are all in quotation marks, in which case you will have to figure out how to use a search-and-replace tool (powershell or sed or whatever) to only replace line endings after an odd number of quotes on a line.
0
 
stevengraffCommented:
MS Word is pretty good and doing global search and replace on control characters, isn't it?
0
 
mlmccCommented:
Are you trying to eliminate the move to Crystal step?

mlmcc
0
 
webdevsquareAuthor Commented:
@mlmcc I use Crystal because Goldmine needs the quotations around every field and from what I know, Excel won't do that for me automatically (could be wrong)

@superdave  when I open with excel, it jumbles. When I use the Import Data feature, I set it as comma delimited. where is the carriage return info?

0
 
mlmccCommented:
Can you create a small CSV file that shows the issue.

I assume by using Crystal you can massage the CSV file so it works on input.

mlmcc
0
 
webdevsquareAuthor Commented:
sampledb.txt

Check it out.
0
 
webdevsquareAuthor Commented:
Any thoughts guys?
0
 
SuperdaveCommented:
It has CRLF everywhere but all fields are in quotes, so basically, if there are an odd number of quotation marks on a line, append the next line to get rid of the interior line feed.  Then repeat the process starting with the same line.

I could figure out how to do it in the Vim editor or in Python or Perl; you could probably use Powershell too but I don't know about that.
0
 
webdevsquareAuthor Commented:
@superdave, thanks for the feedback.  I see what you mean about the odd number of quotation marks logic.  Are any of the other editors you mentioned (free) and easy to download/use?
0
 
SuperdaveCommented:
Vim is an editor; the other two are scripting languages.  They're all free and available at the obvious www.*.org URLs.
0
 
AmickCommented:
Because the account number appears to be a distinct pattern, perhaps that's where the logic should be applied. Just keep appending segments together until the beginning of the segment matches the account number pattern.
0
 
webdevsquareAuthor Commented:
I appreciate all the feedback, but my strength is definitely not in figuring out a new editor and applying this logic.  
0
 
stevengraffConnect With a Mentor Commented:
Clicking one's profile may lead to contact information. I'd love to take a crack at it. Post a snippet if you'd like.
0
 
webdevsquareAuthor Commented:
@mlmcc didn't know. thanks.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.