AlphaDAO Import

[Walter]

Hi,

I am importing data from a user selected Excel file via AlphaDAO
On my development machine it creates the following data types when importing the data to the dbf file:
1. chr
2. exponentnumeric
3. exponentnumeric
3. chr
4. chr

Based on the above settings, everything is working fine.

When I test the exact same thing on 2 other machines with the same excel file the data types created in the dbf files are:
1. chr
2. chr
3. chr
3. chr
4. chr

causing a "field and expression types must match error" when starting the append operations.
I have tried to overwrite the data type settings in the "local table field defenition" on the AlphaDAO settings, also no luck.

I have also tried to change the data type in the excel file with this code - also not working:

Dim xlApp as p 
xlApp = ole.create("Excel.Application") 
xlApp.Workbooks.Open(vFileName) 
xlApp.Columns("A:I").Select()
xlApp.Selection.NumberFormat = "@"
xlapp.DisplayAlerts = .F. ' XL will not display warning regarding overwriting file
xlApp.ActiveWorkbook.SaveAs(vFileName)
xlApp.Workbooks.Close()
xlapp.DisplayAlerts = .T.
xlApp.Quit()

Is there another way I can make sure the imported data from the Excel files are either all chr fields, or the field types as mentioned at the beginning.

I really need a consistent import operation, as the user are importing loads of excel files daily - all the same format.

Any ideas please?

Walter

[Al Buchholz]

Re: AlphaDAO Import

Walter

Hi, how are you?

Look at the ADO import operation and use the override field definitions to make sure that you get what you want.....

regards

[Walter]

Re: AlphaDAO Import

Hello Al,

Good and you?
This Excel import is getting the better of me the past day or so.

I have tried the override, but am also getting the field type must match error.
""Error 1091 - Field types don't match
while converting ResultSet to DBF table""

Is there any other way I can "Force" the data types to all character maybe, without havng to change the Excel files??

Regards
Walter

[Walter]

Re: AlphaDAO Import

Hi all,

Managed to resolve the issue with a bit of work in the excel file itself.

To convert a column to the correct format I did the following:

1. Column values to text values:
- Format the whole column to "text"
- Go to the cell value, press "F2" then "Enter" on the keyboard. This will change the cell value to a text value.

2. Column values to number value:
- Create a new column and format it to number
- add the number "1" in all the cells in the new column(copy and paste),
- copy the data only from the "old" column(not the whole column, just the data inside the column)
- use "Paste Special" to paste the data into the new column, and under the paste special properties, use "multiply". This would convert the values in the old column to NUMBER in the new column. The just remove the old column and save and import the data into Alpha.

It is a bit tedious, but I have not been able to find another way to get consistent import results.

Maybe there are much better ways out there, but it would help out if you are under pressure.

Regards
Walter

[Sed]

Re: AlphaDAO Import Excel and Text format

Hi,

I have experienced similar issues like Walter's. And have like him not found a quick solution other than pressing F2 and entering to ensure that a number is saved as text.
What is strange is that the sheet is in text format, but yet alpha only picks up on the data that have a "green" corner in the upper left part of the cell.

Has anyone found a quicker solution than having to press F2 and enter to ensure that the information is imported correctly into alpha. Some of my sheets have more than 3000 entries.

Thanks
Sed

[Doug Page]

Re: AlphaDAO Import

Without getting into macros (and you could probably find some using google), I would:

1) insert 2 columns next to the one I was converting
2) use a function like TEXT to create a formula to convert the number to the format you want.
3) Copy and paste that equation into the whole first column
4) select that whole column of text, move my cursor to the start of the next column and do a PASTE SPECIAL and select Values
5) Delete the first 2 columns

HTH

[Sed]

Re: AlphaDAO Import

Thanks,

I'll give you solution a shot..... seems like it could be my last resort.

Sed:

This is what happened in the meanwhile before i noticed Doug Page's post w.r.t my dilemma

FYI: ironically when i used the alpha import ado genie and "forced" the change of field type when importing the table i.e. forced the field type to character, only the data that did not have a "green corner" in the upper left of the cell was imported - so it's a no win situation.

[Stan Mathews]

Re: AlphaDAO Import

2. Column values to number value:
- Create a new column and format it to number
- add the number "1" in all the cells in the new column(copy and paste),
- copy the data only from the "old" column(not the whole column, just the data inside the column)
- use "Paste Special" to paste the data into the new column, and under the paste special properties, use "multiply". This would convert the values in the old column to NUMBER in the new column. The just remove the old column and save and import the data into Alpha.

FUNCTION tonum AS L (filename AS C, column AS C )
Dim xlApp as p 
xlApp = ole.create("Excel.Application") 
xlApp.Workbooks.Open(filename) 
'xlApp.Visible = .T. 'comment this line out if you don't want to see Excel 
lastrow = xlApp.Activecell.SpecialCells("11").Row
xlApp.Range(column+"2:"+column+alltrim(str(lastrow))).Select() 'the "2" assumes a column text header (fieldname), 1 if not
xlApp.Selection.NumberFormat = "0" ' choose your own
for i = 1 to lastrow
	xlApp.Range(column+alltrim(str(i))).Select()
	xlApp.Selection.Value = xlApp.Selection.Value
next i
xlapp.DisplayAlerts = .F.
xlApp.ActiveWorkbook.SaveAs(filename,33) '33 is a constant for Excel 4.0, dbf4 is 11
xlApp.Workbooks.Close()
xlapp.DisplayAlerts = .T.
xlApp.Quit() 
END FUNCTION

[Stan Mathews]

Re: AlphaDAO Import

Try this for the text columns.

FUNCTION totxt AS L (filename AS C, column AS C )
Dim xlApp as p 
dim myCell as c 
xlApp = ole.create("Excel.Application") 
xlApp.Workbooks.Open(filename) 
xlApp.Visible = .T. 'comment this line out if you don't want to see Excel 
lastrow = xlApp.Activecell.SpecialCells("11").Row
xlApp.Range(column+"2:"+column+alltrim(str(lastrow))).Select()
xlApp.Selection.NumberFormat = "@"
xlapp.DisplayAlerts = .F.
xlApp.ActiveWorkbook.SaveAs(filename,33) '33 is a constant for Excel 4.0, dbf4 is 11
xlApp.Workbooks.Close()
xlapp.DisplayAlerts = .T.
xlApp.Quit() 
END FUNCTION

[Sed]

Re: AlphaDAO Import

Thanks Doug,

Your fix works.

I have a question for Stan and his solution: Thanks for posting it, am I to include this function in my excel I suppose and not in Alpha? I'm quite green at coding in vbscript, am i understanding you correctly?

Thanks again
Sed

[Stan Mathews]

Re: AlphaDAO Import

NO. Both Alpha udfs take a filename and a column designation as parameters. You supply those and Alpha will convert the data in the designated column to the desired type.

totxt("C:\temp.xls","B")

should open "C:\temp.xls" and change the values in column B to text

tonum("C:\temp.xls","A")

should open "C:\temp.xls" and change the values in column A to numeric.

Both could be inline xbasic on a button onpush event before an import was run.

[Sed]

Re: AlphaDAO Import

Thanks a lot Walter . Much appreciated. :)

Fantastic. Got it.

Sed