Results 1 to 13 of 13
  1. #1
    New Lounger
    Join Date
    Jan 2012
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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:\BasesTestes\Testes\TabelaParaImpo rtar.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.

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,537
    Thanks
    0
    Thanked 23 Times in 23 Posts
    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.

  3. #3
    New Lounger
    Join Date
    Jan 2012
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.

  4. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,176
    Thanks
    200
    Thanked 781 Times in 715 Posts
    This Article gives the reason for the problem but as far as I can tell it does not provide a solution in your situation.
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  5. #5
    New Lounger
    Join Date
    Jan 2012
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.

  6. #6
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,176
    Thanks
    200
    Thanked 781 Times in 715 Posts
    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.
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  7. #7
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    10,272
    Thanks
    130
    Thanked 1,153 Times in 1,062 Posts
    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.

  8. #8
    New Lounger
    Join Date
    Jan 2012
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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?

  9. #9
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    10,272
    Thanks
    130
    Thanked 1,153 Times in 1,062 Posts
    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.

  10. #10
    New Lounger
    Join Date
    Jan 2012
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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??

  11. #11
    New Lounger
    Join Date
    Dec 2009
    Location
    Luling, LA
    Posts
    6
    Thanks
    0
    Thanked 2 Times in 2 Posts
    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.

  12. #12
    New Lounger
    Join Date
    Dec 2009
    Location
    Minnesota
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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
    “My father says almost the whole world’s asleep. Everybody you know, everybody you see, everybody you talk to. He says only a few people are awake. And they live in a state of constant total amazement.”

    “ From the Movie ‘Joe Vs The Volcano’

  13. #13
    New Lounger
    Join Date
    Jun 2012
    Location
    Belgium
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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:\Temp\test.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.
    Last edited by Prolix; 2012-06-07 at 03:29.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •