Gossamer Forum
Home : General : Databases and SQL :

Translating Dates from flat file?

Quote Reply
Translating Dates from flat file?
wow, this is messed up, I can't post in the regular mode. Oh well, My questioin is: I have flatfiles from DBMan that I need to import into SQL tables for (DBManSQL) However, my date fields in DBMan are formated as: MMM-DD-YYYY (ex: Jun-19-2003) and I would to use a date or datetime field in the SQL version rather than a text field. Is there a way to translate all these dates before I upload the files and convert them into the SQL versoin? Also, I'm already converting the pipes (|) into tabs with Editpad Pro text editor so if there's a way to do this in excel? or something that would be ok with me.
Lynette
Hollister, Ca
Quote Reply
Re: [ltillner] Translating Dates from flat file? In reply to
1- For your (|) to tab problem. I think excel allows you to save as a tab dilimented file (.txt) or a comma dilimented file (.csv). Might wanna open up excel import text and save as and where it says save as type pick the one you, in ur case its tab dililmented file (.txt) which isnt that far down the drop down list.

2- As for your date problem you can use excel aswell. Just import the data by going to the DATA and then picking IMPORT ExTERNAL DATA or something similar.
Then you can select the entire column by clicking on the very top of that column and then right click on it select FORMAT CELLS. Then you will see a window open with 6 tabs. The first tab is what you are looking for. On your left side of that window you will see a list of which you can select and change attributes, select the DATE one and then on your right side you will TYPE, go down the list till you see the one you want. Then click ok and the entire row should have changed to that format. Now with the date changed you can do step 1 and save as a tab dilimented file (.txt).

Hope this helps.