Import lay-out of excelltable in access possible?

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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))
 
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
 
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?
 
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.
 
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.
 
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]]";


Douglas J. Steele said:
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!)


Hans said:
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.
Click to expand...
Click to expand...
 
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!)


Hans said:
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]]";


Douglas J. Steele said:
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!)


Hans said:
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.
Click to expand...
Click to expand...
Click to expand...
 
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

Douglas J. Steele said:
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!)


Hans said:
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]]";


Douglas J. Steele said:
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.
Click to expand...
Click to expand...
Click to expand...
 
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
 
Back
Top