Importing data to excel (newline/carriage return issue)

[Tandaina]

Hello folks. Excel newbie here hoping for a point in the right
direction.

I'm a linux girl, I don't do Windows and know nothing about Excel so
please bear with me. I've been asked to solve a problem for my managers
so I'm delving into Excel.

We are exporting data from our DB2 database in roughly the following
format:

"Employee Name", "Date", "Task", "Employee's Task Comments"
"Employee Name", "Date", "Task", "Employee's Task Comments"
"Employee Name", "Date", "Task", "Employee's Task Comments"

You get the idea. The first three fields are not an issue. But that
last field is a free form text field in our application that can (and
usually does) include things like tabs and newline characters. When we
try to import this data into Excel the newlines in the comments field
cause a new row in Excel to be created. This is obviously not what we
want.

What my managers would like to see is the comments preserved the way
they would be seen in our application. IE one cell with tab and newline
characters maintained in that cell. I'm guessing there has to be away
to do this, perhaps when I'm doing the data import from the text file?
(I'm using Data->Import External Data and going throught the wizard.
So I can tell Excel that last field is " delimited text but it still
breaks the field on newlines.)

Any nudges in the right direction would be appreciated. Programming I
know, but Excel is a foreign tool and the help was less than useful.

Chris

 

[Dave O]

Hi, Chris-
As an idea, can you lash together a script of one type or another
(perl?) that reads the file and strips out the linefeed (or whatever is
causing Excel to interpret a new row), and replaces it with a carriage
return? Sounds like you should then be able to import to excel with no
worries.

Dave O

 

[Dave O]

Hi, Chris-
As an idea, can you lash together a script of one type or another
(perl?) that reads the file and strips out the linefeed (or whatever is
causing Excel to interpret a new row), and replaces it with a carriage
return? Sounds like you should then be able to import to excel with no
worries.

Dave O

 

[stevebriz]

My thoughts are along the lines of Dave O
You can do this in VBA within excel
I would read the file the each line or all lines into a buffer and
replace the offending tabs, vb newline etc with a space. then delimit
on commas
Additioinally if the comments field has commas in it then then next
step would be to
Read it into a 2D array ( eg x,y) based on splitting commas and join
all from y=>4
Hope this makes sense

 

[Tandaina]

My thoughts are along the lines of Dave O
You can do this in VBA within excel
I would read the file the each line or all lines into a buffer and
replace the offending tabs, vb newline etc with a space. then delimit
on commas
Additioinally if the comments field has commas in it then then next
step would be to
Read it into a 2D array ( eg x,y) based on splitting commas and join
all from y=>4
Hope this makes sense

Hmm, I was sort of afraid that would be the answer. Saving the file
as a csv file and just using open instead of import gets me sort of
what you are talking about. No strangly broken lines BUT we also loose
the formatting within the last cell.

Since we want to preserve newlines (IE turn them into newlines within
the cell, I believe excel calls them hard returns?) what character
would I need to insert?

 

[Dave Peterson]

I think I'd try to change the tabs to spaces and the returns to a unique, unused
character in the DB2 file--but I don't know if that's even possible.

Then you could just import the data as-is and do a mass change to change that
unique character back to alt-enter when you open the file in excel.

If that's not possible...

If you just use File|Open and parse it as comma delimited, do you end up with a
line with 4 columns used, then some lines with just column A used, then a line
with 4 columns used, then more lines with just column A used?

Kind of like:

a b c d
x
y
z
a b c d
x
y
z
w

where each letter represents a cell?

Maybe you could just loop from the top to the bottom. If the row has 4 columns
used, don't touch it.

If the row only uses column A, then append that info (with an alt-enter) to
column D of the good row (and delete that crummy row).

 

[Earl Kiosterud]

Chris,

Excel is notorious for not following text file conventions. In this case,
it's not recognizing what's inside text qualifiers (quotes) and treating new
line sequences found there as end-of-line. How rude.

Here's one approach. This Excel macro will read the file into the first
column. After that, you can use Data - Text to columns to parse it on
commas into the remaining columns. That will remove the quotes from your
fields.

Sub ReadFile()
Dim FileName As String
Dim InsideField As Boolean
Dim Indata As String * 1
Dim Roww As Long
Dim Stuff As String

FileName = "aa Text File.txt"
Cells.ClearContents
Roww = 1
Open FileName For Binary As #1
Do
Get #1, , Indata
If Indata = """" Then InsideField = Not InsideField ' flip
If Asc(Indata) = 10 And Not InsideField Then ' legitimate record delimiter
Roww = Roww + 1
Stuff = ""
Else ' just another character
If Asc(Indata) <> 13 Then
Stuff = Stuff & Indata
Cells(Roww, 1) = Stuff
End If
End If
Loop While Not EOF(1)
Close #1
End Sub


It's barebones and scarcely tested or optimized, but maybe it'll get you
going. It responds to the usual CR (13) LF (10) for end-of-line, and leaves
one LF (10) in the field, which is the Excel new-line-inside-a-cell
(Alt-Enter) character. You might have to tweak it if your end-of-line
sequences are different.

 

[ExcelJockey]

hi Tandaina

i'm a linux guy too. here is another plan of attack in addition th
excellent suggestions here. perhaps overkill but good for futur
reference..

(using perl modules on cpan.org

encode the data from each field into something XML friendly, parse i
into XML, then import the XML into Excel 200

http://office.microsoft.com/en-gb/assistance/ha011019641033.asp

then unencode the data in excel. (CPAN has win32 excel modules fo
manipulating spreadsheets.