If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
importing data from Excel
I am importing data from Excel and even if I change the datatype in Excel of
the data from General to Text- when I import it into an Access db it strips the leading zeros off the data which I need. I have already tried importing into an existing table where the field is already text. I am using MS Access 2003 and Excel 2003. Any suggestions? I know if I import the data into Excel manually and choose it text that way - it imports into Access with the leading zeros. However, I need to use an Excel spreadsheet that is created automatically by a download process from our application..and with this copy of the spreadsheet - even after changing that field to text...it strips off the leading zeros upon import into access table. Thanks! -- MorningStarFan |
#2
|
|||
|
|||
importing data from Excel
The only way I'm familiar with for having leading zero's stripped is when
the data is numeric. It sounds like you've covered the Access side by importing into a field defined as text. The only other side I can imagine is the Excel side. Is there a chance that the data that is displayed in Excel is actually numeric, but simply formatted to appear with leading zeros? Regards Jeff Boyce Office/Access MVP "MorningStarFan" wrote in message ... I am importing data from Excel and even if I change the datatype in Excel of the data from General to Text- when I import it into an Access db it strips the leading zeros off the data which I need. I have already tried importing into an existing table where the field is already text. I am using MS Access 2003 and Excel 2003. Any suggestions? I know if I import the data into Excel manually and choose it text that way - it imports into Access with the leading zeros. However, I need to use an Excel spreadsheet that is created automatically by a download process from our application..and with this copy of the spreadsheet - even after changing that field to text...it strips off the leading zeros upon import into access table. Thanks! -- MorningStarFan |
#3
|
|||
|
|||
importing data from Excel
A "bad" feature of ACCESS 2003 and earlier versions...
The problem is that Jet is seeing numbers in the first 25 or so rows of your EXCEL spreadsheet, so it assigns a number data type to that field, and then, if it finds a text value in the field in a later row, it errors and won't import that value. You can work around this by adding apostrophes to the beginning of the number values in the EXCEL cells (which tells Jet that the numbers in fact are text), or you can put a text value (such as what you posted) in the first row in the EXCEL sheet. -- Ken Snell MS ACCESS MVP "MorningStarFan" wrote in message ... I am importing data from Excel and even if I change the datatype in Excel of the data from General to Text- when I import it into an Access db it strips the leading zeros off the data which I need. I have already tried importing into an existing table where the field is already text. I am using MS Access 2003 and Excel 2003. Any suggestions? I know if I import the data into Excel manually and choose it text that way - it imports into Access with the leading zeros. However, I need to use an Excel spreadsheet that is created automatically by a download process from our application..and with this copy of the spreadsheet - even after changing that field to text...it strips off the leading zeros upon import into access table. Thanks! -- MorningStarFan |
#4
|
|||
|
|||
importing data from Excel
Ken Snell [MVP] wrote: A "bad" feature of ACCESS 2003 and earlier versions... This is one of those times when knowing the difference between Access and Jet (which without doubt Ken does) is important. The issue is with Jet; not Jet on the Access side but Jet on the Excel side. The problem is that Jet is seeing numbers in the first 25 or so rows of your EXCEL spreadsheet, so it assigns a number data type to that field, and then, if it finds a text value in the field in a later row, it errors and won't import that value. The number of rows to scan is determined by a local machine registry key (usually found to be 16). If you set this to zero, it forces all rows to be scanned. Another registry key tells Jet (on the Excel side) what to do in a 'mixed types' situation: the default (and most useful) is to import as text, meaning NVARCHAR(255). You can work around this by adding apostrophes to the beginning of the number values in the EXCEL cells (which tells Jet that the numbers in fact are text), or you can put a text value (such as what you posted) in the first row in the EXCEL sheet. Changing the data may not be desirable or possible, If the local machine's settings can be changed, I'd recommend doing so to allow all rows to be scanned. Details at: http://www.dicks-blog.com/archives/2...ed-data-types/ |
#5
|
|||
|
|||
importing data from Excel
Putting a text value into the first row in the EXCEL spreadsheet has worked
for what I needed to do. Much thanks to all who posted solutions! -- MorningStarFan " wrote: Ken Snell [MVP] wrote: A "bad" feature of ACCESS 2003 and earlier versions... This is one of those times when knowing the difference between Access and Jet (which without doubt Ken does) is important. The issue is with Jet; not Jet on the Access side but Jet on the Excel side. The problem is that Jet is seeing numbers in the first 25 or so rows of your EXCEL spreadsheet, so it assigns a number data type to that field, and then, if it finds a text value in the field in a later row, it errors and won't import that value. The number of rows to scan is determined by a local machine registry key (usually found to be 16). If you set this to zero, it forces all rows to be scanned. Another registry key tells Jet (on the Excel side) what to do in a 'mixed types' situation: the default (and most useful) is to import as text, meaning NVARCHAR(255). You can work around this by adding apostrophes to the beginning of the number values in the EXCEL cells (which tells Jet that the numbers in fact are text), or you can put a text value (such as what you posted) in the first row in the EXCEL sheet. Changing the data may not be desirable or possible, If the local machine's settings can be changed, I'd recommend doing so to allow all rows to be scanned. Details at: http://www.dicks-blog.com/archives/2...ed-data-types/ |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Powerpoint toolbars explode after importing Excel data | [email protected] | Powerpoint | 10 | December 7th, 2005 05:54 PM |
Can we import an Excel sheet to use as base for report? | StargateFan | New Users | 9 | November 4th, 2005 07:32 PM |
importing data from Excel worksheet to another worksheet | jbrick | Worksheet Functions | 0 | August 11th, 2005 05:51 PM |
Trouble...Importing Excel Data files to Outlook 2000 | Terri | General Discussions | 1 | January 7th, 2005 04:26 AM |
Importing Data into an Excel Pivot Table via Access | Andrew | Running & Setting Up Queries | 1 | November 17th, 2004 06:59 PM |