I would like to import a spreadsheet to an access database, on column has ages 1-89 plus another that's says 90+ which in turn create a Import Error. Using the DoCmd.TransferText, is it possible to import everything as it is including 90+ in the column of all other numbers?
-
is 90 + a separate column, or is it an actual value in the same column? what is the exact error you are getting? you also say spreadsheet, so you want to use Docmd.TransferSpreadsheet unless that was just a typo where you wrote TransferText. Also make sure that the [HasFieldNames] argument is set accordinglyO. Gungor– O. Gungor2017-03-02 03:05:44 +00:00Commented Mar 2, 2017 at 3:05
-
Its in the same coloumn with number 1-89. It creates two tables, one with the data with the 90+ cell blank, as well as a another table shower each row with an import errorA. Kru– A. Kru2017-03-06 18:05:49 +00:00Commented Mar 6, 2017 at 18:05
-
190+ should in itself not cause a problem, it should be treated as an integer. you are not providing adequate information: include a snapshot of the spreadsheet with columns/values included as well as the structure of your destination table in access. also, docm.transfertext will not import excel, you need docmd.transferspreadsheetO. Gungor– O. Gungor2017-03-06 18:28:33 +00:00Commented Mar 6, 2017 at 18:28
Add a comment
|
1 Answer
If you import data into a table that doesn't already exist, Access will create one for you. It automatically determines the data type of each column based on the first few rows that are imported. If your source data contains a mixture of data types in one column, then you may experience this error.
There are 2 solutions:
- Build the import table to be of the correct data type for your data (i.e. specify that the age column is Short Text). Then import to the pre-defined import table.
- Ensure that the CSV or Excel file stores each age as a string i.e.
"20"instead of20(in Excel, format the cell as Text, so it is left aligned or start cell contents with an apostrophe'20or use formulaic notation="20").
You could do either of those things, but it would be better to do both of them if possible.
