Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

cf_csv_to_file csv_query

Status
Not open for further replies.
Katiris

Katiris

Programmer
Feb 23, 2005
25
US
I have a query that I converted a date field to a varchar so I could eliminate the time stamp but when I use the cf_csv_to_file csv_query custom tag the date gets changed from

6/6/05 to 6/06/2005 in the csv file. Can anyone help me with this?
 
Sort by date Sort by votes
is that the actual value in the CSV file (view in note pad) or the view in excel? Excel will format dates depending on how you have them formated.

We've heard that a million monkeys at a million keyboards could produce the complete works of Shakespeare; now, thanks to the Internet, we know that is not true.
 
Upvote 0 Downvote
When I open it with notepad it is 06/06/05 - so now I'm really confused. I'm new to this so I'm not sure whats happening.
 
Upvote 0 Downvote
note pad will show you exactly what CF is producing. like when you view source of a cfm page. If you're using excel to view the data, excel will show the date in the default date format.



We've heard that a million monkeys at a million keyboards could produce the complete works of Shakespeare; now, thanks to the Internet, we know that is not true.
 
Upvote 0 Downvote
Okay I understand that whats confusing now is I can replicate how the old system generates it but I take it to MAS200 and it bombs. Thats whats throwing me. I will try a couple more ideas and see which works out.
 
Upvote 0 Downvote
The only format that "our" MAS200 will except those dates are 6/6/05 I need to be able to suppress the leading zero's if you can help with that I would be in business. Thank you for your help today.
 
Upvote 0 Downvote
We may not all be familiar with the custom tag you are using. Can you post the relevant code that generates the CSV file, or updates it?
 
Upvote 0 Downvote
<cf_csv_to_file csv_query = "SELECT InvoiceNumber, TypeChk, ChartofAccounts,
Bank_name, Address1, Address2, Address3,
City, State, Zip, CheckDate,
Borrowername, Sellername,
SearchedPropertyAddress,
City1, State1, Zip1,
ChkDate,
Amount, Description, Payee
FROM temp_checkdetails_csv_final"
csv_query_datasource = "Accutrac"
csv_table_headers = "Invoice Number|Type|Chart of Accounts|Bank Name|Address1|Address2|Address3|City|State|Zip|CheckDate|Borrowername|Sellername|SearchedPropertyAddress|City1|State1|Zip1|Date|Amount|Description|Payee"
csv_data_fields = "InvoiceNumber|TypeChk|ChartofAccounts|Bank_Name|Address1|Address2|Address3|City|State|Zip|CheckDate|Borrowername|Sellername|SearchedPropertyAddress|City1|State1|Zip1|ChkDate|Amount|Description|Payee"
csv_filepath="#directory#\#order#">

There is also a .cfm file that I have that is required in my directory to run it, but its encrypted. I got it from a site my supervisor used for custom tags.

 
Upvote 0 Downvote
Sounds like maybe the relevant code is that CFM tag file you have. That's the code that is actually formatting your data. If it's encrypted, you may be SOL. What is the source database? If it is SQLServer, you may try something like this for your csv_query value:

SELECT InvoiceNumber, TypeChk, ChartofAccounts,
Bank_name, Address1, Address2, Address3,
City, State, Zip, CheckDate,
Borrowername, Sellername,
SearchedPropertyAddress,
City1, State1, Zip1,
month(chkdate) + '/' + day(chkdate) + '/' + right(year(chkdate),2) AS ckd,
Amount, Description, Payee

If it's another RDBMS, you may be able to find the correct syntax to do the same basic thing. If you are pulling data from a CSV originaly, I'm not sure if you'll be able to do this.
 
Upvote 0 Downvote
Um...since CF is formatting the data correctly, the CSV file formats the data correctly, and Excel shows the data incorrectly (with leading digits)...wouldn't you just format the column in Excel to display the value the way that MAS wants it? Or maybe don't use Excel at all, just have the CSV file got straight to MAS.



Hope This Helps!

ECAR
ECAR Technologies, LLC

"My work is a game, a very serious game." - M.C. Escher
 
Upvote 0 Downvote
Yes I can have excel format the file. The object of the whole processes is so that no one has to do anything expect import the file into MAS200. At this point I can send the data over from the database like 6/06/05. I need to get the format to be 6/6/05 to be correct. You can't for some reason do any formatting within the csv_query so it has to be totally correct before it's written to the csv file. And I have no access to the MAS200 -
 
Upvote 0 Downvote
why can't you do this without a custom tag? i make CSV's all the time. It's a whole lot easier than you think.

We've heard that a million monkeys at a million keyboards could produce the complete works of Shakespeare; now, thanks to the Internet, we know that is not true.
 
Upvote 0 Downvote
As I indicated to you I'm new to this area. We are still only using CF4.5. That's why I used the tag. If there is another way please feel free to share. Up until this time I never had to do this type of thing. I'm open to learning. Thanks...K
 
Upvote 0 Downvote
A keyword search in this area brings up a few threads. one of them is thread232-1057039 I know there are some others but can't find them, this should help you get going though.

My key word search was "csv cffile"

We've heard that a million monkeys at a million keyboards could produce the complete works of Shakespeare; now, thanks to the Internet, we know that is not true.
 
Upvote 0 Downvote
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top