Error in importing data

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I often get a "SF_Export_ImportErrors" table when I try to import data into my SF_Export table.

This error occurs due to one field, country_code. country_code is of text type
e.g. of values for country_code are

160
170
180 an
540A

Alll rows returned with errors are records with 540A as its country_code

Please kindly advise if there is any settings I can make when importing the .csv to prevent this error from occuring

Thanks & regard
yann
 
Yann,

Access has this funny habit of "deciding" on the field
type of linked files... If you are trying to import from
an Excel file, chances are your problem is in Access
reading the linked file, before it even tries to append..
open the linked file to see if it reads the 540A's
correctly... if not, you might try to set the field type
to text in Excel for the whole column.
Past this point, problems in the actual append action:
I get this kind of problem all the time with linked Excel
sheets, so I have found ways to deal with it... what I do
is create a query that reads the linked table, and use an
expression Str([Field]) on the problematic field instead
of the field itself, so as to make sure it gets accross as
text, and then import from the query rather than from the
field directly (or do it straight in my append query) .
Not sure it will solve your problem, I don't even know
it's Excel for sure...but I thought it's woth a shot.

HTH,
Nikos
-----Original Message-----
I often get a "SF_Export_ImportErrors" table when I try
Click to expand...
to import data into my SF_Export table.
This error occurs due to one field, country_code. country_code is of text type.
e.g. of values for country_code are:

160,
170,
180 and
540A

Alll rows returned with errors are records with 540A as its country_code.

Please kindly advise if there is any settings I can make
Click to expand...
when importing the .csv to prevent this error from
occuring?
 
Dear Nikos

Thanks for your help.

Sadly,I am still getting the same error after I've set the field type to text in Excel

Would u mind being more specific about the query u mentioned u created to read the linked table? I dun really understand wat needs to be done. FYI, my excel file is not a linked file

Thanks & regard
yan
 
Hi Yann,

I think that what's happening is that Access sees that the first rows of
data contain numeric values for country_code, and therefore imports it
as a number field. Then it has problems when it encounters a non-numeric
value.

You can work round this in any of the following ways:

1) create the table yourself with the field types you need, then import
the spreadsheet data. The field names in the table must exactly match
the column headings in Excel.

2) make sure that at least one row near the top of the Excel table
contains values that can only be interpreted as the data types you need
(e.g. text that cannot be interpreted as a number if you want the column
to become a text field). Sometimes the simplest way to do this is to
insert a first row of "dummy" data into Excel just for this, and then
delete it from the Access table once the data has been imported.

3) Access assigns field types on the basis of the data it finds in the
first dozen or so rows of the spreadsheet table. It pays no attention to
cell formats. Sometimes a useful trick is to put an apostrophe ' in
front of numeric values in thecells (e.g. '999): this forces Excel and
Access to treat them as text, but the apostrophe is not displayed in
Excel or imported into Access. These little functions will add and
remove apostrophes:

Sub AddApostrophes()
Dim C As Excel.Range
For Each C In Application.Selection.Cells
If IsNumeric(C.Formula) Then
C.Formula = "'" & C.Formula
End If
Next
End Sub

Sub RemoveApostrophes()
Dim C As Excel.Range
For Each C In Application.Selection.Cells
C.Formula = C.Formula
Next
End Sub

(Just to make things more confusing, Access applies different rules
when you're linking Excel data rather than importing it. Simplifying
somewhat: when importing, any text value in the first few rows will
cause a field to be imported as text. When linking, any *numeric* value
in the first few rows will cause a field to be linked as numeric even if
all the other values are non-numeric.)

3) Write your own import code using Automation to get the values direct
from the worksheet cells and recordset operations or queries to append
them into your table.
 
Dear John

Thanks for your reply
FYI, work around 1 doesn't work. I thot this would solve the problem, but apparently, access redetermine the field type while exporting the first dozen of data from excel

work around 2 works, but I need it to be automated, as my user will be simply clicking a button and i'll import the tables for them. Will try out work around 3. Thanks for even including the 2 functions. :

Thank you

yan

----- John Nurick wrote: ----

Hi Yann

I think that what's happening is that Access sees that the first rows o
data contain numeric values for country_code, and therefore imports i
as a number field. Then it has problems when it encounters a non-numeri
value.

You can work round this in any of the following ways

1) create the table yourself with the field types you need, then impor
the spreadsheet data. The field names in the table must exactly matc
the column headings in Excel

2) make sure that at least one row near the top of the Excel tabl
contains values that can only be interpreted as the data types you nee
(e.g. text that cannot be interpreted as a number if you want the colum
to become a text field). Sometimes the simplest way to do this is t
insert a first row of "dummy" data into Excel just for this, and the
delete it from the Access table once the data has been imported

3) Access assigns field types on the basis of the data it finds in th
first dozen or so rows of the spreadsheet table. It pays no attention t
cell formats. Sometimes a useful trick is to put an apostrophe ' i
front of numeric values in thecells (e.g. '999): this forces Excel an
Access to treat them as text, but the apostrophe is not displayed i
Excel or imported into Access. These little functions will add an
remove apostrophes

Sub AddApostrophes(
Dim C As Excel.Rang
For Each C In Application.Selection.Cell
If IsNumeric(C.Formula) The
C.Formula = "'" & C.Formul
End I
Nex
End Su

Sub RemoveApostrophes(
Dim C As Excel.Rang
For Each C In Application.Selection.Cell
C.Formula = C.Formul
Nex
End Su

(Just to make things more confusing, Access applies different rule
when you're linking Excel data rather than importing it. Simplifyin
somewhat: when importing, any text value in the first few rows wil
cause a field to be imported as text. When linking, any *numeric* valu
in the first few rows will cause a field to be linked as numeric even i
all the other values are non-numeric.

3) Write your own import code using Automation to get the values direc
from the worksheet cells and recordset operations or queries to appen
them into your table
 
Finally found a way that works. You simply have to create an import specification, that specify what is the data type of each field to be imported as. Save the specification and u can call that specification everytime u need to import data into the same table structure.

How to create specification?
1) select Get External Data -> Import from File menu.
2) select the file to be imported.
3) Click Advanced... button on left bottom area of dialog box.
4) set the data type for each field name to be imported
5) Click Save As... to save specification.

Next time when u need to import table into table, simply follow step 1 to 3 as above, but this time click Specs... to select the specs you have saved previously and the data will be imported as specified in the specs.

U can also used this saved specification when using docmd.transfertext in VBA.

Hope this helps for people facing the same problem as I had been facing.

yann
 
Back
Top