how to Import Excel file- Expert suggestion VBA

[MUKESHY12390]

Hello frnds ,

I am facing problem while importing data from Excel to Access.I know to how to import excel file by manually and through vba .

trying to import range(A1:A10) to access and this range hold four type of data .

1)blank
2)Number
3)blank ( length is zero but when I do CTR+Down cursor stop here)
4)number ( number having text format)


I am not able to import last two (3 and 4 ) type of data in access.I have written code to solve this problem but macro checks each cell and correct the format and again this is little time consuming.(originally I have 68 columns with 48000 rows)

any suggestion would be appreciate .

 

[Trevor G]

What code are you using? Have you considered using an import into a temp table (Whole Sheet) then use a query to extract and append to the table you want, then use a delete object and combine all into a macro.

 

[xenou]

As a rule, if you are importing numeric data from Excel you MUST have numbers in the first rows, not blanks or numbers stored as text. For a workaround, you can fill the first 10 rows with dummy numbers before importing, any number that will never be in your real dataset, such as -9999999. After importing, delete the rows with that number so that your dummy data is no longer in the table. Access will use the first rows to determine the datatype for all the rows. You may still have import errors if there are blanks, string data, or numbers stored as text - you will have to test. It is best to have numbers, and only real numbers, in the data being imported as numeric data. Otherwise, it is hard to be sure of the results.

 

[xenou]

Following up on Trevor's idea, it is not uncommon to take that approach. In that case, you might have the temp table use all TEXT fields, so that at the first step you know you have all the data in the temp table. Then you can do what you need with the data to clean it up.

 

[MUKESHY12390]

thank you Trevor and Xenou for reply.

@ Trevor G - I am doing same process , first import to "temTable" then append it to target table.

@ Xenou -No chance user can edit table.

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tmp_mksfile", oPath, True

'current solution which I have that Before importing data format all cell.
Below code is just for number column.
'''
Set of = ows.Rows("1:1").Find(What:=str1, LookAt:=xlWhole)
 oExcel.ActiveWindow.WindowState = 1
If Not of Is Nothing Then
ocolmn = of.Column
    For i = 2 To lr
        '  Application.ScreenUpdating = True
        If Len(ows.Cells(i, ocolmn)) = 0 Then
            ows.Cells(i, ocolmn) = ""
        End If
        
        Text_oVALUE = VBA.CDbl(ows.Cells(i, ocolmn))
        
        ows.Cells(i, ocolmn) = Text_oVALUE
Next i

 

[xenou]

You might do a find and replace and get it done all at once rather than looping one cell at a time. Or go with Trevor's idea and import to a temp table, then use an UPDATE statement to fix the blanks (whether Null or Empty String)

 

[MUKESHY12390]

thanks for reply . I'll try your suggestion.

-Trevor's idea and import to a temp table, then use an UPDATE statement to fix the blanks (whether Null or Empty String) --- I am already using this method but it isn't seem working.

 

[xenou]

Hi,
Post your code (if possible), and a 6 or 7 rows of sample data (if possible) so we can test and replicate the problem.