Import lay-out of excelltable in access possible?

[Guest]

I'd like to import (text)data from excell into access. However, I used
"alt+tab" in my excelltable to separate sentences from one-another. After the
import into access, there appears a rectangle in my datatable where I used
"alt+tab" and the different sentences are no longer separated. Is it possible
to import the lay-out of my excelltable as well?

Thx

Hans

 

[Douglas J. Steele]

Excel uses Chr(10) for a new line character, Access uses Chr(13) & Chr(10)
(in that order).

Import the data as you are, then run an Update query that uses the Replace
function to correct the new line character:

UPDATE MyTable SET MyField = Replace(MyField, Chr(10), Chr(13) & Chr(10))

 

[Guest]

Douglas,

Thanks for your help. I ran the updatequery, but access made some additional
tables and filled them with zero's. The tables with my data are not
corrected. Could you advise me?

Thx

 

[Douglas J. Steele]

I have no idea what you mean by "access made some additional tables and
filled them with zero's."

What are these tables? What was the SQL of the query you ran?

 

[Guest]

My query looks like:

Field Field1
Table Table1
Change "UPDATE Table1 SET Field1"="Replace[Field1, Chr[10], Chr[13] &
Chr[10]]"

My original data were imported/stored in Field1. After running the query,
access stored them in Field 4. Fields 1 - 3 are now all filled with 0. The
lay-out however remains the same.

 

[Douglas J. Steele]

Go to the SQL View (you can get to it through the View menu when the query's
open) and copy the SQL into your response.

 

[Guest]

UPDATE Blad1 SET Blad1.Veld1 = "UPDATE Blad1 SET Veld1"="Replace[Veld1,
Chr[10], Chr[13] & Chr[10]]", Blad1.Veld2 = "UPDATE Blad1 SET
Veld2"="Replace[Veld2, Chr[10], Chr[13] & Chr[10]]", Blad1.Veld3 = "UPDATE
Blad1 SET Veld3"="Replace[Veld3, Chr[10], Chr[13] & Chr[10]]";

Go to the SQL View (you can get to it through the View menu when the query's
open) and copy the SQL into your response.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

My query looks like:

Field Field1
Table Table1
Change "UPDATE Table1 SET Field1"="Replace[Field1, Chr[10], Chr[13] &
Chr[10]]"

My original data were imported/stored in Field1. After running the query,
access stored them in Field 4. Fields 1 - 3 are now all filled with 0. The
lay-out however remains the same.

 

[Douglas J. Steele]

Change that to:

UPDATE Blad1 SET Veld1 = Replace(Veld1, Chr(10), Chr(13) & Chr(10)), Veld2
= Replace(Veld2, Chr(10), Chr(13) & Chr(10)), Veld3 = Replace(Veld3,
Chr(10), Chr(13) & Chr(10));



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

UPDATE Blad1 SET Blad1.Veld1 = "UPDATE Blad1 SET Veld1"="Replace[Veld1,
Chr[10], Chr[13] & Chr[10]]", Blad1.Veld2 = "UPDATE Blad1 SET
Veld2"="Replace[Veld2, Chr[10], Chr[13] & Chr[10]]", Blad1.Veld3 =
"UPDATE
Blad1 SET Veld3"="Replace[Veld3, Chr[10], Chr[13] & Chr[10]]";

Go to the SQL View (you can get to it through the View menu when the
query's
open) and copy the SQL into your response.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

My query looks like:

Field Field1
Table Table1
Change "UPDATE Table1 SET Field1"="Replace[Field1, Chr[10], Chr[13]
&
Chr[10]]"

My original data were imported/stored in Field1. After running the
query,
access stored them in Field 4. Fields 1 - 3 are now all filled with 0.
The
lay-out however remains the same.

 

[Guest]

Douglas

I copied the text below. However I got an error message, the rows with cells
that contained chr(10) are all deleted if I ignore the error message
(conversion of the type, key conflicts and validation are mentioned in the
error message).

Regards

Hans

Change that to:

UPDATE Blad1 SET Veld1 = Replace(Veld1, Chr(10), Chr(13) & Chr(10)), Veld2
= Replace(Veld2, Chr(10), Chr(13) & Chr(10)), Veld3 = Replace(Veld3,
Chr(10), Chr(13) & Chr(10));



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

UPDATE Blad1 SET Blad1.Veld1 = "UPDATE Blad1 SET Veld1"="Replace[Veld1,
Chr[10], Chr[13] & Chr[10]]", Blad1.Veld2 = "UPDATE Blad1 SET
Veld2"="Replace[Veld2, Chr[10], Chr[13] & Chr[10]]", Blad1.Veld3 =
"UPDATE
Blad1 SET Veld3"="Replace[Veld3, Chr[10], Chr[13] & Chr[10]]";

Go to the SQL View (you can get to it through the View menu when the
query's
open) and copy the SQL into your response.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


My query looks like:

Field Field1
Table Table1
Change "UPDATE Table1 SET Field1"="Replace[Field1, Chr[10], Chr[13]
&
Chr[10]]"

My original data were imported/stored in Field1. After running the
query,
access stored them in Field 4. Fields 1 - 3 are now all filled with 0.
The
lay-out however remains the same.

 

[Guest]

Douglas

I made a small adaptation that seems to function very well

UPDATE Blad1 SET Blad1.Veld1 = Replace(Blad1.Veld1, Chr(10), Chr(13) &
Chr(10)), Blad1.Veld2 = Replace(Blad1.Veld2, Chr(10), Chr(13) & Chr(10)),
Blad1.Veld3 = Replace(Blad1.Veld3, Chr(10), Chr(13) & Chr(10));

Anyway, thanks for the help!

Regards

Hans