Go Back   Access World Forums > Microsoft Access Discussion > General

 
Reply
 
Thread Tools Rating: Thread Rating: 2 votes, 5.00 average. Display Modes
Old 01-13-2011, 05:20 AM   #1
SImonG
Newly Registered User
 
Join Date: Jan 2011
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
SImonG is on a distinguished road
Importing CSV file drops text from one field but keeps numbers

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 is offline   Reply With Quote
Old 01-13-2011, 05:52 AM   #2
Uncle Gizmo
Nifty Access Guy
 
Uncle Gizmo's Avatar
 
Join Date: Jul 2003
Location: Newbury Berks UK
Posts: 10,323
Thanks: 542
Thanked 941 Times in 892 Posts
Uncle Gizmo is a jewel in the rough Uncle Gizmo is a jewel in the rough Uncle Gizmo is a jewel in the rough
Send a message via Skype™ to Uncle Gizmo
Re: Importing CSV file drops text from one field but keeps numbers

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.
__________________
Code:
                 |||||
               @(~‘^‘~)@
-------------oOo---U---oOo-------------
|                                     |
|      Uncle Gizmo              |
|                                     |
|                                     |
| Get $20 worth of "Nifty Code"       |
|      
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
| | Ooo | |_________________ooO____( )________| ( ) ) / \ ( (_/ \_)
Uncle Gizmo is offline   Reply With Quote
Old 01-13-2011, 05:57 AM   #3
SImonG
Newly Registered User
 
Join Date: Jan 2011
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
SImonG is on a distinguished road
Re: Importing CSV file drops text from one field but keeps numbers

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 is offline   Reply With Quote
Old 01-13-2011, 05:07 PM   #4
Uncle Gizmo
Nifty Access Guy
 
Uncle Gizmo's Avatar
 
Join Date: Jul 2003
Location: Newbury Berks UK
Posts: 10,323
Thanks: 542
Thanked 941 Times in 892 Posts
Uncle Gizmo is a jewel in the rough Uncle Gizmo is a jewel in the rough Uncle Gizmo is a jewel in the rough
Send a message via Skype™ to Uncle Gizmo
Re: Importing CSV file drops text from one field but keeps numbers

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!
__________________
Code:
                 |||||
               @(~‘^‘~)@
-------------oOo---U---oOo-------------
|                                     |
|      Uncle Gizmo              |
|                                     |
|                                     |
| Get $20 worth of "Nifty Code"       |
|      
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
| | Ooo | |_________________ooO____( )________| ( ) ) / \ ( (_/ \_)
Uncle Gizmo is offline   Reply With Quote
Old 01-13-2011, 10:42 PM   #5
SImonG
Newly Registered User
 
Join Date: Jan 2011
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
SImonG is on a distinguished road
Re: Importing CSV file drops text from one field but keeps numbers

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

Thanks

Simon
SImonG is offline   Reply With Quote
Old 01-13-2011, 10:48 PM   #6
David Eagar
Newly Registered User
 
Join Date: Jul 2007
Location: Far South Coast, NSW, Australia
Posts: 924
Thanks: 0
Thanked 0 Times in 0 Posts
David Eagar is on a distinguished road
Re: Importing CSV file drops text from one field but keeps numbers

Another approach is to create the table in Access, and format the problem field as text and then import into an existing table
David Eagar is offline   Reply With Quote
Old 01-13-2011, 11:12 PM   #7
vbaInet
AWF VIP
 
Join Date: Jan 2010
Location: U.K.
Posts: 26,374
Thanks: 0
Thanked 2,423 Times in 2,389 Posts
vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all
Re: Importing CSV file drops text from one field but keeps numbers

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.

vbaInet is offline   Reply With Quote
Old 01-13-2011, 11:48 PM   #8
SImonG
Newly Registered User
 
Join Date: Jan 2011
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
SImonG is on a distinguished road
Re: Importing CSV file drops text from one field but keeps numbers

Thanks chaps

That seems to have fixed it.

Simon
SImonG is offline   Reply With Quote
Old 01-14-2011, 01:35 AM   #9
David Eagar
Newly Registered User
 
Join Date: Jul 2007
Location: Far South Coast, NSW, Australia
Posts: 924
Thanks: 0
Thanked 0 Times in 0 Posts
David Eagar is on a distinguished road
Re: Importing CSV file drops text from one field but keeps numbers

Quote:
Originally Posted by vbaInet View Post
1. Create a table in Access with the 50 fields and their respective data types.

Gee, didn't I just suggest that?
David Eagar is offline   Reply With Quote
Old 01-14-2011, 01:44 AM   #10
vbaInet
AWF VIP
 
Join Date: Jan 2010
Location: U.K.
Posts: 26,374
Thanks: 0
Thanked 2,423 Times in 2,389 Posts
vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all
Re: Importing CSV file drops text from one field but keeps numbers

Quote:
Originally Posted by David Eagar View Post
Gee, didn't I just suggest that?
You may have the credit
vbaInet is offline   Reply With Quote
Old 01-14-2011, 02:07 AM   #11
SImonG
Newly Registered User
 
Join Date: Jan 2011
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
SImonG is on a distinguished road
Re: Importing CSV file drops text from one field but keeps numbers

Thanks guys
SImonG is offline   Reply With Quote
Old 01-14-2011, 03:49 AM   #12
John.Woody
Newly Registered User
 
John.Woody's Avatar
 
Join Date: Sep 2001
Location: Polesworth, Warwickshire, UK
Posts: 354
Thanks: 2
Thanked 13 Times in 7 Posts
John.Woody is on a distinguished road
Re: Importing CSV file drops text from one field but keeps numbers

Quote:
Originally Posted by SImonG View Post
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

John.Woody is offline   Reply With Quote
Reply

Tags
csv , import problems , ms access

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Importing from a text file MarionD Modules & VBA 8 07-02-2007 03:05 AM
Importing Combination Text and Numbers SarahAPhillips Tables 2 10-14-2005 01:18 AM
Importing Text file with numbers that need to be text format jamphan Macros 0 01-31-2005 07:48 AM
Importing Text file data into Access decimal field djpearce Tables 2 05-13-2004 09:28 AM
exporting numbers to text file creeping General 1 04-15-2003 08:57 PM




All times are GMT -8. The time now is 07:01 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World