Visual Basic - NonDisplay Characters

[NJonge01]

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!

[ADezii]

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:

'Test String in [txtTest]
Philadelphia
is
the
city
of
brotherly
love.

Dim intCRPosition  As Integer
Dim intCounter As Integer
Dim aCRs() As Integer       'Array to store the Positions of Carriage Returns
 
intCRPosition = 0           'initialize CR Position
 
For intCounter = 1 To Len(Me![txtTest])
  If Mid$(Me![txtTest], intCounter, 1) = vbCr Then
    intCRPosition = intCRPosition + 1
    ReDim Preserve aCRs(1 To intCRPosition)     'must Redimension Array
      aCRs(intCRPosition) = intCounter
  End If
Next
 
For intCounter = LBound(aCRs) To UBound(aCRs)
  Debug.Print "Carriage Return at position: " & aCRs(intCounter)
Next

OUTPUT:

Carriage Return at position: 13
Carriage Return at position: 17
Carriage Return at position: 22
Carriage Return at position: 28
Carriage Return at position: 32
Carriage Return at position: 43

NOTE: A Line Feed (vbLf) may or may not be after the Carriage Return (vbCr)

[nico5038]

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)

[ADezii]

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.

[NJonge01]

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!

[NJonge01]

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!

[NJonge01]

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.

[nico5038]

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)