Access "Guesses" incorrectly when creating table to store Spreadsheet data

[MSAccessRookie]

I have the following command that is being used to import an Excel Spreadsheet containing Invoices into an Access Database Table called tblInvoices

DoCmd.TransferSpreadsheet acImport, , "tblInvoices", strSourceInFile, True
​

The import works fine with the exception of the Column that contains the Vendor Invoice Number. The reason for this is that although most of the values in the Vendor Invoice Number Column appear to be Numeric, there are a few (15 so far) that contain Alphanumeric Data. As a result, the Column itself has been defined as Text.

When Access imports the data, it ignores the Text setting, and creates a Number Type Field instead, resulting in 15 conversion errors. Any idea as to what I am doing incorrectly?

 

[SOS]

Re: Access "Guesses" incorrectly when creating table to store Spreadsheet data

Do the initial import to create the table, then go into the table in design view, change the field datatype (or delete the field and add it back with the correct datatype). Then just clear the table and import to the existing table - problem solved.

 

[MSAccessRookie]

Re: Access "Guesses" incorrectly when creating table to store Spreadsheet data

Thank you for taking time to reply. I am sure that would work fine except that my import statement seems to make a new table each time.

 

[SOS]

Re: Access "Guesses" incorrectly when creating table to store Spreadsheet data

except that my import statement seems to make a new table each time.

What code are you using?

 

[MSAccessRookie]

Re: Access "Guesses" incorrectly when creating table to store Spreadsheet data

DoCmd.TransferSpreadsheet acImport, , "tblInvoices", strSourceInFile, True
​

 

[SOS]

Re: Access "Guesses" incorrectly when creating table to store Spreadsheet data

I just tested with that same code and it doesn't replace the table for me. If the table exists it just replaces the data.

So, if you change the data type, it changes it back? It works fine for me. I took a number field, changed it to text and it came in as text. I changed the field back to numeric and it came in as numeric but it didn't replace the table.

 

[MSAccessRookie]

Re: Access "Guesses" incorrectly when creating table to store Spreadsheet data

I just tested with that same code and it doesn't replace the table for me. If the table exists it just replaces the data.

So, if you change the data type, it changes it back? It works fine for me. I took a number field, changed it to text and it came in as text. I changed the field back to numeric and it came in as numeric but it didn't replace the table.

The table exists, the Column in the Spreadsheet is defined properly, the Column in the Table is defined properly and has no data (all rows were deleted). The conversion errors are still happening

 

[SOS]

Re: Access "Guesses" incorrectly when creating table to store Spreadsheet data

I would probably need to see the database and the Excel file to figure it out. I'm not that great at doing things without being "hands on."

 

[Kiwiman]

Re: Access "Guesses" incorrectly when creating table to store Spreadsheet data

Howzit

Access does that with Excel - don't know why. I suppose it evaluates the first row of data at takes that as the data type - best fit?? Anything else well too bad. Really drove me barmy...

Have you created an import spec? That way you can determine the datatyep of each field and it will get around this problem. Of course an import spec will only be useful if you are iomporting many files with the same structure.

 

[MSAccessRookie]

Re: Access "Guesses" incorrectly when creating table to store Spreadsheet data

Howzit
... Have you created an import spec? ...

I have not created an import spec, because I was unaware that I could do anything like that. I guess I am going to need to read up on how to do so. Are there any examples that I can be pointed to?

 

[Kiwiman]

Re: Access "Guesses" incorrectly when creating table to store Spreadsheet data

Howzit


I just realised you are importing a spreadsheet - not reading the whole question. I used to save the file as a .csv format (probably can be done programattically) first then used the transfertext command - that requires an import spec.

Sorry if this is of no use to you. But if it is...

You can look at this post - the last one that shows Boblarsons pictorial view.


http://www.access-programmers.co.uk/forums/showthread.php?t=173032

Alternatively:

From the database window

• Rt Click anywhere in teh white space and select Import
• Select your file - Change file type to the required type
• From Import Wizad - seect next and then select your delimiter and if the fiel has headings etc
• Select Advanced
• You can then check the import paramteres there including the data types \ text qualifiers etc
• Before you exit save the import using the save as

You can then use the saved import spec to import other files in the same format, and specify the import you saved in the

 

[gemma-the-husky]

Re: Access "Guesses" incorrectly when creating table to store Spreadsheet data

you do actually get more control importing a csv than a spreadsheet - acccess just assumes with a spreadsheet, and you cant overwrite the settings

--------
so one way to get what you want

insert a new blank row 2 in the spreadsheet

in the problem column on row put the word "text" (or any word)

now the column will import as text. Just delete the spurious row after the import

-----------
alternatively save as csv, then import the csv - you do get more control, but it takes longer to save the csv first