Import type xl8 error (data misread)

[baltman]

I'm using the import command to import data and one field in the excel sheet is being converted to a (bad) date. The field has either a "1" or a "2" and is formatted as a number. It is being imported as "12/31/18" or a "01/01/19", which would be the correct date as far a Excel is concerned, but I just want my "1" or "2".

I can fix this myself in code, but I was wondering if there are any fixes for this or other issues with IMPORT since I want my code to be dummy proof, I don't want any surprises.

I'd rather use IMPORT than COM because of the speed/ease...

Thanks
Brian

 

[rgbean]

Brian,
I believe Import doesn't care what format the data is viewed in, but how it was originally entered. (Or more specifically how the first row for that column was entered.)

Rick

 

[baltman]

The 1st row is just a field name. I think the Excel workbook has some un-cleaned up info associated with the column. It's happening even if I change data in the column or move it.

I think the only way to make sure it doesn't happen is to either automate a copy-paste-special-values into a new workbook before the import which would unfortunately going to add a fair amount of run time, or look for a 'date field' with slashes and convert it back to numbers since dates are apparently only imported as numbers (e.g. 37797) anyway.

Brian

 

[baltman]

FYI- the quickest solution appears to be un-formatting the Excel sheet prior to import using the following snippet:

loExcel = CreateObject("Excel.Application&quot
WITH loExcel
.visible=.f.
.Workbooks.Open(VarFileFullPath)
.DisplayAlerts = .f.
.Rows("1:65536&quot.select
.Selection.NumberFormat = "General"
.ActiveSheet.saveas("&varuserlocation\XL_Values.xls&quot
.quit
ENDWITH
loExcel=.null.

SET DEFAULT TO ("&varuserlocation&quot
ERASE temp_import.dbf
IMPORT FROM ("&varuserlocation\XL_Values.xls&quot TYPE XL8
USE RENAME "XL_Values.DBF" to temp_import.dbf