Solved

Importing a CRLF from and Excel Spreadsheet.

Posted on 2001-06-05
15
548 Views
Last Modified: 2010-08-05
I already have a method of importing an excel spreadsheet into an Access 200 DB. However, one of the fields that gets imported is multi lined in it. It's in one single cell in Excel but when it gets into Access it's one constant string. Is there a character or something I could put into the Excel file so that once it's imported into Access it will still be mulitlined? We've tried inserting things like a solid | or the solid square symbols but they just get imported and show up as a solid pipe or square in the Access table. However, in testing if you copy the symbol and paste it does do a CRLF.
0
Comment
Question by:scully12
  • 6
  • 5
  • 3
  • +1
15 Comments
 
LVL 8

Expert Comment

by:dovholuk
ID: 6157660
do you have a VBA routine that is importing the data?

if so, when you import the data check that particular field for chr(13) or chr(10). i would guess that the Chr(10) is stored in an excel spreadsheet and not in access. thus when access displays the data, you get the solid bar you mentioned.

is this the case? i had a problem similar to this with the way Word and Access store data in text fields. Word/Excel store the extra chr(10) (if i remember correctly)

try parsing the data on the import and stripping out the chr(10)'s

hope this helps.

dovholuk
0
 

Author Comment

by:scully12
ID: 6157671
I don't have a VBA routine that is importing the data. I'm just using the Get External Data->Import from the File menu. Is there something I could try for that?
0
 
LVL 54

Expert Comment

by:nico5038
ID: 6157704
Did you try to change the receiving field to memo format?

Nic;o)
0
 
LVL 2

Expert Comment

by:gregdavey
ID: 6159093
Memo is definitely the answer. Let us know if that works


Greg
0
 

Author Comment

by:scully12
ID: 6160934
Was I supposed to put anything in between the 2 seperate lines before I imported it? I didn't do it myself but I have some people here who do all the importing and they claim it didn't work. I'm gonna try it myself and I'll let you know. Thanks,
0
 
LVL 54

Expert Comment

by:nico5038
ID: 6160942
BTW, just a thought, I did import multiline fields from excel, however you'll only see the firstline in the table. You'll need to use the cursor-down to get to the "lower" lines, or use a multiline field on a form.

Nic;o)
0
 

Author Comment

by:scully12
ID: 6161122
After we maually edit each record the address it does just fine. However, after it's imported it looks something like this until we change each record:
Company Name[]ATTN: Somebody[]123 Somewhere St[]City, ST, Zip
The [] are actually complete outlined squares but if I paste the exact line in this box from Excel I get:
Company Name
ATTN: Somebody
123 Somewhere St
City, ST, Zip
Which is what I'd like to get in Access 2000.
I did try the test myself using Memo for the data type
and have the same problem.
0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 54

Expert Comment

by:nico5038
ID: 6161167
The squares represent "non-printable" characters. Could be e.g. tab's or just a single LineFeed iso the required Linefeed/CarriageReturn.
You can edit these by looping through the field character by character and change the tab?Linefeed? into the vbCRLF value.

If you paste one cell here, we can have a look.

Nic;o)
0
 

Author Comment

by:scully12
ID: 6161663
I can't really paste it in here any better than I already have. So I created an example. It's just an example DB with one record imported that didn't turn out like I wanted it to. Then you can use the Excel file in the zip to try an import. It's zipped up an only about a 12k download.

http://members.nbci.com/andyg/db1.zip
0
 
LVL 54

Expert Comment

by:nico5038
ID: 6162012
Hi scully,

Your problem is that there are socalled "soft-returns" (SHIFT + Return) in the text.
The text:
ABC
ATTN: Somebody
123 Somewhere St
City, ST  Zip

Gives as ASCII values:
 65 66 67 10 65 84 84 78 58 32 83 111 109 101 98 111 100 121 10 49 50 51 32 83 111 109 101 119 104 101 114 101 32 83 116 10 67 105 116 121 44 32 83 84 32 32 90 105 112

Use a loop like:
Dim intI As Integer
For intI = 1 To Len(Me.Address)
IF asc((Mid(Me.Address, intI, 1)) = 10 then
  me.txtResult = Me.txtResult & vbCRLF
else
  me.txtResult = Me.txtResult & Mid(Me.Address, intI, 1))
endif

Next intI

Nic;o)
0
 

Author Comment

by:scully12
ID: 6162111
Ok, so I guess I'll need to write up a VBA routine to do the importing then in order to include something like the code snippet above. Do you know of any good sites that might offer some samples in writing a VBA routine to import from an Excel spreadsheet. I've always just used the Import feature in Access 2000. I'm gonna up the points and award them to nico5038 for sticking with me. Thanks,
0
 
LVL 2

Expert Comment

by:gregdavey
ID: 6162617
Do you think you would be better off writing the routine to 'fix' the data once it is in Access. It strikes me that it would be easier than trying to import the whole thing using code.

Nico's code looks pretty good for 'fixing' the bad field. I would suggest you try implementing that first.

Otherwise, writing the code to do the import is possible, but it is a big job

Greg
0
 
LVL 54

Accepted Solution

by:
nico5038 earned 200 total points
ID: 6162684
Hi scully12,

You can do both. When you link the .xls for importing the data using a query, you can use a function around this field like:
Address2:fncCRLF(address)

the code for fcnCRLF is placed in a module (last tab of database window):

Function fncCRLF(strInput as string) as string
' function to replace soft Carriage Return with vbCRLF
Dim intI As Integer

For intI = 1 To Len(strInput)
    IF asc((Mid(strInput, intI, 1)) = 10 then
        fncCRLF= fncCRLF & vbCRLF
    else
        fncCRLF = fncCRLF & Mid(strInput, intI, 1))
    endif
Next intI

End function

Success !

Nic;o)



0
 

Author Comment

by:scully12
ID: 6163506
Allright, I was confused but I get it now. Looks like this should fix it for me. Thanks,
0
 
LVL 2

Expert Comment

by:gregdavey
ID: 6166174
Wow Nico

That was amazing! :)

Greg
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now