Note: I'm using Excel 2002. I'm working with a .csv file that has a column for birthdates. The problem is that the column is not in the format that I need it to be. Currently, it is M/D/YYYY, or MM/DD/YYYY, or M/DD/YYYY, or MM/D/YYYY. Basically, each one gets formatted with as few characters as needed, with no 0's as place holders. I need them all to be in MM/DD/YYYY format. Easy, right? Well yeah, getting them in that format is very easy, so that's not the problem. What I need help with is that when I try and save as a .csv file, I'm told that .csv is not compatible with some features contained in my file and that proceeding with the save may cause me to lose those features. I click "Yes", re-open the file, and to my disappointment the date format has reverted back to its original state. I can save the file as a .xls just fine, but I HAVE to have it in .csv in order to use it with our database software. I tried saving to tab delimited text, then saving the text file as .csv, but I get the same error as before. So as I sit here, it seems there is no way to save this as .csv while still getting these birthdates in the correct format (which is required for import!). There are 59,491 records too, so doing it by hand is obviously out of the question. Anybody got an idea? Hopefully I'm overlooking a simple solution.
That is strange. I know excel inside and out, and can't help you with this one. CSV is a pain in the ass.
No solution for you. But, why do you have to have it in a particular format for the database? Can you convert them all to numbers (days since 1/1/1900) and then have the database change that back into a date? Can you parse the date into 3 separate columns for month day and year and have the database put them back together?
Can you change the dates field to a custom (rather than a date) and then save it as a .csv? It'll ask you the same question as before, but when I opened up the .csv file in notepad it had the correct format.
It's not a flexible database application like Access. It's non-profit fundraising software that has a very particular import module which requires .csv, period. Everything from the .csv has to be paired with a value already existing within the software too, so there is no way to split the day, month, and year and then bring them back together. F___ this thing.
Strange. I opened it using notepad and the formatting was there, but when I open the .csv in Excel, it's not. I guess it's a matter of Excel not being able to show the changed format, rather than actually removing it when I save as a .csv. If that's the case, then I think I'm ok. Unfortunately, I won't know until about 5pm, because I have to wait until everyone else is gone home and out of the system for the day before I can run the import. Thanks for the tip. Hopefully this works.
When do you need this done by? I can write a small C# program that does excel to CSV. But I'm leaving work soon today, and I don't have Visual Studio access at home. If you can wait till tomorrow afternoon, I could help you out.
You're very welcome. I'm sorry that I didn't check to see if it'd work when you brought it back into Excel. I figured if it kept the format in the .csv it'd be good enough for the end application. Hopefully, that's just a minor annoyance and you've now got a suitable workaround for the date format problem.
Here's what happened when I tried opening the .txt in Excel... The set up/import screen has MM/DD/YYYY formatting but once I click Finish, I get this... You're right though. The .csv file does appear to have retained the MM/DD/YYYY formatting so it should be ok for the intended purpose. I just wish I knew what was up with Excel.
If you have the text date field in A1, use this in an adjacent cell: =DATE(VALUE(TRIM(MID(A1,FIND("/",A1,FIND("/",A1)+1)+1,50))),VALUE(MID(A1,1,FIND("/",A1)-1)),VALUE(MID(A1,FIND("/",A1)+1,FIND("/",A1,FIND("/",A1)+1)-FIND("/",A1)-1 ))) That'll format it for you. Copy it down, and you're finished.
There's gotta be a way to do something like this in a VB macro, and apply it to the date to get 2 characters in the month or year: Code: split fields, use "/", name them F1, F2, F3, etc. string MONTH = F1 string DATE = F2 ... etc ... if (MONTH <= 9) then MONTH = "0"+MONTH; ... same for DATE ... string NEWBDATE save MONTH+"/"+DATE+"/"+YEAR+etc. as NEWBDATE etc. I don't know too much VB, but you can use the script on a test TEXT file and then open it in Notepad to see your changes. maybe? Why do you have to wait until 5 p.m....? Is it only for import purposes? EDIT: Just saw the previous post before mine. Scribo, that ***** is WICKED cool right there, yo. Nice scripting.
Thanks. You can really make Excel do some powerful things if you know how to use it. You could also use a VB script, but this way is a bit easier to implement. For the record, I use a VB script in Excel to parse and set up the Magic Number table in my Magic Number thread. I use them all the time at work, and you can do some <B>very</B> powerful stuff like creating custom Word documents and saving them to custom places on the network, interact with databases, and even manipulate Outlook messages. That last one kind of sucks, because you used to be able to do a lot of cool interplay with Excel and Outlook, but Micro$oft stripped that out after the Melissa virus (which I now know isn't that difficult to write). I keep one computer around running Office 2000 so I can use that functionality.
<B>ClutchCityReturns</B>, I just reread your post, and you'll need to add this formula to the one pasted in above: =TEXT(B1,"MM/DD/YYYY") This assumes you put the first formula in B1. That will create a text field in MM/DD/YYYY format. You can range value those to a fresh excel page and save that as CSV.
I was able to import the .csv file with no problems yesterday. Thanks to everyone for their input. However, I have a new question. I know how to use the SUM function, but what I need to do is sum about 7,500 rows individually. There has to be a way to do them all simultaneously, but I can't figure it out. Here's a picture for reference... For example, I need L2 through Q2 to total into R2...I need L3 through Q3 to total into R3...I need L4 through Q4 to total into R4...and so on. Anybody?
First, you'd write the SUM function for R2. After that, when you highlight on the R2 field, you should see a little black square on the bottom right corner of that field. Drag that little square all the way down to where it needs to end. That should fill those fields with the SUM function with the appropriate rows.
You hilight the cell that has your formula, then move your cursor down to the lower right corner (there the tiny little black box is) then you click+hold+drag down to wherever you need to go. It should auto fill the entire thing for you. EDIT: D'oh! Got beat to it.
