Determine Field types when using SQLSTRINGCONNECT and Excel

[ontsjc]

Hello All,

I'm using SQLSTRINGCONNECT and SQLEXEC to query data from an Excel Sheet. Everything is going fine with the exception that I'm never sure what datatypes I will get from the excel sheet. I would like to return everything as a character or a memo field into the cursor. Significant figures are important for the values returned and decimal places get added and subtracted when the data is returned as a numeric datatype. Is there a way to force it to return only memo fields or characters?

I'm using VFP 9 SP2 and Excel 2010.

Thanks.

 

[Dan Freeman]

Use Cast() in your field list to force data types.

 

[ontsjc]

Thanks danfreeman. I appreciate the response.

 

[Olaf Doschke]

It would be good, if you'd simply post the code and connection string, at least specify the driver you're using to connect to an excel sheet.

With SQLEXEC() you always execute the commands that driver supports, not VFP SQL. Dan could be correct, that the driver you use could offer Cast(), but it does not necessarily exist.

In the first place you depend on the VFP side of SQLEXEC() in how it transforms the ODBC driver result into a foxpro cursor. In the second place you depend on the language the ODBC driver supports to transform data types.

Using a cursoradapter instead of SQLConnect/SQLExec you will be able to use the same driver and connectionstring or DSN and will be able to use the schema definition of the cursoradapter to get the result in the form you want.

Bye, Olaf.

 

[JRB-Bldr]

Alternatively...

Once you get whatever field type Excel decides to provide, you could go through field-by-field and, if not as desired, change the field type to what you want with the ALTER TABLE command.

Or - A bit of a kludge, but with Excel you sometimes have to finagle it to get it to work as needed...

You could always use Excel Automation BEFORE your SQLEXEC() to make certain that ALL of the Excel 'fields' were as desired.

One way that I have found that works when done manually is to Add a new Row just below the column header row (i.e. insert a new row 2) and then put a value into each cell of that new row that is consistent with the field type that you want.

For example:
For Char/Text fields insert a letter such as 'c'

Then after the data is acquired, that new row/record can be deleted.

Good Luck,
JRB-Bldr

 

[Mike Lewis]

Personally, I would go for the simple option:

Get the raw data back from Excel in whatever data types it happen to be. Then, within VFP, copy it it a new cursor, with each field transformed to characters.

Something like this:

SQLEXEC(nHandle, "SELECT * ....", "csrRaw")

SELECT ;
  TRANSFORM(Field1) AS Field1, ;
  TRANSFORM(Field2) AS Field2, ;
  .... ;
  FROM csrRaw INTO csrFinal

That would meet the requirement, provided you know the field names in advance, of course.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[Olaf Doschke]

The problem with the raw data approach may be, that the Excel driver returns bad types, for example truncated values missing decimals. If that's the case a cursoradapter with it's possibility to predefine a schema of the result can help.

Bye, Olaf.

 

[ontsjc]

Wow thanks for the interest. I'll explain my needs a little more (this might be a little long winded, sorry).

I'm dealing with environmental chemistry data. My data sets come to me in a variety of different excel formats. Previously I would just manually make the first row after the column headers a value of 'x' and then highlight my selection and choose save as dbf from the excel menus and then open my files in FoxPro and work with them as happy as can be. Everything (sampling dates, numbers, chemical names) would save over as a character field. Now that I've been upgraded to Office 2010, that doesn't work anymore so I figured I would write a Fox procedure that effectively did the same thing, only perhaps a little slicker. The reason I use character fields instead of just numeric is for analytical precision. A value of 2.60 is different for my purposes than a value of 2.6. Bringing them over as text would preserve the precision (or rather significant figures) of the concentrations.

Here's the snippet of code I culled from the microsoft website and have been trying to alter:

lcConnstr = [Driver=] + ;
[{Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};] + ;
[DBQ=] + lcXLBook

IF !FILE(lcXLBook)
? [Excel file not found]
RETURN .F.
ENDIF
*Connect to the excel file

lnSQLHand = SQLSTRINGCONNECT(lcConnstr)

*-- Connect successful if we are here. Extract data...
lcSQLCmd = [Select * FROM &lcSheet]
lnSuccess = SQLEXEC( lnSQLHand, lcSQLCmd, [xlResults] )
? [SQL Cmd Success:], IIF( lnSuccess > 0, 'Good!', 'Failed' )
IF lnSuccess < 0
LOCAL ARRAY laErr[1]
AERROR( laErr )
? laErr(3)
SQLDISCONNECT(lnSQLHand)
RETURN .F.
ENDIF

SQLDISCONNECT(lnSQLHand)

I am trying to make something general that will just bring in the excel data as is, and since the structures vary I can't rely on knowing the field names ahead of time. I tried opening the excel file via automation and changing all cell types to text before running this code above, but then I loose the date formatting of dates and it just returns the numeric excel dates.

I haven't tried the cursoradapter approch yet.

Thanks for all your ideas.

 

[JRB-Bldr]

One of the benefits of VFP is that there are often a whole variety of equally acceptable methods to accomplish a task involving data manipulation.

"Previously I would just manually make the first row after the column headers a value of 'x' and then highlight my selection and choose save as dbf"

Well some of that would still work.

You could do what you were previously doing manually (i.e. "make the first row after the column headers a value of 'x'") but this time do it with VFP Automation of Excel. Yes it will still work.

But you are correct, the SaveAs DBF no longer exists, however the SaveAs CSV does exist (apparently it is: FileExtStr = ".csv": FileFormatNum = 6). If so, you could continue with the above Excel Automation by saving the Excel data into a CSV file.

NOTE - if you want to try this, first do everything in Excel 2010 manually while recording a Macro. Then when Done, stop recording and examine/Edit the Macro to see what Automation code will be needed.

Once you have the CSV file of the Excel data, then in VFP just create a data table/cursor to receive the data and do an APPEND FROM MyDataFile.csv DELIMITED and work with your data as needed.

Good Luck,
JRB-Bldr

 

[Olaf Doschke]

If you need a general solution working for any sheet, I think you're out of luck. For querying any table and wanting to turn all of it's fields to text the same would be true.

Cursoradapter will help you for a concrete excel file structure only, so this needs to be adapted to each different type of excel sheets. I think you don't get around this.

The added line with x would have the same effect on date columns, eg you would not get out the dates but a numeric value in days since some ultimo date or so. It's still worth a try, but I think that would be the outcome. Maybe I'm wrong.

Your goal also is set a bit wrong from my point of view. Turning all the data into text degrades the data quality. You can't sort a formatted date text in chronological order for example. I'd go about this and try to extract the real structure of the excel columns and introduce a better conversion to the vfp field types than the excel driver in conjunction with SQLPassthrough is capable. That means automating the process of defining the schema of a cursoradapter to get a better data translation of excel to dbf. Not a simple task, but not impossible.

Bye, Olaf.

 

[JRB-Bldr]

The added line with x would have the same effect on date columns, eg you would not get out the dates but a numeric value in days since some ultimo date or so.

Actually what you get is a character representation of the date (much like doing a DTOC()).

Very much like getting a character representation of numbers where all significant digits would be retained on a CSV output.

And once the character dates were acquired into a VFP data table's character fields, doing an ALTER TABLE XLS2VFP ALTER EnterDate D would quickly and easily convert them in VFP for sorting purposes, etc.

Again, there are more than one equally acceptable approaches. Its up to you to decide which works best for you.

Good Luck,
JRB-Bldr

 

[ontsjc]

Well after all your great advice and suggestions, I finally came back to this task and went an entirely differnt way. It probably isn't pretty but it seems to work well for my purposes. I though I'd post it here, not sure why, just because I guess:

PROCEDURE SaveDBF
*Saves an excel sheet as a dbf file

CLOSE ALL
CLEAR ALL
clear

LOCAL lcProgPath,lcinExcel,lcoutDBF,lcOutfile,oleExcelName,i,lcSheetName,lcFinalOut,lnSize,lnNumFiles

*Determine our files and paths
lcProgPath = JUSTPATH(SYS(16,0))
lcProgPath = ALLTRIM(SUBSTR(lcProgPath,ATC(':\',lcProgPath)-1,250))+'\'

lcinExcel = GETFILE('XLS*','Select File','Select',0,'Select File XLS to Save as a DBF')
IF EMPTY(lcinExcel)
RETURN
ENDIF

lcOutfile = STRTRAN(UPPER(JUSTFNAME((lcInExcel))),'.XLS','')

lcoutDBF = PUTFILE('Save DBF:',(lcOutfile),'DBF')
IF EMPTY(lcoutDBF )
RETURN
ENDIF
lcpath = JUSTPATH((lcoutDBF))

*Open the file and read sheet names out
oleExcelName = CREATEOBJECT("Excel.Application")
WITH oleExcelName
.visible = .f.
.displayalerts = .f.
.Workbooks.Open((lcinExcel))
*read sheets into cursor to select which ones to save out
CREATE CURSOR sheets (select L,sheet C(100))
SELECT sheets
For i = 1 To oleExcelName.Sheets.Count
lcSheetName = oleExcelName.Sheets(i).Name
APPEND BLANK
REPLACE sheet WITH lcSheetName
endfor
GOTO top
*Opens a simple form with grid first column checkbox second sheet name that allows me to select which sheet(s) to save out
DO FORM (lcProgPath)+"sheetnames.scx" WITH (lcinExcel)
READ events

SELECT sheets
COUNT FOR sheets.select = .T. TO lnNumFiles
GOTO top
SCAN
IF sheets.select = .T.
lcSheetName = ALLTRIM(Sheets.sheet)
*Like to know where we are
WAIT WINDOW 'Working on Sheet: '+(lcSheetName) NOWAIT noclear
lcFinalOut = STRTRAN(lcoutDBF,'.DBF','')+' '+ALLTRIM(lcSheetName)+'.dbf' &&Tack the sheet name on to the dbf name
lcTempTxt = STRTRAN(lcFinalOut,'.dbf','.csv')
.Sheets((lcSheetName)).Select
nLastColumn = oleExcelName.ActiveSheet.UsedRange.Column - 1 + oleExcelName.ActiveSheet.UsedRange.Columns.Count
*Make sure we have close to the correct cell width no rotated cells or merged cells
.Cells.Select
IF oleExcelName.Selection.WrapText = .T.
.Selection.WrapText = .F.
ENDIF

.Selection.Orientation = 0

IF oleExcelName.selection.mergeCells = .T.
.Selection.MergeCells = .F.
endif

.Cells.EntireColumn.AutoFit

*Create the table to bring in this text file
lcTable = "CREATE TABLE '"+(lcFinalOut)+"' ("
FOR i = 1 to nLastColumn
*get column size
.Range(oleExcelName.Cells(1,(i)), oleExcelName.Cells(1,(i))).Select
lnSize = oleExcelName.Selection.ColumnWidth+10&& added in just to make sure we get everything since excel's column widths are unreliable
lcTable = lcTable+" dc"+ALLTRIM(STR(i,10,0))+" C("+alltrim(STR(lnSize,10,0))+"),"
ENDFOR
lcTable = SUBSTR(lcTable,1,LEN(lcTable)-1)+")"
&lcTable
USE DBF() ALIAS DataInput
.ActiveWorkbook.SaveAs((lcTempTxt),6) &&csv
.ActiveWorkbook.Close
SELECT DataInput
APPEND FROM (lcTempTxt) TYPE csv
DELETE FILE (lcTempTxt) &&Don't leave txt files hanging around
USE IN DataInput
lnNumFiles = lnNumFiles - 1
IF lnNumFiles > 0
.Workbooks.Open((lcinExcel))
endif
ENDIF

ENDSCAN
ENDWITH

oleExcelName = .NULL.
RELEASE oleExcelName
WAIT WINDOW 'Finished' NOWAIT

 

[Olaf Doschke]

ColumnWidth is giving you a roundabout value for the length in chars, quote: "One unit of column width is equal to the width of one character in the Normal style. For proportional fonts, the width of the character 0 (zero) is used."

Also a Column may have much more text in it as the visual width is, think about how excel lets text flow into neighbour cells, if these have no value. (eg open a new sheet, type in "Hello, cruel World!" in A1 and it will overlap into B1. ColumnWidth of that cell will be about 11, even though the value has 19 chars. The simplest you could do is create a cursor with each field being a memo.

Save as CSV of course converts everything into text, so you get to your goal. Be warned though, that not so special characters can cause trouble, for example commas or line breaks within cells.

Bye, Olaf.

 

[brigmar]

I do this as a matter of course within my organization.

Per worksheet, we break it down as:
1: Use OLEDB (excel 2007) with extended properties "HDR=No; IMEX=1". HDR=No prevents excel from naming columns thereby bringing over the column names into the DBF, and IMEX=1 prevents data type analysis and brings over the sheet as all memos.
2: Analyze each column for "known" column headings and build an ALTER TABLE statement to change the structure/rename fields.

In reality, we pause after analysis for fine-tuning by the user, allowing them to refine the column analysis by hand.

Roughly....

  lcConnTemplate = [Provider=Microsoft.ACE.OLEDB.12.0;Data Source=$FILENAME$;Extended Properties="Excel 12.0;HDR=No;IMEX=1";] && Excel 2007 OLEDB
  lcExcelFile = "c:\excel document.xls"
  lcSheet = "Sheet 1"
  .
  .
  .
  ** GET THE RAW DATA FROM THE EXCEL SHEET INTO A CURSOR (tranfile) AS MEMO FIELDS/Floats
  lcConnStr = STRTRAN( m.lcConnTemplate, "$FILENAME$", m.lcExcelFile)
  oCA = CREATEOBJECT("CursorAdapter")
  WITH oCA
    .Alias          = "tranfile"
    .DataSourceType = "ADO"
    oConn = NEWOBJECT("adodb.connection")
    WITH oConn
      .ConnectionString = m.lcConnStr
      .Open( m.lcConnStr )
      oRS = .Execute( "SELECT * FROM ["+m.lcSheet+"$]" )
      lnNumFields = oRS.Fields.Count
    ENDWITH
    IF .CursorFill(,,,oRS)
      GO BOTTOM && This speeds up the translation
    ELSE
      AERROR(laError)
      m.oktocontinue = .F.
      **** ERROR PROCESSING HERE
    ENDIF 
    .CursorDetach
  ENDWITH

This creates the cursor 'tranfile'.
All of the fields are Memos and named F1,F2,F3, etc.
That's our starting point for the analysis.