alphas rejected on import excel initial numeric column 1

[PizMac]

I've seen various posts re this subject but can't find a solution out there that doesn't involve either amending the registry (and I can't do that at my client's site) or manual intervention (and we don't want that). I am importing an excel file via transerSpreadsheet in code behind a form, and if a column starts off numeric Access assumes it's so and then rejects any subsequent non-numerics it finds. I know if I make the 1st row non-numeric then it's fine, but these files come from an external source and we don't want any manual intervention. I am thinking I may have to resort to writing a VB exe and calling it from my Access code to convert excel to csv (all quoted) first but that seems a bit overkill for such a simple task. And remember - you can't set up a specification for Excel files so that doesn't help. If anyone's got a solution I'd be grateful.

 

[Remou]

Can you not import into an existing table? If necessary, the table can be built using a link or connection to the spreadsheet to get the number and type of columns.

 

[PizMac]

the form imports many files, all different format, some have column headings some not, and differing data. I would happily make all columns text and work around that if I could. I'm interested in your statement "If necessary, the table can be built using a link or connection to the spreadsheet to get the number and type of columns. " - how would I do that in code without any user intervention. I have the file path and name in a variable.
Thanks

 

[Remou]

You can use TransferSpreadsheet to link as well as to import. It is also possible to use ADO to connect to a spreadsheet. However, "some have column headings some not" is more of a problem. I do not think that there is any way in code to tell if a spreadsheet has or has not got column headings.

Here is a start.

Dim rs As DAO.Recordset

XLPath = "C:\Docs\"
XLName = "TT"
DoCmd.TransferSpreadsheet acLink, acSpreadsheetTypeExcel9, "TempXL", XLPath & XLName & ".xls", True

Set rs = CurrentDb.OpenRecordset("TempXL")

For Each fld In rs.Fields

    strSQL = strSQL & ",[" & fld.Name & "] Text(255)"
    
Next

strSQL = "CREATE TABLE " & XLName & " ( " & Mid(strSQL, 2) & " )"
CurrentDb.Execute strSQL

DoCmd.DeleteObject acTable, "TempXL"

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, XLName, XLPath & XLName & ".xls", True

 

[PizMac]

with a bit of tweaking (move the 'DoCmd.DeleteObject acTable, "TempXL" ' after the 'next' if you're going to use this!) that's just what I wanted!

Thanks a lot - solved a long-term problem

 

[PizMac]

Hi Remou
I hope you're watching this thread still... I thought it was all fixed but I'm still getting import errors even though it is importing to a file that's all text

Here is my code, I've done some research and it seems if there are more than 25 numeric rows then any subsequent alphs are rejected - under 25 and it's OK
Would really aqppreciate any help again - Thanks

Dim rs As DAO.Recordset ' link to xls file
DoCmd.TransferSpreadsheet acLink, acSpreadsheetTypeExcel9, "TempInput", "42067005.xls", 0
set rs = CurrentDb.OpenRecordset("TempInput")
mySQL = ""
For Each myTabFld In rs.Fields
mySQL = mySQL & ",[" & myTabFld.Name & "] Text(255)"
Next
rs.close
DoCmd.DeleteObject acTable, "TempInput" ' delete link
mySQL = "CREATE TABLE tempinput ( " & Mid(mySQL, 2) & " )"
CurrentDb.Execute mySQL
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel97, "TempInput", "42067005.xls", 0

and here's what's in the excel file, problem is last but 1 row, col B:-

82601039069 826023400091 DEBORAH BRUMLOW
82601039070 826023400091 DEBORAH BRUMLOW
82601039071 826023400091 DEBORAH BRUMLOW
82601039072 826023400091 DEBORAH BRUMLOW
82601039073 826023400091 DEBORAH BRUMLOW
82601039074 826023400091 DEBORAH BRUMLOW
82601038998 826040589813 ADRIAN MARTORANA
82601039054 826021420092 PHILIP CROSS
82601039055 826021420092 PHILIP CROSS
82601039056 826021420092 PHILIP CROSS
82601039057 826021420092 PHILIP CROSS
82601039058 826021420092 PHILIP CROSS
82601039059 826023400091 DEBORAH BRUMLOW
82601039060 826023400091 DEBORAH BRUMLOW
82601039061 826023400091 DEBORAH BRUMLOW
82601039062 826023400091 DEBORAH BRUMLOW
82601039063 826023400091 DEBORAH BRUMLOW
82601039064 826023400091 DEBORAH BRUMLOW
82601039065 826023400091 DEBORAH BRUMLOW
82601039066 826023400091 DEBORAH BRUMLOW
82601039067 826023400091 DEBORAH BRUMLOW
82601039068 826023400091 DEBORAH BRUMLOW
82601039061 826023400091 DEBORAH BRUMLOW
82601039062 826023400091 DEBORAH BRUMLOW
82601039064 826023400091 DEBORAH BRUMLOW
82601039067 A826023400091 DEBORAH BRUMLOW
82601039068 826023400091 DEBORAH BRUMLOW

 

[KornGeek]

I've dealt with this in the past by programmatically opening the spreadsheet and checking the first entry in each text column. If the data does not already start with an apostrophe, I prepend one. I then save the data, and then import the spreadsheet. (Unfortunately, I don't have access to the code and it's been a couple of years, so I can't provide specific details on how I did this.)

What this does is forces Access to view those columns as text regardless of the data that is stored in them.

 

[PWise]

This is the way that I work around this kind of problem.

I use a query to link to the Excell with this connection string.

SELECT * from [Excel 5.0;HDR=yes;[b]IMEX=0[/b];DATABASE=PathAndFilenae].[Sheet1$];

Imex=0 access reads the spread sheet correctly

 

[PizMac]

Thanks all for the ideas - I've tried everything but still can't do it - I tried to edit the 1st row and save it as text but if I link to the table I can view the data, spot that's it's numeric - but can't change it and save it - very frustrating!!
I tried this:-

mySQL = "SELECT * from [Excel 5.0;HDR=NO;IMEX=0;DATABASE=FilePathandName].[Sheet1$];"
Set rs = CurrentDb.OpenRecordset(mySQL)
rs.MoveFirst
If IsNumeric(rs!F2) Then rs!F2 = "'" & Mid(Str(rs!F2), 2)

and got "data type conversion error" - I also notice that if I just link to the excel sheet from the menu I can't edit the data so can't get at it that way. HHHEEELLLPPP!!!

 

[PWise]

Try updating directly into the excell sheet

UPDATE [Excel 5.0;HDR=NO;IMEX=0;DATABASE=PathAndFilenae;].[Sheet1$] SET [Sheet1$].F1 = "" & [f1]
WHERE IsNumeric([f1])=True;

 

[PizMac]

I have finally got a solution! it involves invoking Excel from Access and changing the cells in row 1 to text, then saving the excel sheet. You can then use TransferSpreadsheet as normal and all columns will import as text. I will post the code here so that other poor souls like me might not have to trawl the

http://WWW for

what seems like years!

Sub MakeXLtext(ColHdgs) '-1 if sheet has column headings
Dim objXL As Object, objActiveWkb As Object
Dim XLnewInst As Boolean, iCol As Integer, iRow As Integer
if fIsAppRunning("Excel") Then
'fIsAppRunning is a module I got from
'

http://www.mvps.org/access/modules/index.html

Set objXL = GetObject(, "Excel.Application")
XLnewInst = False
Else
Set objXL = CreateObject("Excel.Application")
XLnewInst = True
End If

objXL.Application.Workbooks.Open myFullFile
Set objActiveWkb = objXL.Application.ActiveWorkBook
'loop round every column in row 1 making text
If ColHdgs = -1 Then
iRow = 2
Else
iRow = 1
End If
With objActiveWkb
For iCol = 1 To Worksheets(1).UsedRange.Columns.Count
myTemp = .Worksheets(1).Cells(iRow, iCol)
If IsNumeric(myTemp) Then
myTemp = "'" & Mid(Str(myTemp), 2)
.Worksheets(1).Cells(iRow, iCol) = myTemp
End If
Next iCol
End With

objActiveWkb.close savechanges:=True
If XLnewInst Then objXL.Application.Quit
Set objActiveWkb = Nothing
Set objXL = Nothing

End Sub

Good luck everyone!

 

[PHV]

I'd replace this:
For iCol = 1 To Worksheets(1).UsedRange.Columns.Count
with this:
For iCol = 1 To [!].[/!]Worksheets(1).UsedRange.Columns.Count

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[PizMac]

thanks - wonder how it worked without the . ??