UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Error With Data Once Imported, Access 2010    
 
   
Brepea
post May 17 2017, 10:15 AM
Post#1



Posts: 354
Joined: 11-January 09
From: UK


HI - import data from an excel file to Access. The issue is as per attached. The data imported is coming from a legacy system which is a text field...

The data is inputted in some cases as follows:

"The man walked...
the road...
There he saw a light!"

i.e. carriage returns (randomly)...

When I import this data into access it displays the data as per attachment.

Does anyone know how i can prevent this happening?

Attached File  Data_Import.png ( 122.23K )Number of downloads: 2
Go to the top of the page
 
HiTechCoach
post May 17 2017, 10:31 AM
Post#2


UtterAccess VIP
Posts: 18,986
Joined: 29-September 03
From: Oklahoma City, Oklahoma


Are you wanting to maintain the Line Breaks?

Is yes then...

The most common cause I see for this is from the original data having only a Line Feed (LF). Not a Carriage Return (CR) and Line Feed (LF) combination.

To fix this you need to replace the Chr(10) ( LF) wth a CRLF using vbCRLF or Chr(13) & Chr(10).

To test, use InStr() to see if there is a vbCRLF. If not Rplace() the Chr(10) with vbCRLF

--------------------
Boyd Trimmell aka Hi Tech Coach ( HiTechCoach.com free Access stuff)
Microsoft MVP 2010-2015 - Access Expert
Inventory Control, Accounting, BPM, and CRM Software Developer
"If technology doesn't work for people, then it doesn't work."
Go to the top of the page
 
Brepea
post May 18 2017, 02:21 AM
Post#3



Posts: 354
Joined: 11-January 09
From: UK


No - actually I want to group data together as in a memo field / long text.
Go to the top of the page
 
HiTechCoach
post May 18 2017, 01:26 PM
Post#4


UtterAccess VIP
Posts: 18,986
Joined: 29-September 03
From: Oklahoma City, Oklahoma


OK. You want to combine the lines int a single memo field.

QUOTE
FWIW: Personally, I would not do that. I prefer to have each as a separate record in a "notes" sub/child table.



To combine the data into a single field, I would first import al the data from Excel into a temp table. Then use an update query or VBA code to combine all the records into a single memo field.

TIP: Test on a copy of your database.

--------------------
Boyd Trimmell aka Hi Tech Coach ( HiTechCoach.com free Access stuff)
Microsoft MVP 2010-2015 - Access Expert
Inventory Control, Accounting, BPM, and CRM Software Developer
"If technology doesn't work for people, then it doesn't work."
Go to the top of the page
 
Brepea
post May 19 2017, 10:30 AM
Post#5



Posts: 354
Joined: 11-January 09
From: UK


Sorry - no - this is not what i meant.

If you look at the two images- the second image is what my data looks like in Access post import...so you can see in example that the "k" of the "knowledge" has somehow been overlay-ed with some other character....

So I don't want to put all the text of all fields into one memo field - not at all, but i just want text that is written on separate line within same cell in Excel to be imported in one field in Access so that there is no characters which have been overlay-ed with -- whatever that is...

so effectively in Excel data looks like this:

"One day a man walked...
"down the road...
"he saw the sky was blue"

It currently imports into Access as follows: "One day a man walked...d(strange character overlays the "d") own the road...h(a strange character overlays the "h")e saw the sky was blue"

Hope that helps explain it...
This post has been edited by Brepea: May 19 2017, 10:34 AM
Go to the top of the page
 
HiTechCoach
post May 19 2017, 12:05 PM
Post#6


UtterAccess VIP
Posts: 18,986
Joined: 29-September 03
From: Oklahoma City, Oklahoma


There is probably a character that has an ASCII code outside of the displayable character set.

I also have this issue when people past from Excel and Word into an Access from control.

Here is some code to help yu clean up the data:

Removing Non-Alphanumeric characters from a string


--------------------
Boyd Trimmell aka Hi Tech Coach ( HiTechCoach.com free Access stuff)
Microsoft MVP 2010-2015 - Access Expert
Inventory Control, Accounting, BPM, and CRM Software Developer
"If technology doesn't work for people, then it doesn't work."
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    25th May 2017 - 10:44 PM