Solved

Importing a CRLF from and Excel Spreadsheet.

Posted on 2001-06-05
15
547 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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
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…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

758 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

18 Experts available now in Live!

Get 1:1 Help Now