How do I change data type in importing field from Excel?

Topic

New Reply

Hi there

I’m using Access 2007 to import data from Excel using VBA. I have this code:

Private Sub cmd1Importa_Click()
Dim myRec As DAO.Recordset
Dim strQry As String
Dim dbExcel As DAO.Database
Dim rsExcel As DAO.Recordset

Set myRec = CurrentDb.OpenRecordset(“Tabela1”)

Set dbExcel = OpenDatabase(“C:BasesTestesTestesTabelaParaImportar.xlsx”, False, True, “Excel 12.0; IMEX=1;”)
Set rsExcel = dbExcel.OpenRecordset(“Plan1$”)

Do While Not rsExcel.EOF
myRec.AddNew
myRec.Fields(“Coisa1”) = rsExcel.Fields(“Coisa1”)
myRec.Fields(“Coisa2”) = rsExcel.Fields(“Coisa2”)
myRec.Fields(“Coisa3”) = rsExcel.Fields(“Coisa3”)
myRec.Update

rsExcel.MoveNext
Loop
End Sub

But the ODBC driver reads only the first 16 records and chose for the column 3 (Coisa3) data type String with 255 characters max. And in the worksheet that I have texts with over 255 characters that are not being imported. I can choose the data type of the third column as memo? How?

Thanks.

Reply | Quote

Replies

Why don’t you use DoCmd.TransferSpreadsheet to import into a table, then use an append query to copy from the imported table to the table you require.

Reply | Quote

Hi patt!
Thank you for you attention. I have a complex system and need validate data before importing. I import the spreadsheet that has in fact 72 columns and can reach more than 10,000 lines. Then I check data and answer with personalized messages to user. For example I say the row and columm where there is a no valid information in the sheet and abort the operation to the user can correct and try again later. I also format some columns concatenate some data and despite having a single worksheet in Excel I divide the information into three different tables. For this reason I need to do that anyway.

But I have only the problem in the description column which sometimes I have more than 255 characters, and the Jet 4.0 engine brings only 255 because does not recognize the field as Memo in Excel.

Reply | Quote

This Article gives the reason for the problem but as far as I can tell it does not provide a solution in your situation. :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Yes, that worries me a month. I thought someone would know of a change DataTypes in arecordset open. Or maybe how to choose them for the opening.

Reply | Quote

I think the main point here is that changing your code isn’t going to solve the problem as the Jet 4.0 engine will only pass 255 characters. :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

I have built a few apps that import from Excel to Access, but I chose to do it using .Net (Windows Forms apps). Anyway, what .net allows me to do is to open an Excel app and navigate through the data, cell to cell, obtaining the individual values of each cell, and then build the appropriate SQL statements to add each record to the database, using ADO. As this uses the Excel object model, you can do it from VBA too and I think you will find that you won’t have any problems with that.

Reply | Quote

Hi ruirib,
Normally use DAO I’m not familiar with ADO. But I can do. Would you know how performance is that? Why do the way is almost instantaneous to import 10,000 in WinXP with a Core Duo and 2 GB takes about 14 seconds. If I rewrite all the code this would be a big job and very time demanding for me. I’m afraid of ending up with a very slow process. What do you think?

Reply | Quote

Well this approach will take more time, definitively, as each cell in Excel is accessed individually. How does that compare to what you do, I really can’t tell. I chose this independent app route, because the excel sheets in question are not simple, they do aren’t just sheets continous rows of cells with data, so the “database approach” wouldn’t work. I have the impression that it won’t be slow to the point of making this a non option for you, but I can’t simply state that beyond any doubt.

You can use DAO to write the resulting records to Access, that is not a problem. I never use DAO and I think I never used it, really.

Reply | Quote

Then I try… But in a first time I found a temporary solution. Changing the windows registry in the key Office:

HKEY_LOCAL_MACHINE_SOFTWARE Microsoft Office 12.0 Access Connectivity Engine Engines Excel TypeGuessRows = 0 (decimal)

Putting the value to 0 Jet shall read the entire column before define the data type. Or almost. Verily read the line up 15,000 or so. So while I do not have spreadsheets with over 15,000 lines will have no problem.

Just one last detail! Anyone know where is this key in Win 7??

Reply | Quote

I have solved this problem a couple of ways. one is to open the excel file and export it to a tab delimited text file then import that to an access table.

Another that may not be practical for you is that I use SQL Server Express. It has a SQL Server Import and Export wizard. You can select an excel file to export and at the point that you select the sheet to import, there is an option to edit mappings. You can override the default 255 to as much as 4000.

SQL Server Express is free and you can map an access table to a SQL Server table. You may have to use SQL Server Express as your database to get around the restrictions that Access has.

Reply | Quote

I usually do this by setting up a delimited text import specification. Setting up a spec allows you to change the data type when importing into a table. This usually means importing a tab delimited CSV file. Then i use Vba to import the file while calling the specification such as,

DoCmd.TransferText acImportDelim, “Import Specification”, strCourse, strInputFileName, True

Reply | Quote

Hi,
This reply might be a bit late…
It is possible to import large cells with over 255 characters from Excel into MS Access with an ADO connection.

Here is a demo :

Function From_Excel_To_Table()
‘Demonstrate the use of ADO recordset to import large MS Excel cells into MS Access.
Dim cnx As ADODB.Connection
Dim rst As ADODB.Recordset
Dim strConnection As String
Dim strPath As String
Dim r As Integer
Dim c As Integer

strPath = “C:Temptest.xls”

Set cnx = New ADODB.Connection
Set rst = New ADODB.Recordset

‘ADO Connection string.
strConnection = “Provider=Microsoft.Jet.OLEDB.4.0;” & _
“Data Source=” & strPath & “;” & _
“Extended Properties=””Excel 8.0;MAXSCANROWS=16;”””
cnx.CursorLocation = adUseClient
cnx.Open strConnection

‘The MS Excel sheet is retrieved as a recordset
rst.Open “Select * from [Sheet2$]”, cnx, adOpenStatic

‘Per default, the first line of data is considered as the one with the field names.
‘Read all rows, all columns and show in immediate window
For r = 1 To rst.RecordCount
For c = 0 To rst.Fields.Count – 1
Debug.Print r, rst.Fields.Item(c).Name, rst.Fields.Item(c).Value, “vartype: “, VarType(rst.Fields.Item(c).Value)
Next c
rst.MoveNext
Next r

rst.Close
cnx.Close

Set rst = Nothing
Set cnx = Nothing

End Function

As you can see, the large text shows in full (> 255 chars) in the debug window and the VarType is 8 = Text string

You can use this method to read your large cells into a string variable. VBA strings can be over 255 chars.

Reply | Quote