Error importing large excel file

[Joker via AccessMonster.com]

Hello,

I am trying to import a pretty large excel file into Access. It's 598 rows
and goes to column CU. It's giving me the error "An error occurred trying to
import file 'C:\Desktop\Book3.xls'. The file was not imported.". I've set
up a table for all the fields with the correct sizes and I've also tried
setting all fields to memo which I thought would work regaurdless but it
didn't. Is there symbols that won't import into Access? They do use
brackets in the file and the symbol "§" but they seem to import. Any
thoughts? Thanks.

 

[Klatuu]

598 rows is actually a very small file. Going from A to CU is only 99 fields
and Access allows 255 fields.
So those are not the problem.
Do you get a table that shows the errors? Try the import manually. The
error message may tell you it had problems and show the name of the error
table it created. If you don't get the message, it is not data in the
fields.

Now, I have described some things that are probably not the problem, I can't
be sure what is.

 

[Joker via AccessMonster.com]

Thanks for your response. By importing it manually do you mean the
TransferSpreadsheet Method?

598 rows is actually a very small file. Going from A to CU is only 99 fields
and Access allows 255 fields.
So those are not the problem.
Do you get a table that shows the errors? Try the import manually. The
error message may tell you it had problems and show the name of the error
table it created. If you don't get the message, it is not data in the
fields.

Now, I have described some things that are probably not the problem, I can't
be sure what is.

Hello,

[quoted text clipped - 6 lines]

brackets in the file and the symbol "§" but they seem to import. Any
thoughts? Thanks.

 

[Klatuu]

No, I mean by using File, Get External Data, Import. Just to see what the
issue might be.

Are you importing to an existing table or a new table? If to an existing
table, does it have the same number of fields with the same names?
--
Dave Hargis, Microsoft Access MVP

Thanks for your response. By importing it manually do you mean the
TransferSpreadsheet Method?

598 rows is actually a very small file. Going from A to CU is only 99 fields
and Access allows 255 fields.
So those are not the problem.
Do you get a table that shows the errors? Try the import manually. The
error message may tell you it had problems and show the name of the error
table it created. If you don't get the message, it is not data in the
fields.

Now, I have described some things that are probably not the problem, I can't
be sure what is.

Hello,

[quoted text clipped - 6 lines]

brackets in the file and the symbol "§" but they seem to import. Any
thoughts? Thanks.

 

[Joker via AccessMonster.com]

Yet again, thank you. I had been trying to do it that way and it brings up
the wizard. I've tried importing it with creating a table and I've imported
one line which came in fine and used that as a template for my import.
Neither worked.

No, I mean by using File, Get External Data, Import. Just to see what the
issue might be.

Are you importing to an existing table or a new table? If to an existing
table, does it have the same number of fields with the same names?

Thanks for your response. By importing it manually do you mean the
TransferSpreadsheet Method?

[quoted text clipped - 14 lines]

 

[Klatuu]

Well, without the file to work with, I don't know what else I can offer. It
appears you have tried most everything.

The last thing I can think of to try is to copy the data from the current
file to a new file and try to import that.
--
Dave Hargis, Microsoft Access MVP

Yet again, thank you. I had been trying to do it that way and it brings up
the wizard. I've tried importing it with creating a table and I've imported
one line which came in fine and used that as a template for my import.
Neither worked.

No, I mean by using File, Get External Data, Import. Just to see what the
issue might be.

Are you importing to an existing table or a new table? If to an existing
table, does it have the same number of fields with the same names?

Thanks for your response. By importing it manually do you mean the
TransferSpreadsheet Method?

[quoted text clipped - 14 lines]

brackets in the file and the symbol "§" but they seem to import. Any
thoughts? Thanks.

 

[Joker via AccessMonster.com]

Tried that too. Thanks so much for you time and knowledge. I really do
appreciate it.

Well, without the file to work with, I don't know what else I can offer. It
appears you have tried most everything.

The last thing I can think of to try is to copy the data from the current
file to a new file and try to import that.

Yet again, thank you. I had been trying to do it that way and it brings up
the wizard. I've tried importing it with creating a table and I've imported

[quoted text clipped - 11 lines]

 

[Ken Snell \(MVP\)]

If you were to add up the number of characters in all the cells in one row
of the EXCEL file, how many do you get?
--

Ken Snell
<MS ACCESS MVP>

Tried that too. Thanks so much for you time and knowledge. I really do
appreciate it.

Well, without the file to work with, I don't know what else I can offer.
It
appears you have tried most everything.

The last thing I can think of to try is to copy the data from the current
file to a new file and try to import that.

Yet again, thank you. I had been trying to do it that way and it brings
up
the wizard. I've tried importing it with creating a table and I've
imported

[quoted text clipped - 11 lines]

brackets in the file and the symbol "§" but they seem to import.
Any
thoughts? Thanks.

 

[Klatuu]

Good question, Ken.
One other thought.
Try saving the xls file as a csv file and using TransferText.
--
Dave Hargis, Microsoft Access MVP

If you were to add up the number of characters in all the cells in one row
of the EXCEL file, how many do you get?
--

Ken Snell
<MS ACCESS MVP>

Tried that too. Thanks so much for you time and knowledge. I really do
appreciate it.

Well, without the file to work with, I don't know what else I can offer.
It
appears you have tried most everything.

The last thing I can think of to try is to copy the data from the current
file to a new file and try to import that.
Yet again, thank you. I had been trying to do it that way and it brings
up
the wizard. I've tried importing it with creating a table and I've
imported
[quoted text clipped - 11 lines]
brackets in the file and the symbol "§" but they seem to import.
Any
thoughts? Thanks.

 

[Joker via AccessMonster.com]

Ken,

How would I go about doing that? Is there a built in Excel function to do
that? Thanks.

Good question, Ken.
One other thought.
Try saving the xls file as a csv file and using TransferText.

If you were to add up the number of characters in all the cells in one row
of the EXCEL file, how many do you get?

[quoted text clipped - 15 lines]

 

[Klatuu]

With the Excel file open, File, Save As
Then in the Save File Dialog, at the bottom, change the File Type to .csv
--
Dave Hargis, Microsoft Access MVP

Ken,

How would I go about doing that? Is there a built in Excel function to do
that? Thanks.

Good question, Ken.
One other thought.
Try saving the xls file as a csv file and using TransferText.

If you were to add up the number of characters in all the cells in one row
of the EXCEL file, how many do you get?

[quoted text clipped - 15 lines]

Any
thoughts? Thanks.

 

[Joker via AccessMonster.com]

Thanks Klatuu again. I have actually tried it that way too. I was meaning
the way to sum the charicters in an entire range. I know LEN counts
charicters, should I just do lens to all of the fields then do a sum at the
end?

With the Excel file open, File, Save As
Then in the Save File Dialog, at the bottom, change the File Type to .csv

Ken,

[quoted text clipped - 9 lines]

 

[Klatuu]

Actually, what you want to know is the length of any one string in a cell.
so all you need to do is to the Len for the entire column, then you can sort
by the column with the Len function in it. You want to see if there are any
cells that have over 255 characters.

--
Dave Hargis, Microsoft Access MVP

Thanks Klatuu again. I have actually tried it that way too. I was meaning
the way to sum the charicters in an entire range. I know LEN counts
charicters, should I just do lens to all of the fields then do a sum at the
end?

With the Excel file open, File, Save As
Then in the Save File Dialog, at the bottom, change the File Type to .csv

Ken,

[quoted text clipped - 9 lines]

Any
thoughts? Thanks.

 

[Joker via AccessMonster.com]

Yes, there are quite a few that are over 255 characters but I thought
importing them into a memo field, that wouldn't make a difference.

Actually, what you want to know is the length of any one string in a cell.
so all you need to do is to the Len for the entire column, then you can sort
by the column with the Len function in it. You want to see if there are any
cells that have over 255 characters.

Thanks Klatuu again. I have actually tried it that way too. I was meaning
the way to sum the charicters in an entire range. I know LEN counts

[quoted text clipped - 8 lines]

 

[Millicent Wright]

nitro

 

[Klatuu]

Now we are getting into an area with which I am not familiar.
I have not had to do any importing or exporting with memo fields involved,
so I don't know that I can help with that.
I sugges you post a new question specifically asking if and how to import
excel files into memo fields.

--
Dave Hargis, Microsoft Access MVP

Yes, there are quite a few that are over 255 characters but I thought
importing them into a memo field, that wouldn't make a difference.

Actually, what you want to know is the length of any one string in a cell.
so all you need to do is to the Len for the entire column, then you can sort
by the column with the Len function in it. You want to see if there are any
cells that have over 255 characters.

Thanks Klatuu again. I have actually tried it that way too. I was meaning
the way to sum the charicters in an entire range. I know LEN counts

[quoted text clipped - 8 lines]

Any
thoughts? Thanks.

 

[Ken Snell \(MVP\)]

I had this vague recollection that there is a limit of 2000 characters that
can be imported / exported in a single record.... Mind you, it's been a
long time since I've tried to exceed this amount in an export/import.

So, I just ran a test. I set up an EXCEL file with 15 columns, with a text
string of 297 characters in each cell in the columns -- total character
count of 4455. Data imported without problem into a new table -- and the
table set up the data type as Memo. So, 2000 isn't a limit for importing
EXCEL data.

I went back to your first post. You say you're using this path to the file:
C:\Desktop\Book3.xls

I think this path string is wrong. For Windows XP, the Desktop is actually a
subfolder with this path:
C:\Documents and Settings\<user name>\Desktop

If your file is actually on the Desktop, then your path likely is invalid.
Try moving the file to a folder under the C drive (e.g., C:\Temp\) and try
the import again.
--

Ken Snell
<MS ACCESS MVP>

Yes, there are quite a few that are over 255 characters but I thought
importing them into a memo field, that wouldn't make a difference.

Actually, what you want to know is the length of any one string in a cell.
so all you need to do is to the Len for the entire column, then you can
sort
by the column with the Len function in it. You want to see if there are
any
cells that have over 255 characters.

Thanks Klatuu again. I have actually tried it that way too. I was
meaning
the way to sum the charicters in an entire range. I know LEN counts

[quoted text clipped - 8 lines]

Any
thoughts? Thanks.

 

[Klatuu]

The limit for a record in Access is 4000 characters, excluding memo and OLE
fields.
--
Dave Hargis, Microsoft Access MVP

I had this vague recollection that there is a limit of 2000 characters that
can be imported / exported in a single record.... Mind you, it's been a
long time since I've tried to exceed this amount in an export/import.

So, I just ran a test. I set up an EXCEL file with 15 columns, with a text
string of 297 characters in each cell in the columns -- total character
count of 4455. Data imported without problem into a new table -- and the
table set up the data type as Memo. So, 2000 isn't a limit for importing
EXCEL data.

I went back to your first post. You say you're using this path to the file:
C:\Desktop\Book3.xls

I think this path string is wrong. For Windows XP, the Desktop is actually a
subfolder with this path:
C:\Documents and Settings\<user name>\Desktop

If your file is actually on the Desktop, then your path likely is invalid.
Try moving the file to a folder under the C drive (e.g., C:\Temp\) and try
the import again.
--

Ken Snell
<MS ACCESS MVP>

Yes, there are quite a few that are over 255 characters but I thought
importing them into a memo field, that wouldn't make a difference.

Actually, what you want to know is the length of any one string in a cell.
so all you need to do is to the Len for the entire column, then you can
sort
by the column with the Len function in it. You want to see if there are
any
cells that have over 255 characters.

Thanks Klatuu again. I have actually tried it that way too. I was
meaning
the way to sum the charicters in an entire range. I know LEN counts
[quoted text clipped - 8 lines]
Any
thoughts? Thanks.

 

[Ken Snell \(MVP\)]

Oops, yes, forgot to test with less than 255 characters in a cell and then
with lots of cells.... will have to do that later.

 

[Klatuu]

I doubt that is necessary, Ken. The OP stated his spreadsheet goes through
column CU which, if I remember correclty, is column 99. So 99 * 255 is not
even close.
Also, he did say many of the fields were > 255 and he was trying to import
them in to memo fields. Here, my experience fails, as I have never attempted
importing or exporting memo fields.