By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,480 Members | 2,189 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,480 IT Pros & Developers. It's quick & easy.

Visual Basic - NonDisplay Characters

P: 44
I would like to take some data that I've cut and paste from an Excel document. the editor of the excel document has in single cells multiple "rows" of data that I would like to put on single rows of a table in Access. The affect of the multiple rows comes from a "CTRL return" I believe when creating the excel document.

When I paste the excel data into a text document, the multiple rows in the excel spreadsheet appear as a single string with a nondisplay character (representing the CRLF) delimiting them.

when I try to process this text file into a table, I wanted to use the "instr" function of VB to find the non-display character but I don't know how to represent it as the "string" to find.

I ultimately need the position of that non display character to insert the characters before it as 1 row of data and then the characters of data after it as the next row.

Any suggestions?

Thanks!
Oct 6 '07 #1
Share this Question
Share on Google+
7 Replies


ADezii
Expert 5K+
P: 8,597
I would like to take some data that I've cut and paste from an Excel document. the editor of the excel document has in single cells multiple "rows" of data that I would like to put on single rows of a table in Access. The affect of the multiple rows comes from a "CTRL return" I believe when creating the excel document.

When I paste the excel data into a text document, the multiple rows in the excel spreadsheet appear as a single string with a non-display character (representing the CRLF) delimiting them.

when I try to process this text file into a table, I wanted to use the "instr" function of VB to find the non-display character but I don't know how to represent it as the "string" to find.

I ultimately need the position of that non display character to insert the characters before it as 1 row of data and then the characters of data after it as the next row.

Any suggestions?

Thanks!
Here is a little code I wrote prior to going to bed which will find each occurrence of a Carriage Return (vbCr) in a String and place their positions in an Array (aCRs) for later retrieval. The only Assumption is that the String is in a Text Box named [txtTest]. The test string and code is listed below:
Expand|Select|Wrap|Line Numbers
  1. 'Test String in [txtTest]
  2. Philadelphia
  3. is
  4. the
  5. city
  6. of
  7. brotherly
  8. love.
Expand|Select|Wrap|Line Numbers
  1. Dim intCRPosition  As Integer
  2. Dim intCounter As Integer
  3. Dim aCRs() As Integer       'Array to store the Positions of Carriage Returns
  4.  
  5. intCRPosition = 0           'initialize CR Position
  6.  
  7. For intCounter = 1 To Len(Me![txtTest])
  8.   If Mid$(Me![txtTest], intCounter, 1) = vbCr Then
  9.     intCRPosition = intCRPosition + 1
  10.     ReDim Preserve aCRs(1 To intCRPosition)     'must Redimension Array
  11.       aCRs(intCRPosition) = intCounter
  12.   End If
  13. Next
  14.  
  15. For intCounter = LBound(aCRs) To UBound(aCRs)
  16.   Debug.Print "Carriage Return at position: " & aCRs(intCounter)
  17. Next
OUTPUT:
Expand|Select|Wrap|Line Numbers
  1. Carriage Return at position: 13
  2. Carriage Return at position: 17
  3. Carriage Return at position: 22
  4. Carriage Return at position: 28
  5. Carriage Return at position: 32
  6. Carriage Return at position: 43
NOTE: A Line Feed (vbLf) may or may not be after the Carriage Return (vbCr)
Oct 7 '07 #2

nico5038
Expert 2.5K+
P: 3,072
I guess that not all fields have this multiple lines problem, but some.
In a case like that I would import the textdata fields with carriage returns into a memo field and create a lookup table to hold the separate values.

After the import some code is needed to extract the multiple values from the textfield and that can be done by using the Split() function and to loop through the created array to insert a line in the lookup table for the unique key of the current record and the found array entry.
In the Split() function the vbCR or the vbCRLF can be used as indicated in the coment ofADezii.

Nic;o)
Oct 7 '07 #3

ADezii
Expert 5K+
P: 8,597
I guess that not all fields have this multiple lines problem, but some.
In a case like that I would import the textdata fields with carriage returns into a memo field and create a lookup table to hold the separate values.

After the import some code is needed to extract the multiple values from the textfield and that can be done by using the Split() function and to loop through the created array to insert a line in the lookup table for the unique key of the current record and the found array entry.
In the Split() function the vbCR or the vbCRLF can be used as indicated in the coment ofADezii.

Nic;o)
Hello Nico, good pickup on the use of the Split() Function. I was focused on the exact positions of the Carriage Returns, and did not consider its use.
Oct 7 '07 #4

P: 44
Works perfectly!

The only change I had to made was due to a misunderstanding I had of the input data.

I did not realize that vbCF and vbLF could be used separately.

With the clue you gave me, I found that Excel actually creates just the 'line feed'.

When I use your code looking for vbLF in the data that I receive from Excel, it works perfectly!

This will be a very, very helpful utility for me.

Thanks!
Oct 7 '07 #5

P: 44
I used the code that was created & it works great. I'm not familiar with the "Split" function. I'll read up on that approach. Thank you!
Oct 7 '07 #6

P: 44
Wow... the split function is really handy.

Given that the need was identified, it was received by MSFT, they developed the function, and now it has been distributed with Visual Basic since who knows when... I guess my technical issue isn't exactly "cutting edge"!

LOL.

Thanks again. This forcum has been extremely helpful to me since discovering it. In order to resolve problems I discover, I only need to post a new question 1 in 5-10 times. Usually the problem has already been discussed.
Oct 7 '07 #7

nico5038
Expert 2.5K+
P: 3,072
Glad we could help.
Very good that you only needed my cryptical description to find out how the Split does work :-)
I prefer to start giving directions as I don't like to spoil the "I found it" experience :-)

Well done and success with your application !

Nic;o)
Oct 7 '07 #8

Post your reply

Sign in to post your reply or Sign up for a free account.