Importing alphanumeric data into text field

[Dylan Snyder]

I have searched this forum but didn't find the answer, I hope I'm not the only one with this problem.
I have two text fields in a table that are updated through an excel file import. In the excel file, both fields contain data that is mostly numeric, but there are always about a quarter that contain letters as well. When I go to import the excel file, it sets to null any value in the fields that contains letters. If I sort the excel file in descending order for the field, it will import, but I have two fields that do this, so this brings more problems. Does anyone know why you can't just import anything in any order into a text field?

 

[David Eagar]

Have you tried formatting the columns in Excel as text?

 

[gemma-the-husky]

if david's suggestion doesnt work then

insert a new row2, and put some random text at the top of the column - you will get a spurious row imported, but access will treat the columns as text now

or
save it as a csv, then try again - you get more control over csv imports

 

[Dylan Snyder]

This is great advice. And honestly, I hadn't even considered that the problem was with Excel, so this has me thinking in the right direction. I'll try both of the suggestions tomorrow when I reimport the spreadsheet. Thanks.

 

[gemma-the-husky]

its not a problem per se, its just that access gives you no flexibility when you import a spreadsheet - it does what it thinks is correct

if you import a csv file, you can specify the format you want each column to take - so even if every item is numeric, you can still tell access to load it as text

 

[namliam]

Microsoft products trying to think...

Sometimes I really want to chuck it all out the window.... Then again it is one of the reasons why it is sometimes hard to grasp. Thereby allowing us to make a living

 

[David Eagar]

This is great advice. And honestly, I hadn't even considered that the problem was with Excel, so this has me thinking in the right direction. I'll try both of the suggestions tomorrow when I reimport the spreadsheet. Thanks.

Hey, this is an Access forum, of course the problem was going to be with Excel!

 

[G_Hosa_Phat]

Well, I'm glad I came across this forum/thread. I'm currently in the process of developing a VB application that imports data from an Excel spreadsheet to an Access database table. The only problem I have is that, like the OP, I'm having issues where some of the data is getting ignored during the import process. MOST data comes across normally. The issue only appears to be happening in very specific cases, and it's really getting on my nerves.

The data that I'm getting from Excel is basically a text format column, although the original file actually has it set as "GENERAL" format (boo). The column is a ZIP code field, but the original data is entered as "##### ####" (5 characters, a space, and 4 characters). So, because of the space, and because I can't change the original Excel file, we're importing this field as text.

Okay, so where's the problem? Well, if the data does not include the last 4 characters, then Excel assumes it's a numeric value (BOO) and the import routine ignores it completely. It just moves on to the next field as if the field is null. In fact, stepping through my code, that's EXACTLY how it reads the field value. (BOOOO!)

I've tried manually setting the field format in Excel to "TEXT", both for the individual field, and for the entire column. Neither of these options work. Of course, if I manually change the actual data in the field to a text string (like the word "Text"), that comes in just fine.

So, after all of that, my question comes down to this: Is there any way that anyone has found (Access VBA, VB, etc.) to actually get the field value imported in a situation like this without actually modifying the Excel file?

 

[Dylan Snyder]

I still haven't found anything-not in an automated transfer spreadsheet method at least. If you only have one field that does this, you can sort it dwcending in Excel-but if you have multiple fields, thie will not work-At least for me.

 

[gemma-the-husky]

i generally find that having a dummy second row in the spreadsheet works - just put some text - say the word 'text' in any particular column in row 2, and that should force access to treat the column as text, rather than numbers - i've never had a problem with it this way

this isnt a problem as i generally find i need to kill some superfluous rows anyway.

-------
as i say, its a bit more long winded, but saving the spreadsheet as csv first , then gives you absolute control over the import - so you can ignore columns, declare column types etc etc - in import specs.

 

[G_Hosa_Phat]

@Dylan - Well, the problem occurs in multiple records, so that's that then.

@gemma - Thanks for the insight. I may have to do that at some point.

The thing is, though, that the column is already being treated as text. In fact, my VB code specifically identifies the field in Excel as a text field, then skips over the data when it "believes" that the data is something other than text (numeric). It's only on certain records way down in the spreadsheet that the import bombs and skips the value entirely.

 

[G_Hosa_Phat]

A new thought was posed to me by a co-worker:

What if we put the apostrophe character to ALL of the fields in the column of Excel data in question? This won't affect the data in the fields already setup as text, but for the records with "numeric" data, it will force it to a text string.

I tried by manually doing this to one record that was having a problem. I plugged in an apostrophe ahead of the existing field data and Excel gave me it's fun little warning about "This field has numeric data formatted as text... BLAH, BLAH, BLAH". Then I went back to my import function and ran it. Lo and behold, IT WORKED!!!

Now I just need to find a way to do this programatically to prevent the need to manually do this every time we get an Excel file. Heck I can do this to ALL columns that are supposed to be text when imported, and I'd never have this issue again!

 

[Dylan Snyder]

yeah that would work. I know nothing of Excel VBA, so I'm not sure how to do it in excel. you could take len( ) and if it has four characters, insert an apostrophe. THat may work in your case. Or, it may import the apostophe if you do it on the access side. -this may have to be done through excel.-But I'm no expert.

 

[G_Hosa_Phat]

@Dylan - Okay, my post may have been a bit misleading. I'm actually doing the import through a Visual Basic 6 project, not working through Access or Excel VBA. However, I've actually gotten this problem figured out. It's requiring the type of data manipulation I identified in my last post. I'll post the VB code here, and perhaps this can translate to some VBA equivalent that can be used...

Public Function CheckStringData(ByVal sFileName As String, SheetName As String) As Boolean
    On Error GoTo ErrHandler

    Dim I As Integer
    Dim ExApp As New Excel.Application
    Dim NumRows As Long

' ****************************************************************
' ** [+] Check Excel file for numeric values in text fields and **
' **     convert the data format by prepending an apostrophe in **
' **     the Excel field before importing to Access             **
' ****************************************************************

    ExApp.Workbooks.OpenText FileName:=AppInfo.PATH & "\" & sFileName
    ExApp.Worksheets(SheetName).Activate
    NumRows = ExApp.Worksheets(SheetName).UsedRange.Rows.Count

    For I = 1 To NumRows

' ****************************************************************
' ** Change the column letter (ExApp.Range("[column]")... to    **
' ** to the column that you want to check.  This will only      **
' ** prepend the apostrophe to those cells that contain numeric **
' ** data that should be formatted as text/string data.  Fields **
' ** that are already all text/string data will not be changed. **
' ** Repeat for each column you want checked.                   **
' ****************************************************************

        If IsNumeric(ExApp.Range("B" & I).Cells.Value) Then ExApp.Range("B" & I).Cells.Value = "'" & ExApp.Range("B" & I).Cells.Value

    Next I

' ------ Close the Excel file to prevent locking others out ------

    ExApp.Workbooks.Close
    Set ExApp = Nothing

    I = vbNull

    CheckStringData = True
    Exit Function

' ------------------------- Error Handling -----------------------
                            
ErrHandler:
    
    displayError Err.Number & " " & Err.Source, Err.Description & "Download.bas CheckStringData"
    Err.Clear

' ------ Close the Excel file to prevent locking others out ------

    ExApp.Workbooks.Close
    Set ExApp = Nothing

    CheckStringData = False

' ****************************************************************
' ** [-] Verifying Excel data formats for import to Access      **
' ****************************************************************
End Function

The sFileName variable is setup because we have multiple Excel files from which we are pulling data, so we have to do this multiple times. Of course, the SheetName variable is... you guessed it, the name of the worksheet in the Excel file.

We call the function in the much bigger downloading procedure as soon as the Excel file is open, and then move on to actually doing the import. After multiple tests, this seems to be working just fine. Maybe someone can look at this and come up with a VBA version that others can use.

:: NOTE :: Just as an FYI, this implementation will prompt the user to save the changes to the Excel sheet, to which they must click Yes to actually make the changes stick (duh). The only real "drawback" I've found with the function is that, once the save prompt comes up, it then takes focus away from the active window (in my tests, it actually sends the active window to the background). I haven't tried to reassign the focus or to automate the acceptance of the changes because it simply isn't that important to me at this point. If someone else wants to add a snippet to handle those minor annoyances...

 

[Dylan Snyder]

what if you automatically imported the spreadhseet to a temp table, then exported it to the same file, and then imported it back? Would access format it as text when it exported the file, thus "fixing" the problem when it imported the second time? just a thought.