troubles with delimited files

I receive orders via CSV files. Below is one of the troublemakers. If you 
notice, the sku# is 9185 and the 16 digit following ends in 9185. Great! No 
matter if I open as CSV, TXT, or cut-n-paste, then opt to delimit into 
columns, the 16 digit number will always resolve to 9990000000009180 which 
is a mismatch. Also, with the cell not wide enough, it displays as 9.99E+15. 
All other similar 16 digit numbers would display as 999E+15 and delimit 
without issue.

0140160852,John Doe,222 daley 
st,,,boston,MA,03125,US,555-555-5555,CG,,9185,9990000000009185,2,10626315001,1,Y,UOL,,,store

What am I doing wrong here?
thanks! 


0
shank
11/24/2009 2:55:37 AM
excel 39879 articles. 2 followers. Follow

3 Replies
1074 Views

Similar Articles

[PageSpeed] 29

Import the file as a .txt using the Data Import Wizard. On the third
panel of this you can specify how you want each field of data to be
treated, and for that field you will have to set it to Text rather
than General.

Hope this helps.

Pete

On Nov 24, 2:55=A0am, "shank" <sh...@tampabay.rr.com> wrote:
> I receive orders via CSV files. Below is one of the troublemakers. If you
> notice, the sku# is 9185 and the 16 digit following ends in 9185. Great! =
No
> matter if I open as CSV, TXT, or cut-n-paste, then opt to delimit into
> columns, the 16 digit number will always resolve to 9990000000009180 whic=
h
> is a mismatch. Also, with the cell not wide enough, it displays as 9.99E+=
15.
> All other similar 16 digit numbers would display as 999E+15 and delimit
> without issue.
>
> 0140160852,John Doe,222 daley
> st,,,boston,MA,03125,US,555-555-5555,CG,,9185,9990000000009185,2,10626315=
00=AD1,1,Y,UOL,,,store
>
> What am I doing wrong here?
> thanks!

0
Pete_UK
11/24/2009 10:08:41 AM
I did. However, that slows the process way down. Anyway to get Excel to 
"assume" text on any particular fields for given file types? I supposed that 
would be a script thingy. If ext "xyz" is opened make field 4 text... etc.

thanks


"Pete_UK" <pashurst@auditel.net> wrote in message 
news:482970eb-b893-4414-bff3-9b2829c06172@o13g2000vbl.googlegroups.com...
Import the file as a .txt using the Data Import Wizard. On the third
panel of this you can specify how you want each field of data to be
treated, and for that field you will have to set it to Text rather
than General.

Hope this helps.

Pete

On Nov 24, 2:55 am, "shank" <sh...@tampabay.rr.com> wrote:
> I receive orders via CSV files. Below is one of the troublemakers. If you
> notice, the sku# is 9185 and the 16 digit following ends in 9185. Great! 
> No
> matter if I open as CSV, TXT, or cut-n-paste, then opt to delimit into
> columns, the 16 digit number will always resolve to 9990000000009180 which
> is a mismatch. Also, with the cell not wide enough, it displays as 
> 9.99E+15.
> All other similar 16 digit numbers would display as 999E+15 and delimit
> without issue.
>
> 0140160852,John Doe,222 daley
> st,,,boston,MA,03125,US,555-555-5555,CG,,9185,9990000000009185,2,1062631500�1,1,Y,UOL,,,store
>
> What am I doing wrong here?
> thanks!


0
shank (44)
11/24/2009 9:24:04 PM
Pete is correct, but to clarify *why* this works - Excel will only keep 
15 significant digits of a number (this is well documented), so it is 
actually storing all the way up to ...918 and then 'fills in' the last 
digit with a 0 when it displays. If it was 20 digits long, you would see 
5 zeroes at the end.
This is a common problem with serial numbers. I've also seen people 
complain that it does the same with credit card numbers, although  the 
prospect of people storing whole CC numbers in Excel is more frightening 
than the problem the mismatch causes.

Pete_UK wrote:
> Import the file as a .txt using the Data Import Wizard. On the third
> panel of this you can specify how you want each field of data to be
> treated, and for that field you will have to set it to Text rather
> than General.
> 
> Hope this helps.
> 
> Pete
> 
> On Nov 24, 2:55 am, "shank" <sh...@tampabay.rr.com> wrote:
>> I receive orders via CSV files. Below is one of the troublemakers. If you
>> notice, the sku# is 9185 and the 16 digit following ends in 9185. Great! No
>> matter if I open as CSV, TXT, or cut-n-paste, then opt to delimit into
>> columns, the 16 digit number will always resolve to 9990000000009180 which
>> is a mismatch. Also, with the cell not wide enough, it displays as 9.99E+15.
>> All other similar 16 digit numbers would display as 999E+15 and delimit
>> without issue.
>>
>> 0140160852,John Doe,222 daley
>> st,,,boston,MA,03125,US,555-555-5555,CG,,9185,9990000000009185,2,1062631500�1,1,Y,UOL,,,store
>>
>> What am I doing wrong here?
>> thanks!
> 
0
AdamV
11/25/2009 9:43:12 AM
Reply:

Similar Artilces:

Email trouble with New User in Active Directory
Below is a trail of posts that have been made. My problem has yet to resolved and I need some help quickly. The only other thing I can think of is to reinstall Exchange Server 2003. Hopefully someone will know the answer. Thanks! Subject: Re: Email not Setup when new user created in Active Directory From: "Bryan Hill" <bthill@comcast.net> Sent: 8/15/2004 12:30:21 PM We have other clients that are working fine. Just when we try to set up new ones - it will create the SMTP address but not the X400 address. I tried manually forcing the X400 but it will...

xml file attachment will not open
i can not open an xml file attachment to a mail message. it started to fail after installing a critical update. ...

macro-import another xls file
Hi, i need a macro to import another xls file.When i run macro i need a window to appear and to browse to the file i need to import. I allways open another file, so i want to browse for the file. Can this be done? Thanks! Try some code like the following: Sub AAA() Dim FName As Variant Dim Filter As String ' Excel 2003 workbooks Filter = "Excel Files (*.xls),*.xls" ' Excel 2007 workbooks workbooks Filter = "Excel Files (*.xlsx;*.xlsm;*.xlsb),*.xlsx;*.xlsm;*.xlsb" ' Excel 2003 and 2007 Filter = "Excel Files (*.xls;*.xlsx;*.xlsm;*.xlsb),*...

Trouble with the template.
I want to reset the 'normal' template to default to "Times New Roman" 10point font, and the pages numbered centre top. This seems to work when I set and reopen Word2003 from the Administrator Template vis: C:\Documents and Settings\Administrator\Application Data\Microsoft\Templates. But it will not work when I open Word2003 normally from the desktop. I have deleted the "Normal.dot" from the following and copied in the "Normal.dot" from the Administrator above: C:\Documents and Settings\All Users\Application Data\Microsoft\Templates...

IFS file
I am sorry to re-post this message. But nobody yet pay any attention to it and I realy want to get some answer on the matter. Pleasse help. __________________________________________________________________________ I never had this kind of file (with extension of IFS) listed in the MDBDATA folder before until few days ago: (to give you an example, one of such file is 4EB-88B3C1A0-8A6A9125.ifs) The Exchange server is running fine. Should I concern about these files? What cause that to happen and how to prevent it to happen again? Should I delete those files? (Note: the files were generated f...

.LRD File
Don't know what to think of something I noticed the other day. After setting up online bill payments with my bank, I noticed that whenever I started Money(2004), a file named filename.LRD (filename is my Money filename)would be created in the My Documents folder. As soon as I closed down the program, the .LRD file would be erased. I suspect that this is some kind of Money temp file, but I just never noticed it before on other versions of Money. It seems to be a text file. Can anyone reproduce the file? When you open Money, check the My Documents folder, or better yet, do a search ...

Inserted images from file are showing blurry on screen, why?
Recently any image format I insert in Publisher 2007 appears blurry on screen. It looks fine in print preview, but it's difficult to know what your document will look like without printing. Why is this happening all of a sudden. Was always fine before. Puzzling. --- frmsrcurl: http://msgroups.net/microsoft.public.publisher/ Try; View | Pictures | Picture display...what do you have it set at? -- ~~~~~~~~~~~~~~~~~~ Rob Giordano Microsoft MVP Expression Web "Pam" wrote in message news:#NQ4N1QdKHA.2164@TK2MSFTNGP02.phx.gbl... > Recently any image for...

Problem with "att-1.unk" file
I use MS Outlook 2000 SR1 (9.0.0.3821). When receiving e-mail with attached file (extention doesn't matter) from one of my partners I always see this file as "att-1.unk" file. He uses MS Outlook too. This "att-1.unk" may be "stripped" of additional header using programs designed for "winmail.dat" files (for instance tnef.exe). I verified, that my partner used "Plain text", not "Rich text formant" when sending his e-mails. I received the same "att-1.unk" file when I tried to use Outlook Express instead of MS Outlook. Its...

M03-WinXP -- Is .MNY file size an issue?
In early 2003 I experienced file corruption problems and an MS support rep helped me to run the salvage program on my .mny file. He told me that my problem resulted from the .mny file's growing too large. It was about 4 meg, which he said was too big. He recommended that I archive every year. I just looked at my file. Haven't archived since the beginning of 2003, so I archived. The .mny file only shrunk from 3.9 to 3.5 meg. Started to look here and read the FAQ page. I see tht some of you recommend against archiving, so I renamed the archive file back to my primary name...

trouble with CrmIsapi.dll
I was experiencing the same problem with the SDK as many have posted to this group ... "the WhoAmI() call results in a http error 405 when running sample code" I followed some of the advice; checked my .srf mapping to CrmIsapi.dll and re-registered the CrmIsapi.dll. when I re-register CrmIsapi.dll I get an error 80004005 - unspecified. ??? I copied it to various places and retried it ... same result. I got the original from the disk and retried ... same result. unmapped it from .srf, restarted and retried to register it ... same result. Anyone experienced this. What's up with m...

Excel 2007 files not opening properly..
I have Office 2007 installed.. the one that doesn't come with Outlook. So, I'm still using Outlook 2003. When I double click an XLS file to open it, Excel opens and the file never opens. I have to goto FILE, OPEN, then hit CANCEL and the file will display. This only happens for Excel. Word 2007 works fine. I have tried to uninstall the software and reinstall it, but it did not fix the problem. Anyone else have this problem or know how to fix it? We have Office 2007 on a few computers and I'm the only one with this problem. I found a fix for the problem, which seemed to...

How do I break a linked file
How do I break a linked file. When I go to Edit>Links, I don't see anyway to delete the link. TIA! "Floridagal" <Floridagal@discussions.microsoft.com> wrote in message news:774815FD-8D43-4C4C-8CDD-0A2F21257162@microsoft.com > How do I break a linked file. When I go to Edit>Links, I don't see > anyway to delete the link. > > TIA! You need the first utility on this page: http://www.acctadv.com/exceldownloads.php When I can't find links, I'll use Bill Manville's FindLink program: http://www.oaltd.co.uk/MVP/Default.htm Floridagal wro...

file has grown
i have an excel 97 file which has suddenly grown from about 800K to nearly 4MB, i don't know why. it is a faily simple file, no formulas, just a big sortable, filterable list really. this is making it cumbersome to use across our WAN. i wonder if it has something to do with recently including a lot of comments? any help to reduce it's size would be appreciated. You may need to look at your files "Used Range." This is the area of each sheet that you have been into, and therefore Excel has to remember the whole sheet, rather than the section you use. I had a file that w...

unexpected conversion trouble
I wanted to open an Excel file I hadn't been working on for a while, an Excel couldn't do it. After some investigations, I found that even if the file still had th .xls extension, it seemed to be conveted in .doc format (in fact, whe I open it in notepad, I can see at the end some words that look like MSWord signature). I don't know how this happened, but I'm reall embarassed beacause i really need the data in this file. I tried to change the extension to .doc, open the file in Word and the import in Excel, but I can hardly see part of the file. What's more, i is a 3-she...

Trouble with Outlook 2003 and PST File
Hi, We have a user who is using Outlook 2003 and moves most emails to a PST file which is considered archived email data. They have reached the Max limited on this PST file of 1.99GBs and its not letting them open the PST file. It is trying to repair it when they try to open the PST file. Here is the message they receive: "Can't move the items. The file <path>\<filename>.pst has reached its maximum size. To reduce the amount of data in this file, select some items that you no longer need, and then permanently delete them." I can't even get it open to delete...

can't open file as its read only or encrypted
Microsoft Office 2003 Excel I have several files which I can't open as it says they are read only or encrypted, as I have not done anything to them except create them I don't understand why this has happened, I have just created them from a template. I made a new file today and that opens ok Did you ever find an answer to your question? I have a similar problem and nothing I have tried has worked. "wazzyjan" wrote: > Microsoft Office 2003 > Excel > I have several files which I can't open as it says they are read only or > encrypted, as I have not done...

Creating a file for commercial printer
I have to create a pdf from a publisher file for a commercial printer. All images 300 dpi .tiff files. Is it better to create using the linked file or the embedded file. The feature is checked to print high res graphics from linked, but what would be the best way? apugh@htcnet.net wrote: >I have to create a pdf from a publisher file for a commercial >printer. All images 300 dpi .tiff files. Is it better to create >using the linked file or the embedded file. The feature is checked to >print high res graphics from linked, but what would be the best way? Perhaps it might be ...

How to delete files and mails beyond recovery?
Hi, Some customers want to erase beyond recovery: - empty disk space - swap file - deleted files - deleted emails (outlook/exchange) - windows temps - install temps - local user data temps - etc. The solution should: - Require no/very little enduser knowledge. - Preferrably work automatic at boot / shutdown / scheduler. - Use Windows internal features (via registry keys / tools / whatever). - Work for stand alone PC's and/or in Win SBS2003 environment (eventually also on the SBS server). A mix of internal settings and externnal tools is also acceptable Ccleaner comes cl...

saving files
Created workbook in Excel 2007 with 4 worksheets and trying to save as an 97 - 2003 workbook. Computer freezes and sometimes an error message appears Compatibility checker that data beyond 256 (IV) columns by 65,536 rows, will not be saved. Any help??? hi if you do have data beyond 256 columns and 65536 row, there is no help. the maximum number of columns in ver2003 to 97 is 256 and the maximum number of rows is 65536. this cannot be increased. regards FSt1 "stacey" wrote: > Created workbook in Excel 2007 with 4 worksheets and trying to save as an 97 ...

ASX file
I received a message with an attached .asx file. None of the programs I have seems to open it. Please advise what program available for a Mac will open the file. thank you <wgempp@netsense.net> wrote in message news:1109872332.063810.105220@f14g2000cwb.googlegroups.com... > I received a message with an attached .asx file. None of the programs > I have seems to open it. Please advise what program available for a > Mac will open the file. > > thank you > It is a Windows Media file. Depending on the encoding, you can open it with Windows Media Player for the Ma...

exporting to a CSV file
Hi, I have to write a tool to generate a CSV file from database. The database is Informix and I will be using Perl DBD to write it in a CSV format. From what I know of CSV file, I have to basically separate columns by comma. However I have two problems:- 1. One of the columns is a DATE field in the format mm/dd/yyyy. I see that when it saves it as CSV file, Excel has problem converting it correctly. For some dates it shows the column as 10/28/2003 and for some it shows as 10 02 2003. I suspect the default date format in Excel is dd mm yyyy. How do I force Excel to acknowledge that column as ...

Trouble with Pivot Tables & Named Ranges
Can I use a dynamic named range setup in one file as a range in pivot tables in another file? When I have the data source file (with the named range) and the pivot report files open, the pivot reports update just fine. But the moment I close the source data, the pivot reports no longer can update and I get a message that the source can't be found. If I reopen the source, the pivot tables work fine again - but as soon as I close it up, the reports no longer work. The source file is very large and I don't want to open it every time I have to update all the reports tha...

I am in trouble
Hi, I am a student in Cambodia.I don't have any girlfriend. I tried everything. I looked at them wrote poem to them it doesnt work. I got an account from facebook, hi5 friendster myspace. I have 0 girl friend. What should I do? I am so desperate. I am good looking and 250 kg only. Please help me or at least send me some foood I am hungry Best Regards Hungry Guy __________ Information from ESET NOD32 Antivirus, version of virus signature database 4944 (20100314) __________ The message was checked by ESET NOD32 Antivirus. http://www.eset.com You're tr...

What is best way to read binary file into string?
Hi all, I am now building a routine that reads a small (no larger than 100 bytes) binary file and saves the contents in a string. The file can contain characters anywhere in the 0 - 255 ASCII range. After looking at MSDN Lib. and on the 'net I found a few approaches to this problem. I selected one; the resulting code: Dim strBuf As String = "" Dim i As Int32 'Check to see if file exists. If System.IO.File.Exists(strFile) Then 'File exists. Read it. Dim fs As New System.IO.FileStream(strFile, System.IO.FileMode.Open) ...

converting .ics file to Publisher
I am trying to convert my internet calendar into a Publisher document. How can I make that conversion? Copy/paste? Print screen and insert as an image? Any particular reason why you cannot use Publisher's calendars? -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "RevJon" <RevJon@discussions.microsoft.com> wrote in message news:466AFEDD-B315-4DEE-A05B-29D3C8D345D8@microsoft.com... >I am trying to convert my internet calendar into a Publisher document. How > can I make that conversion? I tried the c...