Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Importing a CRLF from and Excel Spreadsheet.

Posted on 2001-06-05
15
Medium Priority
?
560 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

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

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses

885 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