Importing a CRLF from and Excel Spreadsheet.

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.
scully12Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
nico5038Connect With a Mentor Commented:
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
 
dovholukCommented:
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
 
scully12Author Commented:
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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
nico5038Commented:
Did you try to change the receiving field to memo format?

Nic;o)
0
 
gregdaveyCommented:
Memo is definitely the answer. Let us know if that works


Greg
0
 
scully12Author Commented:
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
 
nico5038Commented:
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
 
scully12Author Commented:
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
 
nico5038Commented:
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
 
scully12Author Commented:
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
 
nico5038Commented:
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
 
scully12Author Commented:
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
 
gregdaveyCommented:
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
 
scully12Author Commented:
Allright, I was confused but I get it now. Looks like this should fix it for me. Thanks,
0
 
gregdaveyCommented:
Wow Nico

That was amazing! :)

Greg
0
All Courses

From novice to tech pro — start learning today.