Import Issue with a data type conversion

[roaml]

Hello,

This is my first time importing data and I am having a data type problem with one column.

I am using the macro below to import a spreadsheet into an Access 2000 table. Everything is working well except for a particular field. This field is called "District" in the spreadsheet. The District field currently has a combination of text and numeric data. When the District data is imported in an Access table, the District data is converted into a number data type causing errors.
How can I import the spreadsheet data into an Access table as text fields?

<MACRO>
Action:
DeleteObject

Object Type: Table
Object Name: trouble_ticket_report

Action:
TransferSpreadsheet

Transfer Type: Import
Spreadsheet Type: Microsoft Excel8-9
Table Name: trouble_ticket_report
File Name: C:\trouble_ticket_report
Has Field Names: Yes
Range: (blank)
<MACRO>

Thank you.

 

[lespaul]

in your excel spreadsheet, what does the data look like in the first record? is it all numbers? then access will determine it is a numeric field. If the first record has information that contains both numbers and characters, access will determine it should be a text field.

Are you creating a new table each time you import?

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[roaml]

Hi Leslie,

Actually, the first record is a number. It is a "1". Is there a way to get around this using the Macro?

I am deleting the original table in Access and creating a new table.

Thank you.

 

[lespaul]

instead of deleting your table, just delete the records. That way you use the same table structure each time and you won't have this problem.

DELETE FROM TableName

To get around this using your Macro, you need to insert a record in your excel sheet before the existing first record that has mixed character/numeric information so that Access treats it as a string and not a number.

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[roaml]

Okay, so if I wanted to delete the records instead of the table, can I do this by using the same macro or through code?

 

[roaml]

Okay, so if I wanted to delete the records instead of the table, can I do this by using the same macro or through code?

 

[lespaul]

Sure, save the query that deletes the records and then in the macro call the run query command.

Leslie

 

[roaml]

Thanks Leslie. I will work on this.

 

[KornGeek]

I once encountered this issue when importing into an existing table. If the first few records were all numeric on a field, then the import expected them to all be numeric, regardless of how I had set up the table into which the data was being imported.

The way I got around this was to use VBA to open up Excel, go through the first record, and in any field where I expected text but found numeric data, I would add an apostrophe to the beginning of the data. This keeps the data the same, but indicates to Excel (and thus Access) that it is text data.

Unfortunately, I no longer have the code that I used to do this, so I am unable to post an example of how it was done.