problem importing excel

[Michael Peters]

the Excel data that I would like to import into Access sometimes contain
line breaks, like this:

623
749

in one cell.
In Access, the imported data looks like this then:

623749

what can I do about this?

-Michael

[Bruce Lawrence]

What do you want it to look like in Access?
Do you want the first 3 numbers or the 2nd?

I think you're going to have to format that data in Excel prior to
importing.

[Michael Peters]

What do you want it to look like in Access?

Do you want the first 3 numbers or the 2nd?

I want them to look as they are meant, i.e. like the first 3 numbers

I think you're going to have to format that data in Excel prior to
importing.

they are formatted as text. It doesn't have to do anything with formatting.

The problem is that line breaks in Excel are chr(10) whereas in Access they
are chr(13)chr(10). I need some setting that translates that. If these were
intelligent programs, they would translate it like that by default. <sigh>

-Michael

[Smartin]

Michael Peters wrote:

>What do you want it to look like in Access?
Do you want the first 3 numbers or the 2nd?

I want them to look as they are meant, i.e. like the first 3 numbers

>I think you're going to have to format that data in Excel prior to
importing.

they are formatted as text. It doesn't have to do anything with formatting.

The problem is that line breaks in Excel are chr(10) whereas in Access they
are chr(13)chr(10). I need some setting that translates that. If these were
intelligent programs, they would translate it like that by default. <sigh>

-Michael

<sighif the person who created the data in the first place... </sigh>
Oh, heck. We have to deal with these things, right?

Totally untested...

Place in a standard module:

Public Function TruncCHR10(DataIn as String) as String
Dim L as Long
L = Instr(1, DataIn, Chr(10))
TruncCHR10 = DataIn
If L 0 Then
TruncCHR10 = Left(DataIn, L - 1)
EndIf
End Function

After you import the globbed-up data, run an update query along the lines of

UPDATE MBTable.MBData
SELECT TruncCHR10(MBData);

HTH
--
Smartin

[pietlinden]

Michael Peters wrote:

What do you want it to look like in Access?
Do you want the first 3 numbers or the 2nd?

I want them to look as they are meant, i.e. like the first 3 numbers

I think you're going to have to format that data in Excel prior to
importing.

they are formatted as text. It doesn't have to do anything with formatting.

The problem is that line breaks in Excel are chr(10) whereas in Access they
are chr(13)chr(10). I need some setting that translates that. If these were
intelligent programs, they would translate it like that by default. <sigh>

-Michael

You want some bit of software to read your mind? Hmm... sounds dodgy.
Personally, I'd try to split the stuff out in Excel, or use something
like this:

Public Sub TruncCHR10()
Dim rs As DAO.Recordset
Dim vData As Variant
Dim intCounter As Integer

Set rs = DBEngine(0)(0).OpenRecordset("SELECT PK, NumberList FROM
xlsTestData;", dbOpenForwardOnly)
Do Until rs.EOF
vData = Split(rs.Fields("NumberList"), Chr(10))
For intCounter = LBound(vData) To UBound(vData)
Debug.Print rs.Fields("PK"), vData(intCounter)
Next intCounter
rs.MoveNext
Loop

rs.Close
Set rs = Nothing
End Sub

But instead of just printing the split data to the debug window, write
it to a child table, where it belongs. And then you can query etc
without a clairvoyant computer.

[Michael Peters]

thanks for your help everybody!

I finally managed to solve the problem this way:

The chr(10) only linebreaks in Excel had to be replaced by ordinary
chr(13)chr(10) carriagereturn-linebreaks for Access.

I first exported the sheet into XML. The chr(10) line breaks turned up in
the XML code as &10; - I simply replaced them with &13;&10; using an ascii
editor, and re-imported them into Excel. The result could then be imported
into Access and the line breaks turned up correctly this time.

-Michael

[Smartin]

Michael Peters wrote:

thanks for your help everybody!

I finally managed to solve the problem this way:

The chr(10) only linebreaks in Excel had to be replaced by ordinary
chr(13)chr(10) carriagereturn-linebreaks for Access.

I first exported the sheet into XML. The chr(10) line breaks turned up in
the XML code as &10; - I simply replaced them with &13;&10; using an ascii
editor, and re-imported them into Excel. The result could then be imported
into Access and the line breaks turned up correctly this time.

-Michael

Just curious--did you end up with multiple values in a single cell?

--
Smartin