Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel chart issue - cannot include a cell in my data series | Excel Discussion (Misc queries) | |||
issue with excel 2003 data import | Excel Discussion (Misc queries) | |||
Excel Macro to Copy & Paste | Excel Worksheet Functions | |||
Excel 2003, Convert EXISTING Worksheet Data to XML? | Excel Discussion (Misc queries) | |||
Importing data from Excel to Outlook | Excel Discussion (Misc queries) |