Question Importing CSV file drops text from one field but keeps numbers (1 Viewer)

SImonG

New member
Local time
Today, 21:22
Joined
Jan 13, 2011
Messages
5
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
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 19:22
Joined
Jul 9, 2003
Messages
16,244
I recall having a similar problem several years ago when trying to import an excel spreadsheet.

My investigations revealed that the first row of data in the spreadsheet contained numbers, this led the import routine to specify this particular column as number format, hence the problem when it tried to reference text in the same column.

I’m wondering if you are experiencing a similar issue.
 

SImonG

New member
Local time
Today, 21:22
Joined
Jan 13, 2011
Messages
5
Hi Uncle Gizmo

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

Cheers

SImon
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 19:22
Joined
Jul 9, 2003
Messages
16,244
I think the solution was to make sure that the first cell, the cell which I assume MS Access used to decide how to format the whole column, just make sure the cell contains either text format if you want the rest to be in text, or number format if you want the rest of the column to be returned as number format.

In other words you need to add an extra row of data right at the beginning which contains correctly formatted entries.

Alternatively I think you can gain access to the mapping process, the process where access decides which sort of data the column contains and impose your own condition on that. However I don’t have any experience in this area of coding, yet!
 

SImonG

New member
Local time
Today, 21:22
Joined
Jan 13, 2011
Messages
5
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

Registered User.
Local time
Tomorrow, 06:22
Joined
Jul 2, 2007
Messages
924
Another approach is to create the table in Access, and format the problem field as text and then import into an existing table
 

vbaInet

AWF VIP
Local time
Today, 19:22
Joined
Jan 22, 2010
Messages
26,374
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

New member
Local time
Today, 21:22
Joined
Jan 13, 2011
Messages
5
Thanks chaps

That seems to have fixed it.

Simon
 

John.Woody

Registered User.
Local time
Today, 19:22
Joined
Sep 10, 2001
Messages
354
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.

Code:
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
 

Users who are viewing this thread

Top Bottom