Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
Password Reminder
Register
| FAQ | Members List | 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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #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.
Reply With Quote
  #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
Reply With Quote
  #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

Reply With Quote
  #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
Reply With Quote
  #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.

Reply With Quote
  #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
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

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


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



All times are GMT -4. The time now is 01:56 PM.


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