Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2
Default Importing data to excel (newline/carriage return issue)

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

  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 427
Default Importing data to excel (newline/carriage return issue)

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

  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 427
Default Importing data to excel (newline/carriage return issue)

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

  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 195
Default Importing data to excel (newline/carriage return issue)

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

  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2
Default Importing data to excel (newline/carriage return issue)


stevebriz wrote:
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?



  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 35,218
Default Importing data to excel (newline/carriage return issue)

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).




Tandaina wrote:

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 Peterson
  #7   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 611
Default Importing data to excel (newline/carriage return issue)

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.

--
Earl Kiosterud
www.smokeylake.com
-----------------------------------------------------------------------
"Tandaina" wrote in message
ups.com...
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



  #8   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1
Default Importing data to excel (newline/carriage return issue)

hi Tandaina,

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

(using perl modules on cpan.org)

encode the data from each field into something XML friendly, parse it
into XML, then import the XML into Excel 2003

http://office.microsoft.com/en-gb/as...019641033.aspx

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

------------------------------------------------------------------------
ExcelJockey's profile: http://www.exceljockeys.com/forums/p...iewprofile&u=4
View this thread: http://www.exceljockeys.com/forums/v...c.php?t=304562

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel chart issue - cannot include a cell in my data series Russell-stanely Excel Discussion (Misc queries) 0 April 21st 06 05:29 PM
issue with excel 2003 data import LNguyen Excel Discussion (Misc queries) 0 December 8th 05 08:42 PM
Excel Macro to Copy & Paste [email protected] Excel Worksheet Functions 0 December 1st 05 02:56 PM
Excel 2003, Convert EXISTING Worksheet Data to XML? [email protected] Excel Discussion (Misc queries) 4 November 16th 05 05:45 AM
Importing data from Excel to Outlook JTRNPTNY Excel Discussion (Misc queries) 1 August 5th 05 03:35 PM


All times are GMT +1. The time now is 04:08 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"