Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

How to save Excel 2010 with Memo fields 1

Status
Not open for further replies.

markros

Programmer
May 21, 2007
3,150
US
In the thread184-1633357 I read about solutions but they don't seem to work for Excel 2010. The memo field is getting truncated. Do you know of any way to get Excel fully output the Memo field for Excel 2010?

Thanks.

PluralSight Learning Library
 
What of that thread did you try? Brigmars code in the last post? With Excel 2010 what you could change is using a newer driver than Microsoft.ACE.OLEDB.12.0, I think now its Microsoft.ACE.OLEDB.14.0

Bye, Olaf.
 
Do you know of any way to get Excel fully output the Memo field for Excel 2010?

Do you mean that you are outputting from Excel?
Or is that a typo?

The memo field is getting truncated

Like Olaf asks above - what specific approach are you using that is causing the Memo field contents to be truncated?

Good Luck,
JRB-Bldr

 
I've tried Craig Boyd's approach AppendFromExcel and a similar approach by Cetin Basoz. In both cases the Memo field got truncated to 255 characters. The first row does not contain data (was empty), but rows down bellow have long text.

I implemented a post-processing of the file to fix that particular column using Excel Automation. I'll try to use 14.0 now.

PluralSight Learning Library
 
I agree to dan, or you only want to state you give up.

Why don't you try Brigmars code? I indirectly suggested to try that. Simple reasoning is, that Brigmar says "I do this as a matter of course within my organization", which hints it will probably work. Just probably.

Bye, Olaf.
 
The last code in that thread is very similar to what I use. For 14.0 it gave me an error 'Provider not found'. I know I've recently installed the latest versions of provider for both 64 and 32 bit versions (as VFP needs 32 bit version).

This is the code I use, but it doesn't work for Excel 2010:
Code:
********************************
*!* Simple Sample Usage
********************************
*!*   DIMENSION aWrkSht(1), aCols(1)
*!*   m.lcXlsFile = GETFILE("Excel:XLS,XLSX,XLSB,XLSM")
*!*   IF FILE(m.lcXlsFile)
*!*      CLEAR
*!*      ?AWorkSheets(@aWrkSht,m.lcXlsFile,.T.)
*!*      ?AWorkSheetColumns(@aCols,m.lcXlsFile,"Sheet1")
*!*      AppendFromExcel(m.lcXlsFile, "Sheet1", "MyTable", "column1,column2,column3", "Recnum Is Not Null", "field1,field2,field3", "field1 > 14000")
*!*      SELECT MyTable
*!*      GO TOP IN "MyTable"
*!*      BROWSE LAST NOWAIT
*!*   ENDIF
*!*   CopyToExcel("C:\Test.xlsx", "Sheet1", "MyTable") && try xls, xlsb, and xlsm as well

**********************************
FUNCTION AppendFromExcel(tcXLSFile, tcSheet, tvWorkarea, tcExcelFieldList, tcExcelWhereExpr, tcTableFieldList, tcTableForExpr, tlNoHeaderRow)
   **********************************
   * PARAMETER Information
   * tcXLSFile := a string specifying an excel file (*.xls, *.xlsx, *.xlsm, *.xlsb) on disk
   * tcSheet := a string specifying the name of a worksheet within the excel workbook (can also be a range Sheet1$A1:C20 for instance)
   * tvWorkarea [optional] := the Alias, Work Area, or File Name of the table you want the worksheet result set appended to (default is currently selected Alias)
   * tcExcelFieldList [optional] := a comma delimited list of columns you want from the worksheet (default is '*' - all columns)
   * tcExcelWhereExpr [optional] := a valid SQL Where clause to be used when querying the worksheet (default is '1=1')
   * tcTableFieldList [optional] :=  a comma delimited list of fields you want the worksheet result set inserted into (default is '*' - all fields)
   * tcTableForExpr [optional] := a valid VFP Where clause to be used when querying the worksheet result set (cursor) (default is '.T.')
   * tlNoHeaderRow [optional] := pass .T. if the worksheet does not contain a header row, .F. is the default which specifies that a header row does exist
   *
   * RETURN Information
   * returns numeric, the number of records inserted into tvWorkArea
   *
   * Provider Information
   * the default provider being used in the SQLStringConnect function can be downloaded and installed from:
   * [URL unfurl="true"]http://www.microsoft.com/downloads/details.aspx?FamilyID=7554F536-8C28-4598-9B72-EF94E038C891&displaylang=en[/URL]
   **********************************
   LOCAL lnSelect, laErr[1], laTableFields[1], laExcelFields[1], lnFieldCounter, ;
      lcSQLAlias, lnResult, lcInsertValues, lcFieldList, lcNvlFieldList, ;
      lcFieldType, lcExcelFieldType, lcNvlFieldName, lcTempAlias, loExc, lnReturn, ;
      lcHeaderRow, llOpenedtvWorkArea, loException as Exception 
   lnSelect = SELECT(0)
   lnReturn = 0
   IF NOT FILE(m.tcXLSFile)
      ERROR 1, m.tcXLSFile
   ENDIF

   IF !USED(m.tvWorkarea) AND TYPE("m.tvWorkArea") = "C" AND FILE(DEFAULTEXT(m.tvWorkarea,"DBF"))
      SELECT 0
      USE (DEFAULTEXT(m.tvWorkarea,"DBF")) SHARED AGAIN
      tvWorkarea = ALIAS()
      llOpenedtvWorkArea = .T.
   ELSE
      IF !USED(m.tvWorkarea)
         tvWorkarea = ALIAS()
      ENDIF
   ENDIF
   IF TYPE("m.tvWorkArea") = "N"
      tvWorkArea = ALIAS(m.tvWorkArea)
   ENDIF
   
   tcSheet = ALLTRIM(EVL(m.tcSheet,"Sheet1$"))
   IF AT("$",m.tcSheet) = 0
      tcSheet = m.tcSheet + "$"
   ENDIF
   tcExcelFieldList = EVL(m.tcExcelFieldList,"*")
   tcExcelWhereExpr = EVL(m.tcExcelWhereExpr,"1=1")
   tcTableFieldList = EVL(m.tcTableFieldList,"*")
   tcTableForExpr = EVL(m.tcTableForExpr,".T.")
   lcSQLAlias = SYS(2015)
   lcTempAlias = SYS(2015)
   lnSQL = -1
   lcHeaderRow = IIF(EMPTY(m.tlNoHeaderRow), "Yes", "No")
   
   TRY
      SELECT (m.tvWorkarea)
      
      Local loConn as ADODB.Connection, loRS as ADODB.Recordset, loCursor as CursorAdapter
      
      loConn = CreateObject("ADODB.Connection")
      loConn.ConnectionString = [Provider=Microsoft.ACE.OLEDB.12.0;Data Source="] + m.tcXLSFile + [";Extended Properties="Excel 12.0;HDR=] + m.lcHeaderRow + [;";]
      =CURSORSETPROP("FetchMemo",.t.,0)

      try
        loConn.Open()
        
      If loConn.State = 1   && connection open
         loRS = CreateObject("ADODB.Recordset")
         loRS.ActiveConnection = loConn
         loCursor = CreateObject("CursorAdapter")
         loCursor.Alias = m.lcSQLAlias
         loCursor.DataSourceType = "ADO"
         loCursor.DataSource = loRS
         loCursor.FetchMemo = .t.
         loCursor.SelectCmd = "SELECT " + m.tcExcelFieldList + " FROM [" + m.tcSheet + "] Where " + m.tcExcelWhereExpr
         lnSQL = 0
         If !loCursor.CursorFill()
            lnSQL = - 1
            aerror(laErr)
            *ERROR m.laErr[2]
            oAppObj.write_log('ADODB Connection CursorFill method resulted in error for ' + m.tcXLSFile + ' sheet ' + m.tcSheet + CHR(13) + ;
                              'Error message is: ' + m.laErr[2], PROGRAM(),'E') 
         EndIf 
      ELSE 
         lnSQL = - 1 
         oAppObj.write_log('ADODB Connection can not be opened for ' + m.tcXLSFile + ' sheet ' + m.tcSheet + CHR(13) + ;
                              'Error message is: ' + m.laErr[2], PROGRAM(),'E')
      EndIf       
      CATCH TO loException 
         lnSQL = - 1
         oAppObj.write_log('ADODB Connection can not be opened for ' + m.tcXLSFile + ' sheet ' + m.tcSheet + CHR(13) + ;
                              'Error message is: ' + loException.Message, PROGRAM(),'E')
                       
      endtry
      if m.lnSQL < 0
         lnSQL = sqlstringconnect([Provider=Microsoft.ACE.OLEDB.12.0;Data Source="] + m.tcXLSFile + [";Extended Properties="Excel 8.0;HDR=] + m.lcHeaderRow + [;";])

      *!* Alternate using DSN that comes with Office install (MSDASQL = OLEDB wrapper for ODBC)
      *!*         m.lnSQL = SQLSTRINGCONNECT("Provider=MSDASQL.1;" ;
      *!*            +"Persist Security Info=False;" ;
      *!*            +"DSN=Excel Files;" ;
      *!*            +"DBQ="+FULLPATH(m.tcXLSFile)+";" ;
      *!*            +"DriverId=790;" ;
      *!*            +"MaxBufferSize=2048;" ;
      *!*            +"PageTimeout=5;")

      *!* Try a few other drivers that may be on the user's machine
         if m.lnSQL < 0

            aerror(laErr)

            oAppObj.write_log('Connection with Provider=Microsoft.ACE.OLEDB.12.0 is failing for ' + m.tcXLSFile + ' sheet ' + m.tcSheet + chr(13) + ;
               'Error message is: ' + m.laErr[2], program(),'E')

            lnSQL = sqlstringconnect("Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};" ;
               + "DBQ="+fullpath(m.tcXLSFile)+";")
            
            if m.lnSQL < 0 and upper(alltrim(justext(m.tcXLSFile))) == "XLS" && can we try using the older driver?
               
            aerror(laErr)

            oAppObj.write_log('Connection with Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)} is failing for ' + m.tcXLSFile + ' sheet ' + m.tcSheet + chr(13) + ;
               'Error message is: ' + m.laErr[2], program(),'E')
               if m.lnSQL < 0
                  lnSQL = sqlstringconnect("Driver={Microsoft Excel Driver (*.xls)};" ;
                     + "DBQ="+fullpath(m.tcXLSFile)+";")
                  
               endif
            endif
            if m.lnSQL < 0
               aerror(m.laErr)

               oAppObj.write_log('Connection with old Driver={Microsoft Excel Driver (*.xls)} is failing for ' + m.tcXLSFile + ' sheet ' + m.tcSheet + chr(13) + ;
               'Error message is: ' + m.laErr[1,2], program(),'E')
                error m.laErr[2]
            endif
         endif
      endif
      
      IF m.lnSQL >=0 AND NOT USED(m.lcSQLAlias) 
         lnResult = SQLEXEC(m.lnSQL,[SELECT ] + m.tcExcelFieldList + [ FROM "] + m.tcSheet + [" Where ] + m.tcExcelWhereExpr, m.lcSQLAlias)
         IF m.lnResult < 0
            AERROR(m.laErr)
            ERROR m.laErr[2]
         ENDIF
      ENDIF

There is more code, but the top code seems to be very similar to that used in the other thread and I verified that the cursor created already has memo fields truncated. BTW, adding IMEX=1 to it makes no difference for truncation.

PluralSight Learning Library
 
Where do you get the error message?

Which of the various attempts to connect is failing?
 
>For 14.0 it gave me an error 'Provider not found'.

That means the provider is not installed, doesn't it?

Did you install the 64bit or 32bit Version of Office 2010? VFP can only work with 32bit dlls, ODBC drivers or OLEDB Providers.

Bye, Olaf.
 
Hm,

googling a bit I find references on Version 14.0, also an Excel Application instance returns 14.0 as oExcel.Version. But I also only find the 12.0 provider here on my office PC.

And I also just use that version this way, which works for me:
Code:
lcConnString = [Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\your\excel\file.xlsx;Extended Properties="Excel 12.0 Xml;HDR=YES";]
loConn = CreateObject('ADODB.Connection')
loConn.Mode = 1  && adModeRead
loConn.Open( lcConnString  )
loSchema = loConn.OpenSchema(20) && adSchemaTables
loDOM = CreateObject('MSXML.DOMDocument')
loSchema.Save(loDOM, 1)
Xmltocursor(loDOM.XML, "curTables")
loSchema.Close()
lcTable = Alltrim(curTables.Table_Name)
loRs = loConn.Execute(RTRIM(Textmerge("Select * From [<<lcTable>>]")))
loDOM = CreateObject('MSXML.DOMDocument')
loRS.Save(loDOM, 1)
Xmltocursor(loDOM.XML, "curExceldata")
loRS.Close()

Note: Excel 12.0 Xml is also correct for Excel 14, as the xml schema didn't change. This uses both the 12.0 provider and msxml.domdocument plus XML features of VFP. It works for me, but I don't know how large your xlsx files are, it imports a few lines, in the order of about 100, quite fast.

This gives me larger text, but also short text into memo fields in the final VFP cursor. But that problem is much easier to cope with than truncated values.

Bye, Olaf.
 
Some more notes about this code:

It determines the tables as first step into curTables. A typical Excel sheet has three sheets, labelled table1, table2, table2 and excels technical table names for these sheets are table1$, table2$, table3$. But that differs in locale versions of Excel! So you better get the table names as excel outputs them via OpenSchema(adSchemaTables).

And you can also read in data from all sheets, if you repeat the loConn.Execute() for all tables in curTables.table_name. If there is more data on further sheets, you also get at this. Overall, this code can be refined, of course.

The main point is, I can read in lengthy texts with this, too. I didn't try all kind of data types, but it works for me for texts, numeric data, and dates/datetime, which already was sufficient for me. Unfortunately you find few info on the type system of Excel and oledb provider sql dialect syntax, but a where clause would also work, using the names of line A in excel.

Bye, Olaf.
 
In the curTables I got some weird entries (in addition to valid ones):

Barcodes$_xlnm#_FilterDatabase

If I try to select from Items$ (name of my worksheet), I got this truncated error

XML Parse error: Syntax error. node "s:Schema" XML = <s:Schema xmlns:s="uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882" id="RowsetSchema">
<s:ElementType na

So, it doesn't seem to work for me (BTW, the field names in the worksheet are two words separated by space such as Matrix Template, etc.)

BTW, I installed OleDB for both - 32 bit and 64 bit.

PluralSight Learning Library
 
How about changing the line 1 field names to not contain spaces, then? The table names can be weird, that's the problem of the oledb provider, as you see from the code the alias of the result cursor is your choice, so that's not a problem. I bet it's spaces in field names.

Bye, Olaf.
 
I'll check without the spaces, but we already defined the format for our spreadsheet.

I'll play with just one sheet and with the names without spaces just for the test.

PluralSight Learning Library
 
Unfortunately, there are some characters that are valid in excel sheet names that are not valid as an oledb table name. Rather than try to translate between the two different sets of legal characters we use automation to rename the sheets to translate1, translate2, etc. before the actual translation.

Also, fields that are greater than 255 characters long are not guaranteed to be converted correctly, although they do contain at least 256 characters. So we use automation in a post process to fix those memo fields that have 256 or more characters.
 
I would also say setting the sheet names and the field names in line1 of each sheet shouldn't be a problem, if it turns out, that this is fixing the read in.
I can only say again, I don't have a problem with memo, I rather have the behaviour all text cells, no matter how short, come in as memo via xmltocursor. Perhaps that has to do with that extra step to save the adodb.recordset (loRS) to XML and read in that XML instead of converting the RS to a vfp cursor, which could also be done via Rs2dbf.prg. I think it's part of VFP COM utils or you download that here:
But actually saving to xml is more modern and perhaps even faster, you have to try it out.

I can live with my code with my xlsx files. Besides name problems of sheets or fields you could also check, if certain types don't come over good.

Bye, Olaf.
 
The above seems like a lot of work to obtain data from a spreadsheet. Also, difficult to debug.

The same could be accomplished by merely creating an excel app instance, loading the spreadsheet into it, and iterating through the cells (assuming you know the table structure) to store what's there to the table, either directly or through an accumulation array.

Code:
lo = CREATEOBJECT("excel.application")
lo.Workbooks.Open("c:\path\myfile.xlsx")
lnRow = 1
DO WHILE lnRow >= 1
    FOR lnCol = 1 to FCOUNT('myTable')
        lcCell = CHR(lnCol + ASC('A') - 1) + ALLTRIM(STR(lnRow,4,0))
        lxValue = lo.Range(lcCell).Value
        IF ISNULL(lxValue)
            * No more data on this row
            lnRow = 0
            EXIT
        ENDIF
        * If we get here, there's data for this field
        * We can either do an explicit replace,
        * or build an insert command, populate an array (probably fastest),
        * or whatever
    NEXT
    * Move to next row
    lnRow = lnRow + 1
ENDDO
lo.Workbooks.Close()
lo.Quit()
* When we get here, we have our data stored to a table, or in
* an array, and it can be handled

Off the top of my head. Might need a little tweaking. Hope this helps.

Best regards,
Rick C. Hodgin
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top