Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Importing a CRLF from and Excel Spreadsheet.

Posted on 2001-06-05
15
Medium Priority
?
556 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

 
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
 
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 800 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

How Blockchain Is Impacting Every Industry

Blockchain expert Alex Tapscott talks to Acronis VP Frank Jablonski about this revolutionary technology and how it's making inroads into other industries and facets of everyday life.

Question has a verified solution.

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

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
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 …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

721 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