Hi,
I have a column of SKU numbers in a spreadsheet that need to be stored as text. The problem is that Excel keeps converting this column to scientific notation so when I import it into Access, I can't query on the SKU field.
This is a huge worksheet with > 1 million records so it must be a solution that suitable for this situation.
Thanks for any advice.
Lyndy
Hi,
you could convert it to text via
=TEXT(A2,"?")
Rule 1: Never merge cells
Rule 2: See rule 1
"Tomorrow I'm going to be famous. All I need is a tennis racket and a hat".
Thanks for your reply.
I have thought of that but the SKU numbers are not consistently the same length. Some have 14; some have 16 or 18. Is there any way tell Excel that I want to covert the contents of cell A2?
Thanks,
Lyndy
If the SKU "numbers" (IDs) might have more than 15 digits, it is important that they are entered into Excel as text, not numbers. Otherwise, Excel replaces the excess digits on the left with zeros. Obviously, that changes the SKU ID, and no amount of post-processing (e.g. using TEXT) will correct that.Originally Posted by lyndy256
I am not familiar with the Access-to-Excel procedure. Does it automatically go into Excel? Or does the Access data really go into a CSV file? Do you have the option to save the Access data into an XML file instead?
If the data goes into a text file (other than XML), and if the SKU IDs are in columns by themselves, not mixed with other truly numeric data, you need to import the text file instead of opening it directly in Excel.
Start Excel -- for example, click on the Excel icon, not the CSV file icon -- then click on Data, Get External Data, From Text. In Step 3, select the SKU ID column under "Data Previous", and select Text under "Column Data Format".
Thanks for your reply.
I should have mentioned that I received the data in txt format and have imported into Excel as you describe.
You can import most types of data into Access, including Excel and text.
I also tried directly importing the text file but I get a lot of errors and is not as smooth as importing Excel data.
Ultimately, my goal is to get the tables with the SKU ids in text format all appended in Access.
The issue of scientific notation seems to be a vexing and common issue in Excel.
Lyndy
I'm sorry. Based on your title and your second comment, I thought you were concerned about importing SKU IDs into Excel. But your original posting makes it clear that you are concerned exporting Excel data as text and importing the text into Access. Right?
Your assertions are incorrect. If the SKU IDs are displayed correctly in Excel to begin with, that is how they are appear in the text file created by Excel. In other words, Excel writes cell values exactly as they appear in the cells. Open the text file in Notepad to confirm.
The problem of displaying them in Scientific form in Excel arises only when you (re)open the text file in Excel directly.
And if you save the SKU IDs into a text file, they are "stored as text" by definition.
If you are having problems importing the text file created by Excel into Access, that seems to be an Access problem, not an Excel problem. In that case, you are posting to the wrong forum.
On the other hand, if Access always interprets unquoted digits as numbers, like Excel does [1], and Access does not have any way to force it to interpret the data as text, like Excel does, arguably you might perceive the problem as: how to coerce Excel into putting quotes around text cell values when they are written to a text file?
The answer is: you cannot. You would have to write a VBA procedure to do that.
Forgive me if I'm still misunderstanding the problem.
-----
[1] Actually, Excel interprets digits as numbers, whether or not they are quoted. Access, too?
Last edited by joeu2004; 11-17-2015 at 02:07 PM. Reason: footnote 1
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks