Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old March 26th, 2004, 12:21 PM
bph bph is offline
Friend of Wrox
 
Join Date: Dec 2003
Location: , , .
Posts: 102
Thanks: 2
Thanked 0 Times in 0 Posts
Default Excel Text Imports Revisited

Source of Data = Excel spreadsheet, several columns wide, one of which contains text which basically describes a company, with hard returns embedded in the spreadsheet to seperate the various details so to speak.

The challenge is to import data from the excel spreadsheet into the db, somehow retaining the hard returns, so that when we produce a report the company description will show on the report with the hard returns as was in the excel spreadsheet.

For example: This is how it would show up in the xcel ss. Some of the details go on for several lines. Need to see the same on report output.
- Publisher of childrens books, with an emphasis on nutrition. New title launching this year.
- Publisher also has advertising support.
- Recently purchased competitor. From 1969-1988, publishers revenues were 60 million per year, but in
1990 saw a slip in revenue as computerized childrens books/games grew in popularity. An so on.

Sorry for long wind. This is beyond anything I have ever done
Can Grow is set to yes.
Any thoughts would be greatly appreciated.
  #2 (permalink)  
Old March 26th, 2004, 04:40 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Harrisburg, PA, USA.
Posts: 1,998
Thanks: 0
Thanked 3 Times in 3 Posts
Default

You probably could search for the ControlChars.CrLf property in that string, and replace it with an empty string.

strText = strText.Replace(ControlChars.CrLf, "")

The ControlChars.CrLf is the equivalent for the vbCrlf in ASP/VB.

Brian
  #3 (permalink)  
Old March 26th, 2004, 04:50 PM
bph bph is offline
Friend of Wrox
 
Join Date: Dec 2003
Location: , , .
Posts: 102
Thanks: 2
Thanked 0 Times in 0 Posts
Default

Thanks Brian. That will help with the import, right? Will I then see the CR properly in the report output from the memo fields?

bph

  #4 (permalink)  
Old March 27th, 2004, 11:10 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Harrisburg, PA, USA.
Posts: 1,998
Thanks: 0
Thanked 3 Times in 3 Posts
Default

Actually, I screwed up and thought this was an ADO.NET question, but it's not. Anyways, do you have the capabilities to use VBA? You could do it that way. It's been a while. Basically, in the VB module, you search through the records, and replace vbCrLf with an empty string, such as:

replace(strText, vbCrLf, "")

You may be able to do this with an update statement in SQL, but I'm not sure.

Sorry about that,

Brian
  #5 (permalink)  
Old March 29th, 2004, 02:08 PM
bph bph is offline
Friend of Wrox
 
Join Date: Dec 2003
Location: , , .
Posts: 102
Thanks: 2
Thanked 0 Times in 0 Posts
Default

Thanks Brian. I'll continue to hack away at it.

  #6 (permalink)  
Old April 1st, 2004, 07:35 AM
Registered User
 
Join Date: Mar 2004
Location: London, , United Kingdom.
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Replaces Excel line feed with Access return.


scid=http://support.microsoft.com:80/support/kb/articles/Q115/5/76.ASP&NoWebContent=1


Similar Threads
Thread Thread Starter Forum Replies Last Post
imports - Chapter 2 sfarber53 BOOK: Beginning Java 2 2 September 19th, 2010 07:43 PM
Imports with .VB files Doldrums VB.NET 2002/2003 Basics 6 November 11th, 2004 09:58 AM
Page 57 Imports Sytem.Web.UI martina68 BOOK: ASP.NET Website Programming Problem-Design-Solution 1 February 16th, 2004 12:28 PM
Help on ListBox imports text tdeng Beginning VB 6 2 December 22nd, 2003 02:46 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.