Question Importing CSV file drops text from one field but keeps numbers

[SImonG]

Hi There

I'm an intermediate Access user trying to import a large CSV file to a Table using the macro "transfer spreadsheet" function. The csv has about 50 fields - all are imported OK except for one ID field. The offending csv field contains numbers (eg "123456") and text (eg "none" or "new"). When imported by the macro, the text values are rejected and thrown into an Import Errors table for type conversion.

I've tried amending the format of the recipient Access table to be text or memo, but it still happens.

Can anybody help solve this problem.

Many thanks

Simon

 

[SImonG]

Hi Uncle Gizmo

It does sound like the same type of problem. Were you able to fix it or get round it?

Cheers

SImon

 

[SImonG]

Ideally I would like to have both text and number values imported to the field. Hmmm.
I'll keep on thinking.

Thanks

Simon

 

[David Eagar]

Another approach is to create the table in Access, and format the problem field as text and then import into an existing table

 

[vbaInet]

Your import is most likely creating a new table and as a result Access must decide what data type to use.

1. Create a table in Access with the 50 fields and their respective data types.
2. Specify that the import should go into this table and it should import correctly.

Or

2. Create an Import Spec for your import to map the fields in the table to the spreadsheet headers and import into the table. This will allow you to use the same Import Spec whenever you need to import again.

 

[SImonG]

Thanks chaps

That seems to have fixed it.

Simon

 

[David Eagar]

1. Create a table in Access with the 50 fields and their respective data types.

Gee, didn't I just suggest that?

 

[vbaInet]

Gee, didn't I just suggest that?

You may have the credit

 

[SImonG]

Thanks guys

 

[John.Woody]

Ideally I would like to have both text and number values imported to the field. Hmmm.
I'll keep on thinking.

Thanks

Simon

Or use a macro in the spreadsheet to convert the fields to text which will allow both text and number entries.

Sub MacroConvertToText()

Dim cell As Object

   For Each cell In Selection
      cell.Value = " " & cell.Value
      cell.Value = Right(cell.Value, Len(cell.Value) - 1)
   Next

End Sub

Create the macro, select the cells to be converted and run the macro.

hth