Import from excel and add to table

[Rpettis31]

I have an excel file which is placed on an ftp site with production reports.
I would like to add the data to a table in my access database.

Basically import the data to the database table at the start of the last
record.

 

[Piet Linden]

I have an excel file which is placed on an ftp site with production reports.  
I would like to add the data to a table in my access database.  

Basically import the data to the database table at the start of the last
record.

What? Does TransferSpreadsheet not work for you?

"At the start of the last record"... Doesn't make sense in a
database. Tables are just heaps of records with no inherent
sequence....

 

[Rpettis31]

I just asked a simple question because I do not use access that much, which
is what I thought this forum was for but thanks for your input.
http://msdn.microsoft.com/en-us/library/bb214134.aspx

 

[Klatuu]

Look in Access Help at the TransferSpreadsheet function. It will do exactly
what you want. What Piet was trying to say is that records in a table are
not in any specific order, so if you have to have the records in a specific
order, you will need to use an index of some kind. If you want them in the
order they were added to the table, an autonumber field added to the table
will do that for you.

 

[ryguy7272]

Have you ever seen Ken Snell’s site? KEN is pretty much the KING of
importing and exporting. This should answer most of your importing (and
exporting) questions:
http://www.accessmvp.com/KDSnell/EXCEL_MainPage.htm

This will probably do what you want:
http://www.accessmvp.com/KDSnell/EXCEL_Import.htm#ImpBrowseFile



HTH,
Ryan---

 

[Bill Sturdevant]

Hi,

I have been struggling with an "Import Excel into Access" issue, and was
very excited when I found your post. I went directly to Ken Snell's site and
there is a link to specifically my problem: I cannot seem to import any text
string longer than 255 characters.

The link on Ken's site is:
http://www.accessmvp.com/KDSnell/EXCEL_Import.htm#DataTypeErr

There it says:"The truncated text string that you see is because Jet
(ACCESS) sees only "short text" (text strings no longer than 255 characters)
".

It suggests this as a method to bypass the problem: "2) Create a blank table
into which you will import the spreadsheet's data. For the field that will
receive the "memo" data, make its data type "Memo". Jet (ACCESS) then will
"honor" the field's datatype when it does the import."

As I recall, this worked in Access/Excel 2003. But I am working in
Access/Excel 2007. I created the blank table, and set the field type to
Memo. When I executed the DoCmd.TransferSpreadsheet acImport, and then
looked at the data, even though the field was still defined as Memo (Jet
"honored" the datatype), the data was still truncated to 255 characters!!

Any suggestions on how to get around this in Access 2007?

 

[Ken Snell [MVP]]

What version of EXCEL are you specifying in the TransferSpreadsheet action?

 

[Bill Sturdevant]

Ken -- glad you spotted my post!!!

I have tried both acSpreadsheetTypeExcel9 and acSpreadsheetTypeExcel12.

The first few rows being imported have just a few characters in the column
in question. The truncation occurs in the 34th row.
--
Bill

What version of EXCEL are you specifying in the TransferSpreadsheet action?

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

 

[Bill Sturdevant]

By the way, the file I am trying to import is a .xlsm. I also tried saving
it as a .xlsx and using acSpreadsheetTypeExcel12. Then I saved it as a .xls
and used acSpreadsheetTypeExcel9. The problem occurs in all cases.
--
Bill

What version of EXCEL are you specifying in the TransferSpreadsheet action?

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

 

[Ken Snell [MVP]]

I've confirmed with a tech person at Microsoft that my suggestion of using
an existing table with a Memo datatype field works with ACCESS 2007 as well.

For EXCEL 2007, you do need to use acSpreadsheetTypeExcel12 as the EXCEL
version type.

Would you be willing to send me a zip file of the ACCESS database and the
EXCEL file so that Microsoft and I can test it to see what might be
happening? You'll find an email address at the web page in my signature.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

 

[Bill Sturdevant]

Ken -- Well, THIS is interesting, and annoying...

In preparation for sending you a demo setup, I took my Access 2007 ACCDB and
cut it down to the bare essentials to show you the problem. After doing so,
I tested it to make sure it showed the problem accurately. And it WORKED!!!

In trying to understand why it worked in the cut-down version but not in my
full version, I found out it worked there also!!!

Here is why I was fooled into thinking that acSpreadsheetTypeExcel12 did not
work...

After I changed from acSpreadsheetTypeExcel9 to acSpreadsheetTypeExcel12, I
kept stopping the code and going to my table to see if the full string had
been imported. To do this, I selected the field in the questionable row and
copied it. Then I opened a NotePad file and pasted it. Turns out that, even
though the field in the table has the full string, the only thing that gets
copied and pasted into NotePad (or Word, or Excel) is the first 255
characters, thus making me think the full string had not been imported.

My problem is solved, but what I have described above is truly discouraging!

 

[Ken Snell [MVP]]

If you click on a field and copy it, Windows will only grab the first 255
characters. To get all the characters, click into the field so that you can
copy the characters directly. Start at the beginning of the string, and
press Ctrl+a on keyboard to select entire text, then copy it (or highlight
all the text from beginning to end, then copy it).

This behavior of Windows is "normal".

Glad the import is working correctly after all!

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/