CSV import with Formatted numbers

Is there a way to format a CSV file such that numbers can 
be formated (like # $,0.00) at the time they are 
imported? We don't want to have people reformat the data 
after the file is imported. We would like the numeric 
picture to be included in the CSV data. This includes 
formula fields as well.

A simple example of the CSV file would be:
UserId,Name,Oct_Sales,Nov_Sales,Tot_Sales
1002,John Doe,1000.00,1500.00,=Sum(C2:D2)
1005,Jane Roe,1420.00,2125.00,=Sum(C3:D3)

I want the Total (in column E) to be formatted such that
it looks like $2,500.00 and $3,545.00 when it is imported.

Thanks in advance.
0
anonymous (74740)
12/19/2003 3:32:57 PM
excel.misc 78881 articles. 3 followers. Follow

3 Replies
163 Views

Similar Articles

[PageSpeed] 35

Unless you could format the sales figures with a $ sign 
when you create the CSV.  Your sum formula will assume the 
$ formatting.

>UserId,Name,Oct_Sales,Nov_Sales,Tot_Sales
>1002,John Doe,$1000.00,$1500.00,=Sum(C2:D2)
>1005,Jane Roe,$1420.00,$2125.00,=Sum(C3:D3)

will result in excel as:

UserId  Name     Oct_Sales Nov_Sales Tot_Sales
1002    John Doe $1,000.00 $1,500.00 $2,500.00 
1005    Jane Roe $1,420.00 $2,125.00 $3,545.00



>-----Original Message-----
>Is there a way to format a CSV file such that numbers can 
>be formated (like # $,0.00) at the time they are 
>imported? We don't want to have people reformat the data 
>after the file is imported. We would like the numeric 
>picture to be included in the CSV data. This includes 
>formula fields as well.
>
>A simple example of the CSV file would be:
>UserId,Name,Oct_Sales,Nov_Sales,Tot_Sales
>1002,John Doe,1000.00,1500.00,=Sum(C2:D2)
>1005,Jane Roe,1420.00,2125.00,=Sum(C3:D3)
>
>I want the Total (in column E) to be formatted such that
>it looks like $2,500.00 and $3,545.00 when it is imported.
>
>Thanks in advance.
>.
>
0
anonymous (74740)
12/19/2003 4:33:30 PM
That does not work for me. The Sum column has no 
formatting - commas or $. (Excel 2003)

>-----Original Message-----
>Unless you could format the sales figures with a $ sign 
>when you create the CSV.  Your sum formula will assume 
the 
>$ formatting.
>
>>UserId,Name,Oct_Sales,Nov_Sales,Tot_Sales
>>1002,John Doe,$1000.00,$1500.00,=Sum(C2:D2)
>>1005,Jane Roe,$1420.00,$2125.00,=Sum(C3:D3)
>
>will result in excel as:
>
>UserId  Name     Oct_Sales Nov_Sales Tot_Sales
>1002    John Doe $1,000.00 $1,500.00 $2,500.00 
>1005    Jane Roe $1,420.00 $2,125.00 $3,545.00
>
>
>
>>-----Original Message-----
>>Is there a way to format a CSV file such that numbers 
can 
>>be formated (like # $,0.00) at the time they are 
>>imported? We don't want to have people reformat the 
data 
>>after the file is imported. We would like the numeric 
>>picture to be included in the CSV data. This includes 
>>formula fields as well.
>>
>>A simple example of the CSV file would be:
>>UserId,Name,Oct_Sales,Nov_Sales,Tot_Sales
>>1002,John Doe,1000.00,1500.00,=Sum(C2:D2)
>>1005,Jane Roe,1420.00,2125.00,=Sum(C3:D3)
>>
>>I want the Total (in column E) to be formatted such that
>>it looks like $2,500.00 and $3,545.00 when it is 
imported.
>>
>>Thanks in advance.
>>.
>>
>.
>
0
anonymous (74740)
12/19/2003 5:42:59 PM
This works, but is not right-aligned.
UserId,Name,Oct_Sales,Nov_Sales,Tot_Sales
1002,John Doe,$1000.00,$1500.00,=Text(Sum
(C2:D2), "$0,000.00")
1005,Jane Roe,$1420.00,$2125.00,=Text(Sum(C3:D3), 
$0,000.00")

Is it better to export html with formatting and import it 
into Excel?

>-----Original Message-----
>That does not work for me. The Sum column has no 
>formatting - commas or $. (Excel 2003)
>
>>-----Original Message-----
>>Unless you could format the sales figures with a $ sign 
>>when you create the CSV.  Your sum formula will assume 
>the 
>>$ formatting.
>>
>>>UserId,Name,Oct_Sales,Nov_Sales,Tot_Sales
>>>1002,John Doe,$1000.00,$1500.00,=Sum(C2:D2)
>>>1005,Jane Roe,$1420.00,$2125.00,=Sum(C3:D3)
>>
>>will result in excel as:
>>
>>UserId  Name     Oct_Sales Nov_Sales Tot_Sales
>>1002    John Doe $1,000.00 $1,500.00 $2,500.00 
>>1005    Jane Roe $1,420.00 $2,125.00 $3,545.00
>>
>>
>>
>>>-----Original Message-----
>>>Is there a way to format a CSV file such that numbers 
>can 
>>>be formated (like # $,0.00) at the time they are 
>>>imported? We don't want to have people reformat the 
>data 
>>>after the file is imported. We would like the numeric 
>>>picture to be included in the CSV data. This includes 
>>>formula fields as well.
>>>
>>>A simple example of the CSV file would be:
>>>UserId,Name,Oct_Sales,Nov_Sales,Tot_Sales
>>>1002,John Doe,1000.00,1500.00,=Sum(C2:D2)
>>>1005,Jane Roe,1420.00,2125.00,=Sum(C3:D3)
>>>
>>>I want the Total (in column E) to be formatted such 
that
>>>it looks like $2,500.00 and $3,545.00 when it is 
>imported.
>>>
>>>Thanks in advance.
>>>.
>>>
>>.
>>
>.
>
0
anonymous (74740)
12/19/2003 6:35:26 PM
Reply:

Similar Artilces:

Does anyone have a format or template for a Education Cover Letter
Does anyone have a format or template for a Education Cover Letter? To Whom It May Concern: Enclosed please find an education. Sincerely, eb -- Suzanne S. Barnhill Microsoft MVP (Word) Words into Type Fairhope, Alabama USA http://word.mvps.org "eb" <eb@discussions.microsoft.com> wrote in message news:4CEF7B84-6F7A-4D07-97B3-F349F3B11E54@microsoft.com... > Does anyone have a format or template for a Education Cover Letter? > "Suzanne S. Barnhill" <sbarnhill@mvps.org> wrote in message news:O3aNFsq2KHA.5212@TK2MSFTNGP04.phx.g...

.CSV to .PRN
I am having trouble saving a .CSV file as a .PRN format - it is cutting the information in the sheet off about 20 columns in - any suggestions? I am doing the basic File - Save as. Thanks, Melissa Have you formatted the sheet with a fixed-width font so you can set the field widths exactly? What is sum of field lengths? There used to be a limit of ~240 characters per line in PRN files. On Mon, 18 Oct 2004 10:13:08 -0700, "Melissa" <Melissa@discussions.microsoft.com> wrote: >I am having trouble saving a .CSV file as a .PRN format - it is cutting the >information i...

importing incredimail into outlook express
Is there a way, or an add-on. or a third party software to import Incredimail mail into outlook express mail ? ZZ Try posting this in an Outlook Express news group - this is not one of them. Outlook is a part of Microsoft Office and is what this group supports. Outlook Express is a part of Internet Explorer and has its own news groups. You can also find some good Outlook Express information here: http://insideoe.tomsterdam.com --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. All unsolicited mail sent to my personal account will be deleted w...

cell formatting zero padding on binary no's
I'm struggling through doing some binary math on excel. Ugh. You'd think Excel is written by programmers, so they would see the value of having some programmer-friendly bitwise math and binary, octal and hex formatting stuff in there, if for no other reason than they could use it themselves. Anyway, that's my rant, here's my question I have some numbers in cells that I convert to binary C5: 55 DEC2BIN(C5) gives me 1010101 I'd really like to display that as 0101 0101 .... ie with a leading zero and a space between the nibbles. It appears that ce...

Importing CSV files problem
Hi Guys, I am currently working on a project that invloves importing of CSV files to Excel. Double-clicking the CSV file opens the file automatically in Excel. We have enclosed our data in quotation marks (i.e. "SomeData") to prevent errors arising from strings that have commas as part of the data. This works fine for us. However, The problem arises when we import data that look like numbers written in scientific notation but are actually some other data which should not be expressed in scientific notation (example "1E306" is expressed as "1E+306"). ...

Excel error while trying to import data from an Access database
This is just an FYI for anyone who might encounter this problem (as I just did)... =-) ::: SYMPTOM ::: In Excel, while trying to create a query into an Access database the following error occurred: MSQRY32.exe has generated errors and will be closed by Windows. You will need to restart the program. ::: CAUSE ::: The Access database contains 'unfavorable' characters in the filename (e.g. "MyAccessDatabase v1.0 (15-Mar-05).mdb" -- it doesn't like the "." and is probably better off without the "(") Rename the database to use a 'safe' file n...

importing account info into production CRM
Has anyone used the DMF to do a secondary import of data into a live CRM environment? I know it is not intended for this purpose, however in this client's scenario they have no data whatsoever in Accounts (they use only leads and contacts currently, but a different department wants to use accounts now). I want to import some account data that is totally unrelated to their existing contacts. I figure I can use the DMF to do this. Anyone have any thoughts/tips/cautions??? Thanks, -- Matt Wittemann http://icu-mscrm.blogspot.com Dear Matt, It is possible to do this. I have done an imp...

Creating a csv
I have a spreadsheet with a list of email addresses in the first column. I want to create a CSV file. If I choose Save as---> csv and then open the csv file in Notepad the list is saved as a list with carriage returns after each email address and no commas. So how do i turn the list into a continuous list with no breaks and a comma seperating each one? I'm using Excel 2007 Thanks Tony Open the file in MSWord and do an edit|replace (or the MSWord 2007 equivalent) to change the paragraph mark (under the More button and then under the Special button) to a comma. Tony Williams wrote: ...

CSV elements don't get split up in cells
Hi, I recently installed a German version of excel and now when I open m CSV files they don't get seperated anymore by the commas. The whol line is just written in one cell. When I had the US version installed the data was split up after eac comma and distributed into the corresponding cell. How can I achieve the same thing in the German version -- Message posted from http://www.ExcelForum.com tom You could try renaming the file to .txt rather than .csv When you try to open this in Excel it will start the Text Import Wizard which will give you much more control over the import of th...

Custom Date Format #2
When working with data from a spreadsheet which originated from a much earlier version of Excel (Excel95, I believe), which I'm now trying to use with Excel 2003. I'm having trouble with the formatting of the date being interpreted as a formula and forcing an equals sign "=" to appear next to the data. When the file is first opened, the original data appears correctly. However, after editing the data in the field and pressing the 'enter' key, the date displayed on the sheet shows "00-Jan-00". When I use the arrow keys to go back up to that parti...

Block HTML Formatting?
I have a web-based app that accepts emails sent to it by Exchange users. I'd like to have Exchange format those emails so that if my users send mail with HTML formatting in the email the emails will be converted to plain text before being forwarded to my app server. Is that possible in Exchange? rickKasten@gmail.com (DrStrangepork) wrote in news:a4cf4e10.0409141239.440d0853@posting.google.com: > I have a web-based app that accepts emails sent to it by Exchange > users. I'd like to have Exchange format those emails so that if my > users send mail with HTML formatting in t...

Import data from Excel with main tasks and subtasks
I'm new to MS Project and need help with importing data from Excel into Project 2000. The data consists of Work Request numbers, individual phase numbers for the Work Request, name of each phase, and (phase) start and end dates. I can open the Excel file in Project and map the fields, however, the data shows up as individual tasks. I need to have the Work Request number and project name be a main, or top-level, task, and the data related to the phases be sub-tasks. Here's an example of how the raw data appears in the Excel file: WorkReq# ProjectName Phase# P...

how do I import footnotes to a publisher document ?
When I import a word document into plublisher, the footnotes do not show up. How can I import the Whole document incluiding the footnotes In Publisher 2002, one has to create headers/footers and then copy and paste footnote text, because Publisher 2002 does not support footnotes directly.. Bear in mind that MS Publisher is a frame-based desktop publisher, as opposed to MS Word that is a full blown word processor.. -- Mike Hall MVP - Windows Shell/user "Tripleaxel1" <Tripleaxel1@discussions.microsoft.com> wrote in message news:4765C400-3F5C-4CB4-8FF7-D92BBE2BCD08@micr...

Import #3
I'm trying to import e-mails from Thunderbird but I keep getting an error that "The mail folder could not be opened. If another application is using this file, please close it and try again." There si nothing else using it. I've rebooted, I've coppied the fiels to another directory, I've renamed the files but I can't get my Outlook 2003 e-mails back in Outlook. Thanks. "Brian E" <beshlemanREMOVE@aains.com> wrote in message news:234ee01c45ebf$1f31d320$a601280a@phx.gbl... > I'm trying to import e-mails from Thunderbird but I keep >...

formatting cells #3
Question: Recently, when attempting to format cells, whether it's for color fill or lines, boldface for type,and a variety of other cell formats--I now get a warning box which says 'too many different cell formats' even when I work with a duplicate of the spread sheet from which I have cleared all formats. Is there some way to correct this malfunction. I have worked for over ten years with excel and have not had this problem. It appeared on my G4 and now has carried over to my G5. I'm running Excel 2004 11.3 Frank Excel has a limit for the number of different formats it can...

Saving As .CSV
Hello, here's the situation: I downloaded an .xls file from my broker and opened it in Excel. The original file was delimited by commas and I had to choose comma delimited in order for Excel to display it in columns/rows correctly. I made some changes to the file, then saved it with a new name. The problem is, I can find no way to save it as a comma delimited file again. Saving it as a Workbook places quotes around the commas and creates spacing between text (when I view it in Wordpad), and saving it as a Text File (Tab delimited) does just that--makes it TAB delimited, not comma...

footnote numbers not superscripted and are too big
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Power PC For some reason the footnote numbers in my text are full-sized and not superscripted. The notes themselves are OK. That's due to the formatting of your Footnote Reference style. Use Format>Style to change it. Cheers On 19/03/10 10:48 AM, in article 59bb581a.-1@webcrossing.JaKIaxP2ac0, "redhawk@officeformac.com" <redhawk@officeformac.com> wrote: > Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Power > PC For some reason the footnote number...

Import of PST Files into Exchange 2003
Hi All, I am setting up an Exchange 2003 environment and was wondering if there was a limit to the PST file size that can be imported into a users mailbox. We have a coupe of users who have PST files of about 1 1/2 GB and I needed to know if I would have a problem importing files of this size. Thanks for any insights. Kev wrote: > Hi All, > > I am setting up an Exchange 2003 environment and was wondering if > there was a limit to the PST file size that can be imported into a > users mailbox. > > We have a coupe of users who have PST files of about 1 1/2 GB and I > n...

Automatic import
Hi, Do you know a command line to import data from files ? I just want to do an automatic import from a dbf file every day without any intervention from a user. Is it possible ? Thx I don't think there's a command line for it. You'd either have to write code that uses the API to do what you want or use a button-pressing script to do what you want. "news.microsoft.com" <personne@microsoft.com> wrote in message news:Ou5VnOATGHA.196@TK2MSFTNGP10.phx.gbl... > Hi, > > Do you know a command line to import data from files ? > I just want to do an aut...

Exporting/importing accounts
Hi Is there a way to export mail accounts from one OL2007 and import into another OL2007? I just don't want to spend time recreating same accounts if I can avoid that. Thanks Regards No, Outlook does not have that ability since the IMO modes of Outlook = 98/2000. --=20 Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. After furious head scratching, John asked: | Hi |=20 | Is there a way to export mail accounts from one OL2007 and import into | another OL2007? I just don't want to spend time recreating same | accounts if I can avoid t...

New Named Range Created Each Time Data Imported into Excel via Macro
I have noticed that each time I import data into an Excel spreadsheet via a macro, a new named range (for the same range) is created. This does not pose a problem, but after a while, I'll have a huge number of named ranges that will never be used. Why does Excel name the range and how can I stop this? I noticed in the recorded macro, there was a line .Name = "drd_5". I commented this out to see what would happen, but it just renamed the range "ExternalData_5". The next one was "ExternalData_6", etc. Thanks, Carroll Rinehart You could refresh with differen...

How can I import multiple tasks from excel?
I hope you can help me, because it is annoying to import each task, one by one. Thanks -- Oscar File, Import doesn't work? See http://www.slipstick.com/Tutorials/import/import.htm if you need help importing from excel. -- Diane Poremsky [MVP - Outlook] Outlook Tips: http://www.outlook-tips.net/ Outlook & Exchange Solutions Center: http://www.slipstick.com Outlook Tips by email: mailto:dailytips-subscribe-request@lists.outlooktips.net EMO - a weekly newsletter about Outlook and Exchange: mailto:EMO-NEWSLETTER-SUBSCRIBE-REQUEST@PEACH.EASE.LSOFT.COM You can access this newsgroup ...

Parsing CSV
Hi, im using A file with records in my mfc application, i need to read*/write records to the file anybody ahs a clue where should i start? Take a look at CStdiofile and CString::Format() (for output) and CString::Tokenize() (for parsing input). You can use ReadString() and WriteString() to access the file line by line. Tom "fima" <fkpkot@gmail.com> wrote in message news:eXA9vp95FHA.1184@TK2MSFTNGP12.phx.gbl... > Hi, > im using A file with records in my mfc application, i need to read*/write > records to the file anybody ahs a clue where should i start? >...

Format Axis
I have a line chart which is pulling data from a different sheet in my workbook. The X axis is the date, formatted: Jan-XX, Feb-XX, etc. The Y axis is dollar amounts formatted as 200000, 400000, etc. - increasing by 200K at each interval. - this is correct. The X axis is defaulting to date the same date all the way across, "Jan-00, Jan-00, Jan-00". So, I'm seeing Jan-00 12 times, where I would like to see each month of the year. When I change the X axis to so that it reads each month, as I'd like... The scale on my Y axis changes to: min = 0 (this is correct) ma...

Importing Access DB into Excel changes my calculated fields in Exc
I currently have an Excel worksheet that imports data from MSAccess. I have calulated fields built into this Excel sheet that calculates info from the results that are returned. When I refreshed this new year, less data is returned and the calculated fields where there is no data returned are showing #REF!. How do I keep the calculated fields static so they don't try to change when new data is brought in and some fields are now blank? See example below. The calculated fields are Variance and Pct Change Columns Year Month Dollars1 Dollars2 Dollars3 Total Variance...